Re: Triggers During COPY

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