Search Postgresql Archives

Re: Duplicate primary keys/rows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



--- Michael Fuhr <mike@xxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux