Обсуждение: Duplicate oid and primary key values
I have a table in a PG 7.4.1 database with 380 duplicate rows,
including duplicate oid and primary key values. Looking through our
backups, the duplicates did not exist before Friday, 02/06/2004. I'm
assuming neither pg_dumpall nor restoring from a pg_dumpall file will
eliminate such duplicates. We upgraded from 7.3.4 to 7.4.1 on
02/02/2004.
What can cause these duplicates?
The server has had several system crashes over the past few days and weeks.
Below is my session with the DB showing an example of the duplicates,
the table structure, and trigger functions.
cos=> select oid, recordnumber from client where recordnumber = 10970;
oid | recordnumber
---------+--------------
2427408 | 10970
(1 row)
cos=> select oid, recordnumber from client where recordnumber < 10971
and recordnumber > 10969;
oid | recordnumber
---------+--------------
2427408 | 10970
2427408 | 10970
(2 rows)
cos=> \d client
Table "public.client"
Column | Type |
Modifiers
----------------------+-------------------------+------------------------------------------------------------------
recordnumber | integer | not null default
nextval('public.client_recordnumber_seq'::text)
recordnumber_display | integer | not null
access | text |
add1 | character varying(255) |
add2 | character varying(255) |
add_id | integer |
age | character varying(255) |
akas | character varying(255) |
besttime | character varying(255) |
birthdate | character varying(255) |
city | character varying(255) |
country | character varying(255) |
creation_date | date | not null default now()
creation_time | time without time zone | not null default now()
custom1 | character varying(255) |
custom10 | character varying(255) |
custom2 | character varying(255) |
custom3 | character varying(255) |
custom4 | character varying(255) |
custom5 | character varying(255) |
custom6 | character varying(255) |
custom7 | character varying(255) |
custom8 | character varying(255) |
custom9 | character varying(255) |
disability | character varying(255) |
edit_date | date | not null default now()
edit_time | time without time zone | not null default now()
edit_id | integer |
education | character varying(255) |
email | character varying(255) |
employer | character varying(255) |
ethnicity | character varying(1) |
extra1 | character varying(255) |
extra8 | character varying(255) |
first | character varying(255) |
gender | character varying(255) |
incomelevel | character varying(255) |
incomenotes | character varying(255) |
insurance | character varying(255) |
last | character varying(255) |
location | character varying(255) |
maritalstatus | character varying(255) |
nochildren | character varying(255) |
otherphone | character varying(255) |
own_id | integer |
phhome | character varying(255) |
phwork | character varying(255) |
prefcontact | character varying(255) |
primarylang | character varying(255) |
referredby | character varying(255) |
restrictorg_id | integer |
serverid | character(5) | not null
ssno | character varying(255) |
state | character varying(255) |
status | integer |
title | character varying(255) |
transportation | character varying(255) |
zip | character varying(255) |
extra2 | character varying(255) |
temp_extra8 | character varying(255) |
extra10 | character varying(255) |
authorize | character varying(1000) |
Indexes:
"client_pkey" primary key, btree (recordnumber)
"idx_client_recordnum_display" unique, btree
(upper_concat(serverid, recordnumber_display))
"idx_client_first" btree ("first")
"idx_client_last" btree ("last")
"idx_client_restrictorg_id" btree (restrictorg_id)
"idx_client_serverid" btree (serverid)
"idx_client_status" btree (status)
Foreign-key constraints:
"$1" FOREIGN KEY (restrictorg_id) REFERENCES
agency_dbs(record_id) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
tgr_client_edit_date BEFORE UPDATE ON client FOR EACH ROW EXECUTE
PROCEDURE fnc_edit_date()
tgr_client_edit_time BEFORE UPDATE ON client FOR EACH ROW EXECUTE
PROCEDURE fnc_edit_time()
tgr_client_recordnumber_display BEFORE INSERT ON client FOR EACH
ROW EXECUTE PROCEDURE fnc_recordnumber_display()
cos=> \connect - postgres
You are now connected as new user "postgres".
cos=# select prosrc from pg_proc where proname = 'fnc_recordnumber_display';
prosrc
---------------------------------------------------------------------------------------
DECLARE
BEGIN
new.recordnumber_display = new.recordnumber;
RETURN new;
END;
(1 row)
cos=# select prosrc from pg_proc where proname = 'fnc_edit_date';
prosrc
----------------------------------------------------
BEGIN
new.edit_date := 'now';
RETURN new;
END;
(1 row)
cos=# select prosrc from pg_proc where proname = 'fnc_edit_time';
prosrc
----------------------------------------------------
BEGIN
new.edit_time := 'now';
RETURN new;
END;
(1 row)
--
Jeff Bohmer
VisionLink, Inc.
_________________________________
303.402.0170
www.visionlink.org
_________________________________
People. Tools. Change. Community.
On Tuesday 10 February 2004 17:10, Jeff Bohmer wrote: > I have a table in a PG 7.4.1 database with 380 duplicate rows, > including duplicate oid and primary key values. Looking through our > backups, the duplicates did not exist before Friday, 02/06/2004. I'm > assuming neither pg_dumpall nor restoring from a pg_dumpall file will > eliminate such duplicates. We upgraded from 7.3.4 to 7.4.1 on > 02/02/2004. > > What can cause these duplicates? > > The server has had several system crashes over the past few days and weeks. Hardware related? Or is it not clear yet? > Below is my session with the DB showing an example of the duplicates, > the table structure, and trigger functions. > > cos=> select oid, recordnumber from client where recordnumber = 10970; > oid | recordnumber > ---------+-------------- > 2427408 | 10970 > (1 row) > > cos=> select oid, recordnumber from client where recordnumber < 10971 > and recordnumber > 10969; > oid | recordnumber > ---------+-------------- > 2427408 | 10970 > 2427408 | 10970 > (2 rows) In the absence of Tom or some other more knowledgable source, try these out. SELECT xmin,cmin,xmax,cmax,tid,oid,* FROM ... to see if these are two versions of the same row Perhaps stick an EXPLAIN ANALYSE on the front of those and see if one is using an index and the other not. It might be a corrupted INDEX, in which case REINDEX should fix it. PS - you probably want now() or CURRENT_DATE etc. in the trigger functions rather than 'now'. -- Richard Huxton Archonet Ltd
On Tue, 10 Feb 2004, Jeff Bohmer wrote: > > I have a table in a PG 7.4.1 database with 380 duplicate rows, > including duplicate oid and primary key values. Looking through our > backups, the duplicates did not exist before Friday, 02/06/2004. I'm > assuming neither pg_dumpall nor restoring from a pg_dumpall file will > eliminate such duplicates. We upgraded from 7.3.4 to 7.4.1 on > 02/02/2004. > > What can cause these duplicates? > > The server has had several system crashes over the past few days and weeks. Check your hardware. bad memory, bad cpu, or bad hard drives can cause these problems. Postgresql, like most databases, expects the hardware to operate without errors.