Thread: pg_dump and restore problem with function as DEFAULT-Constraint
Hello, i have some trouble with dumping and restoring a database with postgresql 7.4.7-6sarge4 (debian sarge). Some tables have plpgsql-functions as DEFAULT contraints. But these functions are dumped after the CREATE TABLE command. Now these tables can't be created on restore. If i'm dumping only structure and import twice in a new and empty database (this works), i can't restore the data-only dump. Cause in the data-only dump the table order was wrong in respect to FOREIGN KEY constraints and the constraints are now active (instead of a full-dump, where constraints will be created after data was inserted). Why DEFAULT (and NOT NULL) constraints would'nt be added after data insertion? (like Primary-Key and Foreign-Key) What can i do to enforce the order of plpgsql functions prior to all table structures? -- Markus Schulz - msc@antzsystem.de
am Tue, dem 27.02.2007, um 17:44:06 +0100 mailte Markus Schulz folgendes: > Hello, > > i have some trouble with dumping and restoring a database with > postgresql 7.4.7-6sarge4 (debian sarge). Is the new database also 7.4? Why not 8.1 oder 8.2? > > Some tables have plpgsql-functions as DEFAULT contraints. But these > functions are dumped after the CREATE TABLE command. Now these tables > can't be created on restore. IIRC a know issue with 7.x, but solved (i hope...) in 8.x. If you upgrade to such a version, you should use the pg_dump(all) from this against the old database. > > If i'm dumping only structure and import twice in a new and empty > database (this works), i can't restore the data-only dump. Cause in the > data-only dump the table order was wrong in respect to FOREIGN KEY > constraints and the constraints are now active (instead of a full-dump, > where constraints will be created after data was inserted). > > Why DEFAULT (and NOT NULL) constraints would'nt be added after data > insertion? (like Primary-Key and Foreign-Key) > > What can i do to enforce the order of plpgsql functions prior to all > table structures? You can use pg_restore with -l to generate a listfile for all objects in the database. Then you can reorder this ($EDITOR) and then use -L to use this ordered listfile to enforce the right order of objects. I hope this helps you. (man pg_restore) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Markus Schulz <msc@antzsystem.de> writes:
> i have some trouble with dumping and restoring a database with
> postgresql 7.4.7-6sarge4 (debian sarge).
> ...
> What can i do to enforce the order of plpgsql functions prior to all
> table structures?
Update to PG 8.x --- IIRC 8.0 was the first release in which pg_dump
really understood about dependencies properly. 7.x is dumping basically
in creation order, which falls down as soon as you start using ALTER
TABLE to create dependencies that weren't there originally.
regards, tom lane
Am Dienstag, 27. Februar 2007 18:28 schrieb A. Kretschmer: > am Tue, dem 27.02.2007, um 17:44:06 +0100 mailte Markus Schulz folgendes: > > Hello, > > > > i have some trouble with dumping and restoring a database with > > postgresql 7.4.7-6sarge4 (debian sarge). > > Is the new database also 7.4? Why not 8.1 oder 8.2? 7.4. We are using some selfwritten modules and currently they are not ported to 8.x. > > Some tables have plpgsql-functions as DEFAULT contraints. But these > > functions are dumped after the CREATE TABLE command. Now these > > tables can't be created on restore. > > IIRC a know issue with 7.x, but solved (i hope...) in 8.x. If you > upgrade to such a version, you should use the pg_dump(all) from this > against the old database. > > > If i'm dumping only structure and import twice in a new and empty > > database (this works), i can't restore the data-only dump. Cause in > > the data-only dump the table order was wrong in respect to FOREIGN > > KEY constraints and the constraints are now active (instead of a > > full-dump, where constraints will be created after data was > > inserted). > > > > Why DEFAULT (and NOT NULL) constraints would'nt be added after data > > insertion? (like Primary-Key and Foreign-Key) > > > > What can i do to enforce the order of plpgsql functions prior to > > all table structures? > > You can use pg_restore with -l to generate a listfile for all objects > in the database. Then you can reorder this ($EDITOR) and then use -L > to use this ordered listfile to enforce the right order of objects. I > hope this helps you. thanks, nice idea. I will try it. -- Markus Schulz - msc@antzsystem.de
Markus Schulz <msc@antzsystem.de> schrieb: > > You can use pg_restore with -l to generate a listfile for all objects > > in the database. Then you can reorder this ($EDITOR) and then use -L > > to use this ordered listfile to enforce the right order of objects. I > > hope this helps you. > > thanks, nice idea. I will try it. I have never used this, please tell me if you have success with this. (And yes, i hope, i never need this...) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Am Dienstag, 27. Februar 2007 20:37 schrieb Andreas Kretschmer: > Markus Schulz <msc@antzsystem.de> schrieb: > > > You can use pg_restore with -l to generate a listfile for all > > > objects in the database. Then you can reorder this ($EDITOR) and > > > then use -L to use this ordered listfile to enforce the right > > > order of objects. I hope this helps you. > > > > thanks, nice idea. I will try it. > > I have never used this, please tell me if you have success with this. > (And yes, i hope, i never need this...) this works. There is only one general problem with pg_restore, it breaks if there were errors cause of duplicates. If you have some stuff in your template and the same in your dump you must solve this manually. Perhaps there should be an "-ignore-errors" option. -- Markus Schulz