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 A4920D1CCAD for ; Thu, 11 Mar 2004 20:53:16 +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 52760-09 for ; Thu, 11 Mar 2004 16:53:15 -0400 (AST) Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by svr1.postgresql.org (Postfix) with ESMTP id 9D4AAD1C9DB for ; Thu, 11 Mar 2004 16:53:08 -0400 (AST) Received: from nick (bilbo.ontko.com [199.164.165.101]) by shire.ontko.com (8.12.3/8.12.3/Debian-6.6) with SMTP id i2BKr6rs029937 for ; Thu, 11 Mar 2004 15:53:12 -0500 Reply-To: From: "Nick Fankhauser - Doxpop" To: "pgsql-admin" Subject: using disable-triggers in pg_dump Date: Thu, 11 Mar 2004 15:53:23 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/213 X-Sequence-Number: 12782 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/