469,332 Members | 6,649 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,332 developers. It's quick & easy.

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
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
3 5042
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
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
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.

Similar topics

3 posts views Thread by Keith | last post: by
7 posts views Thread by Sjaak van Esdonk | last post: by
3 posts views Thread by Jonathan Bishop | last post: by
1 post views Thread by jason_s_ford | last post: by
1 post views Thread by James Robinson | last post: by
3 posts views Thread by rick | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.