Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Re: BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite

From: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
To: Xuân Baldauf <xuan--2009(dot)03--submitbug--support--postgresql(dot)org(at)baldauf(dot)org>
Cc: Matteo Beccati <php(at)beccati(dot)com>, Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Re: BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Date: 2009-03-05 20:37:22
Message-ID: 3073cc9b0903051237r421f3493me21ebc99833f1a15@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Joshua TolleyDate: 2009-03-05 21:43:43
Subject: Re: Make SIGHUP less painful if pg_hba.conf is notreadable
Previous:From: Xuân BaldaufDate: 2009-03-05 20:27:34
Subject: Re: Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite

pgsql-bugs by date

Next:From: carlDate: 2009-03-06 13:24:54
Subject: BUG #4695: update bug
Previous:From: Xuân BaldaufDate: 2009-03-05 20:27:34
Subject: Re: Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group