From: Nick Fankhauser - Doxpop <nickf@doxpop.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: using disable-triggers in pg_dump
Date: Thu, 11 Mar 2004 15:53:23 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGEEHNLJAA.nickf@doxpop.com> (raw)
Hi-
One of our processes requires merging data from a staging database into a
production database. I'd like to do this without taking the production
database off-line, but I'm running into a problem with triggers.
One of the tables is populated and maintained solely by triggers set up on
three other tables, so as those tables are being copied into the production
database, many triggers are running, thus slowing the process considerably.
Since the data created by the triggers already exists in this table in the
database to be merged, if I could copy all of the data in with the triggers
off, the process would be much faster and the end result identical.
However, I need those triggers on to handle other updates & inserts in the
production database unless it is taken off-line for duration of the merge
process.
I've noted the --disable-triggers option for pg_dump, but it appears that
this option would shut off the triggers for all sessions, not just the
session in which I am doing the copy.
The command that pg_dump generates looks like this:
UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'court_config'::pg_catalog.regclass;
Can someone confirm that this is indeed affecting the database for all
sessions and if so, suggest a way to turn off the triggers just for the
session doing the data copy?
Thanks
-Nick
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: nickf@doxpop.com
Subject: Re: using disable-triggers in pg_dump
In-Reply-To: <NEBBLAAHGLEEPCGOBHDGEEHNLJAA.nickf@doxpop.com>
References: <NEBBLAAHGLEEPCGOBHDGEEHNLJAA.nickf@doxpop.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox