Re: ALTER TABLE TODO items

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: ALTER TABLE TODO items
Date: 2004-05-05 17:47:21
Message-ID: 8133.1083779241@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> On Wed, 2004-05-05 at 10:36, Bruce Momjian wrote:
>> Do we still want this TODO?
>>
>> o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];

> I think we should leave since it is still functionality that people will
> want.

It's not that no one would want it, it's that the code impact (and risk
of bugs) associated with separate logical and physical column numbers
seems very disproportionate to the value. The main argument for it
AFAIR was to support column type substitution via drop col/add col/
reorder col. Now that we have a better way I think the value of such a
feature wouldn't be worth the work/risk.

> recreating the entire table is likely to cause excessive i/o and disk
> space issues compared to a potentially much nicer add column/update
> column/drop column routine.

How you figure that? The UPDATE step will in itself require 2X disk
space --- and after that you'll need a VACUUM FULL to get it back.
The implementation Rod came up with is much nicer.

> Hmm... upon further thought, if the above implementation stands up, istm
> that its machinations could also be used to implement the reordering
> functionality... ie. rewrite the table and fix up any dependencies as
> needed.

True. In fact, this example that I put into the regression tests may be
food for thought:

create table another (f1 int, f2 text);
insert into another values(1, 'one');
insert into another values(2, 'two');
insert into another values(3, 'three');

select * from another;
f1 | f2
----+-------
1 | one
2 | two
3 | three
(3 rows)

alter table another
alter f1 type text using f2 || ' more',
alter f2 type bigint using f1 * 10;

select * from another;
f1 | f2
------------+----
one more | 10
two more | 20
three more | 30
(3 rows)

regards, tom lane

In response to

Responses

Browse pgsql-committers by date

  From Date Subject
Next Message Bruce Momjian 2004-05-05 17:51:23 Re: ALTER TABLE TODO items
Previous Message Tom Lane 2004-05-05 17:28:57 pgsql-server/src/backend/utils/adt Tag: REL7_4 ...

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2004-05-05 17:48:59 initdb failure in CVS
Previous Message Tom Lane 2004-05-05 17:38:02 Re: Multiple Xids in PGPROC?