[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Updating pg_attribute to widen column
From:       "Greg Sabino Mullane" <greg () turnstep ! com>
Date:       2012-11-30 15:33:52
Message-ID: ad89a602b962c10466c21f561de0b8f8 () biglumber ! com
[Download RAW message or body]


-----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@turnstep.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-----




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]