pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrder of triggers - totally lost
9+ messages / 4 participants
[nested] [flat]
* Order of triggers - totally lost
@ 2003-08-13 10:11 SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:54 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 1 reply; 9+ messages in thread
From: SZŰCS Gábor @ 2003-08-13 10:11 UTC (permalink / raw)
To: pgsql-sql
Dear Gurus,
Please help to solve this chaos: I have a simple query that triggers several
plpgsql triggers on several tables.
Table A has a BIUD (Before Insert or Update or Delete) trigger (henceforth
A_BIUD and analogues).
Table B has triggers BIUD, AI and AU.
Table B1 has triggers BIUD and AD.
Table B2 has triggers BIUD and AIUD.
Consider the following, my preferred trigger order for a specific UPDATE on
table A:
A_BIUD, update:
insert into B (foobar); -- inserting a single tuple
B_BIUD, insert -- irrelevant
B_AI, insert:
insert into B1 (foo2); -- one or more tuples
B1.BIUD, insert:
if B is done then raise exception;-- "done" is false by default.
insert into B2 (foo2); -- the same one or more tuples
B2.BIUD, insert -- irrelevant
update B set done=true WHERE foobar; -- updating the same single tuple
B_BIUD, update -- irrelevant
B_AU, update:
update B2 set done=true WHERE foo2; -- the same one or more tuples
B2.BIUD, update -- irrelevant
In short:
A_BIUD, update
B_BIUD, insert
B_AI, insert
B1_BIUD, insert
B2_BIUD, insert
B_BIUD, update
B_AU, update
However: the triggers run in the following order, until the abovementioned
exception aborts the transaction:
A_BIUD, update
B_BIUD, insert
B_BIUD, update
A_BIUD, update ends here
B_AI, insert
B1_BIUD, insert: exception.
The docs say in trigger-datachanges.html:
"Changes made by query Q are visible by queries that are started after query
Q, no matter whether they are started inside Q (during the execution of Q)
or after Q is done.
This is true for triggers as well ..."
Trying to understand that, I have a feeling that the update in A_BIUD should
already see the results of the preceding insert, including the results of
triggers activated by that insert.
What may be wrong? Any ideas to re-organize parts of the triggers? May
putting the update to an A_AU trigger help? I tried it, still have problems
(not sure it's still the trigger order), but the trigger order is still
strange for me:
A_BIUD
B_BIUD, insert
B_AI
B1_BIUD, insert
A_AU
B2_AIUD, insert (!)
B2_BIUD, insert (???)
B_AU
G.
------------------------------- cut here -------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Order of triggers - totally lost
2003-08-13 10:11 Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
@ 2003-08-13 14:54 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 15:40 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 9+ messages in thread
From: Jonathan Gardner @ 2003-08-13 14:54 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wednesday 13 August 2003 03:11, SZŰCS Gábor wrote:
>
> What may be wrong? Any ideas to re-organize parts of the triggers?
> May putting the update to an A_AU trigger help? I tried it, still
> have problems (not sure it's still the trigger order), but the
> trigger order is still strange for me:
>
I'd need some solid code to solve this. Can you send the create
statements and the insert statement that started it all? I get the
feeling that you may have more triggers than you really need.
- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/OlEmWgwF3QvpWNwRAnPdAKDEVKuZb+hRuF0VT2XvE2jf/NNlGwCfUnl9
FP6qKYkr8zAokDNaK4CI6rE=
=0kMs
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Order of triggers - totally lost
2003-08-13 10:11 Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:54 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
@ 2003-08-13 15:40 ` SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 15:55 ` Re: Order of triggers - totally lost Josh Berkus <josh@agliodbs.com>
2003-08-13 16:20 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 2 replies; 9+ messages in thread
From: SZŰCS Gábor @ 2003-08-13 15:40 UTC (permalink / raw)
To: pgsql-sql
Dear Jonathan,
Thanks for your will to help.
It would be a bit difficult to clean up everything as much as possible
(while keeping the problem) and send the skeleton.
It looks like the A_AU trigger I mentioned solved the problem.
Actually, the situation I painted is much much simplified compared to the
real one (about 20 or more tables are accessed during that "simple 1-line
update"). What I'd probably use best, are some generic guidelines:
* what is sure about trigger execution order?
(Cristoph Haller partially answered my question, quoting future plans)
* are there generic recommendations what kind of things to put in
before and after triggers?
* how about FOR EACH STATEMENT triggers?
(we only use FOR EACH ROW triggers)
G.
------------------------------- cut here -------------------------------
----- Original Message -----
From: "Jonathan Gardner" <jgardner@jonathangardner.net>
Sent: Wednesday, August 13, 2003 4:54 PM
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wednesday 13 August 2003 03:11, SZŰCS Gábor wrote:
>
> What may be wrong? Any ideas to re-organize parts of the triggers?
> May putting the update to an A_AU trigger help? I tried it, still
> have problems (not sure it's still the trigger order), but the
> trigger order is still strange for me:
I'd need some solid code to solve this. Can you send the create
statements and the insert statement that started it all? I get the
feeling that you may have more triggers than you really need.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Order of triggers - totally lost
2003-08-13 10:11 Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:54 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 15:40 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
@ 2003-08-13 15:55 ` Josh Berkus <josh@agliodbs.com>
2003-08-13 16:10 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
1 sibling, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2003-08-13 15:55 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; pgsql-sql
G:
> * what is sure about trigger execution order?
> (Cristoph Haller partially answered my question, quoting future plans)
My perspective: multiple triggers of the same type on the same table are a
really bad idea if execution order matters. Any setup like this is
automatically maintenence hell, even when we add some sort of "trigger
priority" feature.
If your table needs multiple BEFORE operations, you should put the operations
into one large procedure with branching logic.
> * are there generic recommendations what kind of things to put in
> before and after triggers?
before triggers: re-formating input and inserting complex defaults before
saving to table; historical archiving of old data; data validation.
after triggers: updating related tables; chronological logging of activity.
> * how about FOR EACH STATEMENT triggers?
> (we only use FOR EACH ROW triggers)
These will not work until 7.4, and then there will be some limitations (which
will hopefully go away in 7.5).
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Order of triggers - totally lost
2003-08-13 10:11 Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:54 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 15:40 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 15:55 ` Re: Order of triggers - totally lost Josh Berkus <josh@agliodbs.com>
@ 2003-08-13 16:10 ` SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 17:16 ` Re: Order of triggers - totally lost Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 9+ messages in thread
From: SZŰCS Gábor @ 2003-08-13 16:10 UTC (permalink / raw)
To: pgsql-sql
Dear Josh,
thanks for the guidelines.
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
Sent: Wednesday, August 13, 2003 5:55 PM
> My perspective: multiple triggers of the same type on the same table are a
> really bad idea if execution order matters. Any setup like this is
> automatically maintenence hell, even when we add some sort of "trigger
> priority" feature.
No such thing here. At least, no such that may affect the same tuple (i.e.
AI and AU are separate triggers, but I won't gain anything with a single AIU
trigger that runs twice, in unpredictable order)
> before triggers: re-formating input and inserting complex defaults before
> saving to table; historical archiving of old data; data validation.
>
> after triggers: updating related tables; chronological logging of
activity.
just about the same as we use it, but just hours ago, I had to put some of
the after stuff (updating related tables) to the before trigger, because it
seemed to be much more simple.
> > * how about FOR EACH STATEMENT triggers?
> > (we only use FOR EACH ROW triggers)
>
> These will not work until 7.4, and then there will be some limitations
(which
> will hopefully go away in 7.5).
Am I reading you right, and statement triggers don't work in 7.3? or some
aspect of the order of statement- and row-level triggers?
G.
------------------------------- cut here -------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Order of triggers - totally lost
2003-08-13 10:11 Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:54 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 15:40 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 15:55 ` Re: Order of triggers - totally lost Josh Berkus <josh@agliodbs.com>
2003-08-13 16:10 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
@ 2003-08-13 17:16 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2003-08-13 17:16 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; pgsql-sql
G -
> Am I reading you right, and statement triggers don't work in 7.3? or some
> aspect of the order of statement- and row-level triggers?
Correct, they don't work in 7.3.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Order of triggers - totally lost
2003-08-13 10:11 Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:54 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 15:40 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
@ 2003-08-13 16:20 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 17:20 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
1 sibling, 1 reply; 9+ messages in thread
From: Jonathan Gardner @ 2003-08-13 16:20 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wednesday 13 August 2003 08:40, SZŰCS Gábor wrote:
> Actually, the situation I painted is much much simplified compared to
> the real one (about 20 or more tables are accessed during that
> "simple 1-line update"). What I'd probably use best, are some generic
> guidelines:
>
I try to avoid triggers unless it is completely obvious what they are
doing and why I should use them. This tends to minimize the number of
them hanging around. I am a programmer - python, perl mostly - so the
logic flow of triggers isn't something I can keep a firm handle on all
the time.
Here are some examples of when I use triggers:
- One column is completely dependent on one or more other columns
(e.g., total_revenue, total_cost, profit). A 'before' insert/update
trigger works here. That one trigger can do all of the calculations for
the row.
- A row is summary of several other rows in other tables. This is
useful for collecting real-time stats, but is difficult to get right.
Here, I use 'after' triggers. I also copiously document how it works,
because there are always nasty bugs waiting to bite.
- A particularly nasty constraint, that isn't as simple as "not null".
If you noticed, the foreign key constraints are implemented with three
triggers - one on the referencing table, and two on the referenced
table. There are some other situations where you may want constraints
that aren't as clear-cut as a foreign key that will require multiple
'before' triggers on multiple tables.
When I handle a complicated procedure that involves inserting multiple
rows into multiple tables, I tend to put those into plpgsql procedures.
That way, I can keep control of everything and keep it clean. For
instance, placing an order with several items.
My tables only end up with a couple of triggers, if any. I have a ton of
stored procedures lying around, however -- pretty much one for each
"action" a user would take to modify the database. If a trigger
triggers another trigger, they aren't dependant on the order thereof,
or even the existance of the other trigger.
You may also want to examine PostgreSQL's RULE system (CREATE RULE). I
think some of your triggers may be interchangeable with rules.
- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/OmU2WgwF3QvpWNwRAklXAJ4hv+2Fx5jZXG6ykpOMMNLvG655owCdFtEo
+eV+ZcrItpOerAPySiSPe2g=
=e1Ao
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Order of triggers - totally lost
2003-08-13 10:11 Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:54 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 15:40 ` Re: Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 16:20 ` Re: Order of triggers - totally lost Jonathan Gardner <jgardner@jonathangardner.net>
@ 2003-08-13 17:20 ` SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 0 replies; 9+ messages in thread
From: SZŰCS Gábor @ 2003-08-13 17:20 UTC (permalink / raw)
To: pgsql-sql
Dear Jonathan,
thanks for your POV.
----- Original Message -----
From: "Jonathan Gardner" <jgardner@jonathangardner.net>
Sent: Wednesday, August 13, 2003 6:20 PM
> I try to avoid triggers unless it is completely obvious what they are
> doing and why I should use them. This tends to minimize the number of
> them hanging around. I am a programmer - python, perl mostly - so the
> logic flow of triggers isn't something I can keep a firm handle on all
> the time.
We prefer using triggers to protect the server as well as client logic and
even wizards (some of them have more than 100 graph vertices and edges) to
help the users avoid illegal operations. In short: double protection.
> My tables only end up with a couple of triggers, if any. I have a ton of
A couple of triggers for most tables, yes. But for over 100 tables, this is
a couple hundred triggers total. Is that what you meant?
> stored procedures lying around, however -- pretty much one for each
> "action" a user would take to modify the database. If a trigger
> triggers another trigger, they aren't dependant on the order thereof,
> or even the existance of the other trigger.
>
> You may also want to examine PostgreSQL's RULE system (CREATE RULE). I
> think some of your triggers may be interchangeable with rules.
You have a point there... not sure I'll have the time to experiment
(we are ordered to make only small changes to be easily commitable to the
in-production version)
G.
------------------------------- cut here -------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Order of triggers - totally lost
@ 2003-08-13 14:22 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 9+ messages in thread
From: Christoph Haller @ 2003-08-13 14:22 UTC (permalink / raw)
To: pgsql-sql; surrano@mailbox.hu
> The docs say in trigger-datachanges.html:
>
> "Changes made by query Q are visible by queries that are started after
query
> Q, no matter whether they are started inside Q (during the execution
of Q)
> or after Q is done.
>
> This is true for triggers as well ..."
>
> Trying to understand that, I have a feeling that the update in A_BIUD
should
> already see the results of the preceding insert, including the results
of
> triggers activated by that insert.
I've seen that
PostgreSQL Weekly News - July 25th 2003
Even though we are nearing release, it doesn't hurt to look
ahead, as
evidenced by several additions to the TODO list this week: delay
resolution of array expression type to assignment coercion can be
performed on empty array expressions, have AFTER triggers execute after
the appropriate SQL statement in a function, not at the end of the
function, more details on possible tablespace definitions were added,
add PL/PHP, allow PL/pgSQL to name columns by ordinal position (e.g.
rec.(3)), and prevent COMMENT ON DATABASE from using a database name.
Some of these already have folks working on them, however if you are
interested in lending a hand please don't be afraid to speak up.
Looks like this passage about AFTER triggers is what you're expecting,
but is not implemented yet.
>
> What may be wrong? Any ideas to re-organize parts of the triggers? May
> putting the update to an A_AU trigger help? I tried it, still have
problems
> (not sure it's still the trigger order), but the trigger order is
still
> strange for me:
>
No ideas, sorry. Maybe move over to the [HACKERS] list for more
professional support.
Regards, Christoph
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-08-13 17:20 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-13 10:11 Order of triggers - totally lost SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:54 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 15:40 ` SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 15:55 ` Josh Berkus <josh@agliodbs.com>
2003-08-13 16:10 ` SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 17:16 ` Josh Berkus <josh@agliodbs.com>
2003-08-13 16:20 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 17:20 ` SZŰCS Gábor <surrano@mailbox.hu>
2003-08-13 14:22 Re: Order of triggers - totally lost Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox