There is a bug connected with invalidation pg catalog cache in trigger functions
Another example of this bug I have already reported [1]
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]
https://www.postgresql.org/message-id/20171030125345.1448.24038@wrigleys.postgresql.org--
Konstantin Evteev
