Re: [GENERAL] 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 g...@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
Re: [GENERAL] Updating pg_attribute to widen column
-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 g...@endpoint.com g...@turnstep.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 g...@turnstep.comwrote: -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 g...@endpoint.com g...@turnstep.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
