Does anyone have answers for these? I read the thread and don't 100% understand it all.
My belief is that at least ROW triggers need fixing (7.3 doesn't have statement, not sure about 7.4).
Currently, if you write a plpgsql procedure which calls more than one insert/update/delete statements, the AFTER triggers for all of these statements will not fire until after the procedure exits. They should fire either just after each row is updated, or just after the most immediately enclosing statement executes. I think the thread wanted the latter.
So, if we have a table with two rows, and a BEFORE and AFTER trigger, and a plpgsql procedure that updates all rows twice, then we should have:
procedure called
procedure executes first update
before trigger fires(row 1)
before trigger fires(row 2)
row 1 updated
row 2 updated
after trigger fires(row 1)
after trigger fires(row 2)
procedure executes second update
before trigger fires(row 1)
before trigger fires(row 2)
row 1 updated
row 2 updated
after trigger fires(row 1)
after trigger fires(row 2)
procedure exitsWhat we have in 7.3 is:
procedure called
procedure executes first update
before trigger fires(row 1)
before trigger fires(row 2)
row 1 updated
row 2 updated
procedure executes second update
before trigger fires(row 1)
before trigger fires(row 2)
row 1 updated
row 2 updated
procedure exits
after trigger fires(row 1)
after trigger fires(row 2)
after trigger fires(row 1)
after trigger fires(row 2)IIRC, the thread did not really discuss whether do intersperse the BEFORE executions with the updates, but doing them all before seems consistent.
Apologies is this has been covered elsewhere...
---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
