Обсуждение: 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