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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
