Skip site navigation (1) Skip section navigation (2)

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Juho Saarikko <juhos(at)mbnet(dot)fi>
Cc: PostgreSQL-documentation <pgsql-docs(at)postgresql(dot)org>
Subject: Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
Date: 2008-03-05 16:20:32
Message-ID: 200803051620.m25GKW328140@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-docs
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(at)momjian(dot)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(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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

In response to

Responses

pgsql-docs by date

Next:From: Phil FrostDate: 2008-03-05 16:32:57
Subject: Re: Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
Previous:From: Bruce MomjianDate: 2008-03-05 15:09:38
Subject: Re: FAQ on Embedding Postgres

pgsql-bugs by date

Next:From: Phil FrostDate: 2008-03-05 16:32:57
Subject: Re: Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
Previous:From: Bruce MomjianDate: 2008-03-05 15:53:38
Subject: Re: [BUGS] BUG #3975: tsearch2 index should not bomb out of 1Mb limit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group