Re: [HACKERS] different column orders in regression test database

On Fri, May 19, 2017 at 7:21 AM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> But this is a bit more suspicious:
>
> Original:
>
>              Table "public.mlparted11"
>  Column |  Type   | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
>  b      | integer |           | not null |
>  a      | integer |           | not null |
> Partition of: mlparted1 FOR VALUES FROM (2) TO (5)
>
> Reloaded:
>
>              Table "public.mlparted11"
>  Column |  Type   | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
>  a      | integer |           | not null |
>  b      | integer |           | not null |
> Partition of: mlparted1 FOR VALUES FROM (2) TO (5)
>
> The same applies for other tables in this partitioning group:
> public.mlparted12, public.mlparted2, public.mlparted4
>
> But the root table public.mlparted matches on both sides.
>
> While you can create all kinds of dubious messes with general
> inheritance, this should probably not be allowed to happen in the
> restricted setting of partitioning.

That's because if you attach a partition with a different column
ordering, pg_dump dumps it with a normal CREATE TABLE ... PARTITION OF
... command, so the ordering it lost.

Example:

create table p (a int, b int) partition by list (a);
create table c (b int, a int);
alter table p attach partition c for values in (42);

Then "c" is dumped as:

CREATE TABLE c PARTITION OF p
FOR VALUES IN (42);

If you wanted to preserve column orders for partitions I guess you'd
have to teach to to detect the difference (ignoring dropped columns?)
and generate the two step create-and-attach commands.

-- 
Thomas Munro
http://www.enterprisedb.com