Обсуждение: ERROR: could not open relation with OID
We are trying to perform a 'reindex database' and it will fail at varying points with a message like:
ERROR: could not open relation with OID 587495058
or
ERROR: could not open relation with OID 587603875
ERROR: could not open relation with OID 587495058
or
ERROR: could not open relation with OID 587603875
When we queried pg_class we got no rows returned:
select oid,* from pg_class where oid in (587603875, 587495058);
oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
-----+---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
(0 rows)
oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
-----+---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
(0 rows)
select oid,* from pg_class where oid>587603875;
oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
-----------+------------------------------+--------------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
587656467 | reindex_audit_network_id_idx | 2200 | 0 | 10 | 403 | 587656467 | 0 | 2 | 65 | 0 | 0 | f | f | i | 1 | 0 | 0 | 0 | 0 | 0 | f | f | f | f |
587656343 | reindex_audit | 2200 | 587656344 | 10 | 0 | 587656343 | 0 | 1 | 65 | 0 | 0 | t | f | r | 8 | 0 | 0 | 0 | 0 | 0 | f | f | f | f |
587656468 | reindex_audit_audit_id_idx | 2200 | 0 | 10 | 403 | 587656468 | 0 | 2 | 65 | 0 | 0 | f | f | i | 1 | 0 | 0 | 0 | 0 | 0 | f | f | f | f |
(3 rows)
There were no reindex processes running in pg_stat_activity and stopping/restarting postgres did not clear up these entries.
oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
-----------+------------------------------+--------------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
587656467 | reindex_audit_network_id_idx | 2200 | 0 | 10 | 403 | 587656467 | 0 | 2 | 65 | 0 | 0 | f | f | i | 1 | 0 | 0 | 0 | 0 | 0 | f | f | f | f |
587656343 | reindex_audit | 2200 | 587656344 | 10 | 0 | 587656343 | 0 | 1 | 65 | 0 | 0 | t | f | r | 8 | 0 | 0 | 0 | 0 | 0 | f | f | f | f |
587656468 | reindex_audit_audit_id_idx | 2200 | 0 | 10 | 403 | 587656468 | 0 | 2 | 65 | 0 | 0 | f | f | i | 1 | 0 | 0 | 0 | 0 | 0 | f | f | f | f |
(3 rows)
There were no reindex processes running in pg_stat_activity and stopping/restarting postgres did not clear up these entries.
What could be causing these errors?
"Susy Ham" <sham@ncircle.com> writes:
> We are trying to perform a 'reindex database' and it will fail at
> varying points with a message like:
> ERROR: could not open relation with OID 587495058
> or
> ERROR: could not open relation with OID 587603875
Ugh :-(
> When we queried pg_class we got no rows returned:
> select oid,* from pg_class where oid in (587603875, 587495058);
Indeed not, since the error message is complaining exactly that it
couldn't find any such pg_class row. What you need to be looking into
is where the reference to the OID came from. I'd try looking into
pg_index and maybe pg_depend to see if there are rows linking to these
OIDs.
Also, in a message of this sort you really oughta expand a bit on your
reasons for needing a 'reindex database' in the first place, and the
procedure you are using for it. If you are trying to recover from
system catalog corruption then there's a number of subtle tricks to use
--- the main one being to run the backend with -P (which means "don't
trust the system indexes").
While I'm asking for specifics: exactly which PG version is this?
regards, tom lane