Re: Unique Constraints using Non-Unique Indexes

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:

> If the uniqueness check used a scan key that consisted of all of the
> Primary Key columns, rather than just the index columns then it would be
> able to scan through non-unique index entries to check uniqueness.
> Interestingly, the current uniqueness check code already scans through
> multiple tuples because of the possible existence of multiple row
> versions. So we just need to supply a different scan key.

The tricky part about unique constraints is guaranteeing that only one
transaction can see their insert as "first". If you just did a simple scan and
went ahead if you don't see any conflicts you would risk having two inserters
not see each others insert and go ahead and index their insert and proceed.

As I understand it we normally protect against that by holding a lock on the
first page of the key we're inserting while we perform the uniqueness check.
Could you still do that in this case? I don't immediately see any problems
aside from reduced concurrency but this code isn't really my forté.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning