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

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. +