Re: BUG #2306: Duplicate primary key

On Tue, Mar 07, 2006 at 04:43:18PM +0000, Andreas Jung wrote:
> PostgreSQL version: 7.4.9

7.4.12 is the latest in that branch; it contains several bug fixes
since 7.4.9.

> This gives me two rows with the same id=5077:
>
> Toolbox2Test=# select * from hierarchy where id >= 5077 order by id;

What's the output of the following command?

SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077 ORDER BY id;

> Search for all rows with id=5077 returns this:
>
> Toolbox2Test=# select * from hierarchy where id = 5077;
[...]
> (1 row)

Does the same query return different results depending on whether
you use an index scan or a sequential scan?  What do you get for
these queries?

SET enable_seqscan TO on;
SET enable_indexscan TO off;
SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077;
SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077;

SET enable_seqscan TO off;
SET enable_indexscan TO on;
SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id >= 5077;
SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077;

--
Michael Fuhr