"Albe Laurenz" <laurenz.albe@wien.gv.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