SEO

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)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Amorn Buchheit wrote:
> 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?

I'm not sure, but you really need to upgrade. You're on 7.3.4, and the
latest minor release of the 7.3 branch is 7.3.18. At the very least
upgrade to that, or even better, to the latest 8.2 release.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings