I am reporting a bug on duplicate rows on primary key. Below is the information:
Thank you for your bug report The report (reference: 3222) will be forwarded to the development team for further investigation. http://wwwmaster.postgresql.org/system/handler.php?page=submitthanks&lang=en &action=support/submitbug&id=3222 Thank you for your bug report The report (reference: 3231) will be forwarded to the development team for further investigation. 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
