BUG #2582: Duplicate entries which violate primary key and unique index on same table - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2582
Logged by:          Steven Azar
Email address:      funkytuba@gmail.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.