Re: different column orders in regression test database

On Fri, May 19, 2017 at 7:21 AM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)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