Re: Data Corruption

Larry,

Just for giggles, you might try and reindex. I've seen this sort of
strangeness before when a unique index got corrupted. As you already know,
this should be a major motivation to upgrade to at least 7.4.17 as there are
some nice fixes that avoid data corruption problems (possibly like yours).
Check the HISTORY file of a recent release for specifics.

On Tue, 5 Jun 2007, Larry Wissink wrote:

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

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954