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

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 />