Обсуждение: BUG #3850: Incompatibility among pg_dump / pg_restore.
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
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
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
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
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