Thread: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger

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

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/