Some time ago I wrote a piece on speeding up dump/restore process using custom solution that was parallelizing process.
Later on I wrote some tools (“fast dump and restore") to do it in more general way.
But all of them had a problem – to get consistent dump you need to stop all concurrent access to your database. Why, and how to get rid of this limitation?
On 4th of March, Kevin Grittner committed patch:
Add a materialized view relations. A materialized view has a rule just like a view and a heap and other physical properties like a table. The rule is only used to populate the table, references in queries refer to the materialized data. This is a minimal implementation, but should still be useful in many cases. Currently data is only populated "on demand" by the CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW statements. It is expected that future releases will add incremental updates with various timings, and that a more refined concept of defining what is "fresh" data will be developed. At some point it may even be possible to have queries use a materialized in place of references to underlying tables, but that requires the other above-mentioned features to be working first. Much of the documentation work by Robert Haas. Review by Noah Misch, Thom Brown, Robert Haas, Marko Tiikkaja Security review by KaiGai Kohei, with a decision on how best to implement sepgsql still pending.
Continue reading Waiting for 9.3 – Add a materialized view relations.
On 1st of February, Peter Eisentraut committed patch:
Add CREATE RECURSIVE VIEW syntax This is specified in the SQL standard. The CREATE RECURSIVE VIEW specification is transformed into a normal CREATE VIEW statement with a WITH RECURSIVE clause. reviewed by Abhijit Menon-Sen and Stephen Frost
Continue reading Waiting for 9.3 – Add CREATE RECURSIVE VIEW syntax
On 27th of February, Heikki Linnakangas committed patch:
Add support for piping COPY to/from an external program. This includes backend "COPY TO/FROM PROGRAM '...'" syntax, and corresponding psql \copy syntax. Like with reading/writing files, the backend version is superuser-only, and in the psql version, the program is run in the client. In the passing, the psql \copy STDIN/STDOUT syntax is subtly changed: if you the stdin/stdout is quoted, it's now interpreted as a filename. For example, "\copy foo from 'stdin'" now reads from a file called 'stdin', not from standard input. Before this, there was no way to specify a filename called stdin, stdout, pstdin or pstdout. This creates a new function in pgport, wait_result_to_str(), which can be used to convert the exit status of a process, as returned by wait(3), to a human-readable string. Etsuro Fujita, reviewed by Amit Kapila.
Continue reading Waiting for 9.3 – Add support for piping COPY to/from an external program.
One of the questions that gets asked quite a lot, is: how can you use variables in your queries? Other databases have them, does PostgreSQL?
Actually the answer is “no". But, it is actually very easy to work around, and what's more important – quite often, what you need are not variables in queries, but rather variables in client. What does it mean? Let's see.
On 2nd of February, Tom Lane committed patch:
Create a psql command \gset to store query results into psql variables. This eases manipulation of query results in psql scripts. Pavel Stehule, reviewed by Piyush Newe, Shigeru Hanada, and Tom Lane
So, lately I have been thinking about integrating explain.depesz.com with psql. Currently, you have to run explain, copy the output, switch to webbrowser, go to explain.depesz.coma> site, paste output, click submit. It's not that it's a lot of work, but it can be simplified.
Continue reading Adding plans to explain.depesz.com from your psql
Whenever you are considering creation of multicolumn index, there is a question what should be the order of columns in this index. I'll try to analyze various cases of this situation.
On 23rd of January, Robert Haas committed patch:
pg_isready New command-line utility to test whether a server is ready to accept connections. Phil Sorber, reviewed by Michael Paquier and Peter Eisentraut
So, you just installed your PostgreSQL, and you have no idea how to use it – there is no icon in the menu of your OS, so how can you use it? Well, with the dreadful command line.
Of course – some people will never get used to textual programs. They need a GUI. That's fine. Not understandable for me, but who am I to judge. But knowing at least a basic things about standard command line tools for PostgreSQL can save you a lot of headache in some cases. Plus – you always have them so these are treated as default programs to use.
Continue reading Command line tools? In XXI century? No way! Yes way!