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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, Zeugswetter Andreas ADI SD <ZeugswetterA(at)spardat(dot)at>, Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>, pgsql-hackers(at)postgresql(dot)org, Gregory Stark <stark(at)enterprisedb(dot)com>
Subject: Re: column ordering, was Re: [PATCHES] Enums patch v2
Date: 2006-12-21 16:43:27
Message-ID: 20414.1166719407@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers pgsql-patches

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> You could make a case that we need *three* numbers: a permanent column
>> ID, a display position, and a storage position.

> Could this not be handled by some catalog fixup after an add/drop? If we
> get the having 3 numbers you will almost have me convinced that this
> might be too complicated after all.

Actually, the more I think about it the more I think that 3 numbers
might be the answer. 99% of the code would use only the permanent ID.
Display position would be used in *exactly* one place, namely while
expanding "SELECT foo.*" --- I can't think of any other part of the
backend that would care about it. (Obviously, client-side code such
as psql's \d would use it too.) Use of storage position could be
localized into a few low-level tuple access functions, probably.

The problems we've been having with the concept stem precisely from
trying to misuse either display or storage position as a permanent ID.
That's fine as long as it actually is permanent, but as soon as you
want to change it then you have problems. We should all understand
this perfectly well from a database theory standpoint: pg_attribute
has to have a persistent primary key. (attrelid, attnum) is that key,
and we can't go around altering a column's attnum without creating
problems for ourselves.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-12-21 16:47:56 Re: column ordering, was Re: [PATCHES] Enums patch v2
Previous Message Lukas Kahwe Smith 2006-12-21 16:33:14 Re: Release 8.2.0 done, 8.3 development starts

Browse pgsql-patches by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-12-21 16:47:56 Re: column ordering, was Re: [PATCHES] Enums patch v2
Previous Message Andrew Dunstan 2006-12-21 16:27:44 Re: column ordering, was Re: [PATCHES] Enums patch v2