-----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@xxxxxxxxxxxx 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
