From: Mario Weilguni <mweilguni@sime.com>
To: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: Altering a column type - Most efficient way
Date: Fri, 11 Jul 2008 10:01:08 +0200
Message-ID: <48771344.3020305@sime.com> (raw)
In-Reply-To: <1215742620.853.10.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>
Ow Mun Heng schrieb:
> On Thu, 2008-07-10 at 09:57 -0400, Alvaro Herrera wrote:
>
>> Ow Mun Heng wrote:
>>
>>
>>> This is what I see on the table
>>>
>>> NEW attypmod = -1
>>> OLD attypmod = 8
>>>
>> 8 means varchar(4) which is what you said you had (4+4)
>> -1 means unlimited size.
>>
>>
>
> This is cool.
>
> 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.
>
> Thus, I'm still a bit hesitant to do the change, although it is
> definitely a very enticing thing to do. ( I presume also that this
> change will be instantaneous and does not need to check on each and
> every row of the table?)
>
> Thanks./
>
>
It should be safe, because the length limit is checked at insert/update
time, and internally, a varchar(20) is treated as something like this:
foo varchar(1000000000) check (length(foo) <= 20)
The change is done without re-checking all rows, and will not fail IF
the new size is longer than the old size.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mweilguni@sime.com, Ow.Mun.Heng@wdc.com, alvherre@commandprompt.com
Subject: Re: Altering a column type - Most efficient way
In-Reply-To: <48771344.3020305@sime.com>
* 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