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

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

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
Cc: "Juho Saarikko" <juhos(at)mbnet(dot)fi>, <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values
Date: 2008-02-20 09:26:35
Message-ID: 877ih0x9kk.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-docs
"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:

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

Unless you need cryptographic security I would not suggest using MD5. MD5 is
intentionally designed to take a substantial amount of CPU resources to
calculate.

Postgres's internal hash method is exposed for most data types as hashtext()
hashfloat8(), hashint4(), etc. These functions were chosen for their
lightweight design.

Cryptographic security is important only if you're concerned with people being
able to intentionally create collisions. In this scenario that's probably not
a top threat. Conceivably someone could create a denial-of-service attack
slowing down your server by causing your indexes to become unbalanced. But it
would be fairly challenging to engineer.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

In response to

Responses

pgsql-docs by date

Next:From: Francisco Olarte SanzDate: 2008-02-20 11:21:03
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values
Previous:From: Heikki LinnakangasDate: 2008-02-20 08:54:50
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values

pgsql-bugs by date

Next:From: Vincent D'HaeneDate: 2008-02-20 09:42:34
Subject: Re: BUG #3951: SELECT ... WHERE Param = ? does not work if Param is of type bytea
Previous:From: Heikki LinnakangasDate: 2008-02-20 08:54:50
Subject: Re: BUG #3965: UNIQUE constraint fails on long column values

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