Re: Order of enforcement of CHECK constraints?

* 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.
> 
> There's at least one regression test case where this can happen, so we've
> been lucky so far that this hasn't caused buildfarm noise.
> 
> We could fix it by, say, having CheckConstraintFetch() sort the
> constraints by name after loading them.
> 
> In principle the same problem could occur for domain CHECK constraints,
> though the odds of multiple CHECKs failing are probably a lot lower.
> 
> Do people think this is worth fixing?

Yes...  I had thought they were sorted and enforced in alphabetical
order similar to how triggers are fired.  Having non-deterministic
check constraint firing seems bad to me.
Thanks,
	Stephen