Обсуждение: Guarantee uniqueness of PGP key
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
Haven't tried, but would the md5 routine in pg_crypto (contrib/) work for you? The odds of your discovering two keys that reduce to the same md5 key is incredibly small, so it's close enough to check for uniqueness. Don't know if that will take input that large, tho'. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of andre > Sent: Monday, May 20, 2002 4:17 PM > To: pgsql-sql@postgresql.org > Cc: pgsql-novice@postgresql.org > Subject: [SQL] Guarantee uniqueness of PGP key > > > 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 4: Don't 'kill -9' the postmaster >
On Mon, May 20, 2002 at 15:17:09 -0500, andre <andre@liquidprivacy.net> wrote: > > The problem is that some PGP keys are over 2713 bytes, and postgres (7.2) > gives the error: You might want to rethink your design. You probably want just the RSA or DSA public key to be unique. Otherwise you will treat different versions of what is essentially the same key as being different. (This might be due to be signed by a different set of keys.) I suspect that this isn't what you want. I think even the craziest people are using keys that should be around 1000 bytes. Paranoid people (or those needing very long lived public keys) are using ones half that size.
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!" -----------------------------------------------------------------------------
Yes, I've decided to relax the unique-ness constraint on the public key. The email address is still guaranteed unique. And we send out an encrypted verification email that only the person with the private key can decrypt and respond to. Thus, it doesn't really hurt to have multiple people paste in the same pub key. If I really wanted uniqueness, I think you (and the others who replied) are correct that I should be storing the 'fingerprint' in a separate column. /andre On Tuesday 21 May 2002 06:54 am, Bruno Wolff III wrote: > On Mon, May 20, 2002 at 15:17:09 -0500, > > andre <andre@liquidprivacy.net> wrote: > > The problem is that some PGP keys are over 2713 bytes, and postgres (7.2) > > gives the error: > > You might want to rethink your design. You probably want just the RSA or > DSA public key to be unique. Otherwise you will treat different versions of > what is essentially the same key as being different. (This might be due > to be signed by a different set of keys.) I suspect that this isn't what > you want. I think even the craziest people are using keys that should > be around 1000 bytes. Paranoid people (or those needing very long lived > public keys) are using ones half that size.