Обсуждение: BUG #2306: Duplicate primary key

The following bug has been logged online:

Bug reference:      2306
Logged by:          Andreas Jung
Email address:      lists@andreas-jung.com
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?

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