Invalidation pg catalog cache in trigger functions

There is a bug connected with invalidation pg catalog cache in trigger
functions
Another example of this bug I have already reported [1]/messages/by-id/20171030125345.1448.24038@wrigleys.postgresql.org -- Konstantin Evteev

The following bug has been logged on the website:

Bug reference: 14879
Logged by: Konstantin Evteev
Email address: konst583(at)gmail(dot)com
PostgreSQL version: 9.4.12
Operating system: Debian GNU/Linux 8 (jessie)
Description:

-- create database tmp;
/*
CREATE ROLE test_role LOGIN
SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
*/

-- 1) 1st session
psql -U test_role -d tmp

tmp=# create table public.test_tbl(test_id int);
CREATE TABLE
tmp=# DROP SCHEMA if exists _test_schema_1_ cascade;
NOTICE: schema "_test_schema_1_" does not exist, skipping
DROP SCHEMA

tmp=# CREATE SCHEMA _test_schema_1_
AUTHORIZATION postgres;
--CREATE SCHEMA

tmp=# alter role test_role set search_path = '_test_schema_1_';
--ALTER ROLE

CREATE OR REPLACE FUNCTION _test_schema_1_.test_func()
RETURNS void AS
$BODY$
BEGIN
raise notice 'call test func';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
-- CREATE FUNCTION

CREATE OR REPLACE FUNCTION public.trig()
returns trigger as
$BODY$
BEGIN
perform test_func();
return new;
END;
$BODY$
language plpgsql;
--CREATE FUNCTION

create trigger t_trig before insert or update on public.test_tbl for each
row execute procedure public.trig();
--CREATE TRIGGER

2) 2-nd session
psql -U test_role -d tmp

insert into public.test_tbl (test_id) values (1);
/*
NOTICE: call test func
INSERT 0 1
*/

3) 1-st session recreate schema 1

DROP SCHEMA if exists _test_schema_1_ cascade;
/*
NOTICE: drop cascades to function test_func()
DROP SCHEMA
*/

CREATE SCHEMA _test_schema_1_
AUTHORIZATION postgres;
-- CREATE SCHEMA

CREATE OR REPLACE FUNCTION _test_schema_1_.test_func()
RETURNS void AS
$BODY$
BEGIN
raise notice 'call test func';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
-- CREATE FUNCTION

4) 2-nd session

insert into public.test_tbl (test_id) values (2);
/*
ERROR: function test_func() does not exist
LINE 1: SELECT test_func()
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT test_func()
CONTEXT: PL/pgSQL function public.trig() line 3 at PERFORM
*/

5) 3-rd session - new session
psql -U test_role -d tmp
insert into public.test_tbl (test_id) values (3);
/*
NOTICE: call test func
INSERT 0 1
*/

6)Workaround in any session recreate trg function - for example in 3rd
session
create or replace function public.trig()
returns trigger as
$BODY$
BEGIN
perform test_func();
return new;
END;
$BODY$
language plpgsql;
--CREATE FUNCTION

7) 2nd session
insert into public.test_tbl (test_id) values (2);
/*
NOTICE: call test func
INSERT 0 1
*/

[1]: /messages/by-id/20171030125345.1448.24038@wrigleys.postgresql.org -- Konstantin Evteev
/messages/by-id/20171030125345.1448.24038@wrigleys.postgresql.org
--
Konstantin Evteev