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

Thanks a lot Stefan. You are right.

Thanks for your support.

Diego Spano

-----Mensaje original-----
De: Stefan Kaltenbrunner [mailto:stefan(at)kaltenbrunner(dot)cc]
Enviado el: Lunes, 07 de Enero de 2008 04:03 p.m.
Para: Diego Spano
CC: pgsql-bugs(at)postgresql(dot)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