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
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 |
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 |