CODEWITHSUNDEEP
postgresql
Joseph Gohlke
Joseph Gohlke

Reputation: 21

Postgres 11 throwing cache lookup failed for type errors

Here is the test case and results:

drop table if exists test1;
drop table if exists test2;
drop trigger if exists test1_tr on test1;
drop function if exists tf_test1;
create table test1 (name varchar(8) not null);
create table test2 (name varchar(8) not null);

\echo create trigger function tf_test1
CREATE OR REPLACE FUNCTION tf_test1() RETURNS trigger AS $BODY$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO test2(name) VALUES (NEW.name);
END IF;
return new;
END
$BODY$
 LANGUAGE 'plpgsql';

\echo create trigger test1_tr
CREATE TRIGGER test1_tr
        AFTER INSERT OR UPDATE OR DELETE ON test1 FOR EACH ROW
        EXECUTE PROCEDURE tf_test1();

\echo Insert
insert into test1 (name) values ('NAME_001');
insert into test1 (name) values ('NAME_002');
insert into test1 (name) values ('NAME_003');
insert into test1 (name) values ('NAME_004');

\echo Select test1
select * from test1;

\echo Select test2
select * from test2;

---------------------------- output -------------------------------

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TRIGGER
DROP FUNCTION
CREATE TABLE
CREATE TABLE
create trigger function tf_test1
CREATE FUNCTION
create trigger test1_tr
CREATE TRIGGER

Insert
INSERT 0 1
psql:test3.sql:28: ERROR:  cache lookup failed for type 113
CONTEXT:  SQL statement "INSERT INTO test2(name) VALUES (NEW.name)"
PL/pgSQL function tf_test1() line 4 at SQL statement
INSERT 0 1
INSERT 0 1

Select test1
   name   
----------
 NAME_001
 NAME_003
 NAME_004
(3 rows)

Select test2
   name   
----------
 NAME_001
 NAME_003
 NAME_004
(3 rows)

We have several servers running various flavors of RHEL 7.x. All Postgres instances are v11. This is happening on about 1/2 of them. There doesn't seem to be any consistent RH version that is the culprit.

I have queried both pg_class and pg_type for the OID referenced as the missing type. In all cases, the result set is empty.

Any help is appreciated.

I would also appreciate an insight into what's happening with Postgres. I'm a long-time Oracle DBA, but fairly new to Postgres. It seems like an internal Postgres error and not really a code problem, but a web search doesn't turn up much.

Upvotes: 0

Views: 4273

Answers (1)

Joseph Gohlke
Joseph Gohlke

Reputation: 21

Follow-up on this to provide some closure. We had increased our buffer and effective cache size in the Postgresql.conf file and also turned Auditing on (pgaudit extension) full blast...For the machines where the PG memory conf parameters exceeded the physical memory of the machine and auditing was turned on, we would get cache lookup errors. A clue about this was the errors would hop around in the job flow, were not consistent from machine to machine and were effectively unsquashable bugs (dropping the offending trigger would just cause the cache error somewhere else in the job stream).

For now, we have increased the physical memory of the servers and turned auditing off. The cache lookup errors are gone. Further tuning is needed so we can eventually turn auditing back on.

Upvotes: 2

Related Questions