Re: Duplicate primary keys/rows

--- Michael Fuhr <mike(at)fuhr(dot)org> wrote:

> On Sun, Oct 09, 2005 at 12:46:51PM -0700, CSN wrote:
> > select * from table1 where id=586;
> > 586|a|b|c|d
>
> Do you get different results from the following
> queries?
>
> SET enable_seqscan TO on;
> SET enable_indexscan TO off;
> SELECT * FROM table1 WHERE id = 586;

This returns 2 rows.

> SET enable_seqscan TO off;
> SET enable_indexscan TO on;
> SELECT * FROM table1 WHERE id = 586;

This returns 1 row.

> > Yet:
> > select * from table1 where id>=585 and id<=587;
> > 585|c|a|e|f
> > 586|a|b|c|d
> > 586|a|b|c|d
> > 587|g|e|r|z
>
> What's the output of the following query?
>
> RESET enable_seqscan;
> RESET enable_indexscan;
>
> SELECT oid, ctid, xmin, cmin, xmax, cmax, *
> FROM table1
> WHERE id >= 585 AND id <= 587;

oid | ctid | xmin | cmin | xmax | cmax
| id
--------+-----------+---------+------+---------+------+-----
125465 | (3143,78) | 1664385 | 0 | 1664386 | 2
| 984
125466 | (2745,50) | 1481020 | 0 | 1682425 | 2
| 985
125466 | (2672,11) | 1445346 | 0 | 1481020 | 0
| 985
125467 | (3159,28) | 1671875 | 0 | 1671876 | 2
| 986

(I'm using a different duplicate row - 985. I deleted
586's duplicate.)

Is this a problem with the index? Would rebuilding
them fix this problem? I'm still curious why this
happened, and somewhat troubled that something like
this can happen.

Thanks for your help,
CSN

> If you get the error 'column "oid" does not exist'
> then you've
> created the table without oids, so just omit oid
> from the select
> list:
>
> SELECT ctid, xmin, cmin, xmax, cmax, *
> FROM table1
> WHERE id >= 585 AND id <= 587;
>
> > Wow, how is this possible? I'm using PG 8.0.3 on
> > Windows XP. This computer has been crashing
> repeatedly
> > lately, if that could be blamed (bad memory? hard
> > disk? I haven't quite figured out why.)
>
> Faulty hardware is one possibile explanation.
>
> --
> Michael Fuhr
>

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com