Thread: pg_dump + function/table hierarchy
Unfortunately I managed to create a problematic database. The problem
occurs if I execute pg_dump and then try to reload the database. Details:
My only definitions in this database are:
> create function x(int4) returns bool as 'select $1 > 10;' language 'sql';
> create table y(z int4 check(x(z)));
Then, after pg_dump I got the next dump:
\connect - postgres
CREATE TABLE "y" ("z" int4,CONSTRAINT "y_z" CHECK (x(z))
);
CREATE FUNCTION "x" (int4 ) RETURNS bool AS 'select $1 > 10;' LANGUAGE 'SQL';
COPY "y" FROM stdin;
\.
Then, loading this dump back into the dropped and recreated database, I
got error messages, because the FUNCTION "x" is not created before the
TABLE "y".
The problem doesn't seems to be solved too easily. In fact, it is not a
good solution that FUNCTIONs are dumped before the TABLEs, because
the 'SQL' FUNCTIONs use the TABLEs in general (and the parser tries to
detect the SQL query). What to do? We need to store some kind of hierarchy
between table and function definitions, and after that could we say
something about the regenerating order. Of course, it needs at least one
new table among the system tables and needs pg_dump to be a bit more
complicated.
Regards,
Zoltan
------------------------------------------------------------------------------
KOVACS, Zoltan tip@pc10.radnoti-szeged.sulinet.hu
At 21:18 29/07/00 +0200, Kovacs Zoltan Sandor wrote: >Unfortunately I managed to create a problematic database. The problem >occurs if I execute pg_dump and then try to reload the database. Details: > >My only definitions in this database are: > >> create function x(int4) returns bool as 'select $1 > 10;' language 'sql'; >> create table y(z int4 check(x(z))); > This is fixed in the next version as well as an experimental version for 7.0.2 found at: ftp://ftp.rhyme.com.au/pub/postgresql/pg_dump/blobs/ The solution (as suggested by Tom Lane) was to sort the items by OID before dumping them. This is not a perfect solution, but it will cover 95% of cases. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
> This is fixed in the next version as well as an experimental version for > 7.0.2 found at: > > ftp://ftp.rhyme.com.au/pub/postgresql/pg_dump/blobs/ > > The solution (as suggested by Tom Lane) was to sort the items by OID before > dumping them. This is not a perfect solution, but it will cover 95% of cases. I tried this new pg_dump. It worked for me very well. But, for a database with 10000 rows it wasn't too fast dumping out (twice slower than the original one) and restoring it was three times slower. Thanks again, Zoltan
At 20:52 31/07/00 +0200, Kovacs Zoltan Sandor wrote: >> This is fixed in the next version as well as an experimental version for >> 7.0.2 found at: >> >> ftp://ftp.rhyme.com.au/pub/postgresql/pg_dump/blobs/ >> >> The solution (as suggested by Tom Lane) was to sort the items by OID before >> dumping them. This is not a perfect solution, but it will cover 95% of cases. >I tried this new pg_dump. It worked for me very well. But, for a >database with 10000 rows it wasn't too fast dumping out (twice slower than >the original one) and restoring it was three times slower. Can you send me the actual times? And the command you used? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 18:26 1/08/00 +0200, Kovacs Zoltan Sandor wrote: >> Can you send me the actual times? And the command you used? >I don't think my database is large enough to give you worthy data (~10000 >lines). But, with the old pg_dump I got 5 secs, with the new one: 9 secs. >I used the -Fc option. The old dump was 377K, the new one is 285K. The time diff and size diff is because the -Fc format compresses the data by default. If you did '-Fc -Z0', you would get 0 compression, a larger file, and a time that was closer to the original. >pg_restore was fast, it was less than 1 sec. The total restoring time with >psql was 29 secs from the old dump, 86 secs from the new one. 86 with the new one is a worry. It should not be that much slower - unless there are a lot of tables, in which case updating of pg_class to disable triggers for each table will slow it down a bit. I assume from this that you used pg_restore to create a script, which you then fed into psql. Is that right? If so, you might like to try: % createdb newdb % pg_restore <backup-file> --db=newdb this avoids using psql and should be faster. I'd be interested to know how slow/fast it is. If it it still slow, is there any chance you could mail me the backup file directly? I'll certainly understand if you say 'no', for obvious reasons. >I use Linux >2.2-12.20 (RedHat 6.1), the server is a 366 MHz Intel Celeron with a >WD64AA hard disk w/ 2048K cache. Should be plenty. >By the way, is there any statistics about the dumping/restoring time with >larger databases? Is there a faster method to backup and restore a >database than I did? As above; dump: use -Z0 (no compression), restore: use --db to attach to the DB directly. Statistics I have done with databases of 100,000 records and 1.3M records show no appreciable difference between the old & new for both dump & restore. The dump always takes longer if compression is used. I have not compared it to piping the output to gzip, but I imagine they are similar. Hope this helps, Philip. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
> Can you send me the actual times? And the command you used? I don't think my database is large enough to give you worthy data (~10000 lines). But, with the old pg_dump I got 5 secs, with the new one: 9 secs. I used the -Fc option. The old dump was 377K, the new one is 285K. pg_restore was fast, it was less than 1 sec. The total restoring time with psql was 29 secs from the old dump, 86 secs from the new one. I use Linux 2.2-12.20 (RedHat 6.1), the server is a 366 MHz Intel Celeron with a WD64AA hard disk w/ 2048K cache. By the way, is there any statistics about the dumping/restoring time with larger databases? Is there a faster method to backup and restore a database than I did? Regards, Zoltan