Re: WIP: generalized index constraints

On Sat, 2009-09-19 at 18:00 -0400, Tom Lane wrote:
> Well, you can't do it *exactly* the same way btree does, but what
> I would envision is first insert the index tuple and then do a
> dirty-snapshot search for conflicting tuples.  The interlock against
> conflicting concurrent inserts doesn't need all this new infrastructure
> you propose; just wait to see if conflicting transactions commit, same
> as we do now.  And I do maintain that that sort of code has a high risk
> of undetected bugs.

How do you prevent deadlocks in the following case?

T1: inserts into index
T2: inserts into index
T1: checks index for conflicts, finds T2
T2: checks index for conflicts, finds T1

We can't say "only wait if your xid is higher" because xid 200 may both
insert and check the index before xid 100 even inserts.

The way I solve this in my current patch is by assigning a sequence
number in a shared memory table for each insert. The sequence number
works because a higher sequence number will always be able to see a
lower sequence number's tuple, so we can safely say "only wait if you
have a higher sequence number".

I can tack the same solution onto your idea, but I would need to keep my
shared memory table and probably some other infrastructure. It may be
less complex than it is currently, however. Simpler ideas welcome.

And to clarify the syntax issue, I assume this means that: ((a||b)::circle &&)

would look for the column in the index that matches that expression, and
then use that attribute number when scanning the index? I'm OK with
that; I don't see a lot of obvious value in having separate expressions
for the constraint and the index (even if it did have value, it would
take some real creativity to find it ;)

Regards,Jeff Davis