pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (6+ messages)

Message-ID: <200501280948.31246.josh@agliodbs.com>
Permalink:  ../200501280948.31246.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200501280948.31246.josh@agliodbs.com

 · 

reply

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