Postgres: `cache lookup failed for constraint 34055`

Question

I'm have an OID that is generating a tuple that is evidently not valid.

This is the error I get when trying to delete a table in psql after some \set VERBOSITY verbose:

delete from my_table where my_column = 'some_value';
ERROR:  XX000: cache lookup failed for constraint 34055
LOCATION:  ri_LoadConstraintInfo, ri_triggers.c:2832

This is what I found elsewhere.

2827             :     /*
2828             :      * Fetch the pg_constraint row so we can fill in the entry.
2829             :      */
2830         548 :     tup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constraintOid));
2831         548 :     if (!HeapTupleIsValid(tup)) /* should not happen */
2832           0 :         elog(ERROR, "cache lookup failed for constraint %u", constraintOid);
2833         548 :     conForm = (Form_pg_constraint) GETSTRUCT(tup);
2834             : 
2835         548 :     if (conForm->contype != CONSTRAINT_FOREIGN) /* should not happen */
2836           0 :         elog(ERROR, "constraint %u is not a foreign key constraint",

I read this means the OID is being referenced in other places. Where are these other places and does anyone know how I to clean something like this up?

I really like the /* should not happen */ comment on line 2831.

Answer 1

I'd say that this means that you have catalog corruption.

Foreign key constraints are internally implemented as triggers. When that trigger fires, it tries to find the constraint that belongs to it. This seems to fail in your case, and that causes the error.

You can see for yourself:

SELECT tgtype, tgisinternal, tgconstraint
   FROM pg_trigger
   WHERE tgrelid = 'my_table'::regclass;

┌────────┬──────────────┬──────────────┐
│ tgtype │ tgisinternal │ tgconstraint │
├────────┼──────────────┼──────────────┤
│      5 │ t            │        34055 │
│     17 │ t            │        34055 │
└────────┴──────────────┴──────────────┘
(2 rows)

Now try to look up that constraint:

SELECT conname
   FROM pg_constraint
   WHERE oid = 34055;

┌─────────┐
│ conname │
├─────────┤
└─────────┘
(0 rows)

To recover from such a corruption, you should restore your latest good backup.

You can try to salvage your data by using pg_dumpall to dump the running PostgreSQL cluster, create a new cluster and restore the dump there. If you are lucky, you now have a good copy of your cluster and you can use that. If the dump or the restore fail because of data inconsistencies, you have to use more advanced methods.

As always in case of data corruption, it is best to first stop the cluster with

pg_ctl stop -m immediate

and make a physical backup of the data directory. That way you have a copy if your salvage operation further damages the data.