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