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.
The idea is to make an array in shared memory with size equal to
max_connections. Each element of the array would hold the oid of an
index, and a tid.
When inserting into a GiST index, take out a lock on the array, and scan
it for other tuples being concurrently inserted into the same index, and
execute some function to determine if any tuples conflict (using the tid
stored in the array). Then, check the visibility of the tuple at that
tid. If the conflicting tuple is live, release the lock on the array and
abort. If the tuple is dead, set the array entry to be invalid, make an
entry in the array, and release the lock. If the tuple has an xmin or
xmax that's still in progress, release the lock on the array, block
waiting on the appropriate xid, and then try again. If no conflicts
exist in the array, make an entry in the array, and release the lock.
Then, search the GiST index using the same function to determine if
conflicts exist in the index. If conflicts exist in the index, check the
visibility information for the tuple and proceed, wait or abort (in the
same way as above). If no conflicts exist, insert.
This should work fine for multi-column indexes where the constraints for
each column are different. For instance, unique and non-overlapping
could be mixed.
I spoke about this idea with several people at EAST and PGCon. In
particular, Teodor had the good idea to store the tid in the array,
rather than the value, to make the idea more general to types of
different sizes.
Thoughts, ideas?
Regards,Jeff Davis
