Re: pg_dump and restore problem with function as DEFAULT-Constraint

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