Re: ALTER TABLE ... REPLACE WITH

On Tue, 2010-12-14 at 13:54 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > There are various applications where we want to completely replace the
> > contents of a table with new/re-calculated data.
>
> > It seems fairly obvious to be able to do this like...
> > 1. Prepare new data into "new_table" and build indexes
> > 2. Swap old for new
> > BEGIN;
> > DROP TABLE "old_table";
> > ALTER TABLE "new_table" RENAME to "old_table";
> > COMMIT;
>
> Why not
>
> BEGIN;
> TRUNCATE TABLE;
> ... load new data ...
> COMMIT;

The above is atomic, but not fast.

The intention is to produce an atomic swap with as small a lock window
as possible, to allow it to happen in real operational systems.

At the moment we have a choice of fast or atomic. We need both.

(Note that there are 2 utilities that already do this, but the
operations aren't supported in core Postgres).

> > What I propose is to write a function/command to allow this to be
> > explicitly achievable by the server.
>
> > ALTER TABLE "old_table"
> > REPLACE WITH "new_table";
>
> I don't think the cost/benefit ratio of this is anywhere near as good
> as you seem to think (ie, you're both underestimating the work involved
> and overstating the benefit). I'm also noticing a lack of specification
> as to trigger behavior, foreign keys, etc. The apparent intention to
> disregard FKs entirely is particularly distressing,

No triggers would be fired. All constraints that exist on "old_table"
must also exist on "new_table". As I said, lots of checks required, no
intention to add back doors.

("Disregard FKs" is the other project, not connected other than both are
operations on tables designed to improve manageability of large tables.)

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services