Re: pg_dump making schema output consistent.

When you said 'certainly not' you're referring to the collation order
impacting the output order of columns within a table correct? which would
be great I can stop exploring that avenue.

Your explaination is also a little confusing, just for clarity going to ask
some explicit questions.

1) 'Ordinarily PG will preserve column order faithfully.' what is column
order? do you just mean the order in which columns were added to a table?
your example implies this.
2) what is the difference between 'PG' and pg_dump? (I'm reading it as PG =
postgresql server process, pg_dump as the dump tool).

atm I'm reading your explaination as:
postgresql the server process oridinarily preserves columns by their
creation order, pg_dump will reflect that order, but if you dump/restore
then the relationship of the ordering changes for parent/child tables.

given that I've correctly understood your email, it doesn't directly
address the issue I'm pointing out with column order in the schema dump.

the example you gave would be perfectly acceptable. I'm going to assume you
left out the fact pg_dump -s also emits the parent columns + the inherit
clause within the child table for brevity.

Given the fact without any schema details you hit on the fact these tables
happen to use table inheritance, I'm inclined to lean towards that being
the root cause of the differences in the schema dumps between machines.

On Mon, Sep 11, 2017 at 4:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> James Lawrence <jljatone(at)gmail(dot)com> writes:
> > unfortunately pg_dump has two problems for this usecase.
> > ...
> > 2) column ordering within a table is inconsistent. couple of our tables
> > columns get reordered (in a consistent manner) depending on which
> developer
> > generates the schema.
>
> > The inconsistent columns is the big problem, I suspect it is somehow
> > related to the collation order of the database.
>
> No, certainly not. Ordinarily PG will preserve column order faithfully.
> The only case I know of where it will not is if you have an inheritance
> relationship and the child table's columns are out of order with respect
> to the parent, which generally requires having done some sort of after-
> the-fact DDL on the parent. For instance
>
> create table parent (a int, b int, c int);
>
> create table child (d int, e int) inherits (parent);
>
> -- at this point child has columns a, b, c, d, e
>
> alter table parent add column f int;
>
> -- at this point child has columns a, b, c, d, e, f
>
>
> If you dump at this point, what'll be emitted is
>
> create table parent (a int, b int, c int, f int);
>
> create table child (d int, e int) inherits (parent);
>
> If you restore that, the child has columns a, b, c, f, d, e, in that
> order. It'd be possible for pg_dump to preserve the original ordering,
> but historically it hasn't done so, and from the aesthetic standpoint
> it's not clear that'd be better. After all, if you now do
>
> create table child2 (d int, e int) inherits (parent);
>
> then child2 is going to have a, b, c, f, d, e in either database.
>
> I don't have enough info to say exactly how this is manifesting
> as your problem, but I think you'll find it's something like that.
>
> regards, tom lane
>

--
James Lawrence