Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 420DC9FB8AF for ; Sat, 21 Apr 2007 13:48:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 07365-01 for ; Sat, 21 Apr 2007 13:48:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mailfilter1.7data.eu (82-100-4-5.net.maiva.cz [82.100.4.5]) by postgresql.org (Postfix) with ESMTP id 718389FB5FA for ; Sat, 21 Apr 2007 13:48:37 -0300 (ADT) Received: from mailfilter1.7data.eu (localhost [127.0.0.1]) by sagator.mailfilter1.7data.eu (Postfix) with ESMTP id B5EAC499B45 for ; Sat, 21 Apr 2007 18:48:34 +0200 (CEST) X-Sagator-Scanner: 0.8.1-1 at 7drouter1; log(status(quarantine(drop(stream2mbox(libclam())))), status(quarantine(drop(SpamAssassinD())))) X-Sagator-ID: 20070421-184834-0001-13838-FIm2Ng@7drouter1 Received: from [192.168.100.100] (unknown [192.168.100.100]) by mailfilter1.7data.eu (Postfix) with ESMTP id 97692499B43 for ; Sat, 21 Apr 2007 18:48:34 +0200 (CEST) Message-ID: <462A4062.3070704@7data.eu> Date: Sat, 21 Apr 2007 18:48:34 +0200 From: David Pufer User-Agent: Thunderbird 2.0.0.0 (X11/20070420) MIME-Version: 1.0 To: pgsql-docs@postgresql.org Subject: Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order References: <4629DBC8.4010700@7data.eu> <24565.1177172440@sss.pgh.pa.us> In-Reply-To: <24565.1177172440@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------050602070401010300070700" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/23 X-Sequence-Number: 4219 This is a multi-part message in MIME format. --------------050602070401010300070700 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Tom Lane wrote: > Michael Glaesemann 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. > > It would be a mistake to assume that FK constraint triggers are fired in > creation order anyway, since the lexical sorting of strings isn't the > same as numeric order (not to mention OID wraparound issues). > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > I tested in in 8.2.3 stable. e.g. command CREATE CONSTRAINT TRIGGER my_constraint_trigger_name AFTER INSERT OR UPDATE OR DELETE ON my_test_table DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE my_test_procedure() created a row with tgname "RI_ConstraintTrigger_28329" and tgconstrname "my_constraint_trigger_name" in system catalog in table pg_trigger, while creation of normal trigger (not constraint trigger) puts its name into tgname column. As Tom notedm a while ago it might be different in CVS HEAD and consequently in 8.3. Thanks for helpful info David Pufer --------------050602070401010300070700 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit 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.

It would be a mistake to assume that FK constraint triggers are fired in
creation order anyway, since the lexical sorting of strings isn't the
same as numeric order (not to mention OID wraparound issues).

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
I tested in in 8.2.3 stable.
e.g. command

CREATE CONSTRAINT TRIGGER my_constraint_trigger_name
AFTER INSERT OR UPDATE OR DELETE
ON my_test_table
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE my_test_procedure()

created a row with tgname "RI_ConstraintTrigger_28329" and tgconstrname "my_constraint_trigger_name" in system catalog in table pg_trigger, while creation of normal trigger (not constraint trigger) puts its name into tgname column. As Tom notedm a while ago it might be different in CVS HEAD and consequently in 8.3.

Thanks for helpful info
David Pufer





--------------050602070401010300070700--