Thread: Stupid question about triggers

Hi,

I have found myself in a situation where I need to quickly delete rows
from a production database. Unfortunately table for the rows to be
deleted have triggers which results massive chain of update operations
on other tables. I do not wish those to happen as I'm about to delete
concerned rows from the other tables as well. (7.4.7)

Would

begin work
select reltriggers from pg_class where relname='foo' for update;
update pg_class set reltriggers=0 where relname='foo';
delete from foo where ...
update pg_class set reltriggers=original value where relname='foo';
commit

work?

Or
begin work
select reltriggers from pg_class where relname='foo';
update pg_class set reltriggers=0 where relname='foo';
delete from foo where ...
update pg_class set reltriggers=original value where relname='foo';
commit

as I'm quite sure the number of triggers for that table will not change
while I'm doing the deletion.

Thank you.

On Friday 09 September 2005 09:20, Mauri Sahlberg wrote:
> Hi,
>
> I have found myself in a situation where I need to quickly delete rows
> from a production database. Unfortunately table for the rows to be
> deleted have triggers which results massive chain of update operations
> on other tables. I do not wish those to happen as I'm about to delete
> concerned rows from the other tables as well. (7.4.7)
>
<snip>

Your proposed syntax looks about correct, though I'd suggest trying on a test
system first if possible.  I would caution though that this type of hacking
about is going to break any referential integrity your database once had with
this data.

If you really are going to be deleting data that has a bunch of related data
in other tables, istm that that should be handled by some foriegn keys, so
I'm a bit suspicious of your db schema based on this email.  Just my .02.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:

>On Friday 09 September 2005 09:20, Mauri Sahlberg wrote:
>
>
>>Hi,
>>
>>I have found myself in a situation where I need to quickly delete rows
>>from a production database. Unfortunately table for the rows to be
>>deleted have triggers which results massive chain of update operations
>>on other tables. I do not wish those to happen as I'm about to delete
>>concerned rows from the other tables as well. (7.4.7)
>>
>>
>>
><snip>
>
>Your proposed syntax looks about correct, though I'd suggest trying on a test
>system first if possible.  I would caution though that this type of hacking
>about is going to break any referential integrity your database once had with
>this data.
>
>
Thanks.

>If you really are going to be deleting data that has a bunch of related data
>in other tables, istm that that should be handled by some foriegn keys, so
>I'm a bit suspicious of your db schema based on this email.  Just my .02.
>
Related, yes, but not referenced. I have written the trigger procedures
and I am mainly responsible for the idiotic schema. You will never make
a good database with description of  Cobol data structures and
requirement that the new systems will do exactly the same things that
the old one did without actually understanding what the old system was
meant in business wise to do. And of course while we were implementing
the new system the business requirements changed and we ended up doing
something that works but is completely laggard in some exceptional cases.

Trigger procedures on that table cause a recalculation of values in the
other table every time the rows in the table in question change. These
cause another trigger procedure to update another table and yet another
trigger procedure on that table causes third table to be updated.
Unfortunately if the row to be deleted contained data from a month long
time past, it would make an avalanche of recalculations from past to the
present, most of them duplicated and unnecessary.

The first trigger procedure would ultimately lead the first "related"
table to contain rows with zero values for the company in question. What
I will next do is to delete the rows from the first triggered table but
not with single delete as it would again cause an avalanche where the
second and third triggered table would be recalculated again and again
for same months. I will go backwards in time and use single deletes to
avoid calculating several rows again and again. Those months that have
no rows will not cause triggers and will not cause recalculation of
other tables.

If this was an update operation I would be out of luck and would
probably have to wait ages for the chain to complete. I tried the delete
with triggers enabled and after 10 hours of calculation aborted it.

And yes, I will test this first in a test database.