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

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

Took bugs list off, as this is clearly not a bug.
>>> Matteo Beccati <php@beccati.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@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

>>> Jaime Casanova <jcasanov@systemguards.com.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

>>> Jaime Casanova <jcasanov@systemguards.com.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

On Thu, Mar 5, 2009 at 2:46 PM, Kevin Grittner
<Kevin.Grittner@wicourts.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

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

Jaime Casanova wrote: <blockquote cite="mid:3073cc9b0903051126t49db73ady7a46528b06d9658d@mail.gmail.com"
type="cite"><prewrap="">On Thu, Mar 5, 2009 at 10:47 AM, Matteo Beccati <a class="moz-txt-link-rfc2396E"
href="mailto:php@beccati.com"><php@beccati.com></a>wrote: </pre><blockquote type="cite"><pre wrap="">Guillaume
Smetha scritto:   </pre><blockquote type="cite"><pre wrap="">On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut <a
class="moz-txt-link-rfc2396E"href="mailto:peter_e@gmx.net"><peter_e@gmx.net></a> wrote:     </pre><blockquote
type="cite"><prewrap="">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.       </pre></blockquote><pre wrap="">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.     </pre></blockquote><pre wrap="">I recently had a similar problem when I added some
domainsto 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.
   </pre></blockquote><pre wrap="">
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. </pre></blockquote> Well, while this behaviour is
well-knownfor 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
caseof "ALTER TABLE ... TYPE" is, by the semantics of that statement, just a side-effect, which may or may not happen,
dependingon how optimized the DBMS is. It is bad design to avoid optimization just because an unnecessary side-effect
wouldbe optimized away.<br /><blockquote cite="mid:3073cc9b0903051126t49db73ady7a46528b06d9658d@mail.gmail.com"
type="cite"><prewrap="">
 
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</pre></blockquote> No. Is it possible to change the column type from VARCHAR(5) to
TEXTwithout a table-rewrite penalty?<br /><br /><br /> ciao,<br /> Xuân.<br /><br />