The following bug has been logged online:
Bug reference: 4913
Logged by: Mathieu De Zutter
Email address: mathieu@dezutter.org
PostgreSQL version: 8.3.7
Operating system: Debian Lenny
Description: Row missing from primary key index
Details:
I have a table log_event with a primary key on an integer 'id', called
log_event_pkey.
The tables contains a duplicate for id = 15723018. The duplicate (note that
besides the id, all data differs) doesn't seem to be known by the index at
all.
Example:
shs=# select id, event_timestamp from log_event where id = 15723018;
id | event_timestamp
----------+----------------------------
15723018 | 2009-05-09 13:47:33.441668
(1 row)
But with indexscan and bitmapscan off, I get:
shs=# select id, event_timestamp from log_event where id = 15723018;
id | event_timestamp
----------+----------------------------
15723018 | 2009-05-09 13:47:48.68558
15723018 | 2009-05-09 13:47:33.441668
(2 rows)
Table definition:
shs=# \d log_event
Table "public.log_event"
Column | Type |
Modifiers
-----------------+-----------------------------+----------------------------
----------------------------
id | bigint | not null default
nextval('log_event_id_seq'::regclass)
user_id | integer |
ip | inet | not null
action_id | integer | not null
object1_id | integer |
object2_id | integer |
event_timestamp | timestamp without time zone | not null
Indexes:
"log_event_pkey" PRIMARY KEY, btree (id)
"log_event_action_id_idx" btree (action_id)
"log_event_timestamp_idx" btree (event_timestamp)
"log_event_user_id_idx" btree (user_id)
Foreign-key constraints:
"log_event_action_id_fkey" FOREIGN KEY (action_id) REFERENCES
config.log_action(id)
In this table definition you can also see that 'id' is generated by a
sequence, which makes it even stranger to contain a dupe.
I have made a copy of the complete pg cluster, so I can debug without
interfering with my production db.