Re: Updating pg_attribute to widen column

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(at)turnstep(dot)com>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(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-----
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>