Re: Column reordering in pg_dump

On Tue, Nov 25, 2008 at 9:18 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
>> After some thought, it seems pretty clear, at least to me, that the
>> third (hypothetical) command should not change the result of "SELECT *
>> FROM tricky" (the contrary conclusion gives rise to a lot of problems,
>> especially if there are other views depending on it).  But what will
>> "pg_dump -t tricky" output at this point?  I suspect it will be
>> necessary to introduce some new syntax here.
>
> Everything that's user-visible needs to use logical positioning.  That
> includes pg_dump.

Obviously.  The point is that the "alias (column_alias, column_alias,
column_alias)" syntax only allows you to alias the columns that are
the first N logical positions.  If you have a view which is aliasing
columns 1..3 of some table, and column 2 of the table gets moved to
position 7, the view definition now needs to alias columns 1, 2, and
7, which isn't possible with the present syntax unless you also alias
3, 4, 5, and 6.

> Changing physical positioning is purely an internal matter.  A first-cut
> implementation should probably just make it identical to logical
> positioning, until the latter is changed by the user (after which,
> physical positioning continues to reflect the original ordering).  Only
> after this work has been done and gotten battle-tested, we can get into
> niceties like having the server automatically rearrange physical
> positioning to improve performance.

Yeah.  The problem with that is that, as Tom pointed out in a previous
iteration of this discussion, you will likely have lurking bugs.  The
bugs are going to come from confusing physical vs. logical vs. column
identity, and if some of those are always-equal, it's gonna be pretty
hard to know if you have bugs that confuse the two.  Now, if you could
run the regression tests with a special option that would randomly
permute the two orderings with respect to one another, that would give
you at least some degree of confidence...

> Column identity is, of course, set in stone as soon as decided for the
> first time.

Agreed...  but I'd still like to hear some thoughts on where to put
the abstraction boundaries.

...Robert