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
