Re: BUG #3724: Duplicate values added to table despite unique index

On Nov 6, 2007 11:16 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Mason Hale" <masonhale(at)gmail(dot)com> writes:
> > However running the same query on the original 8.2.4 database returns
> zero
> > rows:
>
> > prod_1=> select page_id, count(*) from topic_version_page where
> > topic_version_id = 263 group by 1 having count(*) > 1;
> > page_id | count
> > ---------+-------
> > (0 rows)
>
> Is that still true if you do the query with enable_indexscan = off
> and enable_bitmapscan = off? If you see matching rows then, I'd
> suspect a corrupt index in the 8.2.4 installation --- does REINDEXing
> it succeed?
>

With enable_indexscan = off and enable_bitmapscan = off on the
8.2.4instance, it still returns zero rows.

prod_1=> set enable_indexscan = off;
SET
prod_1=> set enable_bitmapscan = off;
SET
prod_1=> select page_id, count(*) from topic_version_page where
topic_version_id = 263 group by 1 having count(*) > 1;
page_id | count
---------+-------
(0 rows)

> For that matter, do you still see dups if you prevent use of the index
> in the 8.2.5 query? Maybe it's that index that is corrupt.

Unfortunately, I'm not able to test that at this point.
To get our production db (the 8.2.5 instance) back in operation I deleted
the extra duplicate rows, so that the update statement would complete.

If I need to reindex the table in 8.2.5 database I'll need to wait until
this evening, barring any emergencies. (It is a 20GB table)

Mason