Re: [BUGS] BUG #2306: Duplicate primary key

2006-03-08 Thread Michael Fuhr 
On Tue, Mar 07, 2006 at 04:43:18PM +, 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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[BUGS] BUG #2306: Duplicate primary key

2006-03-08 Thread Andreas Jung 

The following bug has been logged online:

Bug reference:  2306
Logged by:  Andreas Jung
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 7.4.9
Operating system:   Linux
Description:Duplicate primary key
Details: 

Ihave the following table (with 'id' as primary key:

Toolbox2Test=# \d hierarchy

 Table "public.hierarchy"

 Column  |Type |
Modifiers

-+-+

-+-+
---

 id  | integer | not null default
nextval('public.hierarchy_id_seq'::text)

 parentid| bigint  |

 idprodukt   | bigint  |

 bezeichnung | character varying(160)  |

 neudat  | timestamp without time zone |

 aedat   | timestamp without time zone |

 benutzer| character varying(32)   |

 pos | integer | default 0

 linkindex   | character varying(20)   |

 deleted | boolean | default false

 visible | boolean |

 sorting | boolean |

 comment | character varying(265)  |

 idhierarchy_share   | integer |

 show_gattung_in_bauplan | boolean | default false

 sortierung  | character varying(10)   |

Indexes:

"hierarchy_pkey" PRIMARY KEY, btree (id)

"hierarchy_deleted_idx" btree (deleted)

"hierarchy_idhierarchy_share_idx" btree (idhierarchy_share)

"hierarchy_idprodukt_idx" btree (idprodukt)



This gives me two rows with the same id=5077:

Toolbox2Test=# select * from hierarchy where id >= 5077 order by id;

  id   | parentid | idprodukt |  bezeichnung
 |   neudat   |   aedat

 | benutzer | pos | linkindex | deleted | visible | sorting |   
  comment   | idhierarchy_share |
show_gattung_in_bauplan

| sortierung

---+--+---+-
--++
---

-+--+-+---+-+-+-+---
-+---+--
---

+

  5077 | 4062 |   | Präsentieren   
  || 2005-11-23
12:03:38.617

969 | RossmyU  |   1 | LI353323  | f   | | | CSV
import from test_tools.csv |   | f  
|



  5077 | 4062 |   | Präsentation   
  || 2005-11-24
15:43:50.756

414 | RossmyU  |   0 | LI353323  | t   | | | CSV
import from test_tools.csv |   | t  
|



  5078 | 4062 |   | Rechner 
 || 2005-11-23
12:03:38.61

7969 | RossmyU  |   2 | LI353324  | f   

Search for all rows with id=5077 returns this:

Toolbox2Test=# select * from hierarchy where id = 5077;



  id  | parentid | idprodukt | bezeichnung  | neudat |   aedat  
 | benutzer | pos | linkindex | deleted | visible | sorting |   
comment | idhierarchy_share | show_gattung_in_bauplan |
sortierung

--+--+---+--++--
--+--+-+---+-+-+-+--
--+---+-+---
-

 5077 | 4062 |   | Präsentieren || 2005-11-23
12:03:38.617969 | RossmyU  |   1 | LI353323  | f   | | |
CSV import from test_tools.csv |   | f  
|

(1 row)


Any idea how to resolve this issue?

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq