From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Cc: Thomas F.O'Connell <tfo@sitening.com>
Subject: Re: Triggers During COPY
Date: Fri, 28 Jan 2005 09:48:31 -0800
Message-ID: <200501280948.31246.josh@agliodbs.com> (raw)
In-Reply-To: <0f81625232fe6b2256328804be50c7ae@sitening.com>
References: <f2a4e35d83b33da9b5d007d276df484a@sitening.com>
<200501272141.49531.josh@agliodbs.com>
<0f81625232fe6b2256328804be50c7ae@sitening.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, tfo@sitening.com
Subject: Re: Triggers During COPY
In-Reply-To: <200501280948.31246.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox