pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Cc: Mario Weilguni <mweilguni@sime.com>
Subject: [SOLVED] Re: Altering a column type - Most efficient way
Date: Mon, 14 Jul 2008 09:46:46 +0800
Message-ID: <1216000006.22806.9.camel@neuromancer.home.net> (raw)
In-Reply-To: <48772E39.EE98.0025.0@wicourts.gov>
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>
	<48772E39.EE98.0025.0@wicourts.gov>

On Fri, 2008-07-11 at 09:55 -0500, Kevin Grittner wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> 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).

>  If there's some low-hanging fruit here, +1 for getting that.  

I just tested this out and everything seems to be working fine. (cross
fingers - for now and if I do report back, it means we've crashed and
burned, but as of now... the low hanging fruit is tasty)

This 2 sec change is much preferred over the 3+ hour per table.

I agree with Tom that this is not useful in _all_ cases and may seem to
look like a hack, but it really isn't. Given that the condition that
we're expaning the min length rather than the opposite, it should be
pretty safe.


Guys(/gals) Thanks very much for brightening up a dreadry Monday
morning.




view thread (10+ messages)

Message-ID: <1216000006.22806.9.camel@neuromancer.home.net>
Permalink:  ../1216000006.22806.9.camel@neuromancer.home.net/
Also on:    postgresql.org/message-id/1216000006.22806.9.camel@neuromancer.home.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: Ow.Mun.Heng@wdc.com, Kevin.Grittner@wicourts.gov, alvherre@commandprompt.com, tgl@sss.pgh.pa.us, mweilguni@sime.com
  Subject: Re: [SOLVED] Re: Altering a column type - Most efficient way
  In-Reply-To: <1216000006.22806.9.camel@neuromancer.home.net>
  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> <3462.1215786454@sss.pgh.pa.us> <48772E39.EE98.0025.0@wicourts.gov> <1216000006.22806.9.camel@neuromancer.home.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox