Re: Updating pg_attribute to widen column

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Updating pg_attribute to widen column
Date: 2012-11-30 06:15:05
Message-ID: 2e33b6c899cdd6947fee9d5f8ae893f0@biglumber.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Justin Julicher asked:
> We need to widen a column on a table with millions of rows and the only way
> to do this currently is to migrate the data from one column to another with
> a script and trigger.

Not the only way - the canonical way is to simply use ALTER TABLE.

> I know how to do this via an update to pg_attribute which would incur the
> table scan penalty but I have a number of questions

No, there is no table scan penalty.

> - Does postgres pick up this change straight away?

Not sure exactly what you mean. Certainly, new inserts will respect the
change.

> Are there any caveats to my first question?

Yes. A direct pg_attribute change should be your last resort. Do
an ALTER TABLE if you can. If you must do it via pg_attribute,
test it very well first, and make sure to look at pg_depend. See:

http://blog.endpoint.com/2012/11/postgres-alter-column-problems-and.html

- --
Greg Sabino Mullane greg(at)endpoint(dot)com greg(at)turnstep(dot)com
End Point Corporation 610-983-9073
PGP Key: 0x14964AC8 201211300113
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlC4TtMACgkQvJuQZxSWSsiVDwCcCFYggG7mMf45nMIfoXHBGnMq
TMkAn23VUHK0z/SshzrRACW0+dn5wqPv
=CAQa
-----END PGP SIGNATURE-----

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Justin Julicher 2012-11-30 06:32:55 Re: Updating pg_attribute to widen column
Previous Message Justin Julicher 2012-11-30 06:04:16 Updating pg_attribute to widen column