Re: Proposal: GiST constraints

On Mon, 2008-06-09 at 21:00 -0400, Tom Lane wrote:
> 1. It's btree-specific and can't be shared by other index AMs that might
> wish to implement constraints.
>

This can be solved by my proposal, but I just don't know how it would
apply to something like GIN, for instance. It could replace the unique
constraint for BTree, but I'm not sure it would perform as well. It's
not that my proposal is GiST-specific, it's just that is the only use
case I can think of that is an improvement.

> 2. It involves the index AM reaching into the heap, which is at the
> least a serious failure of modularity.

We need to reach into the heap for visibility information, if we're to
implement any constraints at all. Also, we have to test against values
being inserted by other concurrent transactions, and those values can
be variable in size. What other mechanism do we have to share those
variable-sized values among several backends?

> 3. There's no way to implement a deferred uniqueness check, nor even to
> handle the within-statement conflict problem.

This is the big one.

> Now admittedly I don't have a solution that addresses these objections
> (much less one that does it without losing any performance) but I'm
> hesitant to see us building new features in this area without any idea
> how we will fix these things --- especially #3, which is a SQL-spec
> violation as well as a frequent user complaint. I'd like to have at
> least a design plan for fixing these things, so we know whether we are
> painting ourselves (further) into a corner.

I'll see if I can come up with something. I agree that's an important
problem to solve. Does anyone know how other DBMSs do this? I found this
thread from the TODO:

http://archives.postgresql.org/pgsql-hackers/2006-09/msg01458.php

Regards,
Jeff Davis