Data Corruption

I know this is not really the point of this group, but I'm wondering if
someone can help me with a weird problem on our production dbs. Since
yesterday we have had problems with rows missing from a primary table
(table of users/email addresses). We can't figure out how the rows were
deleted because nothing is designed to delete these rows. The primary
reason I suspect it is a data corruption rather than bad code or
malicious user is that when I try to re-insert the missing row, Postgres
complains that it violates the primary key constraint.

So, any suggestions on where to look for Internal errors? Any known
bugs that could be causing this? Any suggestions on how to fix it?
Fortunately we should be able to recover most data from backup servers,
but it's still a big concern because obviously if it can happen in one
table it could happen anywhere else.

Ok, details.

So far, no hardware problems have been identified. I can go into the
hardware details, but I'll leave that aside for the moment.

Our PG version is: 7.4.7. (Yes I know we should upgrade...)

Here's what happens:

Missing User:

EBPRD1=# select user_id, user_username, user_email, user_password,
user_created_date from users where user_id = 6020900;

user_id | user_username | user_email | user_password |
user_created_date

---------+---------------+------------+---------------+-----------------
--

(0 rows)

Try to re-insert the missing user:

EBPRD1=# insert into users

(user_id, user_username, user_email, user_password, user_created_date )

values (6020900, 'sofifi97(at)optonline(dot)net',
'sofifi97(at)optonline(dot)net', '********', '2003-07-05
08:37:07.620');

EBPRD1-# EBPRD1-# ERROR: duplicate key violates unique constraint
"con_user_id_pri"

That constraint is of course a primary key constraint on the users
table.

I don't think I'd get that error if the row had been deleted in any
normal method.

Any suggestions or relevant postgres reference is greatly appreciated.

Larry.

I don't think the table structure is important, but here it is:

EBPRD1=# \d users

Table "public.users"

Column | Type | Modifiers

--------------------------+--------------------------+------------------
---

user_id | numeric | not null

user_username | character varying(255) | not null

user_email | character varying(255) | not null

user_referrer_url | character varying(2048) |

user_password | character varying(64) | not null

user_rebate_paid | numeric(9,0) |

user_rebate_due | numeric(9,0) |

user_referred1_paid | numeric(9,0) |

user_referred1_due | numeric(9,0) |

user_referred2_due | numeric(9,0) |

user_referred2_paid | numeric(9,0) |

user_referred3_due | numeric(9,0) |

user_referred3_paid | numeric(9,0) |

user_pri_ref_user_id | numeric |

user_sec_ref_user_id | numeric |

user_ter_ref_user_id | numeric |

user_fully_registered_c | character(1) |

user_wants_promo_mail_c | character(1) |

user_spare_flag_b | character(1) |

user_spare_flag_c | character(1) |

user_created_date | timestamp with time zone | not null

user_registration_type | character(3) |

user_username_number | numeric |

user_username_name | character varying(255) |

user_active | character(1) | default
'T'::bpchar

user_email_valid | character(1) | default
'T'::bpchar

user_wants_rebate_mail | character(1) |

user_password_hint | character varying(96) |

user_last_modified | timestamp with time zone |

user_primary_account_id | numeric |

user_unused_num1 | numeric |

user_referrer_banner | character varying(255) |

user_frn_banner_id | numeric(38,0) |

user_signup_page | character varying(50) |

user_showme_info_popup | character varying(1) |

user_showme_promo_popup | character varying(1) |

user_wants_mktg_mail | character varying(1) |

user_wants_sales_mail | character varying(1) |

user_wants_specials_mail | character varying(1) |

user_has_flag | character varying(1) |

user_registration_code | character varying(128) |

Indexes:

"con_user_id_pri" primary key, btree (user_id)

"users_useremail_uk" unique, btree (user_email)

"ix_user_last_modified" btree (user_last_modified)

"user_created_date_index" btree (user_created_date)

"users_pri_ref_indx" btree (user_pri_ref_user_id)

"users_sec_ref_indx" btree (user_sec_ref_user_id)

"users_ter_ref_indx" btree (user_ter_ref_user_id)

"users_username_indx" btree (user_username)

"users_username_no_indx" btree (user_username_name,
user_username_number)

Foreign-key constraints:

"con_user_ref_users_ter" FOREIGN KEY (user_ter_ref_user_id)
REFERENCES users(user_id) ON DELETE CASCADE

"con_user_ref_users_sec" FOREIGN KEY (user_sec_ref_user_id)
REFERENCES users(user_id) ON DELETE CASCADE

"con_user_ref_users_pri" FOREIGN KEY (user_pri_ref_user_id)
REFERENCES users(user_id) ON DELETE CASCADE