Обсуждение: pg_dump and restore problem with function as DEFAULT-Constraint

Поиск
Список
Период
Сортировка

pg_dump and restore problem with function as DEFAULT-Constraint

От
Markus Schulz
Дата:
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

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

От
"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?

>
> 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

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

От
Tom Lane
Дата:
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

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

От
Markus Schulz
Дата:
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

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

От
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...)


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°

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

От
Markus Schulz
Дата:
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