Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id F3BA065050F for ; Fri, 11 Jul 2008 11:55:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 52384-01 for ; Fri, 11 Jul 2008 11:55:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 54AE76502B9 for ; Fri, 11 Jul 2008 11:55:24 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Fri, 11 Jul 2008 09:55:22 -0500 Message-Id: <48772E39.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.3 Date: Fri, 11 Jul 2008 09:55:06 -0500 From: "Kevin Grittner" To: "Alvaro Herrera" , "Tom Lane" Cc: "Pgsql performance" , "Mario Weilguni" , "Ow Mun Heng" Subject: Re: Altering a column type - Most efficient way References: <1215676380.15319.2.camel@neuromancer.home.net> <4875C9FA.7020101@sime.com> <1215682481.15319.14.camel@neuromancer.home.net> <20080710135756.GD3757@alvh.no-ip.org> <1215742620.853.10.camel@neuromancer.home.net> <20080711134423.GA4110@alvh.no-ip.org><20080711134423.GA4110@alvh.no-ip.org> <3462.1215786454@sss.pgh.pa.us> In-Reply-To: <3462.1215786454@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/101 X-Sequence-Number: 30701 >>> Tom Lane wrote: > Alvaro Herrera writes: >> Ow Mun Heng wrote: >>> If it were this simple a change, I'm not certain why (I believe) PG is >>> checking each and every row to see if it will fit into the new column >>> definition/type. > >> Because the code that does the ALTER TYPE is very generic, and it >> doesn't (yet) have an optimization that tells it to skip the check and >> the possible table rewrite in the cases where it's obviously not needed >> (like this one). > > Awhile back I looked into teaching ALTER TYPE that it needn't rewrite > if the type conversion expression parses out as just a Var with > RelabelType, but it seemed that that wouldn't cover very much of the > use-cases where a human thinks that it's "obvious" that no rewrite > is needed. We wouldn't have to cover all possible cases for it to be useful. If there's some low-hanging fruit here, +1 for getting that. The cases which would most often save staff here some time are switching a varchar to a longer maximum length or switching between a domain which is varchar to plain varchar (or vice versa). -Kevin