Re: logical column ordering

On 27.2.2015 23:48, Josh Berkus wrote:
> On 02/27/2015 12:48 PM, Tomas Vondra wrote:
>> On 27.2.2015 21:42, Josh Berkus wrote:
>>> On 02/27/2015 12:25 PM, Tomas Vondra wrote:
>>>> On 27.2.2015 21:09, Josh Berkus wrote:
>>>>> Tomas,
>>>>>
>>>>> So for an API, 100% of the use cases I have for this feature would be
>>>>> satisfied by:
>>>>>
>>>>> ALTER TABLE ______ ALTER COLUMN _____ SET ORDER #
>>>>>
>>>>> and:
>>>>>
>>>>> ALTER TABLE _____ ADD COLUMN colname coltype ORDER #
>>>>
>>>> Yes, I imagined an interface like that. Just to be clear, you're
>>>> talking about logical order (and not a physical one), right?
>>>
>>> Correct. The only reason to rearrange the physical columns is in
>>> order to optimize, which presumably would be carried out by a utility
>>> command, e.g. VACUUM FULL OPTIMIZE.
>>
>> I was thinking more about CREATE TABLE at this moment, but yeah, VACUUM
>> FULL OPTIMIZE might do the same thing.
>
> Actually, I'm going to go back on what I said.
>
> 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?

I also doubt the users will be able to optimize the order better than
users, who usually have on idea of how this actually works internally.

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
);

It might get tricky because of cycles, though.

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