On Thu, Mar 20, 2008 at 02:35:38PM +0000, Simon Riggs wrote:
> The current Unique constraint relies directly upon a Unique index to
> test for uniqueness.
>
> This has two disadvantages:
>
> * only B-Trees currently support Uniqueness
> * We need to create an index on *all* of the columns of the primary key,
> which may end up being a very large index as a result
>
> The uniqueness check code searches for the value being inserted and if a
> value is found that is visible, then we reject. We currently use the
> same index scan key for the uniqueness check as we do for the index
> search.
>
> 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.
>
> If we extended the definition of a PRIMARY KEY to include an existing
> index like this
>
> ALTER TABLE foo ADD PRIMARY KEY (...) USING INDEX index_name;
>
> then we would be able to specify what we want.
>
> 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.
>
> Frequently the full unique key could be 5 or 6 columns, even though the
> leading columns might be sufficiently unique to make this technique
> worthwhile. It's also common to want to store the hash() of a value
> rather than the value itself, but the hash typically won't be
> guaranteeably unique, even though the probability of collisions may be
> very low.
>
> Thoughts?
>
> --
> Simon Riggs
> 2ndQuadrant http://www.2ndQuadrant.com
>
> PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
I like the idea. I am planning to provide unique index support
in the hash index update but wanted to be able to use it for
all of the primary key columns at once. This would allow that
to happen.
Ken