Re: logical column ordering

Tomas Vondra wrote:

> > We need an API for physical column reordering, even if it's just pg_
> > functions. The reason is that we want to enable people writing their
> > own physical column re-ordering tools, so that our users can figure out
> > for us what the best reordering algorithm is.
>
> I doubt that. For example, do you realize you can only do that while the
> table is completely empty, and in that case you can just do a CREATE
> TABLE with the proper order?

Not if you have views or constraints depending on the table definition
-- it's not trivial to drop/recreate the table in that case, but you can
of course think about truncating it, then reorder columns, then
repopulate.

Even better you can cause a full table rewrite if needed.

> But if we want to allow users to define this, I'd say let's make that
> part of CREATE TABLE, i.e. the order of columns defines logical order,
> and you use something like 'AFTER' to specify physical order.
>
> CREATE TABLE test (
> a INT AFTER b, -- attlognum = 1, attphysnum = 2
> b INT -- attlognum = 2, attphysnum = 1
> );

Surely you want an ALTER command as a minimum; perhaps that is enough
and there is no need for options in CREATE.

> It might get tricky because of cycles, though.

If there's a cycle, just raise an error.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services