BUG #15203: trigger does not recognize schema changes when passing on data

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: easteregg(at)verfriemelt(dot)org
Subject: BUG #15203: trigger does not recognize schema changes when passing on data
Date: 2018-05-16 21:06:30
Message-ID: 152650479038.1312.14306642372792295171@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15203
Logged by: ಠ_ಠ
Email address: easteregg(at)verfriemelt(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Feike Steenbergen 2018-05-17 06:41:53 Re: BUG #15198: nextval() accepts tables/indexes when adding a default to a column
Previous Message PG Bug reporting form 2018-05-16 18:26:56 BUG #15202: Unexpected behavior with trigger fired on logical replicaion using pg_notify