Re: ALTER TABLE ... REPLACE WITH

On Thu, 2011-01-20 at 13:14 -0500, Noah Misch wrote:

> When DDL has taken AccessExclusiveLock and a query waits for it, it's the Right
> Thing for that query to wake up and proceed based on the complete, final state
> of that committed DDL. Aside from the waiting itself, the query should behave
> as though it started after the DDL completed.
>
> In my example, the SELECT silently reads data from a table named "old_t". What
> if that were an INSERT? The data falls in the wrong table.
>
> > 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..
>
> Indeed, Heikki's suggestion and my patch would not do such verification. I
> can't see detecting and blocking some patterns of ALTER TABLE RENAME or DROP
> ...; CREATE ...; than we allow today. Those need to stay open-ended, with the
> user responsible for choosing well. So, what's the right concurrent behavior
> around use of those statements? I answer that above.
>
> That said, I see utility in a feature that compares two tables, swaps them if
> similar, and fixes up foreign keys. Having such a feature does not justify
> wrong concurrent behavior around ALTER TABLE RENAME. Having right concurrent
> behavior around ALTER TABLE RENAME does not remove the utility of this feature.
> We should do both.

I agree that the DDL behaviour is wrong and should be fixed. Thank you
for championing that alternative view.

Swapping based upon names only works and is very flexible, much more so
than EXCHANGE could be.

A separate utility might be worth it, but the feature set of that should
be defined in terms of correctly-working DDL behaviour. It's possible
that no further requirement exists. I remove my own patch from
consideration for this release.

I'll review your patch and commit it, problems or objections excepted. I
haven't looked at it in any detail.

Having said that, writing the patch did nothing to convince me this was
the correct approach. Reviews should be reviews, they are not an
opportunity to provide your own alternate version of a patch. That just
confuses things and creates a competitive, not a cooperative
environment. Authors do need to listen to reviewers, so I hope I'm
demonstrating that here.

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