Re: Avoiding rewrite in ALTER TABLE ALTER TYPE

On Wed, Dec 29, 2010 at 11:14:37PM -0500, Robert Haas wrote:
> On Wed, Dec 29, 2010 at 6:46 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > Perhaps. ?A few kooky rows is indeed common, but we're talking about a specific
> > breed of kookiness: 99.9% of the rows have identical bits after an ALTER TYPE
> > transformation expression, and 0.1% have different bits. ?Is that common?
>
> I think it's common enough to be worth worrying about.

Okay. Could you give an example of a specific ALTER TABLE recipe worth worrying
about and subject to degradation under my proposal?

> > Adding a bpchar into the mix makes a negative verification scan possible, as
> > does a USING clause having a truncating effect. ?Continuing the example, these
> > can and would get a negative verification scan:
> > ALTER TABLE t ALTER c TYPE character(6);
> > ALTER TABLE> In case it was not obvious, I'll note that any error thrown by a transformation
> t ALTER c TYPE varchar(5) USING c::varchar(5);
> > Plenty of academic USING clause examples exist:
> > ALTER TABLE t ALTER c TYPE varchar(8) USING CASE c WHEN '<de/>' THEN 'foo' ELSE c END;
>
> I am not really convinced that there's much value in optimizing these
> cases. They're not likely to arise very often in practice,

Just to make sure we're clear: those were examples of what I had intended to
pessimize for the sake of simplicity.

> and DBAs
> like predictability. There's tangible value in being able to say
> "this is going to scan your table at most once - it might rewrite it,
> or it might just verify what's there, or it might decide no scan is
> necessary, but the absolute worst case is one scan with rewrite".
> That's simple to understand and simple to document and probably
> simpler to code too, and I think it covers very nearly all of the
> cases people are likely to care about in practice.

> > 1. Have the user tell us: ALTER TABLE t ALTER c TYPE xml USING c::xml IMPLICIT
> > 2. Mark the text->xml cast as "possibly no-rewrite" and look for that
> > 3. Do a verification scan every time
>
> 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?

Thanks,
nm