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

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

From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>, Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>, pgsql-hackers(at)postgresql(dot)org, Martijn van Oosterhout <kleptog(at)svana(dot)org>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Gregory Stark <stark(at)enterprisedb(dot)com>
Subject: Re: column ordering, was Re: [PATCHES] Enums patch v2
Date: 2006-12-20 11:33:53
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-patches
Tom Lane wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> Force references to go through macros which implement the lookup for the
>> appropriate type?  ie: LOGICAL_COL(table_oid,2) vs.
>> PHYSICAL_COL(table_oid,1)  Perhaps that's too simplistic.
> It doesn't really address the question of how you know which one to
> use at any particular line of code; or even more to the point, what
> mechanism will warn you if you use the wrong one.
> My gut feeling about this is that we could probably enforce such a
> distinction if we were using C++, but while coding in C I have no
> confidence in it.  (And no, that's not a vote to move to C++ ...)
What about a comprimise...

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.


Russell Smith
> 			regards, tom lane
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at

In response to


pgsql-hackers by date

Next:From: MarioDate: 2006-12-20 11:54:04
Subject: Re: psql: core dumped
Previous:From: Martijn van OosterhoutDate: 2006-12-20 11:17:05
Subject: Re: Load distributed checkpoint

pgsql-patches by date

Next:From: Takayuki TsunakawaDate: 2006-12-20 12:05:41
Subject: Re: [PATCHES] Load distributed checkpoint patch
Previous:From: Martijn van OosterhoutDate: 2006-12-20 11:17:05
Subject: Re: Load distributed checkpoint

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