X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 053E13A5AD6 for ; Fri, 28 Jan 2005 17:17:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71833-09 for ; Fri, 28 Jan 2005 17:17:29 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 8A5633A5E4C for ; Fri, 28 Jan 2005 17:17:28 +0000 (GMT) Received: (qmail 31872 invoked from network); 28 Jan 2005 17:17:27 -0000 Received: from host-209.149.56.238.nashville.net (HELO ?10.0.1.2?) (209.149.56.238) by 0 with SMTP; 28 Jan 2005 17:17:27 -0000 In-Reply-To: <200501272141.49531.josh@agliodbs.com> References: <200501272141.49531.josh@agliodbs.com> Mime-Version: 1.0 (Apple Message framework v619.2) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: Content-Transfer-Encoding: 7bit Cc: PgSQL - Performance From: Thomas F.O'Connell Subject: Re: Triggers During COPY Date: Fri, 28 Jan 2005 11:17:23 -0600 To: josh@agliodbs.com X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/586 X-Sequence-Number: 10276 As far as dropping/recreating triggers, there seem to be two strategies: 1. Perform the drop-import-create operation in a transaction, thereby guaranteeing the accuracy of the counts but presumably locking the table during the operation, which could take many minutes (up to an hour or two) in extreme cases. 2. Drop the triggers, import, create the triggers, and update with the import count, recognizing that other updates could've occurred without accumulating updates during the import process, then later (nightly, maybe?) do a full update to recalibrate the counts. In this case the count( * ) involved could also lock the table for a bit pending the sequential scan(s) if the update is performed in a transaction. Otherwise, again, there is a realistic possibility of inaccurate counts occurring and persisting between calibrations. Is there a best practice anywhere here? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 27, 2005, at 11:41 PM, Josh Berkus wrote: > Thomas, > >> Would it be absurd to drop the triggers during import and recreate >> them >> afterward and update the counts in a summ> ary update based on >> information from the import process? > > That's what I'd do. > > -- > --Josh > > Josh Berkus > Aglio Database Solutions > San Francisco