Problems with disabling triggers in Postgres 7.3.9
От | Flávio Suguimoto |
---|---|
Тема | Problems with disabling triggers in Postgres 7.3.9 |
Дата | |
Msg-id | HNEBJEEMBNKOOBGHINHKCECMCAAA.flavio.suguimoto@pragyatechnologies.com обсуждение исходный текст |
Ответы |
Re: Problems with disabling triggers in Postgres 7.3.9
|
Список | pgsql-sql |
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;
В списке pgsql-sql по дате отправления: