public inbox for [email protected]
help / color / mirror / Atom feedImproving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
6+ messages / 3 participants
[nested] [flat]
* Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
@ 2007-04-21 09:39 David Pufer <[email protected]>
2007-04-21 12:44 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Michael Glaesemann <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David Pufer @ 2007-04-21 09:39 UTC (permalink / raw)
To: pgsql-docs
Hello docs hackers,
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.
Regards,
David Pufer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
2007-04-21 09:39 Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
@ 2007-04-21 12:44 ` Michael Glaesemann <[email protected]>
2007-04-21 16:02 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
2007-04-21 16:20 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Tom Lane <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Michael Glaesemann @ 2007-04-21 12:44 UTC (permalink / raw)
To: David Pufer <[email protected]>; +Cc: pgsql-docs
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)
> If multiple triggers of the same kind are defined for the same
> event, they will be fired in alphabetical order by name.
It might be nice to add a similar phrase to the CREATE CONSTRAINT
TRIGGER documentation, but as far as I know, the behavior is the same
for triggers created by either method. The only difference is that
constraint triggers are named by the system rather than by the user.
Thoughts?
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
2007-04-21 09:39 Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
2007-04-21 12:44 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Michael Glaesemann <[email protected]>
@ 2007-04-21 16:02 ` David Pufer <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: David Pufer @ 2007-04-21 16:02 UTC (permalink / raw)
To: pgsql-docs
Michael Glaesemann wrote:
>
> 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)
>
>> If multiple triggers of the same kind are defined for the same event,
>> they will be fired in alphabetical order by name.
>
> It might be nice to add a similar phrase to the CREATE CONSTRAINT
> TRIGGER documentation, but as far as I know, the behavior is the same
> for triggers created by either method. The only difference is that
> constraint triggers are named by the system rather than by the user.
>
> Thoughts?
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
You are right. Thanks for the note of system name that are assigned by
the server by incrementing the num at "RI_ConstraintTrigger_NUM" at each
constraint trigger creation.
Regards,
David Pufer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
2007-04-21 09:39 Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
2007-04-21 12:44 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Michael Glaesemann <[email protected]>
@ 2007-04-21 16:20 ` Tom Lane <[email protected]>
2007-04-21 16:48 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2007-04-21 16:20 UTC (permalink / raw)
To: Michael Glaesemann <[email protected]>; +Cc: David Pufer <[email protected]>; pgsql-docs
Michael Glaesemann <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
2007-04-21 09:39 Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
2007-04-21 12:44 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Michael Glaesemann <[email protected]>
2007-04-21 16:20 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Tom Lane <[email protected]>
@ 2007-04-21 16:48 ` David Pufer <[email protected]>
2007-04-21 17:37 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Michael Glaesemann <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David Pufer @ 2007-04-21 16:48 UTC (permalink / raw)
To: pgsql-docs
Tom Lane wrote:
> Michael Glaesemann <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order
2007-04-21 09:39 Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
2007-04-21 12:44 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Michael Glaesemann <[email protected]>
2007-04-21 16:20 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order Tom Lane <[email protected]>
2007-04-21 16:48 ` Re: Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
@ 2007-04-21 17:37 ` Michael Glaesemann <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Glaesemann @ 2007-04-21 17:37 UTC (permalink / raw)
To: David Pufer <[email protected]>; +Cc: pgsql-docs
On Apr 21, 2007, at 11:48 , David Pufer wrote:
> Tom Lane wrote:
>> Michael Glaesemann <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-04-21 17:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-21 09:39 Improving CREATE CONSTRAINT TRIGGER documentation - trigger firing order David Pufer <[email protected]>
2007-04-21 12:44 ` Michael Glaesemann <[email protected]>
2007-04-21 16:02 ` David Pufer <[email protected]>
2007-04-21 16:20 ` Tom Lane <[email protected]>
2007-04-21 16:48 ` David Pufer <[email protected]>
2007-04-21 17:37 ` Michael Glaesemann <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox