Re: [GENERAL] resizing a varchar column on 8.3.8
On 2011-01-27, Emi Lu em...@encs.concordia.ca wrote: On 01/15/2011 04:22 PM, Jon Hoffman wrote: Hi, I found a post with some instructions for resizing without locking up the table, but would like to get some re-assurance that this is the best way: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data How does that affect data storage and future updates on existing rows? Under mvcc rows are discarded and replaced with new rows when you do updates. Also postgres stores all strings types as text. Varchar(n) is just text with an an added character length constraint. There's no worry about old records not satisfying the new format, or old records beeing too small for reuse. there is rarely a good reason (other than conforming to standards?) to prefer varchar over text when creating tables. I did not see any feedbacks about this topic. I need confirmation that it is safe to do this! Personally, I feel that it is specially useful when there are many view dependencies. Update from data dictionary, all views will be updated automatically, right? The only problem I can see is aomeone in a (read commited) tranaction finding strings in the table that are longer than they expected to find. It feels safe to me. but if you want to sure, on a test database insert some long strings, then make the limit shorter the existing strings and see if it causes any problems. -- ⚂⚃ 100% natural -- 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] resizing a varchar column on 8.3.8
On 01/15/2011 04:22 PM, Jon Hoffman wrote: Hi, I found a post with some instructions for resizing without locking up the table, but would like to get some re-assurance that this is the best way: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data How does that affect data storage and future updates on existing rows? I did not see any feedbacks about this topic. I need confirmation that it is safe to do this! Personally, I feel that it is specially useful when there are many view dependencies. Update from data dictionary, all views will be updated automatically, right? Thanks a lot! -- Lu Ying -- 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] resizing a varchar column on 8.3.8
Hi, I was able to do this without any issues, though I don't have any views. - Jon On Thu, Jan 27, 2011 at 9:52 AM, Emi Lu em...@encs.concordia.ca wrote: On 01/15/2011 04:22 PM, Jon Hoffman wrote: Hi, I found a post with some instructions for resizing without locking up the table, but would like to get some re-assurance that this is the best way: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data How does that affect data storage and future updates on existing rows? I did not see any feedbacks about this topic. I need confirmation that it is safe to do this! Personally, I feel that it is specially useful when there are many view dependencies. Update from data dictionary, all views will be updated automatically, right? Thanks a lot! -- Lu Ying
Re: [GENERAL] resizing a varchar column on 8.3.8
It's one of those It's perfectly safe, as long as nothing goes wrong types of things. It should work, but I'd certainly play on a test server first. And if something goes wrong in the right way, you might not even know it for a while. But generally, it's pretty common to do this one hackish thing with the catalogs. On Thu, Jan 27, 2011 at 1:25 PM, Jon Hoffman j...@foursquare.com wrote: Hi, I was able to do this without any issues, though I don't have any views. - Jon On Thu, Jan 27, 2011 at 9:52 AM, Emi Lu em...@encs.concordia.ca wrote: On 01/15/2011 04:22 PM, Jon Hoffman wrote: Hi, I found a post with some instructions for resizing without locking up the table, but would like to get some re-assurance that this is the best way: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data How does that affect data storage and future updates on existing rows? I did not see any feedbacks about this topic. I need confirmation that it is safe to do this! Personally, I feel that it is specially useful when there are many view dependencies. Update from data dictionary, all views will be updated automatically, right? Thanks a lot! -- Lu Ying -- To understand recursion, one must first understand recursion. -- 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] resizing a varchar column on 8.3.8
On Thu, Jan 27, 2011 at 1:57 PM, Jon Hoffman j...@foursquare.com wrote: This was originally discussed on this list here: http://postgresql.1045698.n5.nabble.com/Smartest-way-to-resize-a-column-td1915892.html Tom Lane suggested doing the resize in a BEGIN block at least to verify that \d tablename reflects the catalog update. - Jon Good point. Best practices are to do all DDL in a transaction if possible. But a lot of folks come from databases that do stupid and unpredictable things when you try to do DDL in a transaction (ok, that's basically damned near all of them except a couple of odd rarities like informix) -- 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] resizing a varchar column on 8.3.8
This was originally discussed on this list here: http://postgresql.1045698.n5.nabble.com/Smartest-way-to-resize-a-column-td1915892.html Tom Lane suggested doing the resize in a BEGIN block at least to verify that \d tablename reflects the catalog update. - Jon On Thu, Jan 27, 2011 at 3:39 PM, Scott Marlowe scott.marl...@gmail.comwrote: It's one of those It's perfectly safe, as long as nothing goes wrong types of things. It should work, but I'd certainly play on a test server first. And if something goes wrong in the right way, you might not even know it for a while. But generally, it's pretty common to do this one hackish thing with the catalogs. On Thu, Jan 27, 2011 at 1:25 PM, Jon Hoffman j...@foursquare.com wrote: Hi, I was able to do this without any issues, though I don't have any views. - Jon On Thu, Jan 27, 2011 at 9:52 AM, Emi Lu em...@encs.concordia.ca wrote: On 01/15/2011 04:22 PM, Jon Hoffman wrote: Hi, I found a post with some instructions for resizing without locking up the table, but would like to get some re-assurance that this is the best way: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data How does that affect data storage and future updates on existing rows? I did not see any feedbacks about this topic. I need confirmation that it is safe to do this! Personally, I feel that it is specially useful when there are many view dependencies. Update from data dictionary, all views will be updated automatically, right? Thanks a lot! -- Lu Ying -- To understand recursion, one must first understand recursion.
[GENERAL] resizing a varchar column on 8.3.8
Hi, I found a post with some instructions for resizing without locking up the table, but would like to get some re-assurance that this is the best way: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data How does that affect data storage and future updates on existing rows? The table has around 6MM rows and is very heavily queried. I'm also using skytools londiste for replication, so I assume that I would run the resize on the subscriber first. Thanks for help, Jon
