pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
using disable-triggers in pg_dump
2+ messages / 2 participants
[nested] [flat]

* using disable-triggers in pg_dump
@ 2004-03-11 20:53 Nick Fankhauser - Doxpop <nickf@doxpop.com>
  2004-03-13 23:14 ` Re: using disable-triggers in pg_dump Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Nick Fankhauser - Doxpop @ 2004-03-11 20:53 UTC (permalink / raw)
  To: pgsql-admin

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/





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: using disable-triggers in pg_dump
  2004-03-11 20:53 using disable-triggers in pg_dump Nick Fankhauser - Doxpop <nickf@doxpop.com>
@ 2004-03-13 23:14 ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Bruce Momjian @ 2004-03-13 23:14 UTC (permalink / raw)
  To: nickf@doxpop.com; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-03-13 23:14 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-11 20:53 using disable-triggers in pg_dump Nick Fankhauser - Doxpop <nickf@doxpop.com>
2004-03-13 23:14 ` Bruce Momjian <pgman@candle.pha.pa.us>

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