January 22nd, 2012 by depesz | Tags: , , , , , , , | Comments Off on Waiting for 9.2 – NULLS from pg_*_size() functions
Did it help? If yes - maybe you can help me?

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

This patch on its own is not very visible, but it nicely solves the problem that I showed, and tried to fix in previous post.

Unfortunately – my fix was not really a fix – it just made the window of opportunity for the problem smaller.

But, thanks to Phils patch, the window is now closed, as we can get all sizes without exceptions.

Example of usage. Previously we had:

$ select pg_relation_size(423536);
ERROR:  could not open relation with OID 423536

but now, it gets me:

$ select pg_relation_size(423536);
 pg_relation_size
──────────────────
           [null]
(1 row)

Of course querying the data by name will still fail:

$ select pg_relation_size('non_existing_table');
ERROR:  relation "non_existing_table" does not exist
LINE 1: select pg_relation_size('non_existing_table');
                                ^

But query like:

select oid::regclass, pg_relation_size(oid) from pg_class;

will work fine, without errors now.

Sorry, comments for this post are disabled.