Thread: BUG #15553: "ERROR: cache lookup failed for type 2" with a functionthe first time it run.

The following bug has been logged on the website:

Bug reference:      15553
Logged by:          Jean-Marc Lessard
Email address:      jm.lessard@contactft.com
PostgreSQL version: 11.1
Operating system:   Windows 2012 R2
Description:

I compiled posgreSQL 11 for windows with MSYS2 64bit (includes mingw64)
following the procedure outline
in
https://www.cybertec-postgresql.com/en/building-postgresql-with-msys2-and-mingw-under-windows/

select version();
                                                               version

-------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 on x86_64-w64-mingw32, compiled by
x86_64-w64-mingw32-gcc.exe (Rev1, Built by MSYS2 project) 8.2.1 20181207,
64-bit

I got an "ERROR:  cache lookup failed for type 2" when a function is run for
the first time in a session.
The function run successfully the second time.
Please find the test case as follow:

Setup:
CREATE SCHEMA IF NOT EXISTS test;
set search_path=test,public;
SELECT current_schema() \gset

CREATE TABLE IF NOT EXISTS dis_con (
   nspname                 NAME                       NOT NULL,
   relname                 NAME                       NOT NULL,
   conname                 NAME                       NOT NULL,
   contype                 NAME                       NOT NULL,   --c =
check constraint, f = foreign key constraint, p = primary key constraint, u
= unique constraint
   condef                  TEXT                       NOT NULL,
   state                   VARCHAR(8)                 NOT NULL,   --staged
(constraint is queued to be dropped) or dropped
   drop_by                 NAME                       NOT NULL,
   drop_tim                TIMESTAMPTZ(0)             DEFAULT
CURRENT_TIMESTAMP NOT NULL,
   CONSTRAINT discon2_pk PRIMARY KEY (nspname, relname, conname)
);

CREATE OR REPLACE FUNCTION disable_constraints2(contype_p CHAR)
RETURNS TABLE(owner_schema TEXT, constraint_name TEXT,
current_constraint_def TEXT, dropped_constraint_def TEXT)
AS $BODY$
DECLARE
   rowcnt         INTEGER := 0;
   con            RECORD;
BEGIN
   --Stage or queue for dropping (update the constraint state) the
constraints that exist which are in the disable_contraints table from a
previous drop with the same definition (defininition checked above).
   UPDATE dis_con d SET state = 'staged', drop_by = session_user, drop_tim=
CURRENT_TIMESTAMP
   WHERE EXISTS (SELECT 1 FROM pg_constraint
                   JOIN pg_class ON conrelid=pg_class.oid
                   JOIN pg_namespace ON
pg_namespace.oid=pg_class.relnamespace
                  WHERE pg_namespace.nspname = current_schema()
                    AND pg_constraint.contype = contype_p
                    AND pg_namespace.nspname=d.nspname AND
pg_class.relname=d.relname AND pg_constraint.conname=d.conname AND
pg_constraint.contype=d.contype AND
pg_get_constraintdef(pg_constraint.oid)=d.condef);
   IF FOUND THEN
      GET DIAGNOSTICS rowcnt = ROW_COUNT;
      RAISE INFO '% constraint(s) are re-staged for dropping.',
rowcnt::TEXT;
   END IF;
   GET DIAGNOSTICS rowcnt = ROW_COUNT;

   --Staging constraints that will be dropped and saving the constraint
definition.
   INSERT INTO dis_con (SELECT nspname, relname, conname, contype,
pg_get_constraintdef(pg_constraint.oid), 'staged', session_user,
CURRENT_TIMESTAMP
                                                     FROM pg_constraint
                                                     JOIN pg_class ON
conrelid=pg_class.oid
                                                     JOIN pg_namespace ON
pg_namespace.oid=pg_class.relnamespace
                                                    WHERE
pg_namespace.nspname = current_schema()
                                                      AND
pg_constraint.contype = contype_p
                                                      AND NOT EXISTS (SELECT
1 FROM dis_con d WHERE pg_namespace.nspname=d.nspname AND
pg_class.relname=d.relname AND pg_constraint.conname=d.conname AND
pg_constraint.contype=d.contype));
   IF FOUND THEN
      GET DIAGNOSTICS rowcnt = ROW_COUNT;
      RAISE INFO '% constraint(s) are staged for dropping.', rowcnt::TEXT;
   END IF;

   --Dropping the staged constraints
   rowcnt := 0;
   FOR con IN SELECT nspname, relname, conname FROM dis_con WHERE
nspname=current_schema() AND contype=contype_p AND state='staged' LOOP
       EXECUTE 'ALTER TABLE
'||quote_ident(con.nspname)||'.'||quote_ident(con.relname)||' DROP
CONSTRAINT '||quote_ident(con.conname);
       UPDATE dis_con SET state='dropped', drop_by = session_user, drop_tim
= CURRENT_TIMESTAMP WHERE nspname=con.nspname AND relname=con.relname AND
conname=con.conname AND contype=contype_p;
       RAISE INFO '% constraint dropped.', con.conname;
       rowcnt := rowcnt + 1;
   END LOOP;

   RETURN QUERY SELECT current_schema()::TEXT, rowcnt::TEXT||'
'||UPPER(contype_p)||'K constraint(s) were
disabled.',NULL::TEXT,NULL::TEXT;

END
$BODY$ LANGUAGE plpgsql
SET search_path = :current_schema, pg_catalog;

Test Case
1. \q and start a new psql session

--Do not forget to set the search path because the function will drop the
constraints in your current_schema. You can recreate them with the select
from dis_con table.
2. set search_path=test,public;
SELECT disable_constraints2('f');
ERROR:  cache lookup failed for type 2
CONTEXT:  SQL statement "UPDATE dis_con d SET state = 'staged', drop_by =
session_user, drop_tim= CURRENT_TIMESTAMP
...
PL/pgSQL function disable_constraints2(character) line 7 at SQL statement

3. Run the function a second time
SELECT disable_constraints2('f');
             disable_constraints2
----------------------------------------------
 (test,"0 FK constraint(s) were disabled.",,)

--just in case
SELECT 'ALTER TABLE '||nspname||'.'||relname||' ADD CONSTRAINT '||conname||'
'||condef||';' FROM dis_con;

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> I got an "ERROR:  cache lookup failed for type 2" when a function is run for
> the first time in a session.

That certainly looks like a bug, but I can't replicate it from the
given instructions.

The function seems to expect that the current schema contains some
foreign key constraints, which it doesn't when I just follow your
script exactly.  However, even if I add a few more tables with
foreign-key relationships, all seems well.

Please provide a complete script that will reproduce the problem
starting from an empty database.  Also, have you got any extensions
loaded, or any unusual configuration settings?

            regards, tom lane

Jean-Marc Lessard
Administrateur de base de données / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com

Jean-Marc Lessard <Jean-Marc.Lessard@ultra-ft.com> writes:
> Hi Tom, were you able to reproduce the issue.

I didn't try; pgaudit is not my thing.

> Or would you rather prefer that I submit the issue to the pgaudit team.

Please report to them.

            regards, tom lane