Обсуждение: Problems with disabling triggers in Postgres 7.3.9
Hi all, I have a problem in a trigger that disable all the triggers of a table. This error occurs randomly and my guess is that occurs when i have a lot of concurrents inserts in the table participation. The error is : RelationBuildTriggers: 2 record(s) not found for rel participation I search all the web and not found a solution... Please help me... This is my trigger code : CREATE OR REPLACE FUNCTION buy4tickets() RETURNS "trigger" AS ' DECLAREmysequence bigint;idticket2 bigint;idticket3 bigint;idticket4 bigint; idticketservice bigint;idticketservice1 bigint;idticketservice2 bigint;idticketservice3 bigint;idticketservice4 bigint;tablenamevarchar;ticketnumberid2 varchar;ticketnumberid3 varchar;ticketnumberid4 varchar; BEGINidticketservice1 := 15;idticketservice2 := 16;idticketservice3 := 17;idticketservice4 := 18; tablename := ''participation''; RAISE NOTICE ''idticketservice1 = % '', idticketservice1;RAISE NOTICE ''idticketservice2 = % '', idticketservice2;RAISE NOTICE''idticketservice3 = % '', idticketservice3;RAISE NOTICE ''idticketservice4 = % '', idticketservice4; IF NEW.fk_id_ticket IS NOT NULL THEN SELECT INTO idticketservice, mysequence fk_id_ticket, sequence FROM ticket WHERE id = NEW.fk_id_ticket; RAISE NOTICE ''idticketservice = % '', idticketservice; IF idticketservice = idticketservice1 THEN RAISE NOTICE ''idticketservice1 = idticketservice ''; RAISE NOTICE ''mysequence = % '', mysequence; SELECT INTO idticket2,ticketnumberid2 id,numberid FROM ticket WHERE fk_id_ticket = idticketservice2::int8 AND sequence = mysequence::int8; RAISE NOTICE ''idticket2 = % '', idticket2; SELECT INTO idticket3,ticketnumberid3 id,numberid FROM ticket WHERE fk_id_ticket = idticketservice3::int8 AND sequence = mysequence::int8; RAISE NOTICE ''idticket3 = % '', idticket3; SELECT INTO idticket4,ticketnumberid4 id,numberid FROM ticket WHERE fk_id_ticket = idticketservice4::int8 AND sequence = mysequence::int8; RAISE NOTICE ''idticket4 = % '', idticket4; EXECUTE ''update pg_class set reltriggers=0 where relname = '' || quote_literal(tablename); IF idticket2 IS NOT NULL THEN EXECUTE ''insert into participation'' || ''(ani, dnis, fk_id_participation, fk_id_ticket, fk_id_service, datetimepart, status, ticketsequence, fk_id_ticketservice, ticketnumberid)'' || '' values( '' || quote_literal(NEW.ani)|| '','' || quote_literal(NEW.dnis) || '','' || NEW.fk_id_participation || '',''|| idticket2 || '','' || quote_literal(NEW.fk_id_service) || '','' || quote_literal(NEW.datetimepart) || '','' || NEW.status|| '',''|| mysequence || '','' || idticketservice2 ||'','' || quote_literal(ticketnumberid2) ||'')''; END IF; IF idticket3 IS NOT NULL THEN EXECUTE ''insert into participation'' || ''(ani, dnis,fk_id_participation, fk_id_ticket, fk_id_service, datetimepart, status, ticketsequence, fk_id_ticketservice, ticketnumberid)'' || '' values( '' || quote_literal(NEW.ani)|| '','' || quote_literal(NEW.dnis) || '','' || NEW.fk_id_participation || '',''|| idticket3 || '','' || quote_literal(NEW.fk_id_service) || '','' || quote_literal(NEW.datetimepart) || '','' || NEW.status || '',''|| mysequence || '','' || idticketservice3 ||'','' || quote_literal(ticketnumberid3) ||'')''; END IF; IF idticket4 IS NOT NULL THEN EXECUTE ''insert into participation'' || ''(ani, dnis,fk_id_participation, fk_id_ticket, fk_id_service, datetimepart, status, ticketsequence, fk_id_ticketservice, ticketnumberid)'' || '' values( '' || quote_literal(NEW.ani)|| '','' || quote_literal(NEW.dnis) || '','' || NEW.fk_id_participation || '',''|| idticket4 || '','' || quote_literal(NEW.fk_id_service) || '','' || quote_literal(NEW.datetimepart) || '','' || NEW.status || '',''|| mysequence || '','' || idticketservice4 ||'','' || quote_literal(ticketnumberid4) ||'')''; END IF; EXECUTE ''update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname = '' || quote_literal(tablename); END IF; END IF; RETURN NULL; END; ' LANGUAGE 'plpgsql' VOLATILE;
Flávio Suguimoto wrote: > Hi all, > > I have a problem in a trigger that disable all the triggers of a table. This > error occurs randomly and my guess is that occurs when i have a lot of > concurrents inserts in the table participation. > > The error is : RelationBuildTriggers: 2 record(s) not found for rel > participation I don't know what's involved in this particular bug, but the short answer is: don't update system catalogs directly. Have your triggers cope with the situation where you don't want to fire them in certain cases. System catalogs are delicate stuff; there are some caches that must be maintained in a coherent manner. Usually the catalogs do not follow MVCC rules to the letter. The "UPDATE pg_class" was used by pg_dump at some point, but it was only a hack and I wouldn't expect it to work correctly when multiple processes are involved. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Thanks Alvaro, I know that is a hack but i don't have (that i know) other way to disable a trigger. The reason that i need to disable is that trigger will be called recursively. Let explain better my case, the trigger that i sent the code is called after inserts in table participation but itself insert 3 more records in the participation table. If i don't disable the trigger it will be calling recursively... There is another walk-around to avoi it? regards, Flávio Suguimoto -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Alvaro Herrera Sent: Thursday, March 09, 2006 10:56 AM To: Flávio Suguimoto Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto wrote: > Hi all, > > I have a problem in a trigger that disable all the triggers of a table. This > error occurs randomly and my guess is that occurs when i have a lot of > concurrents inserts in the table participation. > > The error is : RelationBuildTriggers: 2 record(s) not found for rel > participation I don't know what's involved in this particular bug, but the short answer is: don't update system catalogs directly. Have your triggers cope with the situation where you don't want to fire them in certain cases. System catalogs are delicate stuff; there are some caches that must be maintained in a coherent manner. Usually the catalogs do not follow MVCC rules to the letter. The "UPDATE pg_class" was used by pg_dump at some point, but it was only a hack and I wouldn't expect it to work correctly when multiple processes are involved. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Hi Richard, I have that trigger running on AFTER INSERT of participation table. That trigger inserts 3 new record for each line i inserted in participation. I guess the problem is in these two statement: EXECUTE ''update pg_class set reltriggers=0 where relname = '' || quote_literal(tablename); EXECUTE ''update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname = '' || quote_literal(tablename); These statement is a walk-around to disable and enable the trigger on a table and i use this to avoid the trigger be called recursively. My question is there is another to do this trigger avoiding need to disable/enable the triggers? Or there is an way to solve this problem with RelationBuildTriggers? regards, Flávio Suguimoto -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: Thursday, March 09, 2006 10:56 AM To: Flávio Suguimoto Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto wrote: > Hi all, > > I have a problem in a trigger that disable all the triggers of a table. This > error occurs randomly and my guess is that occurs when i have a lot of > concurrents inserts in the table participation. > > The error is : RelationBuildTriggers: 2 record(s) not found for rel > participation Well, at the very least you should get an exclusive write-lock on the table "participation" before turning its triggers off. However, I'm doubtful that you really want to do that in any case. Can I ask what problem you are trying to solve? Oh, and upgrade to 7.3.14 too - you're missing 5 sets of bug-fixes. -- Richard Huxton Archonet Ltd
Flávio Suguimoto wrote: > Hi Richard, > > I have that trigger running on AFTER INSERT of participation table. That > trigger inserts 3 new record for each line i inserted in participation. Yes, but WHY? What problem are you trying to solve. Presumably there is some difference between the first "participation" row and the other 3 - the status, the ticket-number, something. Test for that difference and you'll know whether you'll need to insert those 3 extra rows or not. -- Richard Huxton Archonet Ltd
Thanks Richard, Until the last version of my application i couldn't know which record is different of another. But i saw that someone created a new column and i guess that i could know if its the first one or ther other 3... Thank you very much... -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Richard Huxton Sent: Thursday, March 09, 2006 11:42 AM To: Flávio Suguimoto Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto wrote: > Hi Richard, > > I have that trigger running on AFTER INSERT of participation table. That > trigger inserts 3 new record for each line i inserted in participation. Yes, but WHY? What problem are you trying to solve. Presumably there is some difference between the first "participation" row and the other 3 - the status, the ticket-number, something. Test for that difference and you'll know whether you'll need to insert those 3 extra rows or not. -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com> writes: > EXECUTE ''update pg_class set reltriggers = count(*) from pg_trigger where > pg_class.oid=tgrelid and relname = '' || quote_literal(tablename); This command is just plain wrong, because the aggregation is done across uncertain scope. Something like update pg_class set reltriggers = (select count(*) from pg_trigger where pg_class.oid=tgrelid) where relname = 'foo' would at least not run the risk of assigning wrong counts. You still have the issue that the commands will touch every table with a given name; there needs to be some thought about schemas here. In general though I agree with Alvaro's comment that touching system catalogs directly is bad practice. You should update to a PG version that has ALTER TABLE DISABLE TRIGGER, and use that. regards, tom lane