BUG #2582: Duplicate entries which violate primary key and unique index on same table

The following bug has been logged online:

Bug reference: 2582
Logged by: Steven Azar
Email address: funkytuba(at)gmail(dot)com
PostgreSQL version: 8.0.3
Operating system: Linux 2.6.15.6-1.smp.x86.i686.cmov #1 SMP Tue Mar 7
00:18:47 EST 2006 i686 athlon i386 GNU/Linux
Description: Duplicate entries which violate primary key and unique
index on same table
Details:

I've got a table that has two different rows with the same "id" and
"soapid"...

"id" is a serial column that has a primary key constraint

"soapid" has a unique index on it.

=> \d soap_transmission
Table
"public.soap_transmission"
Column | Type |
Modifiers
-------------------------------------+-----------------------------+--------
-----------------------------------------------------------
id | integer | not
null default nextval('public.soap_transmission_id_seq'::text)
soapid | character varying(40) | not
null
owner_id | integer | not
null
received_ts | timestamp without time zone | not
null
current_soap_transmission_status_id | integer | not
null default 1
last_status_change_ts | timestamp without time zone | not
null
process_id | integer |
subitem_count | integer |
Indexes:
"soap_transmission_pkey" PRIMARY KEY, btree (id)
"soap_transmission_soapid_key" UNIQUE, btree (soapid)
"soap_transmission_current_soap_transmission_status_id" btree
(current_soap_transmission_status_id)
"soap_transmission_owner_id_idx" btree (owner_id)
"soap_transmission_soapid_idx" btree (soapid)
Foreign-key constraints:
"_soap_transmission_process_id_fk" FOREIGN KEY (process_id) REFERENCES
process(id) ON DELETE SET NULL
"soap_transmission_current_soap_transmission_status_id_fkey" FOREIGN KEY
(current_soap_transmission_status_id) REFERENCES
soap_transmission_status(id) DEFERRABLE INITIALLY DEFERRED
"soap_transmission_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES
entity(id) DEFERRABLE INITIALLY DEFERRED
Triggers:
soap_transmission_post_insert_trigger AFTER INSERT ON soap_transmission
FOR EACH ROW EXECUTE PROCEDURE soap_transmission_post_insert_func()
soap_transmission_pre_insert_trigger BEFORE INSERT ON soap_transmission
FOR EACH ROW EXECUTE PROCEDURE soap_transmission_pre_insert_func()
=> select oid,* from soap_Transmission where received_ts ='2006-06-02
06:57:26'
-> ;
oid | id | soapid | owner_id |
received_ts | current_soap_transmission_status_id |
last_status_change_ts | process_id | subitem_count
-----------+--------+------------------------------------------+----------+-
--------------------+-------------------------------------+-----------------
-----------+------------+---------------
152124702 | 151093 | ac459f569f923414b7fc03135458f632ebea04fa | 100001 |
2006-06-02 06:57:26 | 2 | 2006-06-02
18:16:01.823251 | 14171 |
152124702 | 151093 | ac459f569f923414b7fc03135458f632ebea04fa | 100001 |
2006-06-02 06:57:26 | 4 | 2006-06-02
18:16:36.968431 | |
(2 rows)

> select xmin, cmin, xmax, cmax, ctid, oid from soap_transmission where
received_ts ='2006-06-02 06:57:26'
-> ;
xmin | cmin | xmax | cmax | ctid | oid
-----------+--------+-----------+------+-----------+-----------
250830042 | 18 | 251208168 | 4 | (5985,30) | 152124702
251038216 | 398358 | 390469213 | 0 | (5985,60) | 152124702
(2 rows)

This table is acted on by a trigger on another table:

=> select * from pg_proc where proname =
'soap_transmission_status_post_update_func'
-> ;
proname | pronamespace | proowner |
prolang | proisagg | prosecdef | proisstrict | proretset | provolatile |
pronargs | prorettype | proargtypes | proargnames | prosrc | probin |
proacl
-------------------------------------------+--------------+----------+------
----+----------+-----------+-------------+-----------+-------------+--------
--+------------+-------------+-------------+--------+--------+--------
soap_transmission_status_post_update_func | 2200 | 100 |
37823123 | f | f | f | f | v |
0 | 2279 | | |
BEGIN
UPDATE soap_transmission set
current_soap_transmission_status_id=NEW.soap_transmission_status_id,
last_status_change_ts=NEW.ts where
soap_transmission.id=NEW.soap_transmission_id;
RETURN NEW;
END;

Any help would be appreciated.