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

On Thu, May 17, 2018 at 7:06 AM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> 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/76c7de20bde461aef99a7e38a8ae57
> 1a
>
> 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
>
>

There is no dependency that is available from one function to another, if
you observe,
in this scenario, function test2() creation is success even when the
function test3() doesn't exist.

When the alter table is changed, the related triggers depends on the table
are
rebuilt, but there is no way to identify the internal functions that also
needs to be
reloaded.

If you update the test2() function as follows, then your query gets the
expected results.

CREATE OR REPLACE FUNCTION test2() RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION 'expected c to be 1: %',new.c;
END $$ LANGUAGE plpgsql;

IMO, it is better to use single functions as trigger functions instead of
nested functions to avoid
these kind of problems.

Regards,
Hari Babu
Fujitsu Australia