Обсуждение: BUG #2582: Duplicate entries which violate primary key and unique index on same table
BUG #2582: Duplicate entries which violate primary key and unique index on same table
От
"Steven Azar"
Дата:
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.
"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