Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id CD0B82E0057 for ; Wed, 20 Feb 2008 05:27:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 02885-06-4 for ; Wed, 20 Feb 2008 05:26:39 -0400 (AST) Received: from oxford.xeocode.com (unknown [87.127.95.194]) by postgresql.org (Postfix) with ESMTP id 61A242E004F for ; Wed, 20 Feb 2008 05:26:43 -0400 (AST) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.69) (envelope-from ) id 1JRlDs-0006tL-8D; Wed, 20 Feb 2008 09:26:40 +0000 To: "Heikki Linnakangas" Cc: "Juho Saarikko" , Subject: Re: BUG #3965: UNIQUE constraint fails on long column values In-Reply-To: <47BBEADA.4090308@enterprisedb.com> (Heikki Linnakangas's message of "Wed\, 20 Feb 2008 08\:54\:50 +0000") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.bugs" 2307) References: <200802181130.m1IBUNdu060026@wwwmaster.postgresql.org> <47BBEADA.4090308@enterprisedb.com> From: Gregory Stark Organization: EnterpriseDB Date: Wed, 20 Feb 2008 09:26:35 +0000 Message-ID: <877ih0x9kk.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/176 X-Sequence-Number: 19743 "Heikki Linnakangas" 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!