Re: ALTER TABLE modifications

On Thu, 2003-11-13 at 09:18, Peter Eisentraut wrote:
> Rod Taylor writes:
>
> > Yes, it is certainly fine to do so, but much faster to do the above.
>
> Are table schema changes performance-sensitive operations, and are they
> usually done in bulk? I doubt it.

Perhaps not for you. But I would disagree with both of those points for
a majority of companies.

If the system has high uptime requirements, the timeframe to accomplish
maintenance decreases substantially. For example, I have two 1.5 hour
maintenance windows per year for maintenance. This is a requirement
given by our clients, nearly all of whom insist on it. Telcos and other
enterprise level environments with 24 / 7 usage patterns have different
demands.

So.. Adding 2 new check constraints to a table with 500million entries
takes way way too much time in my eyes.

Toms request (when I initially brought up fixing ADD COLUMN) was that it
would process the DEFAULT and other new constraints in a single pass.
The above syntax simply uses the framework put in place for ADD COLUMN.

I'll not push it if it does not function with complex combinations,
which has not been tested, as such is not included in this patch.

> > -- or say Bytes to MBytes (original column is int8)
> > ALTER TABLE tab ALTER col TYPE integer TRANSFORM col / (1024 * 1024);
>
> You can do this using a plain column type change plus an UPDATE. I'd
> prefer to keep these operations independent.

Yes, if you can do the update first.

Ok.. How about boolean to int2 as the user discovered they require a
third or fouth state? Or vice-versa. An MSSQL, MySQL, etc. migration
would leave a bunch of int2's (since they do not support boolean) in the
PostgreSQL database which have a boolean meaning.

rbt=# select '1'::int4::boolean
rbt-# ;
ERROR: cannot cast type integer to boolean
rbt=# select '1'::boolean;
bool
------
t
(1 row)

Are you going to tell these people to write down the Default, add a new
column, copy the data, remove the old column, re-add the default, and
rename the new column to the old column name?

This is the exact reason many folks have asked for this ALTER form to be
added.

Considering there are already 2 sites out there running with this patch
(I have been asked for advance copies) I do not believe the
functionality is overkill in any respect.

Can you please suggest a better term to use in place of TRANSFORM?
Perhaps UPDATE WITH?