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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-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

Browse pgsql-hackers by date

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

Browse pgsql-patches by date

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