From: Tom Lane <tgl@sss.pgh.pa.us>
To: James Lawrence <jljatone@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_dump making schema output consistent.
Date: Mon, 11 Sep 2017 16:44:14 -0400
Message-ID: <2916.1505162654@sss.pgh.pa.us> (raw)
In-Reply-To: <CALOHWHV0VpsaUBRwqOcvOu-FsiFPwbCZpq2GafDjnA+_1u_g9w@mail.gmail.com>
References: <CALOHWHV0VpsaUBRwqOcvOu-FsiFPwbCZpq2GafDjnA+_1u_g9w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
James Lawrence <jljatone@gmail.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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, jljatone@gmail.com
Subject: Re: pg_dump making schema output consistent.
In-Reply-To: <2916.1505162654@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox