Обсуждение: BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite

The following bug has been logged online:

Bug reference:      4689
Logged by:
Email address:
xuan--2009.03--submitbug--support--postgresql.org@baldauf.org
PostgreSQL version: 8.3.5
Operating system:   Linux 2.6.18-6-amd64
Description:        Expanding the length of a VARCHAR column should not
induce a table rewrite
Details:

Suppose there is a table "sometable" with a column "somecolumn" of type
"VARCHAR(5)".

This table as many rows.

When executing
"ALTER TABLE sometable ALTER COLUMN somecolumn TYPE VARCHAR(7)", the whole
table is re-written, and this rewrite takes many hours. During these hours,
all writers on this table stall, making the database effectively
unavailable.

However, in almost all cases, there is no need for such relaxing of limits
to require a table rewrite.

So the expected run time needed for this statement is about one second, the
actual run time needed for this statement is many hours.

xuan--2009.03--submitbug--support--postgresql.org@baldauf.org wrote:
> When executing
> "ALTER TABLE sometable ALTER COLUMN somecolumn TYPE VARCHAR(7)", the whole
> table is re-written, and this rewrite takes many hours. During these hours,
> all writers on this table stall, making the database effectively
> unavailable.
>
> However, in almost all cases, there is no need for such relaxing of limits
> to require a table rewrite.

While this isn't a bug, it's a reasonable feature request. I've added
this to the TODO page: http://wiki.postgresql.org/wiki/Todo#ALTER

Patches are welcome.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas wrote:
> xuan--2009.03--submitbug--support--postgresql.org@baldauf.org wrote:
>> When executing
>> "ALTER TABLE sometable ALTER COLUMN somecolumn TYPE VARCHAR(7)", the
>> whole
>> table is re-written, and this rewrite takes many hours. During these
>> hours,
>> all writers on this table stall, making the database effectively
>> unavailable.
>>
>> However, in almost all cases, there is no need for such relaxing of
>> limits
>> to require a table rewrite.
>
> While this isn't a bug, it's a reasonable feature request. I've added
> this to the TODO page: http://wiki.postgresql.org/wiki/Todo#ALTER
>
> Patches are welcome.

The question is how you want to implement this in a data type
independent fashion.  You can't assume that increasing the typmod is a
noop for all data types.

On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> The question is how you want to implement this in a data type independent
> fashion.  You can't assume that increasing the typmod is a noop for all data
> types.

Sure. See my previous answer on -hackers (I don't think this
discussion belong to -bugs) and especially the discussion in the
archives about Jonas' patch.

--
Guillaume

Guillaume Smet ha scritto:
> On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> The question is how you want to implement this in a data type independent
>> fashion.  You can't assume that increasing the typmod is a noop for all data
>> types.
> 
> Sure. See my previous answer on -hackers (I don't think this
> discussion belong to -bugs) and especially the discussion in the
> archives about Jonas' patch.

I recently had a similar problem when I added some domains to the
application. ALTER TABLE ... TYPE varchar_dom was leading to a full
table rewrite even though the underlying type definition were exactly
the same (i.e. varchar(64)). I can live with it, but I suppose this fix
might be related to the varlen one.


Cheers

-- 
Matteo Beccati

OpenX - http://www.openx.org

On Thu, Mar 5, 2009 at 10:47 AM, Matteo Beccati <php@beccati.com> wrote:
> Guillaume Smet ha scritto:
>> On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>>> The question is how you want to implement this in a data type independent
>>> fashion.  You can't assume that increasing the typmod is a noop for all data
>>> types.
>>
>> Sure. See my previous answer on -hackers (I don't think this
>> discussion belong to -bugs) and especially the discussion in the
>> archives about Jonas' patch.
>
> I recently had a similar problem when I added some domains to the
> application. ALTER TABLE ... TYPE varchar_dom was leading to a full
> table rewrite even though the underlying type definition were exactly
> the same (i.e. varchar(64)). I can live with it, but I suppose this fix
> might be related to the varlen one.
>

ALTER TABLE ... TYPE does cause a table rewrite even if  new_type =
old_type, and that is actually useful...
for example when you add a fillfactor to an existing table that
fillfactor will not affect the existing data until you rewrite the
table and a convenient way is exactly using ALTER TABLE ... TYPE.

now, back to the problem... is not easier to define a column as TEXT
and to put a check to constraint the length? if you wanna change the
constraint that will be almost free

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Thu, Mar 5, 2009 at 3:27 PM, Xuân Baldauf
<xuan--2009.03--submitbug--support--postgresql.org@baldauf.org> wrote:
>
>
> Well, while this behaviour is well-known for PostgreSQL, this is actually an
> abuse of syntax. If there are legitimate requirements for rewriting a table,
> then there should be explicit syntax for such a feature, like "ALTER TABLE
> ... REWRITE". Rewriting a table in case of "ALTER TABLE ... TYPE" is, by the
> semantics of that statement, just a side-effect, which may or may not
> happen, depending on how optimized the DBMS is. It is bad design to avoid
> optimization just because an unnecessary side-effect would be optimized
> away.
>

note that this is my opinion and not represent the PGDG (Postgresql
Global Development Group) opinion

> now, back to the problem... is not easier to define a column as TEXT
> and to put a check to constraint the length? if you wanna change the
> constraint that will be almost free
>
> No. Is it possible to change the column type from VARCHAR(5) to TEXT without
> a table-rewrite penalty?
>
>

the idea is to make that change once (and to create new tables just with TEXT)

and then you can make ALTER TABLE ... ADD CHECK (length(column) =
a_value) as many times as you want without the need for a table
rewrite

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157