pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with disabling triggers in Postgres 7.3.9
7+ messages / 4 participants
[nested] [flat]
* Problems with disabling triggers in Postgres 7.3.9
@ 2006-03-09 13:18 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-09 13:55 ` Re: Problems with disabling triggers in Postgres 7.3.9 Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: Flávio Suguimoto @ 2006-03-09 13:18 UTC (permalink / raw)
To: 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
'
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with disabling triggers in Postgres 7.3.9
2006-03-09 13:18 Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
@ 2006-03-09 13:55 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-03-09 14:04 ` Re: Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
0 siblings, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2006-03-09 13:55 UTC (permalink / raw)
To: Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with disabling triggers in Postgres 7.3.9
2006-03-09 13:18 Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-09 13:55 ` Re: Problems with disabling triggers in Postgres 7.3.9 Alvaro Herrera <alvherre@commandprompt.com>
@ 2006-03-09 14:04 ` Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
0 siblings, 0 replies; 7+ messages in thread
From: Flávio Suguimoto @ 2006-03-09 14:04 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with disabling triggers in Postgres 7.3.9
@ 2006-03-09 14:25 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-09 14:41 ` Re: Problems with disabling triggers in Postgres 7.3.9 Richard Huxton <dev@archonet.com>
2006-03-09 15:50 ` Re: Problems with disabling triggers in Postgres 7.3.9 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Flávio Suguimoto @ 2006-03-09 14:25 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with disabling triggers in Postgres 7.3.9
2006-03-09 14:25 Re: Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
@ 2006-03-09 14:41 ` Richard Huxton <dev@archonet.com>
2006-03-09 15:11 ` Re: Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2006-03-09 14:41 UTC (permalink / raw)
To: Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with disabling triggers in Postgres 7.3.9
2006-03-09 14:25 Re: Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-09 14:41 ` Re: Problems with disabling triggers in Postgres 7.3.9 Richard Huxton <dev@archonet.com>
@ 2006-03-09 15:11 ` Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
0 siblings, 0 replies; 7+ messages in thread
From: Flávio Suguimoto @ 2006-03-09 15:11 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems with disabling triggers in Postgres 7.3.9
2006-03-09 14:25 Re: Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
@ 2006-03-09 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2006-03-09 15:50 UTC (permalink / raw)
To: Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
=?iso-8859-1?Q?Fl=E1vio_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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-03-09 15:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-09 13:18 Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-09 13:55 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-03-09 14:04 ` Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-09 14:25 Re: Problems with disabling triggers in Postgres 7.3.9 Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-09 14:41 ` Richard Huxton <dev@archonet.com>
2006-03-09 15:11 ` Flávio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-09 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox