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