Обсуждение: Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

reporting=# select version();
                                                  version
----------------------------------------------------------------------------------------------------------
  PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

I've a parent table with several years of monthly partitioned children.
  There has arisen a need to increase the max size of a couple of
varchar fields.  Total size of these tables is approaching ~200 GB, with
the larger monthly tables approximately 7-10GB each.

Would it be safe to use the below process to accomplish this?
Whether I use the below method, or the standard
   ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(new_size);
my assumption is that I should apply the change first to the child
tables, then to the parent???

From
http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

Resize a column in a PostgreSQL table without changing data

You use Post­greSQL. You find that a col­umn you have in a table is of a
smaller length than you now wish. In my case, this was a varchar(20)
that I now wished to make varchar(35). Noth­ing else. I just want to
change the size, keep­ing the data intact.

The ALTER TABLE ...ALTER COLUMN...TYPE... com­mand is use­ful only if
you want to alter the data some­how, or change the data type.
Oth­er­wise, it'll be an aeon before this fin­ishes even inside a
trans­ac­tion on a data­base of any mean­ing­ful size.

Until now, I was not famil­iar with any sen­si­ble mech­a­nism to
sim­ply change the size in PG. But yes­ter­day, Tom Lane him­self
sug­gested some­thing uber­cool in the list.

Let's assume for the sake of sim­plic­ity that your table is called
"TABLE1" and your col­umn is "COL1". You can find the size of your
"COL1" col­umn by issu­ing the fol­low­ing query on the sys­tem tables:

SELECT atttypmod FROM pg_attribute
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

atttypmod
-----------
24
(1 ROW)

This means that the size is 20 (4 is added for legacy rea­sons, we're
told). You can now con­ve­niently change this to a varchar(35) size by
issu­ing this command:

UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

UPDATE 1

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.

On Tue, Nov 22, 2011 at 7:50 AM, Reid Thompson <reid.thompson@ateb.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.

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.

for the future it is better to just use text type, and: check
length(field) < 35;

On Tue, Nov 22, 2011 at 10:21 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Nov 22, 2011 at 7:50 AM, Reid Thompson <reid.thompson@ateb.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

On Tue, 2011-11-22 at 15:55 +0000, Gregg Jaskiewicz wrote:
> for the future it is better to just use text type, and: check
> length(field) < 35;

thanks to all for the respones.

The above seems a prudent way to go in my future.

My assumption is that converting varchar(n) to text would still force a
re-write of the table?  i.e. currently there's no officially 'safe' way
to convert the field type w/o incurring a table re-write.

Reid Thompson <Reid.Thompson@ateb.com> writes:
> My assumption is that converting varchar(n) to text would still force a
> re-write of the table?  i.e. currently there's no officially 'safe' way
> to convert the field type w/o incurring a table re-write.

If you do it through ALTER TABLE, yes.  Since text and varchar are the
same thing on disk, you could get away with just manually updating the
pg_attribute row for the column ... but I'd counsel practicing on a
scratch database ;-)

            regards, tom lane