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

Поиск
Список
Период
Сортировка
От Bruce Momjian
Тема Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
Дата
Msg-id 200803051620.m25GKW328140@momjian.us
обсуждение исходный текст
Ответы Re: Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values  (Phil Frost <phil@macprofessionals.com>)
Список pgsql-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@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. +

В списке pgsql-docs по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: FAQ on Embedding Postgres
Следующее
От: Phil Frost
Дата:
Сообщение: Re: Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values