Re: ALTER TABLE ... REPLACE WITH

On Tue, 2010-12-14 at 21:35 +0200, Heikki Linnakangas wrote:
> On 14.12.2010 20:27, Simon Riggs wrote:
> > 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;
> >
> > Step (2) works, but any people queuing to access the table will see
> >    ERROR:  could not open relation with OID xxxxx
> 
> Could we make that work without error?

Possibly, and good thinking, but its effectively the same patch, just
syntax free since we still need to do lots of checking to avoid swapping
oranges with lemons.

I prefer explicit syntax because its easier to be certain that you've
got it right.

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