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 4ACD53A5E10 for ; Fri, 28 Jan 2005 17:49:36 +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 74585-02 for ; Fri, 28 Jan 2005 17:49:33 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id AF2143A5E4C for ; Fri, 28 Jan 2005 17:49:33 +0000 (GMT) X-EthosMedia-Virus-Scanned: no infections found Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6962024; Fri, 28 Jan 2005 09:51:15 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: Triggers During COPY Date: Fri, 28 Jan 2005 09:48:31 -0800 User-Agent: KMail/1.6.2 Cc: Thomas F.O'Connell References: <200501272141.49531.josh@agliodbs.com> <0f81625232fe6b2256328804be50c7ae@sitening.com> In-Reply-To: <0f81625232fe6b2256328804be50c7ae@sitening.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200501280948.31246.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/587 X-Sequence-Number: 10277 Thomas, > I forgot to mention that I'm running 7.4.6. The README includes the > caveat that pgmemcache is designed for use with 8.0. Well, you could always hire Sean to backport it. > 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. What other operations are ocurring on the table concurrent with the COPY? Copy isn't really intended to be run in parallel with regular insert/update on the same table, AFAIK. > 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. Alternately: bulk load the new rows into a "holding" table. Do counts on that table. Then, as one transaction, drop the triggers, merge the holding table with the live table and update the counts, and restore the triggers. Alternately: Move the copy out of triggers into middleware where you can deal with it more flexibly. Alternately: Resign yourself to the idea that keeping running statistics is incompatible with doing a fast bulk load, and buy faster/better hardware. -- Josh Berkus Aglio Database Solutions San Francisco