From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jacques@quantsolutions.co.za
Subject: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
Date: Fri, 03 May 2024 14:37:12 +0000
Message-ID: <18456-82d3d70134aefd28@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jacques@quantsolutions.co.za
Subject: Re: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
In-Reply-To: <18456-82d3d70134aefd28@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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