BUG #3231: Duplicate rows primary key bug

The following bug has been logged online:

Bug reference: 3231
Logged by: Amorn Buchheit
Email address: amornb(at)yahoo(dot)com
PostgreSQL version: 7.3.4
Operating system: Linux
Description: Duplicate rows primary key bug
Details:

Duplicate primary key record
Report Date: April 13, 2007

Subject: Duplicate primary key record



Your name : Amorn Buchheit

Your email address : amornb(at)yahoo(dot)com





System Configuration

---------------------

Server: IBM
Operating System: Linux

version
-----------------------------------------------------------------
PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC 2.96

There are several tables have two same rows with the same primary key. Some
tables have a unique index with two same
value. This shouldn't happen.
Has this been reported as a bug in this version?





Table "public.student_year_rlt"

Column | Type | Modifiers

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

user_id | integer | not null

school_year_id | integer | not null default 4

grade_level_id | integer | not null

year_detail | character varying(250) |

school_id | integer | not null default 1

status_id | integer | not null default 1

created_date | timestamp with time zone | not null default 'now'

last_modified | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone

Indexes: student_year_rlt_pkey primary key btree (user_id, school_year_id,
school_id)

Foreign Key constraints: school_id_fk FOREIGN KEY (school_id) REFERENCES
school_info_l(school_id) ON UPDATE NO ACTION ON DELETE NO ACTION,

grade_level_id_fk FOREIGN KEY (grade_level_id)
REFERENCES grade_level_l(grade_level_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
status_id_fk FOREIGN KEY (status_id) REFERENCES
status_l(status_id) ON UPDATE NO ACTION ON DELETE NO ACTION,

school_year_id_fk FOREIGN KEY (school_year_id)
REFERENCES school_year_l(school_year_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
user_id_fk FOREIGN KEY (user_id) REFERENCES
user_common(user_id) ON UPDATE NO ACTION ON DELETE NO ACTION

Triggers: RI_ConstraintTrigger_5663425,

RI_ConstraintTrigger_5663426,

tr_add_school_id,

tr_insert_school_id,

tr_last_modified

project=# select * from student_year_rlt where user_id = 792 and
school_year_id = 6; user_id |
school_year_id | grade_level_id | year_detail | school_id | status_id |
created_date | last_modified
user_id | school_year_id | grade_level_id | year_detail | school_id |
status_id | created_date | last_modified
---------+----------------+----------------+-------------+-----------+------
-----+-------------------------------+-------------------------------
792 | 6 | 4 | | 10437 |
1 | 2007-03-08 01:09:17.040851-06 | 2007-04-03 01:09:53.577369-05
792 | 6 | 4 | | 10437 |
1 | 2007-03-08 01:09:17.040851-06 | 2007-04-03 01:09:53.577369-05
(2 rows)

SELECT oid,xmin,xmax,* FROM student_year_rlt WHERE user_id = 792 and
school_year_id = 6;
project=# SELECT oid,xmin,xmax,* FROM student_year_rlt WHERE user_id = 792
and school_year_id = 6;
oid | xmin | xmax | user_id | school_year_id | grade_level_id |
year_detail | school_id | status_id | created_date |
last_modified
---------+---------+------+---------+----------------+----------------+-----
--------+-----------+-----------+-------------------------------+-----------
--------------------
5664062 | 4311665 | 87 | 792 | 6 | 4 |
| 10437 | 1 | 2007-03-08 01:09:17.040851-06 | 2007-04-03
01:09:53.577369-05
5664062 | 4311665 | 87 | 792 | 6 | 4 |
| 10437 | 1 | 2007-03-08 01:09:17.040851-06 | 2007-04-03
01:09:53.577369-05
(2 rows)

project=#
Table "public.bm_student_answers"
Column | Type | Modifiers
-------------------+--------------------------+------------------------
bm_publication_id | integer | not null
user_id | integer | not null
school_id | integer | not null
bm_question_id | integer | not null
bm_answer_id | integer |
correct_answer | boolean | not null default false
time_answered | timestamp with time zone | not null default 'now'
status_id | integer | default 1
created_date | timestamp with time zone | default now()
last_modified | timestamp with time zone | default now()
Indexes: bm_student_answers_pk primary key btree (bm_publication_id,
user_id, school_id, bm_question_id)
Foreign Key constraints: bm_publication_id_fk FOREIGN KEY
(bm_publication_id) REFERENCES bm_publications(bm_publication_id) ON UPDATE
NO ACTION ON DELETE NO ACTION
Triggers: tr_last_modified,
tr_time_answered

project=# select * from bm_student_answers where user_id = 101 and
bm_publication_id = 10944 and bm_question_id = 38270 and school_id = 10437;
bm_publication_id | user_id | school_id | bm_question_id | bm_answer_id |
correct_answer | time_answered | status_id |
created_date | last_modified
-------------------+---------+-----------+----------------+--------------+--
--------------+-------------------------------+-----------+-----------------
--------------+-------------------------------
10944 | 101 | 10437 | 38270 | 154737 | t
| 2007-04-03 15:34:24.010232-05 | 1 | 2007-04-03
15:34:24.010232-05 | 2007-04-03 15:48:42.263418-05
10944 | 101 | 10437 | 38270 | 154737 | t
| 2007-04-03 15:34:24.010232-05 | 1 | 2007-04-03
15:34:24.010232-05 | 2007-04-03 15:48:42.263418-05
(2 rows)

SELECT oid,xmin,xmax,* FROM bm_student_answers WHERE user_id = 101 and
school_id = 10437 and bm_publication_id = 10944
and bm_question_id = 38270;
oid | xmin | xmax | bm_publication_id | user_id | school_id |
bm_question_id | bm_answer_id | correct_answer | time_answered
| status_id | created_date | last_modified
---------+---------+------+-------------------+---------+-----------+-------
---------+--------------+----------------+-------------------------------+--
---------+-------------------------------+-------------------------------
6474066 | 4327254 | 0 | 10944 | 101 | 10437 |
38270 | 154737 | t | 2007-04-03 15:34:24.010232-05 |
1 | 2007-04-03 15:34:24.010232-05 | 2007-04-03 15:48:42.263418-05
6474066 | 4327254 | 0 | 10944 | 101 | 10437 |
38270 | 154737 | t | 2007-04-03 15:34:24.010232-05 |
1 | 2007-04-03 15:34:24.010232-05 | 2007-04-03 15:48:42.263418-05
(2 rows)