Re: ALTER TABLE modifications
От | Rod Taylor |
---|---|
Тема | Re: ALTER TABLE modifications |
Дата | |
Msg-id | 1068735581.32543.43.camel@jester обсуждение исходный текст |
Ответ на | Re: ALTER TABLE modifications (Peter Eisentraut <peter_e@gmx.net>) |
Ответы |
Re: ALTER TABLE modifications
|
Список | pgsql-patches |
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?
В списке pgsql-patches по дате отправления: