Re: Updating pg_attribute to widen column

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