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
'
DECLARE
mysequence bigint;
idticket2 bigint;
idticket3 bigint;
idticket4 bigint;
idticketservice bigint;
idticketservice1 bigint;
idticketservice2 bigint;
idticketservice3 bigint;
idticketservice4 bigint;
tablename varchar;
ticketnumberid2 varchar;
ticketnumberid3 varchar;
ticketnumberid4 varchar;
BEGIN
idticketservice1 := 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;
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
