Re: logical column position

Neil Conway <neilc(at)samurai(dot)com> writes:
> At present, attnum basically does three things: identifies an column
> within a relation, indicates which columns are system columns, and
> defines the order of a relation's columns. I'd like to move this last
> functionality into a separate pg_attribute column named "attpos" (or
> "attlogicalpos"):

"attpos" is a horrid choice of name, because no one will be able to
remember which of "attnum" and "attpos" is which. Pick a more distinct
name. Offhand the best thing I can think of is "attlognum" or "attlogpos".

> - when the table is created, attnum == attpos. System columns
> have attpos < 0, as with attnum. At no point will two
> columns of the same relation have the same attpos.

What are you going to do with deleted columns? I'd be inclined to give
them all attlogpos = 0, but that destroys your last comment.

> (a) ISTM this should also apply to COPY TO and COPY FROM if the user
> didn't supply a column list. Is this reasonable?

Yes, also INSERT INTO, also the implicit ordering of output columns of a
JOIN, also the matching of aliases to columns in a FROM-list alias,
probably one or two other places. SQL exposes column ordering in more
places than just "SELECT *".

> If we want to avoid this, one easy (but arguably unclean) way to
> do so would be to make the initial value of attpos == attnum *
> 1000, and make attpos an int4 rather than an int2. Then, we can
> do most column reordering operations with only a single
> pg_attribute update -- in the worst-case that enough
> re-orderings are done that we overflow the 999 "padding"
> positions, we can just fall-back to doing multiple pg_attribute
> updates. Is this worth doing, and/or is there a better way to
> achieve the same effect?

That seems horribly messy. Just renumber.

> (c) Do I need to consider inheritance?

Yes. I think it'd be good if things were constrained so that columns
1..n in a parent table always matched columns 1..n in every child,
which is not true now after adding/dropping columns. That would make it
easier/cheaper/more reliable to match up which child columns are to be
referenced in an inherited query (see adjust_inherited_attrs). I think
the effective constraints would have to be about the same as what we now
impose on column names in an inheritance hierarchy.

You have not presented any proposal for exactly what ALTER TABLE
operations would be offered to manipulate the column positions.
My recollection is that some consensus was reached on that point
in the last thread we had on this issue --- have you consulted the
archives?

regards, tom lane