operator exclusion constraints [was: generalized index constraints]

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: operator exclusion constraints [was: generalized index constraints]
Date: 2009-09-19 20:32:43
Message-ID: 1253392364.23353.193.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Sat, 2009-09-19 at 10:48 -0700, Jeff Davis wrote:
> CONSTRAINT foo_constr (a <op>, ...)
> { USING INDEX foo_idx | USING method }

I am updating the syntax to be:

CONSTRAINT foo_constr
EXCLUSION (a <op>, ...) { USING method | INDEX foo_idx };

First, I think EXCLUSION makes a perfect noun to fit in that place (like

Second, this makes it possible to avoid specifying the index, and the
system can create one for you by knowing the access method. That makes
the feature a little more declarative.

However, it still doesn't provide a way to express two constraints using
one index all within CREATE TABLE, because the index would need to be
defined before the constraints in that case. I don't see that as a
problem, but Peter had the following concern:

"Another problem this would lead to is that a say dump of a table
definition wouldn't actually contain all the constraints that apply to
the table anymore, because there might be additional stuff such as this
that can't be expressed that way." [1]

I don't think that's a serious problem, I just need to ensure that
indexes referenced by a constraint are dumped before the constraint
itself. Then, I can dump the operator exclusion constraints (OXCs) as
ALTER TABLEs. The "-t" option to pg_dump appears to already dump
constraints as separate ALTER TABLEs. Is there something that I'm

Jeff Davis

[1] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01018.php

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-09-19 20:43:21 Re: WIP: generalized index constraints
Previous Message Jeff Davis 2009-09-19 20:22:22 Re: WIP: generalized index constraints