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.cli ent"
Column | Type |
Modifiers
----------------------+-------------------------+------------------------------------------------------------------
recordnumber | integer | not null default
nextval('public .client_recordn umber_seq'::tex t)
recordnumber_di splay | 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_pke y" primary key, btree (recordnumber)
"idx_client_rec ordnum_display" unique, btree
(upper_concat(s erverid, recordnumber_di splay))
"idx_client_fir st" btree ("first")
"idx_client_las t" btree ("last")
"idx_client_res trictorg_id" btree (restrictorg_id )
"idx_client_ser verid" btree (serverid)
"idx_client_sta tus" btree (status)
Foreign-key constraints:
"$1" FOREIGN KEY (restrictorg_id ) REFERENCES
agency_dbs(reco rd_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_reco rdnumber_displa y BEFORE INSERT ON client FOR EACH
ROW EXECUTE PROCEDURE fnc_recordnumbe r_display()
cos=> \connect - postgres
You are now connected as new user "postgres".
cos=# select prosrc from pg_proc where proname = 'fnc_recordnumb er_display';
prosrc
---------------------------------------------------------------------------------------
DECLARE
BEGIN
new.recordnumbe r_display = new.recordnumbe r;
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org 
