Thread: BUG #3850: Incompatibility among pg_dump / pg_restore.

BUG #3850: Incompatibility among pg_dump / pg_restore.

From
"Diego Spano"
Date:
The following bug has been logged online:

Bug reference:      3850
Logged by:          Diego Spano
Email address:      djspano@jus.gov.ar
PostgreSQL version: 8.1.9
Operating system:   Debian Etch 4.0
Description:        Incompatibility among pg_dump / pg_restore.
Details:

I have two servers running Debian Etch 4.0 and Postgres 8.1.9. and want to
copy Database_A from server1 to server2. It is suppossed that
pg_dump/pg_restore should have no problems.

In server1 run pg_dump, then run pg_restore on server2, but the database
can´t be restored because pg_restore want to restore table rows that have
foreign keys before table that have the primary keys.

"pg_restore: ERROR:  the new record for relation «archivo» violates
restriction. Check "subfk_archivo_seremp".

And records are not added obviously.

I think that pg_dump should export database tablas according to constraints
and relations, don´t?

Thank s for any help.

Diego Spano

Re: BUG #3850: Incompatibility among pg_dump / pg_restore.

From
Stefan Kaltenbrunner
Date:
Diego Spano wrote:
> The following bug has been logged online:
>
> Bug reference:      3850
> Logged by:          Diego Spano
> Email address:      djspano@jus.gov.ar
> PostgreSQL version: 8.1.9
> Operating system:   Debian Etch 4.0
> Description:        Incompatibility among pg_dump / pg_restore.
> Details:
>
> I have two servers running Debian Etch 4.0 and Postgres 8.1.9. and want to
> copy Database_A from server1 to server2. It is suppossed that
> pg_dump/pg_restore should have no problems.
>
> In server1 run pg_dump, then run pg_restore on server2, but the database
> can´t be restored because pg_restore want to restore table rows that have
> foreign keys before table that have the primary keys.
>
> "pg_restore: ERROR:  the new record for relation «archivo» violates
> restriction. Check "subfk_archivo_seremp".
>
> And records are not added obviously.
>
> I think that pg_dump should export database tablas according to constraints
> and relations, don´t?

pg_dump by default will dump table structure first than data and apply
constraints only at the very end so this is usually a "can't happen"
error. If you are nevertheless getting errors like this you are likely
using a data-only dump or seperate schema/data dumps which obviously
cannot provide those guarantees.


regards

Stefan

Re: BUG #3850: Incompatibility among pg_dump / pg_restore.

From
Stefan Kaltenbrunner
Date:
Diego Spano wrote:
> Hi Stefan,

Hi Diego!

Please keep the list CC'd so that other people can participate in the
discussion - I have readded it now ...

>
> I test all possibilities, schema and data togheter, separated files, tar
> files, plain text, from command line, from PG_Admin. But allways is the
> same.

hmm this seems rather strange. A full dump will definitly load all the
data before any constraints are applied - can you please explain in
detail what the exact steps you take when you are doing a dump & restore?



Stefan

Re: BUG #3850: Incompatibility among pg_dump / pg_restore.

From
Stefan Kaltenbrunner
Date:
Diego Spano wrote:
> Stefan / List, these are the steps:
>
> 1- pg_dump sicoba|gzip>/home/backups/pg_backup/backup.pg
>
> 2- createdb sicoba6
>
> 3- psql -d sicoba6 <  backup.pg
>
> And thats all.
>
> Errors appear when trying to add rows to first table, and so on...
>
> Find attached backup.pg.

ok just took a look at that dump and the problem here is that you have
CHECK constraints wrapped in a function that are doing lookups on other
data than the current row.
This is simply not supported (see the manual on that) and because
postgresql does not now that there is some sort of hidden dependency on
some data to exist it cannot actually infer that this might be a
problem(might be worth to consider dumping CHECK constraints after
loading the data though).
If you think that through there might not even be a "correct" way to
dump a database because depending on the complexity of the CHECK
constraint there might not even be a way to load data in the "correct"
way (think circular dependencies or dependencies on special values in
multiple tables).
In short you really need to look into converting the CHECK contraints on
those two tables into triggers which will make this problem go away.


regards


Stefan

Re: BUG #3850: Incompatibility among pg_dump / pg_restore.

From
"Diego Spano"
Date:
Thanks a lot Stefan. You are right.

Thanks for your support.

Diego Spano




-----Mensaje original-----
De: Stefan Kaltenbrunner [mailto:stefan@kaltenbrunner.cc]
Enviado el: Lunes, 07 de Enero de 2008 04:03 p.m.
Para: Diego Spano
CC: pgsql-bugs@postgresql.org
Asunto: Re: [BUGS] BUG #3850: Incompatibility among pg_dump / pg_restore.

Diego Spano wrote:
> Stefan / List, these are the steps:
>
> 1- pg_dump sicoba|gzip>/home/backups/pg_backup/backup.pg
>
> 2- createdb sicoba6
>
> 3- psql -d sicoba6 <  backup.pg
>
> And thats all.
>
> Errors appear when trying to add rows to first table, and so on...
>
> Find attached backup.pg.

ok just took a look at that dump and the problem here is that you have CHECK
constraints wrapped in a function that are doing lookups on other data than
the current row.
This is simply not supported (see the manual on that) and because postgresql
does not now that there is some sort of hidden dependency on some data to
exist it cannot actually infer that this might be a problem(might be worth
to consider dumping CHECK constraints after loading the data though).
If you think that through there might not even be a "correct" way to dump a
database because depending on the complexity of the CHECK constraint there
might not even be a way to load data in the "correct"
way (think circular dependencies or dependencies on special values in
multiple tables).
In short you really need to look into converting the CHECK contraints on
those two tables into triggers which will make this problem go away.


regards


Stefan