Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order

On Apr 21, 2007, at 11:48 , David Pufer wrote:

> Tom Lane wrote:
>> Michael Glaesemann <grzm@seespotcode.net> writes:
>>> On Apr 21, 2007, at 4:39 , David Pufer wrote:
>>>> In my opinion it would be nice to mention in SQL reference
>>>> commands that constraint triggers created by CREATE CONSTRAINT
>>>> TRIGGER are fired in creation order unlike regular triggers
>>>> created by command CREATE TRIGGER. As I know, the firing order
>>>> of the CONSTRAINT TRIGGER is determined by the XXXXX num at
>>>> "RI_ConstraintTrigger_XXXXX" column in pg_trigger table.
>>> I believe this just falls out of the normal order of trigger
>>> firing as explained in the CREATE TRIGGER documentation (http://
>>> www.postgresql.org/docs/8.2/interactive/sql-createtrigger.html)
>> The proposed change is wrong anyway: AFAICS from looking at the
>> code, an RI_ConstraintTrigger_XXXXX name is only generated for
>> triggers that are created in support of FOREIGN KEY constraint
>> syntax. If you use CREATE CONSTRAINT TRIGGER then the trigger name
>> is whatever you say it is. I don't recall if it was always like
>> that, but that's definitely the behavior in CVS HEAD.

<snip />

> As Tom notedm a while ago it might be different in CVS HEAD and
> consequently in 8.3.

Just for my own confirmation, the behavior is different in HEAD.

postgres=# create table a_table (a_id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"a_table_pkey" for table "a_table"
CREATE TABLE
postgres=# create function a_trigger_function () returns trigger
language plpgsql as $$ begin return null; end;$$;
CREATE FUNCTION
postgres=# create constraint trigger a_constraint_trigger after
insert or update or delete on a_table deferrable initially deferred
for each row execute procedure a_trigger_function();
CREATE TRIGGER
postgres=# select tgname, tgconstrname from pg_trigger;
          tgname          |     tgconstrname
-------------------------+----------------------
pg_sync_pg_database     |
pg_sync_pg_authid       |
pg_sync_pg_auth_members |
a_constraint_trigger    | a_constraint_trigger
(4 rows)

postgres=# select version();

version
------------------------------------------------------------------------
------------------------------------------------------------------------
-
PostgreSQL 8.3devel on powerpc-apple-darwin8.9.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5367)
(1 row)

A very nice improvement!

Michael Glaesemann
grzm seespotcode net