Re: operator exclusion constraints

On Wed, Nov 18, 2009 at 9:21 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> All,
>
> FWIW, I'm doing a redesign of a client's production web application
> right now.  I was able, by combining OEC and the Period type from
> pgfoundry, to make a set of constraints for declaratively asserting in a
> sports database:
>
> That the same player couldn't belong to two different teams at the same
> time;
> That the same player couldn't belong to the same team in two different
> positions with overlapping time periods.
>
> This worked as spec'd, and would be extremely useful for this real-world
> app if it was ready to use in production now.
>
> However, I do have an issue with the SQLSTATE returned from the OEC
> violation.  Currently it returns constraint violation, which, from the
> perspective of an application developer, is not useful.  OECs are, in
> application terms, materially identical to UNIQUE constraints and serve
> the same purpose.  As such, I'd far rather see OECs return unique key
> violation instead, as any existing application error-trapping code would
> handle the violation more intelligently if it did.

I guess I'm going to have to vote -1 on this proposal. I code see
inventing a pgsql-specific SQLSTATE value for exclusion constraints,
since they will be a pgsql-specific extension, but reusing the unique
key violation value seems misleading. I admit it may help in a
limited number of cases, but IMHO it's not worth the confusion.

That's just my $0.02, though.

...Robert