Skip site navigation (1) Skip section navigation (2)

Re: BUG #3965: UNIQUE constraint fails on long column values

From: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
To: "Juho Saarikko" <juhos(at)mbnet(dot)fi>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values
Date: 2008-02-20 08:54:50
Message-ID: 47BBEADA.4090308@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-docs
Juho Saarikko wrote:
> I suggest implementing unique hash indexes and automatically creating one
> (and turning the b-tree index into a non-unique one) when a large value is
> inserted to fix this. Alternatively, fix b-trees so they can handle large
> values; however, a hash index should be far more efficient for this specific
> case, since the size of a hash is independent of pre-hash data size.

The current implementation of hash indexes actually store the whole key, 
in addition to the hash, so the size of the hash index is not 
independent of the key size. There has been some discussion on revamping 
the hash index implementation, and changing that among other things, but 
don't hold your breath.

As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a 
pretty good work-around.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-docs by date

Next:From: Gregory StarkDate: 2008-02-20 09:26:35
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values
Previous:From: Kris JurkaDate: 2008-02-19 00:26:14
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values

pgsql-bugs by date

Next:From: Gregory StarkDate: 2008-02-20 09:26:35
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values
Previous:From: Maxime CarbonneauDate: 2008-02-20 05:57:29
Subject: BUG #3972: ERROR: function 59015 returned NULL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group