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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers