pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
5+ messages / 3 participants
[nested] [flat]
* BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
@ 2024-05-03 14:37 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2024-05-03 14:37 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jacques@quantsolutions.co.za
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
@ 2024-05-04 18:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2024-05-04 18:34 UTC (permalink / raw)
To: jacques@quantsolutions.co.za; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
@ 2024-05-06 08:23 Jacques Combrink <jacques@quantsolutions.co.za>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Jacques Combrink @ 2024-05-06 08:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
[image: GoodX Logo] <https://www.goodx.healthcare;
Jacques Combrink
Software Developer
[image: telephone] 012 845 9888
[image: emailad] jacques@quantsolutions.co.za
[image: GoodX Website] <https://www.goodx.healthcare; [image: GoodX YouTube]
<https://www.youtube.com/channel/UCX235mj63BB9gb9YUSxe7Bg; [image: GoodX
Facebook] <https://www.facebook.com/GoodXSoftware/; [image: GoodX LinkedIn]
<https://www.linkedin.com/company/goodx-software/; [image: GoodX Instagram]
<https://www.instagram.com/goodx_software/;
[image: GoodX Group Logos] <https://www.goodx.healthcare;
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
@ 2024-05-06 08:36 Jacques Combrink <jacques@quantsolutions.co.za>
parent: Jacques Combrink <jacques@quantsolutions.co.za>
1 sibling, 0 replies; 5+ messages in thread
From: Jacques Combrink @ 2024-05-06 08:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
> [image: GoodX Logo] <https://www.goodx.healthcare;
> Jacques Combrink
> Software Developer
>
> [image: telephone] 012 845 9888
> [image: emailad] jacques@quantsolutions.co.za
>
> [image: GoodX Website] <https://www.goodx.healthcare; [image: GoodX
> YouTube] <https://www.youtube.com/channel/UCX235mj63BB9gb9YUSxe7Bg; [image:
> GoodX Facebook] <https://www.facebook.com/GoodXSoftware/; [image: GoodX
> LinkedIn] <https://www.linkedin.com/company/goodx-software/; [image:
> GoodX Instagram] <https://www.instagram.com/goodx_software/;
>
> [image: GoodX Group Logos] <https://www.goodx.healthcare;
>
>
> 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
>>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
@ 2024-05-06 14:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jacques Combrink <jacques@quantsolutions.co.za>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2024-05-06 14:41 UTC (permalink / raw)
To: Jacques Combrink <jacques@quantsolutions.co.za>; +Cc: pgsql-bugs@lists.postgresql.org
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-05-06 14:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-03 14:37 BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger PG Bug reporting form <noreply@postgresql.org>
2024-05-04 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-06 08:23 ` Jacques Combrink <jacques@quantsolutions.co.za>
2024-05-06 08:36 ` Jacques Combrink <jacques@quantsolutions.co.za>
2024-05-06 14:41 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox