Thread: using disable-triggers in pg_dump

using disable-triggers in pg_dump

From
"Nick Fankhauser - Doxpop"
Date:
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/



Re: using disable-triggers in pg_dump

From
Bruce Momjian
Date:
Nick Fankhauser - Doxpop wrote:
> 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?

One trick I have heard about is starting a multi-statement transaction,
turning of triggers, which will only be seen by that transaction, do
some work, reenable the triggers, then commit the transaction.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073