On Wed, 2007-02-21 at 13:16 -0500, Andrew Dunstan wrote: > Simon Riggs wrote: > > On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote: > > > >> On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > >> > >>> I'd expect the system being able to reoder the columns to the most > >>> efficient order possible (performance-wise and padding-saving-wise), > >>> automatically. When you create a table, sort the columns to the most > >>> efficient order; ALTER TABLE ADD COLUMN just puts the new columns at the > >>> end of the tuple; and anything that requires a rewrite of the table > >>> (ALTER TABLE ... ALTER TYPE for example; would be cool to have CLUSTER > >>> do it as well; and do it on TRUNCATE also) again recomputes the most > >>> efficient order. > >>> > >> That's exactly what I'm proposing. On table creation, the system > >> chooses an efficient column order for you. > >> > > > > That's fairly straightforward and beneficial. I much prefer Alvaro's > > approach rather than the storage position details originally described. > > Moreover, you'd need to significantly re-write lots of ALTER TABLE and I > > really don't think you want to go there. > > > > There is a problem: If people do a CREATE TABLE and then issue SELECT * > > they will find the columns in a different order. That could actually > > break some programs, so it isn't acceptable in all cases. e.g. COPY > > without a column-list assumes that the incoming data should be assigned > > to the table columns in the same order as the incoming data file. > > > > You seem to have missed that we will be separating logical from physical > ordering. Each attribute will have a permanent id, a physical ordering > and a logical ordering. You can change either ordering without affecting > the other.
I missed nothing, AFAICS. My understanding was that Alvaro was proposing to have just a simple physical re-ordering and that would be altered at CREATE TABLE time. No complexity of multiple column orderings: nice, simple and effective. My only addition was to say: must be optional. > COPY, SELECT and all user-visible commands should follow the logical > ordering, not the physical ordering, which should be completely > invisible to SQL. I agree with comments here about the multiple orderings being a horrible source of bugs, as well as lots of coding even to make it happen at all http://archives.postgresql.org/pgsql-hackers/2006-12/msg00859.php -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate