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: 2011-01-06 04:26:26
Message-ID: 20110106042626.GA28230@tornado.leadboat.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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?

In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2011-01-06 04:55:11
Subject: Re: Streaming replication as a separate permissions
Previous:From: Andrew DunstanDate: 2011-01-06 04:24:50
Subject: Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE

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