Thread: could not open relation with OID

I've seen the "could not open relation with OID" error a couple times
recently and Googling for that error gives responses ranging from *very
scary* to fuggetaboutit.

The error today occurred running this query which is part of a database
status reporting script:
select
     pg_size_pretty(sum(pg_relation_size(oid))::bigint) as "Database
Table Size"
from
   pg_class
where
   relkind = 'r'
;

could not open relation with OID 81285213

Pre-answering some potential questions based on threads I read:

- Version is 8.3 x86_64 (about to be replaced with 9.0).

- Database appears to be functioning fine otherwise and query reran
without error.

- Size just under 100-million total rows and 12GB (counting tables only).

- Backups completing without error.

- Restore of backup to a twin machine completes without error.

- There are lots of scripts and processes that use temporary tables
(there are currently 35 pg_temp_XX schemas with the highest being
pg_temp_55)

So...

Worry or not worry?

Cheers,
Steve

Steve Crawford <scrawford@pinpointresearch.com> writes:
> I've seen the "could not open relation with OID" error a couple times
> recently and Googling for that error gives responses ranging from *very
> scary* to fuggetaboutit.

> The error today occurred running this query which is part of a database
> status reporting script:
> select
>      pg_size_pretty(sum(pg_relation_size(oid))::bigint) as "Database
> Table Size"
> from
>    pg_class
> where
>    relkind = 'r'
> ;

> could not open relation with OID 81285213

> Pre-answering some potential questions based on threads I read:

> - There are lots of scripts and processes that use temporary tables

> Worry or not worry?

Not worry.  This is expected when you run a query like that against a
database where tables are concurrently getting dropped.  In essence,
the failure occurs when a table got dropped between the time the query
saw its entry in pg_class and when pg_relation_size gets called.  It's
difficult to avoid this without introducing painful amounts of locking,
though at times we've debated having pg_relation_size silently return
NULL if the OID can't be found.  (The main objection is that this would
obscure actual mistakes, like passing something other than relation OIDs.)

You might be able to dodge the problem by excluding temp relations from
the reporting query, though whether the sum of only non-temp rel sizes
is of value to you is hard to guess from here.

            regards, tom lane

On 07/13/2011 04:32 PM, Tom Lane wrote:
> Steve Crawford<scrawford@pinpointresearch.com>  writes:
>> I've seen the "could not open relation with OID" error a couple times
>> recently ...
> Not worry....
> You might be able to dodge the problem by excluding temp relations from
> the reporting query, though whether the sum of only non-temp rel sizes
> is of value to you is hard to guess from here.
Data safety was my concern. The error is so rare that I can ignore it.
(The two I saw are the first in the 2+ years this server has been active
and were probably the result of running the report manually when the
database was active rather than the usual nighttime cron-run).

Cheers,
Steve