Re: ALTER TABLE ... REPLACE WITH

On Wed, 2010-12-15 at 10:54 +0100, Csaba Nagy wrote:
> On Tue, 2010-12-14 at 14:36 -0500, Robert Haas wrote:
> > > Well, you have to do that for DROP TABLE as well, and I don't see any
> > > way around doing it for REPLACE WITH.
> >
> > Sure, but in Simon's proposal you can load the data FIRST and then
> > take a lock just long enough to do the swap. That's very different
> > from needing to hold the lock during the whole data load.
>
> Except Simon's original proposal has this line in it:
>
> * "new_table" is TRUNCATEd.
>
> I guess Simon mixed up "new_table" and "old_table", and the one which
> should get truncated is the replaced one and not the replacement,
> otherwise it doesn't make sense to me.

What I meant was...

REPLACE TABLE target WITH source;

* target's old rows are discarded
* target's new rows are all of the rows from "source".
* source is then truncated, so ends up empty

Perhaps a more useful definition would be

EXCHANGE TABLE target WITH source;

which just swaps the heap and indexes of each table.
You can then use TRUNCATE if you want to actually destroy data.

I will go with that unless we have other objections.

> BTW, I would have also used such a feature on multiple occasions in the
> past and expect I would do in the future too.
>
> Cheers,
> Csaba.
>
>

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