Re: Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

From: Robert Treat <rob(at)xzilla(dot)net>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Reid Thompson <reid(dot)thompson(at)ateb(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data
Date: 2011-11-23 05:58:11
Message-ID: CABV9wwP1vQKFEv2meBimgqMto-_bJjXy4_Y_jrRrQn72toBtGQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, Nov 22, 2011 at 10:21 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Tue, Nov 22, 2011 at 7:50 AM, Reid Thompson <reid(dot)thompson(at)ateb(dot)com> wrote:
>> Note that I man­u­ally added the 4 to the desired size of 35..again, for
>> some legacy rea­sons inside PG. Done. That's it. Should we check?
>>
>> d TABLE1
>>
>> TABLE "public.TABLE1"
>> COLUMN  |  TYPE                 | Modifiers
>> --------+-----------------------+-----------
>> COL1    | CHARACTER VARYING(35) |
>>
>> Such a sim­ple yet effec­tive trick. Of course it'd be nicer if this is
>> some­how included in a more proper way in the data­base, but this does the
>> job.
>
> Note that this method works around all the safe guards etc that make
> sure your data is safe and coherent.  It works, as long as you're
> careful what you're doing.
>

And by careful, be aware that there are certainly considerations you
need to have for indexes and/or partition mismatches that might be
involved here. At a minimum I'd suggest upgrading to 8.3.$latest
(ideally the next release, which will likely be out in a couple weeks)
as there are some bugs in this area in older releases (and 8.3.7
certainly qualifies).

> the real solution, to me, is to stop using varchar limits unless
> there's a real reason for them.  I.e. arbitrary limits on things like
> name lengths make no sense in the db.
>

Yeah, I have often subscribed to this idea in the past, though there
is a valid argument for saying that while you don't have a specific
limit you care about, there are values of length that are long enough
that they probably indicate garbage data or something gone wrong. In a
world where Postgres actually handled this problem gracefully (and I
think 9.1 does), I don't think this rule is as clear cut as it used to
be.

Robert Treat
conjecture: xzilla.net
consulting: omniti.com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2011-11-23 06:12:53 Re: wal archiving on a hot-standby server
Previous Message Robert Treat 2011-11-23 05:51:27 Re: autovacuum stuck on a table for 18+ hours, consuming lots of CPU time