Tag: postgresql

Waiting for 9.2 – split of current_query in pg_stat_activity

On 19t of January, Magnus Hagander committed patch:

Separate state from query string in pg_stat_activity
 
This separates the state (running/idle/idleintransaction etc) into
it's own field ("state"), and leaves the query field containing just
query text.
 
The query text will now mean "current query" when a query is running
and "last query" in other states. Accordingly,the field has been
renamed from current_query to query.
 
Since backwards compatibility was broken anyway to make that, the procpid
field has also been renamed to pid - along with the same field in
pg_stat_replication for consistency.
 
Scott Mead and Magnus Hagander, review work from Greg Smith

Continue reading Waiting for 9.2 – split of current_query in pg_stat_activity

Waiting for 9.2 – NULLS from pg_*_size() functions

On 19t of January, Heikki Linnakangas committed patch:

Make pg_relation_size() and friends return NULL if the object doesn't exist.
 
That avoids errors when the functions are used in queries like "SELECT
pg_relation_size(oid) FROM pg_class", and a table is dropped concurrently.
 
Phil Sorber

Continue reading Waiting for 9.2 – NULLS from pg_*_size() functions

OmniPITR 0.3.0

Just released version 0.3.0 of our tool for handling WAL based replication in PostgreSQL – OmniPITR.

Version jump is related to addition of another tool – omnipitr-synch. This tool is used to copy PostgreSQL data dir (including all tablespaces of course) to remote location(s).

While this process is usually simple (call pg_start_backup(), transfer data, call pg_stop_backup()), thanks to the tool it can be wrapped as single call, with standardized logging, and tested logic. It also makes it trivial, and cheap, to setup more than one new slave at a time, without need to read data off master more than once.

Tips N’ Tricks – getting sizes of relations without locks

If you have production DB servers, chances are you're running variant of these queries:

SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'i'
SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'r'

To get summarized size of tables and/or indexes in your database (for example for graphing purposes).

This (getting pg_relation_size for rows in pg_class) has one problem – it can lock, or it can fail.

Continue reading Tips N' Tricks – getting sizes of relations without locks

r/trees ( recursive trees, what did you think about? )

I got asked on irc to show some examples how to use recursive CTE. Apparently my previous post wasn't good enough 🙂

I think that most of the users will use recursive cte to deal with trees I decided to show how to use it, even though it's not my favorite approach to dealing with trees in SQL.

Continue reading r/trees ( recursive trees, what did you think about? )

Waiting for 9.2 – excluding data of table from dump

On 14h of December, Andrew Dunstan committed patch:

Add --exclude-table-data option to pg_dump.
 
Andrew Dunstan, reviewed by Josh Berkus, Robert Haas and Peter Geoghegan.
 
This allows dumping of a table definition but not its data, on a per table basis.
Table name patterns are supported just as for --exclude-table.

Continue reading Waiting for 9.2 – excluding data of table from dump

OmniPITR 0.2.0

Just released version 0.2.0 of OmniPITR. There is one important change – that is OmniPITR backup programs can now create checksum files for backups.

Details in docs, but the basic idea is that to all the other options to omnipitr-backup-{master,slave} we added (Phil Sorber to be exact) –digest option, which lets you specify that you want some checksums (md5, sha1, sha2).

Waiting for 9.2 – Index only scans

On 8th of October, Tom Lane committed patch:

Support index-only scans using the visibility map to avoid heap fetches.
 
When a btree index contains all columns required by the query, and the
visibility map shows that all tuples on a target heap page are
visible-to-all, we don't need to fetch that heap page.  This patch depends
on the previous patches that made the visibility map reliable.
 
There's a fair amount left to do here, notably trying to figure out a less
chintzy way of estimating the cost of an index-only scan, but the core
functionality seems ready to commit.
 
Robert Haas and Ibrar Ahmed, with some previous work by Heikki Linnakangas.

Continue reading Waiting for 9.2 – Index only scans