Обсуждение: 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