Re: Separate the attribute physical order from logical order

Hi,

On Tue, Jun 28, 2022 at 09:00:05AM -0400, Isaac Morland wrote:
> On Tue, 28 Jun 2022 at 05:32, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> > I think that supporting at least a way to specify the logical order during
> > the
> > table creation should be easy to implement (there shouldn't be any
> > question on whether it needs to invalidate any cache or what lock level to
> > use), and could also be added in the initial submission without much extra
> > efforts, which could help with the testing.
> >
>
> I think the meaning of “logical order” (well, the meaning it has for me, at
> least) implies that the logical order of a table after CREATE TABLE is the
> order in which the columns were given in the table creation statement.
>
> If there needs to be a way of specifying the physical order separately,
> that is a different matter.

Well, the way I see it is that the logical order is something that can be
changed, and therefore is the one that needs to be spelled out explicitly if
you want it to differ from the physical order.

But whether the physical or logical order is the one that needs explicit
additional syntax, it would still be nice to provide in a first iteration. And
both versions would be the same to implement, difficulty wise.
>
> ALTER TABLE ADD … is another matter. Syntax there to be able to say BEFORE
> or AFTER an existing column would be nice to have. Presumably it would
> physically add the column at the end but set the logical position as
> specified.

Yes, but it raises some questions about lock level, cache invalidation and such
so I chose to ignore that for the moment.