FAQ
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.

Search Discussions

  • Tom Lane at Aug 19, 2006 at 3:07 pm

    "Steven Azar" <funkytuba@gmail.com> writes:
    PostgreSQL version: 8.0.3
    Description: Duplicate entries which violate primary key and unique
    index on same table
    8.0.3 is very old and has several known data-corruption-causing bugs.
    Please update to the current release in that branch (8.0.8) --- or
    consider migrating to 8.1.* --- and then see if the problem arises
    again.
    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)
    Since the OIDs are the same, these are evidently two different states
    of the same logical row, and only one of them should be considered good.
    There are at least two post-8.0.3 bug fixes that might explain the
    problem: one about premature destruction of t_ctid chains during VACUUM,
    and one about corruption of transaction commit status.

    It's possible you've found yet a different bug, but let's rule out
    the already-fixed ones first.

    As far as cleaning up your immediate corruption goes, you can do
    something like "DELETE FROM ... WHERE ctid = '(...)'" to get rid
    of whichever copy seems older. It might be worth searching the table
    for other multiple occurrences of the same OID, too, just in case.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedAug 18, '06 at 12:55a
activeAug 19, '06 at 3:07p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Steven Azar: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase