From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200803051620.m25GKW328140@momjian.us>
Subject: Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column
 values
In-Reply-To: <47BA12E7.4020506@mbnet.fi>
To: Juho Saarikko <juhos@mbnet.fi>
Date: Wed, 5 Mar 2008 11:20:32 -0500 (EST)
CC: PostgreSQL-documentation <pgsql-docs@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"


Does anyone think it is a good idea to document that our indexes cannot
index arbirarily-long strings?  I see nothing in the documentation now
about it.

---------------------------------------------------------------------------

Juho Saarikko wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> >   
> >> Juho Saarikko wrote:
> >>     
> >>> While I didn't test, I'd imagine that this would also mean that any attempt
> >>> to insert such values to an already unique column would fail.
> >>>       
> >
> >   
> >> Works here in 8.3:
> >>     
> > 	
> >   
> >> 	test=> create table test (x text unique);
> >> 	NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_x_key" for table "test"
> >> 	CREATE TABLE
> >> 	test=> insert into test values (repeat('a', 50000));
> >> 	INSERT 0 1
> >>     
> >
> > That test only works because it's eminently compressible.
> >
> >
> > The short answer to this bug report is that we're not very concerned
> > about fixing this because there is seldom a good reason to have an
> > index (unique or not) on fields that can get so wide.  As was already
> > noted, if you do need a uniqueness check you can easily make a 99.9999%
> > solution by indexing the md5 hash (or some similar digest) of the
> > column.  It doesn't really seem worthwhile to expend development work
> > on something that would benefit so few people.
> >
> > 			regards, tom lane
> >
> >   
> But the documentation needs to be updated to mention this nonetheless. 
> It is a nasty surprise if it hits unawares.
> 
> Besides, it's not such an impossible scenario. I encountered this bug 
> when making an Usenet image archival system. Since the same images tend 
> to be reposted a lot, it makes sense to store them only once, and simply 
> reference the stored image from each context it was posted in. Currently 
> my program does the uniqueness constraining by itself; I was examining 
> having the database enforce it when I ran into this issue.
> 
> Such applications are not exactly rare: bayimg, img.google.com, etc. and 
> of course the innumerable Usenet archival sites could all conceivably 
> want to do something like this. So could any application which monitors 
> potentially repeating phenomena, for that matter. After all, saving a 
> single state of the system only once not only reduces the amount of data 
> stored, but could also help in actual analysis of it, since it becomes 
> trivial to recognize most and least often recurring states.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +