Re: Constraint documentation

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> writes:
> I've put the patch as "Ready".

I think this could be improved some more. Perhaps something like this
(I've not bothered with markup...)

PostgreSQL does not support CHECK constraints that reference table
data other than the new or updated row being checked. While a CHECK
constraint that violates this rule may appear to work in simple
tests, it cannot guarantee that the database will not reach a state
in which the constraint condition is false (due to subsequent changes
of the other row(s) involved). This would cause a database dump and
reload to fail. The reload could fail even when the complete
database state is consistent with the constraint, due to rows not
being loaded in an order that will satisfy the constraint. If
possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express
cross-row and cross-table restrictions.

If what you desire is a one-time check against other rows at row
insertion, rather than a continuously-maintained consistency
guarantee, a custom trigger can be used to implement that. (This
approach avoids the dump/reload problem because pg_dump does not
reinstall triggers until after reloading data, so that the check will
not be enforced during a dump/reload.)

This is a little verbose maybe, but as the text stands, it sounds like
using a trigger is enough to solve all the consistency problems that
a cross-row CHECK has. Which it's not of course.

I'm also wondering whether it's better to put this in the CREATE TABLE
reference page instead of here. While there are certainly benefits in
having the caveat here, I'm a bit troubled by the number of forward
references to concepts that are described later. OTOH, a lot of people
who need the warning might never see it if it's buried in the reference
material.

regards, tom lane