pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcould not open relation with OID
3+ messages / 2 participants
[nested] [flat]
* could not open relation with OID
@ 2011-07-13 22:32 Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 3+ messages in thread
From: Steve Crawford @ 2011-07-13 22:32 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: could not open relation with OID
@ 2011-07-13 23:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2011-07-13 23:32 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: could not open relation with OID
@ 2011-07-13 23:51 Steve Crawford <scrawford@pinpointresearch.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Steve Crawford @ 2011-07-13 23:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-07-13 23:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-13 22:32 could not open relation with OID Steve Crawford <scrawford@pinpointresearch.com>
2011-07-13 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-07-13 23:51 ` Steve Crawford <scrawford@pinpointresearch.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox