Re: Avoiding rewrite in ALTER TABLE ALTER TYPE

On Wed, Jan 5, 2011 at 11:26 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> 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?

I still think you're better off focusing first on the case where we
can skip the whole nine yards, and doing this stuff as a follow-on
patch. Trying to do too many things, especially possibly
controversial stuff, especially in the last CommitFest, often ends up
with the whole patch getting rejected, which makes no one happy.
Submitting the smallest useful, self-contained change you can and then
work up from there. Or at least split out the patch into parts that
can be applied independently, so that if the eventual committer likes
A but not B you at least get A in.

Don't take any of this as a rejection of any of what you're proposing;
I haven't really made up my mind yet, and there are plenty of other
people who would have a say even if I had. Rather, I'd like to
maximize the chances of us at least part of this work committed to
9.1.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company