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
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 |