Обсуждение: 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

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.

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