The following bug has been logged online:
Bug reference: 3231
Logged by: Amorn Buchheit
Email address: [EMAIL PROTECTED]
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 : [EMAIL PROTECTED]
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)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
