Re: Updating pg_attribute to widen column

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

Justin Julicher wrote:
> 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)

Correct.

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

That's actually a little tricky to answer, as I'm not sure to what extent
the system catalogs are cached for existing connections. Certainly any
new connections will see the changes right away. However, if the change
involves a similar column it should not really matter if an existing
client sees the old definition, right? In other words, even if you change
a column from VARCHAR(32) to VARCHAR(64), it doesn't matter if an existing
client sees that change or not - the data will still be stored exactly the
same. You can always put a heavy lock on pg_attribute to ensure everyone
sees the change.

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

iEYEAREDAAYFAlC40cUACgkQvJuQZxSWSsiSbwCg8DQbV22scGQ2luF++iRpUJg2
hFEAnicLrGCs/Nz7buOQ5L6tC80mcVEX
=j5jv
-----END PGP SIGNATURE-----