By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,299 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

Triggers and COPY

P: n/a
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
er**@did-it.com
Did-it.com
The Search Campaign Specialists
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

P: n/a
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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.