BUG #15203: trigger does not recognize schema changes when passing ondata - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #15203: trigger does not recognize schema changes when passing ondata
Date
Msg-id 152650479038.1312.14306642372792295171@wrigleys.postgresql.org
Whole thread Raw
Responses Re: BUG #15203: trigger does not recognize schema changes whenpassing on data
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      15203
Logged by:          ಠ_ಠ
Email address:      easteregg@verfriemelt.org
PostgreSQL version: 10.4
Operating system:   Debian Sid x64
Description:

i created a table with two integer columns and created a triggerfunction to
pass inserted data to a third function to use this data as an input
parameter based on the table.
if i alter the table after function creation, the function becomes not aware
of newly created columns.

strangly enough, after i disconnect and reconnect, the function works as
expected.

i think, the function with the parameter typ which is defined through the
table, should be notified, if the typ - or the table - changes.
i have a gist which contains an example:
https://gist.github.com/verfriemelt-dot-org/76c7de20bde461aef99a7e38a8ae571a

or below:

DROP TABLE IF EXISTS  test CASCADE;  
DROP FUNCTION IF EXISTS test2;

CREATE TABLE test (
    a   INTEGER NOT NULL,
    b   INTEGER NOT NULL
);


CREATE OR REPLACE FUNCTION test2() RETURNS TRIGGER AS $$
BEGIN
    PERFORM test3(new);
END $$ LANGUAGE plpgsql;

CREATE TRIGGER test AFTER INSERT OR UPDATE 
ON test FOR EACH ROW
EXECUTE PROCEDURE test2();


CREATE OR REPLACE FUNCTION test3(IN src test) RETURNS VOID AS $$
BEGIN
    RAISE EXCEPTION 'expected c to be 1: %',src.c;
END $$ LANGUAGE plpgsql;

ALTER TABLE test ADD COLUMN c INTEGER NOT NULL;

-- insert in same session yields an error, that there is no field c

-- testdb=# INSERT INTO test (a,b,c) VALUES (1,1,1);
-- ERROR:  record "src" has no field "c"
INSERT INTO test (a,b,c) VALUES (1,1,1);

-- after reconnect to db

INSERT INTO test (a,b,c) VALUES (2,2,2);

-- testdb=# INSERT INTO test (a,b,c) VALUES (1,1,1);
-- ERROR:  expected c to be 1: 2


pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #15202: Unexpected behavior with trigger fired on logicalreplicaion using pg_notify
Next
From: Feike Steenbergen
Date:
Subject: Re: BUG #15198: nextval() accepts tables/indexes when adding adefault to a column