Thread

1. Triggers and COPY

   Ericson Smith <eric@did-it.com> — 2003-09-25T14:49:43Z

   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?
   
   Just a little more background, I tried to make the function a little 
   more intelligent with OLD and NEW, but in the case of an INSERT, there 
   is no OLD, so there is no comparison.
   
   Regards
   - Ericson Smith
   eric@did-it.com
   Did-it.com
   The Search Campaign Specialists
   
   
   

2. Re: Triggers and COPY

   Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-09-25T15:06:26Z

   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..
   
   HTH
   
     Shridhar
   
   
   

3. Re: Triggers and COPY

   Richard Huxton <dev@archonet.com> — 2003-09-25T15:30:34Z

   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.
   
   -- 
     Richard Huxton
     Archonet Ltd
   
   

4. Re: Triggers and COPY

   Jan Wieck <janwieck@yahoo.com> — 2003-09-29T20:09:05Z

   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@Yahoo.com #