Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5EB782E0050 for ; Wed, 20 Feb 2008 04:55:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75240-04 for ; Wed, 20 Feb 2008 04:55:55 -0400 (AST) Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id 135D82E0055 for ; Wed, 20 Feb 2008 04:55:54 -0400 (AST) thread-index: AchznnomrJUmfabJRMOVPVHrW/8DXA== Received: from [192.168.1.33] ([81.109.253.218]) by mail01.enterprisedb.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.3959); Wed, 20 Feb 2008 03:56:26 -0500 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4133 Message-ID: <47BBEADA.4090308@enterprisedb.com> Date: Wed, 20 Feb 2008 08:54:50 +0000 From: "Heikki Linnakangas" Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.9 (X11/20080110) MIME-Version: 1.0 To: "Juho Saarikko" Cc: Subject: Re: BUG #3965: UNIQUE constraint fails on long column values References: <200802181130.m1IBUNdu060026@wwwmaster.postgresql.org> In-Reply-To: <200802181130.m1IBUNdu060026@wwwmaster.postgresql.org> Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 20 Feb 2008 08:56:26.0890 (UTC) FILETIME=[79FFE6A0:01C8739E] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/175 X-Sequence-Number: 19742 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