Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #15203: trigger does not recognize schema changes when passing ondata

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

From
PG Bug reporting form
Date:
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

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

From
Haribabu Kommi
Date:


On Thu, May 17, 2018 at 7:06 AM, PG Bug reporting form <noreply@postgresql.org> wrote:
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



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

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

From
easteregg@verfriemelt.org
Date:
is there any chance to reload the functions manually? i have a current setup which does a lot more in the functions itself and running these in a CI Test environment triggers this issue. in production this is not a problem, because the session will get restarted before reaching this point.

but to rewrite the trigger functions only for the CI is not really feaseble.

so my two questions: 

a) why is a reconnect sufficient to solve this problem?
b) shouldnt the function become aware of the change defintion of its parameter type?
c) if no - why so, when i reconnect?
d) can i manually reload the internal functions?

with best regards,
richard

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

From
Haribabu Kommi
Date:


On Fri, May 18, 2018 at 4:48 PM, <easteregg@verfriemelt.org> wrote:
is there any chance to reload the functions manually? i have a current setup which does a lot more in the functions itself and running these in a CI Test environment triggers this issue. in production this is not a problem, because the session will get restarted before reaching this point.

but to rewrite the trigger functions only for the CI is not really feaseble.

so my two questions: 

a) why is a reconnect sufficient to solve this problem?

Reconnect will reinitialize all the cache, so the function information is properly built.
 
b) shouldnt the function become aware of the change defintion of its parameter type?

It should. But I am not sure why it is not getting latest definition.
I am not able to reproduce this issue in the development version (11 devel).
I am not sure whether it can be backported to fix this issue? 

d) can i manually reload the internal functions?

I doubt that there is any way to manually reload the functions.

 
Regards,
Hari Babu
Fujitsu Australia

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

From
Tom Lane
Date:
Haribabu Kommi <kommi.haribabu@gmail.com> writes:
> On Fri, May 18, 2018 at 4:48 PM, <easteregg@verfriemelt.org> wrote:
>> b) shouldnt the function become aware of the change defintion of its
>> parameter type?

> It should. But I am not sure why it is not getting latest definition.
> I am not able to reproduce this issue in the development version (11 devel).

Since the OP hasn't shown us exactly what he did to get into this
situation, it's hard to be sure, but I think we fixed this for v11.
At least, v11 makes a reasonable attempt to cope with during-a-session
changes in the rowtypes of named-composite-type variables, which no
prior version did.

> I am not sure whether it can be backported to fix this issue?

Zero chance of that --- it was a large set of changes.

> d) can i manually reload the internal functions?

> I doubt that there is any way to manually reload the functions.

I think a no-op ALTER FUNCTION might be enough, but reconnecting
is probably an easier answer.

            regards, tom lane