Hey hackers:
I'vebeen using pg_dump in a typical three db setup: development,
staging, and live. The output of pg_dump is ordered by oid, so as the
db's histories diverge, the output does as well. That is, if identical
tables get created in the development and staging dbs, for example, but
in a different order, I can't us diff to test this. I was wondering if
there is any reason why the order couldn't be by tablename, instead of
oid, since the ordering of creation of sequences and types and such is
taken care of. 

Ah I think I just figured it out: it's that pesky object
support, isn't it? In order to use a table (class) as a member (field)
of another table, it has to exist first, and the only thing in the
system table that ensures that is oid. Bummer. Hmm, it'd still be useful
for comparision purposes, but it wouldn't gaurantee correct SQL scripts.
Perhaps I'll just hack my local copy with an extra switch for "class
name order output".  Anyone else want it?

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Sun, 12 Sep 1999 20:18:56 -0500
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
pg_dump table order
On Sun, Sep 12, 1999 at 08:18:56PM -0500, Ross J. Reedstrom wrote:
> 
> Ah I think I just figured it out: it's that pesky object
> support, isn't it? In order to use a table (class) as a member (field)
> of another table, it has to exist first, and the only thing in the
> system table that ensures that is oid. Bummer. Hmm, it'd still be useful

Talking to myself: "Gee Ross, why don't you read the friendly comments
in the source you just found the ordey by oid in, explaining exactly
this point?"

Ross "the blind"
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Sun, 12 Sep 1999 20:35:27 -0500
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
Re: [HACKERS] pg_dump table order
> Hey hackers:
> I'vebeen using pg_dump in a typical three db setup: development,
> staging, and live. The output of pg_dump is ordered by oid, so as the
> db's histories diverge, the output does as well. That is, if identical
> tables get created in the development and staging dbs, for example, but
> in a different order, I can't us diff to test this. I was wondering if
> there is any reason why the order couldn't be by tablename, instead of
> oid, since the ordering of creation of sequences and types and such is
> taken care of. 
> 
> Ah I think I just figured it out: it's that pesky object
> support, isn't it? In order to use a table (class) as a member (field)
> of another table, it has to exist first, and the only thing in the
> system table that ensures that is oid. Bummer. Hmm, it'd still be useful
> for comparision purposes, but it wouldn't gaurantee correct SQL scripts.
> Perhaps I'll just hack my local copy with an extra switch for "class
> name order output".  Anyone else want it?
> 

I thought someone already did that.  It may be in 6.5.1.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 12 Sep 1999 22:05:35 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_dump table order
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> Ah I think I just figured it out: it's that pesky object
> support, isn't it? In order to use a table (class) as a member (field)
> of another table, it has to exist first, and the only thing in the
> system table that ensures that is oid. Bummer. Hmm, it'd still be useful
> for comparision purposes, but it wouldn't gaurantee correct SQL scripts.
> Perhaps I'll just hack my local copy with an extra switch for "class
> name order output".  Anyone else want it?

Better idea: make pg_dump smarter, so that it sorts the tables by name
as far as possible without breaking inheritance and membership
dependencies.  It already retrieves the inheritance graph, and it could
certainly figure column-type dependencies too.  I don't think anyone
would object to producing the output in a more meaningful order, so
I see no need for a switch if you can make this work.

I used to know enough about topological sorts to sketch how this ought
to work, but that was years ago :-(.  I do see that the simplest
approach to a sort comparison function, "if a depends on b then say a>b,
else say result of comparing name(a) and name(b)", will not work because
it's not transitive.

			regards, tom lane

Mon, 13 Sep 1999 10:41:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] pg_dump table order 
> Better idea: make pg_dump smarter, so that it sorts the tables by name
> as far as possible without breaking inheritance and membership
> dependencies.  It already retrieves the inheritance graph, and it could
> certainly figure column-type dependencies too.  I don't think anyone
> would object to producing the output in a more meaningful order, so
> I see no need for a switch if you can make this work.
> 
> I used to know enough about topological sorts to sketch how this ought
> to work, but that was years ago :-(.  I do see that the simplest
> approach to a sort comparison function, "if a depends on b then say a>b,
> else say result of comparing name(a) and name(b)", will not work because
> it's not transitive.

I now someone fixed some of that recently, and I thought it was in 6.5.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 13 Sep 1999 11:35:53 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_dump table order

On 13-Sep-99 Bruce Momjian wrote:
>> Better idea: make pg_dump smarter, so that it sorts the tables by name
>> as far as possible without breaking inheritance and membership
>> dependencies.  It already retrieves the inheritance graph, and it could
>> certainly figure column-type dependencies too.  I don't think anyone
>> would object to producing the output in a more meaningful order, so
>> I see no need for a switch if you can make this work.
>> 
>> I used to know enough about topological sorts to sketch how this ought
>> to work, but that was years ago :-(.  I do see that the simplest
>> approach to a sort comparison function, "if a depends on b then say a>b,
>> else say result of comparing name(a) and name(b)", will not work because
>> it's not transitive.
> 
> I now someone fixed some of that recently, and I thought it was in 6.5.

Unfortunately not, if I use some functions in CONSTRANE clause of
CREATE TABLE, I can't restore from backup made by pg_dump.
It's nice idea always dump functions first. 

---
Dmitry Samersoff, dms@wplus.net, ICQ:3161705
http://devnull.wplus.net
* There will come soft rains ...

Mon, 13 Sep 1999 21:31:35 +0400 (MSD)
Dmitry Samersoff <dms@wplus.net>
Re: [HACKERS] pg_dump table order