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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: jacques(at)quantsolutions(dot)co(dot)za
Subject: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
Date: 2024-05-03 14:37:12
Message-ID: 18456-82d3d70134aefd28@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18456
Logged by: Jacques Combrink
Email address: jacques(at)quantsolutions(dot)co(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Floris Van Nee 2024-05-03 18:10:05 RE: error "can only drop stats once" brings down database
Previous Message Matthias van de Meent 2024-05-03 09:35:15 Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()