Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 69F779FB39F
	for <pgsql-docs-postgresql.org@postgresql.org>;
	Sat, 21 Apr 2007 14:37:08 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 80519-10 for <pgsql-docs-postgresql.org@postgresql.org>;
	Sat, 21 Apr 2007 14:36:57 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mtao01.charter.net (mtao01.charter.net [209.225.8.186])
	by postgresql.org (Postfix) with ESMTP id C4C5B9FA601
	for <pgsql-docs@postgresql.org>; Sat, 21 Apr 2007 14:37:04 -0300 (ADT)
Received: from aa06.charter.net ([10.20.200.158]) by mtao01.charter.net
	(InterMail vM.7.08.02.00 201-2186-121-20061213) with ESMTP
	id <20070421173704.EMCR1417.mtao01.charter.net@aa06.charter.net>;
	Sat, 21 Apr 2007 13:37:04 -0400
Received: from [10.0.1.3] (really [66.188.171.190]) by aa06.charter.net
	with ESMTP id <20070421173703.GDTZ1488.aa06.charter.net@[10.0.1.3]>;
	Sat, 21 Apr 2007 13:37:03 -0400
In-Reply-To: <462A4062.3070704@7data.eu>
References: <4629DBC8.4010700@7data.eu>
	<EA1B473D-1D3E-432A-9E02-DA96621C965A@seespotcode.net>
	<24565.1177172440@sss.pgh.pa.us> <462A4062.3070704@7data.eu>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <F72804E8-08C7-4EA2-AA35-70203F25A536@seespotcode.net>
Cc: pgsql-docs@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger
	firing order
Date: Sat, 21 Apr 2007 12:37:07 -0500
To: David Pufer <david.pufer@7data.eu>
X-Mailer: Apple Mail (2.752.2)
X-Chzlrs: 0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/24
X-Sequence-Number: 4220


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