Re: Corrupt index

Here is an example of the misbehavior that I am
seeing:

A simple table with 3 colums (and approximately a
million records):
column_a INTEGER PRIMARY KEY
column_b DATE NOT NULL
column_c DOUBLE NOT NULL

When the index (primary key) gets corrupted, I get the
following behavior when I do a simple SELECT (using
the psql program):

SELECT * FROM example_table WHERE (column_a = 12345);

The result looks like:

column_a | column_b | column_c
--------------------------------
67890 | 2001-01-01 | 100

The main point is that "column_a" is the wrong number.

When I use the EXPLAIN command, it tells me that it's
doing an index scan using the primary key index.
Issuing a "REINDEX TABLE example_table" command fixes
the problem.

I usually detect the problem when an INSERT fails
(batch insert fails due to duplicate key). I also
delete a few thousand records before the inserts. All
of the inserts and deletes are done throught the JDBC3
driver.

Please let me know what additional information you
would like me to provide.

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Amir Becher <abecher(at)yahoo(dot)com> writes:
> > I am experiencing frequent index corruptions
> (almost
> > daily).
>
> Please define your problem: what misbehavior are you
> actually seeing?
> Give us facts, not interpretations.
>
> regards, tom lane

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com