Re: Order of enforcement of CHECK constraints?

On Sunday, March 22, 2015, David Steele <david(at)pgmasters(dot)net> wrote:

> On 3/20/15 3:37 PM, Tom Lane wrote:
> > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)com
> <javascript:;>> writes:
> >> On Fri, Mar 20, 2015 at 4:19 PM, Peter Geoghegan <pg(at)heroku(dot)com
> <javascript:;>> wrote:
> >>> On Fri, Mar 20, 2015 at 12:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <javascript:;>> wrote:
> >>>> We could fix it by, say, having CheckConstraintFetch() sort the
> >>>> constraints by name after loading them.
> >
> >>> What not by OID, as with indexes? Are you suggesting that this would
> >>> become documented behavior?
> >
> >> I think they should be executed in alphabetical order like triggers.
> >
> > Yeah. We already have a comparable, and documented, behavior for
> > triggers, so if we're going to do anything about this I'd vote for
> > sorting by name (or more specifically, by strcmp()).
> >
> > regards, tom lane
>
> +1 for strcmp() ordering. Not only is this logical and consistent with
> the way triggers are fired, names can be manipulated by the user to
> force order when desired. Not sure if that's as important for check
> constraints as it is for triggers but it might be useful, even if only
> for things like unit tests.
>
> --
> - David Steele
> david(at)pgmasters(dot)net <javascript:;>
>
>
It would be nice to know that, at scale, the added comparisons are
negligible since it almost all cases all checks are run and pass and the
order is irrelevant. Would it be possible for all check constraints to
always be run and the resultant error outputs stored in an array which
could then be sorted before it is printed? You get better and stable
output for errors while minimally impacting good inserts.

David J.