Thread: no cascade triggers?
Hello, triggers in PostgreSQL are cascading by default. From section 34.1. of the 8.2 manual: "If a trigger function executes SQL commands then these commands may fire triggers again. This is known as cascadingtriggers. There is no direct limitation on the number of cascade levels. It is possible for cascades to causea recursive invocation of the same trigger; for example, an INSERT trigger might execute a command that insertsan additional row into the same table, causing the INSERT trigger to be fired again. It is the trigger programmer'sresponsibility to avoid infinite recursion in such scenarios." On the italian list we're discussing a case were a user reportedly worked around this (i.e. got rid of unwanted cascading calls) by writing an on insert trigger procedure something on the lines of: ALTER TABLE tab DISABLE TRIGGER USER; -- do more inserts into the same table ALTER TABLE tab ENABLE TRIGGER USER; While this reporetedly worked well in 8.2.1 it does not in 8.2.4 resulting in an error: ERROR: relation "distinta_base1" is being used by active queries in this session Stato SQL: 55006 Now -- while we agree that disabling a trigger from inside itself is a somewhat strange thing to do, we cannot see a good and easy solution to the problem (of avoiding cascading trigger calls). General question: would a "no cascade" clause for triggers be a todo item? Special question: any recomendations for our user? He has a somewhat large number of triggers that do the alter table trick - working around it by means of some context-based logic would be a lot of work... Bye :) Chris.
On Jun 26, 2007, at 10:04 , Chris Mair wrote: > Hello, > > > On the italian list we're discussing a case were a user reportedly > worked around this (i.e. got rid of unwanted cascading calls) by > writing an on insert trigger procedure something on the lines of: > > ALTER TABLE tab DISABLE TRIGGER USER; > -- do more inserts into the same table > ALTER TABLE tab ENABLE TRIGGER USER; > > While this reporetedly worked well in 8.2.1 it does not in 8.2.4 > resulting in an error: > > ERROR: relation "distinta_base1" is being used by active queries > in this session Stato SQL: 55006 > > Now -- while we agree that disabling a trigger from inside itself > is a somewhat strange thing to do, we cannot see a good and easy > solution to the problem (of avoiding cascading trigger calls). > > General question: would a "no cascade" clause for triggers be a > todo item? Instead of putting a trigger on the table, put a rule+trigger on a wrapper view. Cheers, M
Chris Mair <chris@1006.org> writes:
> On the italian list we're discussing a case were a user reportedly
> worked around this (i.e. got rid of unwanted cascading calls) by
> writing an on insert trigger procedure something on the lines of:
> ALTER TABLE tab DISABLE TRIGGER USER;
> -- do more inserts into the same table
> ALTER TABLE tab ENABLE TRIGGER USER;
> While this reporetedly worked well in 8.2.1 it does not in 8.2.4
> resulting in an error:
> ERROR: relation "distinta_base1" is being used by active queries
> in this session Stato SQL: 55006
We could possibly re-allow that (see the comments in AlterTable())
but it seems like an ugly and inefficient technique that we shouldn't
be encouraging. (The implications for system catalog bloat alone
seem enough reason to not recommend this.) Isn't there a cleaner way
to design his application? Maybe refactor the schema to avoid the
recursion in the first place? Or add an "insert origin" column to the
table so that the trigger can easily detect trigger-inserted rows and
do nothing?
regards, tom lane
Tom Lane wrote:
We could possibly re-allow that (see the comments in AlterTable()) but
it seems like an ugly and inefficient technique that we shouldn't be
encouraging. (The implications for system catalog bloat alone seem
enough reason to not recommend this.) Isn't there a cleaner way to
design his application? Maybe refactor the schema to avoid the
recursion in the first place? Or add an "insert origin" column to the
table so that the trigger can easily detect trigger-inserted rows and do
nothing?
Is not so simple.
I tried to follow the A.M. (agentm@temactionfaction.com) suggestion:
"Instead of putting a trigger on the table, put a rule+trigger on a
wrapper view"
But in my tests I always get the same SQL error 55006.
To better explain my problem I attach one of my functions that is easy
to understand.
For each of my products I must have one main supplier and I may have
also some secondary suppliers.
Therefore for each of my articles
I have as many records in table ANAMAT.ART_FOR as the number of my
suppliers for that article.
The main supplier is identified by code 1 in the column ARF_PROGR_FORN.
Secondary suppliers are numbered as 2,3 and so on
If, for example, I want to promote one of the secondary suppliers (let's
say number 3)
to be the main one, I should issue the following update:
UPDATE ANAMAT.ART_FOR SET ARF_PROGR_FORN = 1 where Article=xx and
SUPPLIER=yy.....
The update fires the trigger that inovokes this function :
alter table anamat.art_for DISABLE TRIGGER USER;
1) update the ARF_PROGR_FORN from 3 to 999 for supplier yy
(temporary)
2) update the ARF_PROGR_FORN from 1 to 3 for supplier zz (the old
main one)
3) update the ARF_PROGR_FORN from 999 to 1 for supplier yy (the new
main supplier)
alter table anamat.art_for ENABLE TRIGGER USER;
in other words I switch the code for the two suppliers.
Following is the simplified code, which is working fine with version
8.2.1.
Without disabling triggers I would get an infinite loop of rule
firing...
How can I do the same thing without disabling triggers in version 8.2.4
?
CREATE TRIGGER art_for_upd BEFORE UPDATE ON anamat.art_forFOR EACH ROW EXECUTE PROCEDURE anamat.art_for_upd();
CREATE OR REPLACE FUNCTION anamat.art_for_upd() RETURNS trigger AS $$BEGIN if new.arf_progr_forn !=
old.arf_progr_forn then return new; end if;
if old.arf_progr_forn = 1 then raise exception 'You cannot change the main
supplier'; end if; if new.arf_progr_forn = 1 then alter table anamat.art_for DISABLE TRIGGER
USER; UPDATE ANAMAT.ART_FOR SET ARF_PROGR_FORN = 9999 WHERE ART = new.ART AND ARF_PROGR_FORN =
1 AND (CAPOCONTO <> new.CAPOCONTO OR CONTO <>
new.CONTO); UPDATE ANAMAT.ART_FOR SET ARF_PROGR_FORN = 1 WHERE ART = new.ART AND
ARF_PROGR_FORN=
old.arf_progr_forn;
UPDATE ANAMAT.ART_FOR SET ARF_PROGR_FORN =
old.arf_progr_forn WHERE ART = new.ART AND ARF_PROGR_FORN =
9999;
alter table anamat.art_for ENABLE TRIGGER USER; end if;return new; END;
$$ LANGUAGE plpgsql;
Regards
Villiam Manera
On Wed, Jun 27, 2007 at 11:37:01AM +0200, Manera, Villiam wrote: > To better explain my problem I attach one of my functions that is easy > to understand. > > For each of my products I must have one main supplier and I may have > also some secondary suppliers. > > Therefore for each of my articles > I have as many records in table ANAMAT.ART_FOR as the number of my > suppliers for that article. > > The main supplier is identified by code 1 in the column ARF_PROGR_FORN. > Secondary suppliers are numbered as 2,3 and so on > > If, for example, I want to promote one of the secondary suppliers (let's > say number 3) > to be the main one, I should issue the following update: To be honest, it seems to me your problem is that you're trying to use triggers for something they're not designed for. What seems to be infinitly easier is to write a stored proc that you use like so: SELECT promote_supplier( article, supplier ); No trigger necessary... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
> To be honest..No trigger necessary... I have 1300 triggers and 345 functions (53500 lines of code), most of them written 15 years ago for ALLBASE/SQL (the old Hewlett Packard relational dbms) and converted to pl/pgsql last year. I admit that some of them may look eccentric, but some of them are very old... Having short deadlines for the database migration we didn't have the time to study, understand and rewrite each one so we decided to just convert them since the language is very similar.. Ok, now we have more time, our boss is very happy that postgres performance is 15-20 times better than ALLBASE J so now we could afford the rewriting of functions and triggers :( . If there's no other trick or suggestion, I think I'll build my own home made "pg_trigger" relation containing the reference to all my triggers/functions and a flag for each of them that tells me if it's enabled or not. It could be useful IMHO to at least specify in the documentation the exact behaviour of Postgres ALTER TABLE ENABLE/DISABLE TRIGGER when applied from a trigger on the same table the trigger belong to. Here a more complex example: relation A : list of components of the fashion model (without colours) relation B : list of components of the fashion model for colours and sizes 1) One model inserted on relation A : * Fires then function 1 that inserts colours on relation B * Every recordinserted on B fires function 2 2) Function 2 : * Does some checks, calculations and finally updates the record just inserted with the right amount for every size of the model 3) ... does other non interesting things for the problem. Function 2 disables trigger before updating the relation B because there is another trigger that fires on the update of the q.ty of the size in relation B. Function 2 is invoked not only by a wrapper trigger on relation B, but sometimes directly by user programs. Sorry for the disturb, this is the last one. Villiam