Re: Updating pg_attribute to widen column

[Justin Julicher <justin.julicher@xxxxxxxxx>]

Hi Greg,

First off, thanks for your reply. 

I had actually just read your blog before writing this. 

I should have been more clear in my first post. 

If you use ALTER TABLE it will check every row in the table to make sure the column doesn't exceed the constraint (in 8.4 - I know this has been updated in 9.x) 

As I am trying to update a table with hundreds of millions of rows and the only way to do this efficiently (in an online database with  4 9's availability) is with a pg_attribute update. Previously we have done this via a script that runs for about a week on the database to migrate to another column, but this also involves code changes and lots and lots of testing. 

So my question is - does postgres take an update to pg_attribute instantly and in a reliable manner?

thanks,

Justin.

On Fri, Nov 30, 2012 at 4:15 PM, Greg Sabino Mullane <greg@xxxxxxxxxxxx> wrote:

-----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@xxxxxxxxxxxx  greg@xxxxxxxxxxxx
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-----




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general