Re: Avoiding rewrite in ALTER TABLE ALTER TYPE

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:
>> 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?

Any of the ones you listed in your second set of examples, e.g.:

ALTER TABLE t ALTER c TYPE character(6);

Under your proposal, this can scan the whole table once in read-only
mode, and then realize that it needs to go back and rewrite the whole
table.

>> 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.

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