X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CAC02D1C969 for ; Sat, 13 Mar 2004 23:14:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 89228-04 for ; Sat, 13 Mar 2004 19:14:30 -0400 (AST) Received: from candle.pha.pa.us (candle.pha.pa.us [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id 23EDCD1BA94 for ; Sat, 13 Mar 2004 19:14:27 -0400 (AST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id i2DNELD19905; Sat, 13 Mar 2004 18:14:21 -0500 (EST) From: Bruce Momjian Message-Id: <200403132314.i2DNELD19905@candle.pha.pa.us> Subject: Re: using disable-triggers in pg_dump In-Reply-To: To: nickf@doxpop.com Date: Sat, 13 Mar 2004 18:14:21 -0500 (EST) Cc: pgsql-admin X-Mailer: ELM [version 2.4ME+ PL108 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/215 X-Sequence-Number: 12784 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