Re: Unique constraints for non-btree indexes

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Wed, Jan 18, 2006 at 04:18:10PM -0500, Tom Lane wrote:
>> In btree we can identify a unique page to lock for any given key value
>> to ensure that no one else is concurrently inserting a conflicting
>> key, thus usually allowing concurrent insertions of different keys.
>> But I don't see how you do that for an arbitrary ~ operator.

> The issue I get is deadlocks:

Right, the deadlock risk is exactly the reason you need some secret
sauce or other. Btree's page-level lock ensures that two insertions of
conflicting keys can't overlap (even if they ultimately get stored on
different pages). That's not the only way to fix this but it's a pretty
good way.

BTW, the deadlock risk also applies to deferred uniqueness checks.
Again, in btree it's possible to avoid this if you do a fresh indexscan
(and take a lock on the first scanned page while you do that). If you
try to do it without consulting the index then you need some other way
to break "ties".

regards, tom lane