Обсуждение: 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? 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
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
> 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, Pennsylvania19026
"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
> 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, Pennsylvania19026
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 ...