Обсуждение: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 18456 Logged by: Jacques Combrink Email address: jacques@quantsolutions.co.za PostgreSQL version: 16.2 Operating system: Ubuntu 22.04.4 LTS Description: Postgres version: PostgreSQL 16.2 (Ubuntu 16.2-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit Create the table, insert one record, create function and trigger. ----------------------------------------------- CREATE TABLE test ( id SERIAL, number int ); INSERT INTO test(number) VALUES (0); CREATE OR REPLACE FUNCTION treeger() RETURNS trigger AS $BODY$ if not TD["new"]["number"] > 50: return None plpy.warning(TD) # LOG 1 plpy.execute("INSERT INTO test (number) VALUES (0)") plpy.warning(TD) # LOG 2 return None $BODY$ LANGUAGE 'plpython3u'; CREATE OR REPLACE TRIGGER treeger AFTER UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE treeger(); ----------------------------------------------- Then execute an update like below you will see that the trigger data is the same for LOG 1 and LOG 2; `UPDATE test SET doit=true WHERE id=1;` There are two ways to alter the trigger that will cause the TD to be different for LOG 1 and LOG 2. Either add `OR INSERT` like this; CREATE OR REPLACE TRIGGER treeger AFTER UPDATE OR INSERT ON test FOR EACH ROW EXECUTE PROCEDURE treeger(); or add a condition like this: CREATE OR REPLACE TRIGGER treeger AFTER UPDATE ON test FOR EACH ROW WHEN (NEW.doit) EXECUTE PROCEDURE treeger(); Then execute this again: `UPDATE test SET doit=true WHERE id=1;` Then the trigger data changes after the insert statement in the trigger function. Thanks in advance.
PG Bug reporting form <noreply@postgresql.org> writes: > Then the trigger data changes after the insert statement in the trigger > function. Hmm. TD is defined as a global dictionary like SD, so it saves values across calls to the plpython function. So the inner INSERT operation fires the same trigger, which decides not to print anything, but nonetheless it's changed TD and the outer trigger call will see that. There used to be similar problems in plain plpython functions if they were invoked recursively, because named function arguments are also passed as global variables. We fixed that in a rather hacky way in 1d2fe56e4, ie save and restore those globals when recursing. Probably the same thing could be done with TD. regards, tom lane
Re: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
От
Jacques Combrink
Дата:
Okay thanks for confirming.
Is there something that I can do to get this issue on a list to be fixed, or is this report enough?
Sorry for the questions, first time posting here, not sure how everything works.
Regards
|
On Sat, May 4, 2024 at 8:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
> Then the trigger data changes after the insert statement in the trigger
> function.
Hmm. TD is defined as a global dictionary like SD, so it saves values
across calls to the plpython function. So the inner INSERT operation
fires the same trigger, which decides not to print anything, but
nonetheless it's changed TD and the outer trigger call will see that.
There used to be similar problems in plain plpython functions if they
were invoked recursively, because named function arguments are also
passed as global variables. We fixed that in a rather hacky way in
1d2fe56e4, ie save and restore those globals when recursing.
Probably the same thing could be done with TD.
regards, tom lane
Re: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
От
Jacques Combrink
Дата:
Exhibit A. Will remember to keep signatures and stuff out of these mails.
Regards
Jacques Combrink
On Mon, May 6, 2024 at 10:23 AM Jacques Combrink <jacques@quantsolutions.co.za> wrote:
Okay thanks for confirming.Is there something that I can do to get this issue on a list to be fixed, or is this report enough?Sorry for the questions, first time posting here, not sure how everything works.Regards
Jacques Combrink
Software Developer
012 845 9888
jacques@quantsolutions.co.za
On Sat, May 4, 2024 at 8:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:PG Bug reporting form <noreply@postgresql.org> writes:
> Then the trigger data changes after the insert statement in the trigger
> function.
Hmm. TD is defined as a global dictionary like SD, so it saves values
across calls to the plpython function. So the inner INSERT operation
fires the same trigger, which decides not to print anything, but
nonetheless it's changed TD and the outer trigger call will see that.
There used to be similar problems in plain plpython functions if they
were invoked recursively, because named function arguments are also
passed as global variables. We fixed that in a rather hacky way in
1d2fe56e4, ie save and restore those globals when recursing.
Probably the same thing could be done with TD.
regards, tom lane
Jacques Combrink <jacques@quantsolutions.co.za> writes: > Okay thanks for confirming. > Is there something that I can do to get this issue on a list to be fixed, > or is this report enough? The patch is already queued [1]. It's a bit too late for this week's releases, but it'll be in 17beta1 and in the August quarterly releases. regards, tom lane [1] https://commitfest.postgresql.org/48/4970/