Triggers and COPY

[Ericson Smith]

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

[Shridhar Daithankar]

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

[Richard Huxton]

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

[Jan Wieck]

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