Re: Order of enforcement of CHECK constraints?

On 21/03/15 08:15, Tom Lane 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?
>
>             regards, tom lane
>
>
I think that this is a good idea, I would have implicitly assumed that 
it was deterministic.

Additionally, people could then name CHECK constraints in a way to get 
whatever order they wanted.

The documentation of CREATE TRIGGER says (reading Fabrizio's post 
inspired me to look it up):
"If multiple triggers of the same kind are defined for the same event, 
they will be fired in alphabetical order by name."
So I would have implicitly assumed the same for CHECK constraints, had I 
recently read that.

So I think the current situation is a violation of POLA.


Cheers,
Gavin