BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
| От | PG Bug reporting form |
|---|---|
| Тема | BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger |
| Дата | |
| Msg-id | 18456-82d3d70134aefd28@postgresql.org обсуждение исходный текст |
| Ответы |
Re: BUG #18456: Trigger data in plpython3u trigger-function changes in AFTER UPDATE OR INSERT trigger
|
| Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления: