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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2011-01-06 04:55:11 | Re: Streaming replication as a separate permissions |
Previous Message | Andrew Dunstan | 2011-01-06 04:24:50 | Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE |