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

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