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

Re: column ordering, was Re: [PATCHES] Enums patch

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Russell Smith" <mr-russ(at)pws(dot)com(dot)au>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Andrew Dunstan" <andrew(at)dunslane(dot)net>,"Gregory Stark" <stark(at)enterprisedb(dot)com>,<pgsql-hackers(at)postgresql(dot)org>,"Stephen Frost" <sfrost(at)snowman(dot)net>,"Martijn van Oosterhout" <kleptog(at)svana(dot)org>,"Robert Treat" <xzilla(at)users(dot)sourceforge(dot)net>
Subject: Re: column ordering, was Re: [PATCHES] Enums patch
Date: 2006-12-21 16:26:30
Message-ID: 458A6155.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
>>> On Wed, Dec 20, 2006 at  5:33 AM, in message
<45891FA1(dot)5000902(at)pws(dot)com(dot)au>,
Russell Smith <mr-russ(at)pws(dot)com(dot)au> wrote: 
> 
> The 8.1 documentation for ALTER TABLE states the following.
> 
> Adding a column with a non-null default or changing the type of an 
> existing column will require the entire table to be rewritten. This
may 
> take a significant amount of time for a large table; and it will 
> temporarily require double the disk space.
> 
> 
> Now, we are rewriting the table from scratch anyway, the on disk
format 
> is changing.  What is stopping us from switching the column order at
the 
> same time.  The only thing I can think is that the catalogs will need

> more work to update them.  It's a middle sized price to pay for being

> able to reorder the columns in the table.  One of the problems I have
is 
> wanting to add a column in the middle of the table, but FK
constraints 
> stop me dropping the table to do the reorder.  If ALTER TABLE would
let 
> me stick it in the middle and rewrite the table on disk, I wouldn't 
> care.  It's likely that I would be rewriting the table anyway.  And
by 
> specifying AT POSITION, or BEFORE/AFTER you know for big tables it's

> going to take a while.
> 
> Not that I'm able to code this at all, but I'm interested in feedback
on 
> this option.
 
+1
 
Currently, I often have to make the choice between adding a column at
the "logical" place in relation to the other columns or adding it at the
end.  The former requires creating a whole new table, populating it with
INSERT/SELECT, dropping the old table, renaming the new table, and
restoring permissions, constraints, indexes, etc.  The latter is a
simple ALTER TABLE.  When I choose the former, I save significant time
and reduce errors by using pg_dump to generate a lot of the code; but it
should would be a nice feature if ALTER TABLE could do all this "under
the covers".
 
-Kevin
 


In response to

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2006-12-21 16:27:44
Subject: Re: column ordering, was Re: [PATCHES] Enums patch v2
Previous:From: Zeugswetter Andreas ADI SDDate: 2006-12-21 16:18:02
Subject: Re: column ordering, was Re: [PATCHES] Enums patch v2

pgsql-patches by date

Next:From: Andrew DunstanDate: 2006-12-21 16:27:44
Subject: Re: column ordering, was Re: [PATCHES] Enums patch v2
Previous:From: Zeugswetter Andreas ADI SDDate: 2006-12-21 16:18:02
Subject: Re: column ordering, was Re: [PATCHES] Enums patch v2

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