Re: Unique Constraints using Non-Unique Indexes

On Thu, 2008-03-20 at 18:37 +0100, Martijn van Oosterhout wrote:
> On Thu, Mar 20, 2008 at 02:35:38PM +0000, Simon Riggs wrote:
> > This would then allow us to use a Hash Index or other index as the basis
> > for a Unique Constraint and/or considerably reduce size of indexes.
> 
> I was under the impression that the reason only b-tree supported unique
> indexes was because it could lock one page while to checking visibility
> to block concurrent inserts. AIUI other index types (notably gist) would
> not be able to easily block concurrent inserts because the place a new
> item is entered into the index is not unique nor necessarily deterministic.

The existing code can already move onto other pages. It needs to be able
to do this to support >1 row version(s) in the index. So all the
concepts and code are already there to do what I've suggested.

> Whether hash could support this usage I don't know.

Hash could do it too, but that's other code I've not looked at yet. But
the locking issues are similar are least.

The main barrier to this is really just being able to specify that's
what you want and then have that info passed thru to where its needed.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com 
 PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk