To: Steve Crawford <scrawford@pinpointresearch.com>
cc: "pgsql-admin@postgresql.org Admin" <pgsql-admin@postgresql.org>
Subject: Re: could not open relation with OID 
In-reply-to: <4E1E1CFE.2030500@pinpointresearch.com> 
References: <4E1E1CFE.2030500@pinpointresearch.com>
Comments: In-reply-to Steve Crawford <scrawford@pinpointresearch.com>
	message dated "Wed, 13 Jul 2011 15:32:30 -0700"
Date: Wed, 13 Jul 2011 19:32:21 -0400
Message-ID: <12791.1310599941@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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