Re: Order of enforcement of CHECK constraints?

On Mon, Mar 23, 2015 at 7:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
> > I might be only one objecting here but ...
> > On Sat, Mar 21, 2015 at 12:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> My Salesforce colleagues noticed some tests flapping as a result of
> table
> >> CHECK constraints not always being enforced in the same order; ie, if a
> >> tuple insertion/update violates more than one CHECK constraint, it's not
> >> deterministic which one is reported. This is evidently because
> >> relcache.c's CheckConstraintFetch() just happily loads up the
> constraints
> >> in whatever order it happens to find them in pg_constraint.
>
> > Why is it important to report in deterministic manner?
>
> If nothing else, so as not to have regression-test failures.
>

May be then we are writing tests which are not doing the intended thing.
Unless the test is explicitly testing the behaviour in case of more than
one failing constraint, it looks like the test is not doing the right
thing. If it's testing that case explicitly, by imposing an order, we are
still testing a single constraint failure case.

In case of million inserts or bulk load with constraints on, these few
cycles spent in ordering the constraints might be problematic, unless the
ordering happens only once for a series of inserts.

> > If it really
> > matters, we should probably report all the failing constraints.
>
> That wouldn't in itself make the output deterministic (you'd still have to
> sort); and in any case that's not going to happen because it would require
> running each CHECK constraint in its own subtransaction. Catching errors
> that way is *expensive*. And there's been zero field demand for such a
> behavior, so I don't see us adding cycles for something no one's asked
> for. Sorting the check constraints during relcache load, on the other
> hand, is a negligible burden compared to the cost of reading
> pg_constraint.
>
> regards, tom lane
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company