On Thu, 6 Jun 2013, Tom Lane wrote:
> ijmorlan@uwaterloo.ca writes:
>> My understanding is that pg_dump is supposed to include tables in SQL dumps
>> in alphabetical order by table name.
>
> That's the default behavior as long as there are no ordering constraints
> that would dictate doing something else. You haven't provided enough
> info to tell whether there's such a constraint in your case ...
Thanks, I wanted to establish that before digging up more detail (more
recently, I noticed that the 8.0 Release Notes advertise the use of
alphabetical order as new, although I can't find an indication in the
actual pg_dump documentation that it tries to use alphabetical order).
What I have is a dev instance which is was created by running a psql
script file. I also have a prod instance which was originally created by
running an older version of the script but has had some changes applied.
When I pg_dump the two instances (using --schema-only), I get files that
are identical, except that one table is out of place in the dump from
prod, both in the part where it puts the table definitions and further
down where it grants permissions.
When I load the pg_dump outputs, and then dump the resulting schemas,
those dumps are identical to each other and almost identical to the
original dev dump (although different from the original dev dump by the
addition of extra "::text" type specifiers in a few view definitions).
So, the difference between dev and prod is nothing that shows up in
pg_dump output.
I thought of different foreign key references to the table having crept in
somehow, but a \d+ on both the dev and prod version of the problem table
gives the same results - both versions of the table are referenced by the
same 3 corresponding tables.
I should also add that I'm not clear on when alphabetical order for tables
would not work - with all constraints being added on at the end after all
tables have been created (and populated, in the case of a data dump, which
is not my case), is there ever a time when tables have to be in a
different order? I think views have to be in an order determined by their
dependencies, but not tables.
One final thought I had was to start digging around in the system tables
to find the difference (in principle, the difference *has* to be there,
right?). So I looked at the pg_class records for my problem table as
compared to other tables, and I don't see any suspicious differences. I
suppose next I might start digging into other related pg_* tables to see
if I can find anything.
Obviously, this is still not enough to debug pg_dump (even assuming there
actually is a problem there, which isn't exactly my default explanation).
However, I'm hoping it provides enough detail for you to be able to say
whether there is a chance of this being a real bug.
Isaac Morland CSCF Web Guru
DC 2554C, x36650 WWW Software Specialist