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

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(at)antzsystem(dot)de