Re: different column orders in regression test database

On 2017/05/19 11:02, Peter Eisentraut wrote:
> On 5/18/17 19:07, Thomas Munro wrote:
>> To make normal dump/restore preserve the order, we could either make
>> it *always* write create-then-attach, or do it only if required. I'd
>> vote for doing it only if required because of different column order,
>> because I don't want to see 1,000 partitions dumped in "long format"
>> when the short and sweet CREATE... PARTITION OF ... syntax could
>> usually be used.
>
> Doing it the long way only when necessary makes sense. Maybe never
> doing it the long way also makes sense, as long as we're clear that
> that's what we want.

I tend to prefer the latter - never doing it the long way (which is what
happens today [1]). It's always better for all the partitions to have the
same tuple descriptor as the parent in the target database, which is what
the short CREATE TABLE .. PARTITION OF syntax would result in. The long
format is unavoidable in the case of --binary-upgrade dump mode for
obvious reasons.

Thanks,
Amit

[1]

create table p (a int, b char) partition by list (a);
create table p1 (c int, b char, a int);
alter table p1 drop c;
alter table p attach partition p1 for values in (1);
insert into p values (1, 'a');
select tableoid::regclass, * from p;
tableoid | a | b
----------+---+---
p1 | 1 | a
(1 row)

$ pg_dump

CREATE TABLE p (
a integer,
b character(1)
)
PARTITION BY LIST (a);

CREATE TABLE p1 PARTITION OF p
FOR VALUES IN (1);

COPY p1 (b, a) FROM stdin;
a 1
\.