Re: Triggers and COPY

Richard Huxton wrote:

> On Thursday 25 September 2003 16:06, Shridhar Daithankar wrote:
>> Ericson Smith wrote:
>> > Hi,
>> >
>> > Is there any way to prevent a trigger from firing during a COPY
>> > operation?
>> >
>> > We have a case where we dump the records from a table, truncate it, and
>> > copy the records back in. However, there is a trigger on that table,
>> > which will insert a record in a logging table. Is there a way to prevent
>> > this trigger from firing during the COPY FROM process?
>>
>> Can you drop the trigger during copy? I don't know following will exactly
>> work but something like..
>>
>> begin
>> drop trigger
>> copy
>> recreate trigger
>> commit;
>>
>> could do trick for you..
>
> You might be able to do this with pg_restore too. That's got the ability to
> disable triggers.

You probably want to do it a little finer grained, though still the way
pg_restore does it.

The trick is to do it all inside a transaction. At the beginning you
update pg_class and set the column reltriggers to zero. Then you do the
COPY, restore the old value of reltriggers and commit.

You want to modify pg_class for the relation in question only because
this whole trick creates 2 dead tuples in pg_class, and at some point it
hurts to inflate pg_class with massive amounts of dead tuples.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #