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

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

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

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