Re: Error XX000 After pg11 upgrade

Simon Windsor <simon.windsor@xxxxxxxxxxxxxxx> · Thu, 15 Aug 2019 17:35:28 +0100

Hi

The Full system used to in an Oracle DB and was ported to Postgres 9.5 
about 2+ years ago, and partitioned using inheritance tables.

Since then pg_upgrade has been used to upgrade to pg10 (with apt upgrade 
to take to 10.5 occasionally).

Last week, pg_upgrade was againn used to upgrade to pg11.4.

Since then, large bulk inserts of configuration changes are failing with 
this Error, but adhoc and small changes are working ok.

The actual error is reported by a Java process

Caused by: org.postgresql.util.PSQLException: ERROR: cache lookup failed 
for type 22079
  Where: SQL statement "insert into configObjectsFull_2019 values (new.*)"
PL/pgSQL function configobjectsfull_insert_trigger() line 28 at SQL 
statement
    at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2310)
    at

The DB Tableand Trigger are attached.

As you can see, the id and timestamp are set on insert, and are used to 
determine the partition used.

This issue started after the upgrade to pg11, pg10 and pg9 had no problems.

Any ideas would be appreciated.

Simon

On 15/08/2019 16:31, Tom Lane wrote:
Simon Windsor <simon.windsor@xxxxxxxxxxxxxxx> writes:
I have just upgraded a Db from pg 10.5 to pg11.4 and almost immediately we
are seeing errors like
ERROR,XX000,"cache lookup failed for type 22079"
When inserting into a a partitioned table. About 30% of inserts are
reporting this error.
Hmm ... can you show the full schema (eg, psql \d+ output) for the
partitioned table and its children?  Is there any pattern to the
failing inserts, eg do they all resolve as inserts to the same
partition(s)?

... And an insert trigger uses the current date to dertmine where the
object is stored.
In other words, this isn't actual partitioning as introduced in v10,
but a hand-rolled equivalent?

How do I determine what type 22079 refers to?
Presumably, the problem is that that type OID *doesn't* refer to anything
any more.  You should be asking "where is this dangling reference coming
from?".  It's possibly hiding in the partitioning expression(s) of
this partitioned table, but there's no way to tell with this amount
of info.

How did you do the upgrade exactly?

			regards, tom lane

--
Simon Windsor

Eml: simon.windsor@xxxxxxxxxxxxxxx
Tel: 01454 617689
Mob: 0755 197 9733

“There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.”

Attachment:
objects.sql

Description: application/sql