Re: Facing error while restoring the database

"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> pg_dump does not resolve dependencies, it avoids problems by adding
> constraints after inserting the data.

> It seems that this is not done for CHECK constraints, however - they are
> added when the table is defined.

> I think that this is a bug.

It is not a bug; it is an unsafe and unsupported use of CHECK
constraints.

Using a CHECK to enforce a cross-row constraint is fundamentally broken,
because there is no way for the database to know that the constraint
might be violated after the *other* row is modified. In the example
at hand, a change in sample_one.param_names could leave the constraint
unsatisfied for some rows in sample, but the database wouldn't detect
that.

I think the right fix here would be to redesign the table schema so that
the required cross-table constraint could be expressed as a foreign key.
We don't have enough context to guess at what a better design would
look like, though.

regards, tom lane