Thread: [PL/PGSQL] (Bug/Feature problem) with recursive Trigger
Hello,
I got some problem on trigger which call them self for UPDATE
BEFORE/AFTER.
Here is some test :
The UPDATE test function/table :
--------------------------------
------8<------------8<----------------8<----------
CREATE SEQUENCE id_my_table_seq;
CREATE table "my_table" (
"id_my_table" int4 DEFAULT nextval('id_my_table_seq') PRIMARY KEY,
"row0" text,
"row1" text,
"row2" text
);
INSERT INTO my_table (id_my_table, row0, row1, row2) VALUES (10,
'data0', 'data1', 'data2');
CREATE OR REPLACE FUNCTION my_table_before_update() RETURNS trigger AS '
DECLARE
BEGIN
IF OLD.row0 <> NEW.row0 THEN
RAISE NOTICE ''test1 %'', OLD.row0;
RAISE NOTICE ''test2 %'', NEW.row0;
UPDATE my_table SET row1 = \'toto\' WHERE id_my_table =
NEW.id_my_table;
RAISE NOTICE ''test3 %'', OLD.row0;
RAISE NOTICE ''test4 %'', NEW.row0;
UPDATE my_table SET row1 = \'tata\' WHERE id_my_table =
NEW.id_my_table;
RAISE NOTICE ''test5 %'', OLD.row0;
RAISE NOTICE ''test6 %'', NEW.row0;
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER my_table_before_update BEFORE UPDATE ON my_table FOR EACH
ROW EXECUTE PROCEDURE my_table_before_update();
CREATE OR REPLACE FUNCTION my_table_after_update() RETURNS trigger AS '
DECLARE
BEGIN
RAISE NOTICE ''test7 %'', OLD.row0;
RAISE NOTICE ''test8 %'', NEW.row0;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER my_table_after_update AFTER UPDATE ON my_table FOR EACH
ROW EXECUTE PROCEDURE my_table_after_update();
------8<------------8<----------------8<----------
The test for these trigger :
----------------------------
UPDATE my_table set row0 = 'my_test' WHERE id_my_table = 10;
Result :
--------
On a 7.4.7 :
UPDATE my_table set row0 = 'my_test' WHERE id_my_table = 10;
NOTICE: test1 data0
NOTICE: test2 my_test
NOTICE: test3 data0
NOTICE: test4 my_test
NOTICE: test5 data0
NOTICE: test6 my_test
NOTICE: test7 data0
NOTICE: test8 data0
NOTICE: test7 data0
NOTICE: test8 data0
on a 8.1.4 (without context) :
test=# update my_table set row0 = 'my_test' WHERE id_my_table = 10;
NOTICE: test1 data0
NOTICE: test2 my_test
NOTICE: test7 data0
NOTICE: test8 data0
NOTICE: test3 data0
NOTICE: test4 my_test
NOTICE: test7 data0
NOTICE: test8 data0
NOTICE: test5 data0
NOTICE: test6 my_test
PG7 dont make recursiv, it wait for the end of the trigger BEFORE_UPDATE
to call the new UPDATE stat and forgot the 3rd AFTER_UPDATE. PG8 is
better, it call trigger like real recursiv fonction, but allways dismiss
the 3rd AFTER UPDATE.
Logically, the answer should be :
NOTICE: test1 data0
NOTICE: test2 my_test
NOTICE: test7 data0
NOTICE: test8 data0
NOTICE: test3 data0
NOTICE: test4 my_test
NOTICE: test7 data0
NOTICE: test8 data0
NOTICE: test5 data0
NOTICE: test6 my_test
NOTICE: test7 data0
NOTICE: test8 my_test
At beginning, i made a test to see how pl/pgsql make real recursiv with
an insert function which work :
-----------8<-----------8<-----------8<-----------8<---------------
CREATE SEQUENCE id_test_seq;
CREATE table "test" (
"id_test" int4 DEFAULT nextval(id_test_seq) PRIMARY KEY,
"test" text,
"other_row" text,
);
CREATE OR REPLACE FUNCTION test_insert() RETURNS trigger AS '
DECLARE
categorie_mere RECORD;
categorie_mere_lien RECORD;
RecTmp RECORD;
BEGIN
RAISE NOTICE ''begginning'';
IF NEW.test = ''test'' THEN
INSERT INTO test (test) VALUES (''toto'');
END IF;
RAISE NOTICE ''end'';
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER test_insert BEFORE INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE test_insert();
-----------8<-----------8<-----------8<-----------8<---------------
With a :
INSERT INTO test (test) values ('test');
You obtain in each case :
NOTICE: begginning
NOTICE: begginning
NOTICE: end
NOTICE: end
-------------------------------------------------------------
In fact, what i dont understand, its why PG dont forget to make the 2
update inside the main update, but after, forgot to make the last one.
Any idea ?
Regards,
"Froggy / Froggy Corp." <froggy@froggycorp.com> writes:
> PG7 dont make recursiv, it wait for the end of the trigger BEFORE_UPDATE
> to call the new UPDATE stat and forgot the 3rd AFTER_UPDATE. PG8 is
> better, it call trigger like real recursiv fonction, but allways dismiss
> the 3rd AFTER UPDATE.
There isn't any third AFTER UPDATE because the updates fired by the
trigger override the pending update, and so when the trigger returns
the pending update is abandoned.
This is a really badly designed trigger anyway: why don't you just
modify the NEW row, instead of incurring orders of magnitude more work
by launching an entire new SQL command?
regards, tom lane
Tom Lane wrote: > > "Froggy / Froggy Corp." <froggy@froggycorp.com> writes: > > PG7 dont make recursiv, it wait for the end of the trigger BEFORE_UPDATE > > to call the new UPDATE stat and forgot the 3rd AFTER_UPDATE. PG8 is > > better, it call trigger like real recursiv fonction, but allways dismiss > > the 3rd AFTER UPDATE. > > There isn't any third AFTER UPDATE because the updates fired by the > trigger override the pending update, and so when the trigger returns > the pending update is abandoned. > > This is a really badly designed trigger anyway: why don't you just > modify the NEW row, instead of incurring orders of magnitude more work > by launching an entire new SQL command? I make some reorganization of my table when user make an update. The trigger need to be able to support lot of case, so to make reorganization more simple, i make some test, and change or make change of this table by other part of trigger which are on after_update or before_update. Because the trigger call itself, this way is more easy and decrease considerably all possibility. In fact, i thought that for "recursiv" trigger, PG alocate a new trigger in memory, so it should not be a problem. I dont know if its really bad, but i dont see any more option to do it (btw, i will need to change these part to make trigger working). All trigger for this table work like that. Regards,
"Froggy / Froggy Corp." <froggy@froggycorp.com> writes:
> Tom Lane wrote:
>> This is a really badly designed trigger anyway: why don't you just
>> modify the NEW row, instead of incurring orders of magnitude more work
>> by launching an entire new SQL command?
> I make some reorganization of my table when user make an update. The
> trigger need to be able to support lot of case, so to make
> reorganization more simple, i make some test, and change or make change
> of this table by other part of trigger which are on after_update or
> before_update.
If you are cascading changes to other rows, you should do them in AFTER
triggers. It's not really very sensible to try to do that in a BEFORE
trigger, because a BEFORE trigger shouldn't assume it's seeing the final
version of the row. BEFORE triggers are good for checking or adjusting
the data in the proposed new row, but for pushing consequences out to
other rows, use an AFTER trigger.
regards, tom lane
Its my plan in fact, it was to make some optimisation, because i need to copy all the test from the BEFORE statement to the AFTER. Thx for your help, Regards, Tom Lane wrote: > > "Froggy / Froggy Corp." <froggy@froggycorp.com> writes: > > Tom Lane wrote: > >> This is a really badly designed trigger anyway: why don't you just > >> modify the NEW row, instead of incurring orders of magnitude more work > >> by launching an entire new SQL command? > > > I make some reorganization of my table when user make an update. The > > trigger need to be able to support lot of case, so to make > > reorganization more simple, i make some test, and change or make change > > of this table by other part of trigger which are on after_update or > > before_update. > > If you are cascading changes to other rows, you should do them in AFTER > triggers. It's not really very sensible to try to do that in a BEFORE > trigger, because a BEFORE trigger shouldn't assume it's seeing the final > version of the row. BEFORE triggers are good for checking or adjusting > the data in the proposed new row, but for pushing consequences out to > other rows, use an AFTER trigger. > > regards, tom lane