Re: Error XX000 After pg11 upgrade

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(dot)windsor(at)cornfield(dot)me(dot)uk> 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(dot)windsor(at)cornfield(dot)me(dot)uk
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.”