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(dot)03--submitbug--support--postgresql(dot)org(at)baldauf(dot)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(dot)03--submitbug--support--postgresql(dot)org(at)baldauf(dot)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

On Wed, Mar 4, 2009 at 11:06 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> 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.

I remember someone proposed a patch for that a long time ago:
http://archives.postgresql.org/pgsql-patches/2006-10/msg00154.php

AFAICS there were 2 possible follow-ups to this patch:
- only manage the case of char/varchar (which is the most common case anyway);
- develop a complete infrastructure to handle all the cases as
suggested by Gregory.

--
Guillaume

Heikki Linnakangas wrote:
> xuan--2009(dot)03--submitbug--support--postgresql(dot)org(at)baldauf(dot)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(at)gmx(dot)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(at)gmx(dot)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

Took bugs list off, as this is clearly not a bug.

>>> Matteo Beccati <php(at)beccati(dot)com> wrote:
> 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.

Been there. We also occasionally increase the length of a
verchar-based domain. The process could be made faster and more
convenient by avoiding rewrites when possible. On particularly large
tables I've sometimes ventured into direct updates to the system
tables for these.

-Kevin

On Thu, Mar 5, 2009 at 10:47 AM, Matteo Beccati <php(at)beccati(dot)com> wrote:
> Guillaume Smet ha scritto:
>> On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut <peter_e(at)gmx(dot)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

>>> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> 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.

I find that to be exactly as useful as it would be to have a table
rewrite if I added a new null-capable column, and somewhat less useful
than it would be have a table rewrite on dropping a column.
Maintaining the function of this clever trick should not be the basis
of imposing a burden on relatively common maintenance operations.

> 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

Thanks for the interesting suggestion. I'm not sure I'd want to go
there for various reasons; but even if I wanted to go that route, how
would I modify that constraint without causing the whole table to be
scanned for compliance?

-Kevin

On Thu, Mar 5, 2009 at 2:46 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
>> 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
>
> Thanks for the interesting suggestion.  I'm not sure I'd want to go
> there for various reasons; but even if I wanted to go that route, how
> would I modify that constraint without causing the whole table to be
> scanned for compliance?
>

the table will be scanned but not rewritten

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

>>> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> the table will be scanned but not rewritten

That can still be a very long time on some tables.

And there would still be the issue of dodging all the brickbats thrown
at me by developers whose tools use the system tables to limit the
number of characters a user is allowed to type into an application.

-Kevin

Jaime Casanova wrote:
> On Thu, Mar 5, 2009 at 10:47 AM, Matteo Beccati <php(at)beccati(dot)com> wrote:
>
>> Guillaume Smet ha scritto:
>>
>>> On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut <peter_e(at)gmx(dot)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.
>
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.
> 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?

ciao,
Xuân.

On Thu, Mar 5, 2009 at 3:27 PM, Xuân Baldauf
<xuan--2009(dot)03--submitbug--support--postgresql(dot)org(at)baldauf(dot)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