Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-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

pgsql-hackers by date

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

pgsql-committers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group