Re: problems with table corruption continued - Mailing list pgsql-hackers

Tom & All:

I've been looking into this problem some more and I've been able to
consistantly reproduce the error.  I've testing it on two different
machines; both running 7.1.3 on a i686-pc-linux-gnu configuration.  One
machine is RH7.2 and the other is RH6.2.

I still haven't been able to reproduce the problem with corrupted
indexes/tables (NUMBER OF TUPLES IS NOT THE SAME AS HEAP -- duplicate rows
with same oid & pkey); but I'm hopefull that these two problems are related.
Also, i wanted to inform you that the same two tables became corrupted on
12-15-2001 after my 12-12-2001 reload).

I've attached three files, a typescript, and two sql files.  I found that if
the commands were combined into a single file and run in a single pgsql
session, the error does not occur -- so it's important to follow the
commands exactly like they are in the typescript.  If for some reason the
errors aren't reproducable on your machines, let me know and we will try to
find out what's unique about my setup.

Thanks.

----- Original Message -----
From: "Brian Hirt" <bhirt@mobygames.com>
To: "Postgres Hackers" <pgsql-hackers@postgresql.org>
Cc: "Brian A Hirt" <bhirt@berkhirt.com>
Sent: Wednesday, December 12, 2001 11:30 AM
Subject: [HACKERS] problems with table corruption continued


> Okay, here's a follow up to my previous messages "ACK table corrupted,
> unique index violated."
>
> I've been trying to clean up the corruptions that i mentioned earlier.  I
> felt most comfortable shutting down all my application servers, restarting
> postgres, doing a dump of my database and rebuilding it with a pginit and
> complete reload.  So far so good.  I went to fix one of the corrupted
tables
> and i have another strange experience.  I'm still looking into other
> possibilities such as a hardware failure; but i thought this might be
> interesting or helpful in the context of my previous post:  Basically the
> table with duplicate oid/id now has unique oid from the relead, so I'm
going
> to delete the duplicate rows and recreate the unique index on the identity
> column.
>
> basement=# select count(*),developer_aka_id from developer_aka group by
> developer_aka_id having count(*) <> 1;
>  count | developer_aka_id
> -------+------------------
>      2 |             9789
>      2 |            10025
>      2 |            40869
> (3 rows)
>
> basement=# select oid,* from developer_aka where developer_aka_id in
> (9789,10025,40869);
>   oid  | developer_id | developer_aka_id |    first_name     | last_name
> -------+--------------+------------------+-------------------+-----------
>  48390 |         1916 |             9789 | Chris             | Smith
>  48402 |        35682 |            40869 | Donald "Squirral" | Fisk
>  48425 |         4209 |            10025 | Mike              | Glosecki
>  48426 |         1916 |             9789 | Chris             | Smith
>  48427 |        35682 |            40869 | Donald "Squirral" | Fisk
>  48428 |         4209 |            10025 | Mike              | Glosecki
> (6 rows)
>
> basement=# delete from developer_aka where oid in (48390,48402,48425);
> DELETE 3
> basement=# select count(*),developer_aka_id from developer_aka group by
> developer_aka_id having count(*) <> 1;
>  count | developer_aka_id
> -------+------------------
> (0 rows)
>
> basement=# create unique index developer_aka_pkey on
> developer_aka(developer_aka_id);
> CREATE
> basement=# VACUUM ANALYZE developer_aka;
> ERROR:  Cannot insert a duplicate key into unique index developer_aka_pkey
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>