Re: ALTER TABLE ... REPLACE WITH

On Wed, 2011-01-19 at 17:46 -0500, Noah Misch wrote:

> First, I'd like to note that the thread for this patch had *four* "me-too"
> responses to the use case. That's extremely unusual; the subject is definitely
> compelling to people. It addresses the bad behavior of natural attempts to
> atomically swap two tables in the namespace:
>
> psql -c "CREATE TABLE t AS VALUES ('old'); CREATE TABLE new_t AS VALUES ('new')"
> psql -c 'SELECT pg_sleep(2) FROM t' & # block the ALTER or DROP briefly
> sleep 1 # give prev time to take AccessShareLock
>
> # Do it this way, and the next SELECT gets data from the old table.
> #psql -c 'ALTER TABLE t RENAME TO old_t; ALTER TABLE new_t RENAME TO t' &
> # Do it this way, and get: ERROR: could not open relation with OID 41380
> psql -c 'DROP TABLE t; ALTER TABLE new_t RENAME TO t' &
>
> psql -c 'SELECT * FROM t' # I get 'old' or an error, never 'new'.
> psql -c 'DROP TABLE IF EXISTS t, old_t, new_t'
>
> by letting you do this instead:
>
> psql -c "CREATE TABLE t AS VALUES ('old'); CREATE TABLE new_t AS VALUES ('new')"
> psql -c 'SELECT pg_sleep(2) FROM t' & # block the ALTER or DROP briefly
> sleep 1 # give prev time to take AccessShareLock
>
> psql -c 'EXCHANGE TABLE new_t TO t &
>
> psql -c 'SELECT * FROM t' # I get 'new', finally!
> psql -c 'DROP TABLE IF EXISTS t, new_t'
>
> I find Heikki's (4D07C6EC(dot)2030200(at)enterprisedb(dot)com) suggestion from the thread
> interesting: can we just make the first example work? Even granting that the
> second syntax may be a useful addition, the existing behavior of the first
> example is surely worthless, even actively harmful. I tossed together a
> proof-of-concept patch, attached, that makes the first example DTRT. Do you see
> any value in going down that road?

As I said previously on the thread you quote, having this happen
implicitly is not a good thing, and IMHO, definitely not "the right
thing".

Heikki's suggestion, and your patch, contain no checking to see whether
the old and new tables are similar. If they are not similar then we have
all the same problems raised by my patch. SQL will suddenly fail because
columns have ceased to exist, FKs suddenly disappear etc..

I don't see how having a patch helps at all. I didn't think it was the
right way before you wrote it and I still disagree now you've written
it.

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