Re: Problems with disabling triggers in Postgres 7.3.9

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(at)archonet(dot)com]
Sent: Thursday, March 09, 2006 10:56 AM
To: Flávio Suguimoto
Cc: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Richard Huxton
Sent: Thursday, March 09, 2006 11:42 AM
To: Flávio Suguimoto
Cc: pgsql-sql(at)postgresql(dot)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

=?iso-8859-1?Q?Fl=E1vio_Suguimoto?= <flavio(dot)suguimoto(at)pragyatechnologies(dot)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