Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 988202E003E for ; Wed, 20 Feb 2008 07:51:58 -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 66088-02 for ; Wed, 20 Feb 2008 07:51:49 -0400 (AST) Received: from oxford.xeocode.com (unknown [87.127.95.194]) by postgresql.org (Postfix) with ESMTP id 03F4A2E0032 for ; Wed, 20 Feb 2008 07:51:55 -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 1JRnUJ-0007Ig-HU; Wed, 20 Feb 2008 11:51:47 +0000 To: "Heikki Linnakangas" Cc: "Juho Saarikko" , Subject: Re: BUG #3965: UNIQUE constraint fails on long column values In-Reply-To: <47BC0E41.3040704@enterprisedb.com> (Heikki Linnakangas's message of "Wed\, 20 Feb 2008 11\:25\:53 +0000") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX" 65332) References: <200802181130.m1IBUNdu060026@wwwmaster.postgresql.org> <47BBEADA.4090308@enterprisedb.com> <877ih0x9kk.fsf@oxford.xeocode.com> <47BC0E41.3040704@enterprisedb.com> From: Gregory Stark Organization: EnterpriseDB Date: Wed, 20 Feb 2008 11:51:47 +0000 Message-ID: <87tzk3x2uk.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/182 X-Sequence-Number: 19749 "Heikki Linnakangas" writes: "Heikki Linnakangas" writes: > Gregory Stark wrote: >> "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. > > Return type of hash* functions is just 32 bits. I wonder if that's wide enough > to avoid accidental collisions? Depends on the application of course... Oh, I missed that you were suggesting a UNIQUE index. That seems unsafe to me even for md5 or its ilk. But that would depend on the application too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!