Thread: Order of triggers - totally lost

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 --
irrelevantB_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 -------------------------------

> 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

-----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-----

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)*
arethere generic recommendations what kind of things to put in  before and after triggers?* how about FOR EACH
STATEMENTtriggers?  (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.

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

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 -------------------------------

-----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-----

I need to change column data type from integer to float8
That mean to droping table and recreate a new one and can lost the original
object id.
Do i need to recreate all views and triggers that relate to that table?
if that so, is there anyway to do that without touching views and triggers?


Thanks,
yudie

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 BerkusAglio Database SolutionsSan Francisco

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 -------------------------------

Yudie writes:

> I need to change column data type from integer to float8
> That mean to droping table and recreate a new one and can lost the original
> object id.
> Do i need to recreate all views and triggers that relate to that table?
> if that so, is there anyway to do that without touching views and triggers?

Not really.

Perhaps it will be easier if you do a dump of the affected objects, edit
the dump file, and reload.

-- 
Peter Eisentraut   peter_e@gmx.net