Re: ALTER TYPE 0: Introduction; test cases

On Tue, Jan 11, 2011 at 09:24:46AM +0000, Simon Riggs wrote:
> On Sun, 2011-01-09 at 16:59 -0500, Noah Misch wrote:
>
> > This begins the patch series for the design I recently proposed[1] for avoiding
> > some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. I'm posting these
> > patches today:
>
> These sound very good.

Thanks.

> I have a concern that by making the ALTER TABLE more complex that we
> might not be able to easily tell if a rewrite happens, or not.
>
> Perhaps we should add a WITHOUT REWRITE clause? That would allow a user
> to specify that they do not wish a rewrite, so if the AT requires them
> to have one it would then fail.

These changes do make it harder to guess how much work the ALTER TABLE will do.
Indeed, about 1/4 of my own guesses prior to writing were wrong. Something like
WITHOUT REWRITE might be the way to go, though there are more questions: if it
does not rewrite, does it scan the table? Which indexes, if any, does it
rebuild? Which foreign key constraints, if any, does it recheck? With patch 0,
you can answer all these questions by enabling DEBUG1 messages and trying the
command on your test system. For this reason, I did consider adding a VERBOSE
clause to show those messages at DETAIL, rather than unconditionally showing
them at DEBUG1. In any case, if a WITHOUT REWRITE like you describe covers the
important question, it's certainly easy enough to implement.

> You might point out I didn't do anything like that for my ALTER TABLE
> patch, and I would agree with you. WITHOUT ACCESS EXCLUSIVE LOCK might
> be an option here also.

True. At least we could completely document the lock choices on the ALTER TABLE
reference page. The no-rewrite cases are defined at arms length from ALTER
TABLE, and users can define their own, so it's a tougher fit.