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.
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() |