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

Re: Avoiding rewrite in ALTER TABLE ALTER TYPE

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Noah Misch <noah(at)leadboat(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:57:45
Message-ID: AANLkTikN=A_K1ED_g85P5cdEpBM0=R9QpJJny3E9Yx=Y@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-12-30 06:44:41
Subject: future-proofing relkind tests, take two
Previous:From: Noah MischDate: 2010-12-30 05:24:05
Subject: Re: Avoiding rewrite in ALTER TABLE ALTER TYPE

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