Skip site navigation (1) Skip section navigation (2)

Re: Avoiding rewrite in ALTER TABLE ALTER TYPE

From: Noah Misch <noah(at)leadboat(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Avoiding rewrite in ALTER TABLE ALTER TYPE
Date: 2010-12-30 05:24:05
Message-ID: 20101230052405.GA1158@tornado.leadboat.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-12-30 05:57:45
Subject: Re: Avoiding rewrite in ALTER TABLE ALTER TYPE
Previous:From: Robert HaasDate: 2010-12-30 04:22:14
Subject: does anyone still care about synchronous replication?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group