Re: Long running update

The actual table that I am updating is 134GB of the 135GB. Each row has
one foreign key to another table, but no tables have a foreign key to
this table.

If I cancel the update then I can drop the new column, rename the old
column, do a vacuum, and then I should be left with what I started with,
right?

How can I expand that column without using this query?

I am using PostgreSQL 7.4.5.

Thanks for the help,

Andrew

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Sunday, October 16, 2005 12:07 PM
To: Andrew Janian
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Long running update

"Andrew Janian" <ajanian(at)scottrade(dot)com> writes:
> I needed to expand the size of one of the varchar columns in a table
of
> my 135GB database.

How big is the particular table you are doing this on? What foreign key
relationships is it involved in? (I wonder if the time is going into FK
checking more than the actual update...) What PG version exactly?

Personally, at this point I'd cancel the update while there's still time
to do a VACUUM before you have to be operational again. You could do
the required operation (increasing a varchar's limit) in O(1) time by
twiddling the system catalogs. You're going to need a VACUUM anyway
because of all the dead tuple versions left behind by the UPDATE.

regards, tom lane