Re: Constraint documentation

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
Cc: Patrick Francelle <patrick(at)francelle(dot)name>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Pantelis Theodosiou <ypercube(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Lætitia Avrot <laetitia(dot)avrot(at)gmail(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Brad DeJong <bpd0018(at)gmail(dot)com>, Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Constraint documentation
Date: 2018-11-14 23:02:53
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2018-11-14 23:58:42 Re: Speeding up INSERTs and UPDATEs to partitioned tables
Previous Message Andres Freund 2018-11-14 21:49:48 Re: Refactoring the checkpointer's fsync request queue