Обсуждение: Triggers and COPY
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
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
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
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 #