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 по дате отправления:

Предыдущее
От: Matthias van de Meent
Дата:
Сообщение: Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()
Следующее
От: Floris Van Nee
Дата:
Сообщение: RE: error "can only drop stats once" brings down database