Re: Avoiding rewrite in ALTER TABLE ALTER TYPE

On Thu, Dec 30, 2010 at 08:35:34PM -0500, Noah Misch wrote:
> On Thu, Dec 30, 2010 at 12:57:45AM -0500, Robert Haas wrote:
> > On Thu, Dec 30, 2010 at 12:24 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > > On Wed, Dec 29, 2010 at 11:14:37PM -0500, Robert Haas wrote:
> > >> I think for any pair of types (T1, T2) we should first determine
> > >> whether we can skip the scan altogether. ?If yes, we're done. ?If no,
> > >> then we should have a way of determining whether a verify-only scan is
> > >> guaranteed to be sufficient (in your terminology, the verification
> > >> scan is guaranteed to return either positive or error, not negative).
> > >> If yes, then we do a verification scan. ?If no, we do a rewrite.
> > >
> > > How would we answer the second question in general?
> >
> > I am not sure - I guess we'd need to design some sort of mechanism for that.
>
> Okay, here goes. Given...<snip>

That seems to be working decently. However, It turns out that changes like
text->varchar(8) and varchar(8)->varchar(4) don't fall into either of those
optimization categories. An implicit varchar length coercion will truncate
trailing blanks to make the string fit, so this legitimately requires a rewrite:

CREATE TEMP TABLE t (c) AS SELECT 'foo '::text;
SELECT c || '<-' FROM t;
ALTER TABLE t ALTER c TYPE varchar(4);
SELECT c || '<-' FROM t;

In light of that, I'm increasingly thinking we'll want a way for the user to
request a scan in place of a rewrite. The scan would throw an error if a
rewrite ends up being necessary. Adding a keyword for that purpose, the syntax
would resemble:

ALTER TABLE <name> ALTER [COLUMN] <colname> [SET DATA] TYPE <typename>
[IMPLICIT] [ USING <expression> ]

I had wished to avoid this as something of a UI wart, but I don't see a way to
cover all important conversions automatically and with a single-pass guarantee.
This would cover the rest.

Thoughts?