PostgreSQL SQL - Problems with disabling triggers in Postgres 7.3.9

Author

Problems with disabling triggers in Postgres 7.3.9

Flávio Suguimoto

2006-03-09, 7:26 am

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 : RelationBuildTrigger
s: 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,ticketnumb
erid2 id,numberid FROM ticket WHERE
fk_id_ticket = idticketservice2::in
t8 AND sequence = mysequence::int8;
RAISE NOTICE ''idticket2 = % '', idticket2;

SELECT INTO idticket3,ticketnumb
erid3 id,numberid FROM ticket WHERE
fk_id_ticket = idticketservice3::in
t8 AND sequence = mysequence::int8;
RAISE NOTICE ''idticket3 = % '', idticket3;

SELECT INTO idticket4,ticketnumb
erid4 id,numberid FROM ticket WHERE
fk_id_ticket = idticketservice4::in
t8 AND sequence = mysequence::int8;
RAISE NOTICE ''idticket4 = % '', idticket4;

EXECUTE ''update pg_class set reltriggers=0 where relname = '' ||
quote_literal(tablen
ame);

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(ticket
numberid2) ||'')'';
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(ticket
numberid3) ||'')'';
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(ticket
numberid4) ||'')'';
END IF;

EXECUTE ''update pg_class set reltriggers = count(*) from pg_trigger
where pg_class.oid=tgrelid and relname = '' || quote_literal(tablen
ame);

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

Alvaro Herrera

2006-03-09, 9:24 am

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 : RelationBuildTrigger
s: 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

Flávio Suguimoto

2006-03-09, 9:24 am

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 : RelationBuildTrigger
s: 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Flávio Suguimoto

2006-03-09, 9:24 am

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(tablen
ame);

EXECUTE ''update pg_class set reltriggers = count(*) from pg_trigger where
pg_class.oid=tgrelid and relname = '' || quote_literal(tablen
ame);

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
RelationBuildTrigger
s?

regards,
Flávio Suguimoto

-----Original Message-----
From: Richard Huxton & #91;mailto:dev@archo
net.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 : RelationBuildTrigger
s: 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Richard Huxton

2006-03-09, 9:24 am

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

2006-03-09, 9:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2006-03-09, 11:24 am

=?iso-8859-1?Q?Fl=E1vio_Suguimoto?= <flavio. suguimoto@pragyatech
nologies.com> writes:
> EXECUTE ''update pg_class set reltriggers = count(*) from pg_trigger where
> pg_class.oid=tgrelid and relname = '' || quote_literal(tablen
ame);

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links