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

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 <emilu@encs.concordia.ca> wrote:

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 <jon@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 <emilu@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.

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.marlowe@gmail.com>wrote:

On Thu, Jan 27, 2011 at 1:57 PM, Jon Hoffman <jon@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)

On 2011-01-27, Emi Lu <emilu@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