Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 111264759C6; Mon, 20 May 2002 22:37:45 -0400 (EDT) Received: from ra.bpsi.net (ra.bpsi.net [199.199.134.1]) by postgresql.org (Postfix) with ESMTP id 643E5475BC5; Mon, 20 May 2002 22:37:38 -0400 (EDT) Received: (from duccini@localhost) by ra.bpsi.net (8.11.0/8.11.0) id g4L2bZk18929; Mon, 20 May 2002 21:37:35 -0500 (CDT) Date: Mon, 20 May 2002 21:37:33 -0500 (CDT) From: "D. Duccini" X-Sender: duccini@ra.bpsi.net To: andre Cc: pgsql-sql@postgresql.org, pgsql-novice@postgresql.org Subject: Re: Guarantee uniqueness of PGP key In-Reply-To: <20020520201709.A3F8A80269@pepe.lfcity.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200205/204 X-Sequence-Number: 3834 good grief yeah, look at using the MD5 or SHA1 hash of the key itself -d On Mon, 20 May 2002, andre wrote: > Hi, > > More generically, how do I best guarantee that any large varchar is unique > before inserting? > > In my application, I need to ensure that each e-mail address and > corresponding PGP public key are both unique. > > I originally implemented this by adding a uniqueness contrainst to both > columns. easy. > > The problem is that some PGP keys are over 2713 bytes, and postgres (7.2) > gives the error: > > ERROR: btree: index item size 4336 exceeds maximum 2713 > > I thought perhaps I could use a hash or rtree index, but neither of them work > for unique-ness. > > I saw in another posting that it may be possible to raise this limit to 8k or > so, but I don't want to do this because 1) it requires a recompile of > postgres (and rollout to dev, stage, prod machines), and 2) a key could > theoretically be longer than 8k anyway. > > I can guarantee unique-ness by removing the index and doing a select before > the insert. If there is a matching row, then I am trying to insert a dup. > bail. > > My concern is that doing this without an index will be monumentally slow for > any large-ish number of keys. > > Any bright ideas? All suggestions appreciated. > > /andre > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------