Trigger firing order odd?

I just noticed that if two updates are done in the same PLPGSQL procedure,
then all the BEFORE triggers fire just before the updates, but that all the
AFTER triggers fire when the procedure exits. This is contrary to what
happens in Dec RDB, not sure about others. It is also a little
counter-intuitive.

I would expect:

Begin
update...;
update...;
End;

to fire the BEFORE, the AFTER, then the BEFORE & AFTER again.

Sample code below.
---------------------

CREATE TABLE zzz (
f1 integer
);

--
-- TOC entry 632 (OID 3098251)
-- Name: zzz_tg_proc (); Type: FUNCTION; Schema: public; Owner: pgsql
--

CREATE or REPLACE FUNCTION zzz_tg_proc () RETURNS "trigger"
AS 'Begin Raise NOTICE ''In trigger %'',TG_NAME; return NEW;end'
LANGUAGE plpgsql;

CREATE FUNCTION zzz_proc () RETURNS void
AS 'Begin Raise NOTICE ''In proc'';
update zzz set f1 = 2 where f1=1;
Raise NOTICE ''Updating again'';
update zzz set f1 = 1 where f1=2;
Raise NOTICE ''Leaving Proc'';
return;
end;' Language 'plpgsql';

--
-- TOC entry 633 (OID 3098523)
-- Name: zzz_proc (); Type: FUNCTION; Schema: public; Owner: pgsql
--

CREATE FUNCTION zzz_proc () RETURNS void
AS 'Begin Raise NOTICE ''In proc''; insert into zzz values(1); Raise
NOTICE ''Leaving Proc''; return;end'
LANGUAGE plpgsql;

CREATE FUNCTION zzz_upd () RETURNS void
AS 'Begin Raise NOTICE ''In proc''; update zzz set f1 = 1 where f1=1;
Raise NOTICE ''Leaving Proc''; return;end'
LANGUAGE plpgsql;

CREATE TRIGGER zzz_upd_bef_tg
BEFORE UPDATE ON zzz
FOR EACH ROW
EXECUTE PROCEDURE zzz_tg_proc ();

CREATE TRIGGER zzz_upd_aft_tg
AFTER UPDATE ON zzz
FOR EACH ROW
EXECUTE PROCEDURE zzz_tg_proc ();

CREATE TRIGGER zzz_add_bef_tg
BEFORE INSERT ON zzz
FOR EACH ROW
EXECUTE PROCEDURE zzz_tg_proc ();

CREATE TRIGGER zzz_add_aft_tg
AFTER INSERT ON zzz
FOR EACH ROW
EXECUTE PROCEDURE zzz_tg_proc ();

insert into zzz values(1);
NOTICE: In trigger zzz_add_bef_tg
NOTICE: In trigger zzz_add_aft_tg

select zzz_proc();
NOTICE: In proc
NOTICE: In trigger zzz_upd_bef_tg
NOTICE: Updating again
NOTICE: In trigger zzz_upd_bef_tg
NOTICE: Leaving Proc
NOTICE: In trigger zzz_upd_aft_tg
NOTICE: In trigger zzz_upd_aft_tg

----------------------------------------------------------------
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 |/