BUG #3231: Duplicate rows primary key bug - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3231
Logged by:          Amorn Buchheit
Email address:      amornb@yahoo.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@yahoo.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)