On Mon, Mar 23, 2015 at 7:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes: > I might be only one objecting here but ... > On Sat, Mar 21, 2015 at 12:45 AM, Tom Lane <tgl@sss.pgh.pa.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