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

Re: unique index problems

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Costin Grigoras <costing(at)cs(dot)pub(dot)ro>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: unique index problems
Date: 2004-05-08 22:09:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Some time back, Costin Grigoras <costing(at)cs(dot)pub(dot)ro> wrote:
> how could duplicate records appear when the unique index existed ?

After re-reading this old bug report, I realized that all of the methods
you were applying would make use of the index itself to search for
duplicates --- the count(*) subquery and the trigger's delete would most
likely use indexscan plans, unless you were to take steps to prevent it.

That means that we only have to make one assumption to explain all the
symptoms: the index got corrupted in such a way that it would not find
certain rows.  Then it would be possible to insert new rows with the
same keys without the duplication being detected, either by the index
itself or by the queries you were using.

When you tried to reindex, the uncorrupted new index of course detected
the duplicates.

Of course the evidence is long gone about *how* the index got
corrupted.  Had you had any recent system crashes or hardware problems?

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Dani MezherDate: 2004-05-10 20:53:19
Subject: insert/upate/delete permissions on views
Previous:From: Bruce MomjianDate: 2004-05-08 03:00:35
Subject: Re: [BUGS] BUG #1148: server restarts depending on stats options

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