Re: Proposal: GiST constraints

On Mon, 2008-06-09 at 13:28 -0400, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > I would like to consider adding constraints to GiST indexes. I think it
> > is possible to add constraints that are more sophisticated than just
> > UNIQUE. My use case is a non-overlapping constraint, but I think it's
> > possible for this to be more general.
>
> I would like to see something that replaces the current btree-only kluge
> for UNIQUE, if we're going to try to do something "general". IOW, don't
> think of this as GiST-specific.
>

I was concerned that the BTree kludge would outperform what I am
suggesting for the case of UNIQUE, and might therefore still be
necessary.

My proposal requires an extra index lookup, because in GiST a
conflicting tuple isn't necessarily found near the place it might be
inserted. Maybe that cost is not too high, because for the case of BTree
UNIQUE it would just be accessing the same pages twice (once to test for
conflicts, and once to insert).

I'm not sure exactly what you have in mind when you say "kludge". My
proposal doesn't solve the problem of "update foo set a = a + 1", in
which the UNIQUE constraint may fail when it should succeed. I don't see
how that problem can be solved without deferring the constraint checking
until the end of the statement, which sounds costly.

Regards,
Jeff Davis