Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order

From: David Pufer <david(dot)pufer(at)7data(dot)eu>
To: pgsql-docs(at)postgresql(dot)org
Subject: Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
Date: 2007-04-21 16:48:34
Message-ID: 462A4062.3070704@7data.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

Tom Lane wrote:
> Michael Glaesemann <grzm(at)seespotcode(dot)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

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Michael Glaesemann 2007-04-21 17:37:07 Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
Previous Message Tom Lane 2007-04-21 16:20:40 Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order