Thread: ERROR: could not open relation with OID
This morning we got the following error from a daily script that produces a simple largest-table report: ERROR: could not open relation with OID 597597503 I reran the script and it completed without error. Our server is running 9.1.15 from PgDg Ubuntu repos and the query run by the script is: SELECT relname AS TABLE, lpad ( pg_size_pretty ( pg_relation_size ( oid ) ), 9 ) AS SIZE, ( 100 * pg_relation_size ( oid ) / ( SELECT sum ( pg_relation_size ( oid ) ) FROM pg_class WHERE relkind = 'r' ) ) ::numeric ( 4, 1 ) AS percent FROM pg_class WHERE relkind = 'r' ORDER BY relpages DESC LIMIT 60; My research hasn't led to a definitive conclusion on how to trace this issue and how much I should be concerned about this error. If it is of importance, it appears that a temporary table and temporary index were being created within the same second that the query was run. Any advice? Cheers, Steve
> This morning we got the following error from a daily script that > produces a simple largest-table report: > ERROR: could not open relation with OID 597597503 > From a bit of Googling, it seems that Postgres was unable to open the physical file that contains the relation. Is it possible that there was an error accessing the disk? How is the filesystem that contains your PGDATA directory mounted? Kind regards, Andomar
On 04/22/2015 11:40 AM, Steve Crawford wrote: > This morning we got the following error from a daily script that > produces a simple largest-table report: > ERROR: could not open relation with OID 597597503 > > I reran the script and it completed without error. > > Our server is running 9.1.15 from PgDg Ubuntu repos and the query run by > the script is: > > SELECT > relname AS TABLE, > lpad ( pg_size_pretty ( pg_relation_size ( oid ) ), 9 ) AS SIZE, > ( 100 * pg_relation_size ( oid ) / > ( SELECT > sum ( pg_relation_size ( oid ) ) > FROM > pg_class > WHERE > relkind = 'r' ) ) ::numeric ( 4, 1 ) AS percent > FROM > pg_class > WHERE > relkind = 'r' > ORDER BY > relpages DESC > LIMIT 60; > > My research hasn't led to a definitive conclusion on how to trace this > issue and how much I should be concerned about this error. > > If it is of importance, it appears that a temporary table and temporary > index were being created within the same second that the query was run. > > Any advice? WHERE relkind = 'r' AND relpersistence != 't' > > Cheers, > Steve > > -- Adrian Klaver adrian.klaver@aklaver.com
On 04/22/2015 01:25 PM, Adrian Klaver wrote: > >> If it is of importance, it appears that a temporary table and temporary >> index were being created within the same second that the query was run. >> >> Any advice? > > > WHERE > relkind = 'r' > AND > relpersistence != 't' So to confirm. Fix the query and don't worry? Cheers, Steve
On 04/22/2015 02:37 PM, Steve Crawford wrote: > On 04/22/2015 01:25 PM, Adrian Klaver wrote: >> >>> If it is of importance, it appears that a temporary table and temporary >>> index were being created within the same second that the query was run. >>> >>> Any advice? >> >> >> WHERE >> relkind = 'r' >> AND >> relpersistence != 't' > So to confirm. Fix the query and don't worry? Well it is software, so always worry:) To reduce the worry level I would confirm that there is really no current relation with an OID of 597597503. Still given that the query ran successfully on retry and that it failed in the presence of a temporary table creation I would say the evidence is that a temporary table disappeared out from under the query. > > Cheers, > Steve > > -- Adrian Klaver adrian.klaver@aklaver.com