Thread: does this mean i have a corruption?

I have a table to which i bulk load datasets of about up to 20,000
 rows at a time. It has a primary key, plus a dataset id (bioassay_id),
however i just ran this query:

bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where
bioassay_id = 5153 group by bioassay_id;
 bioassay_id | count
-------------+-------
        5153 | 20000
        9712 |   120

bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where
bioassay_id = 9712 group by bioassay_id;
 bioassay_id | count
-------------+-------
        9712 |  4624


any ideas why bioassay_id 9712 appears in the first query?? does this
suggest corruption somewhere?

thanks for any help

adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney@sgul.ac.uk> writes:
> bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where
> bioassay_id = 5153 group by bioassay_id;
>  bioassay_id | count
> -------------+-------
>         5153 | 20000
>         9712 |   120

That's pretty interesting :-(.  What PG version is this?  Is there an
index on bioassay_id, and if so is the query using it?  REINDEXing
the index might fix it.

            regards, tom lane

Tom Lane wrote:
> Adam Witney <awitney@sgul.ac.uk> writes:
>> bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where
>> bioassay_id = 5153 group by bioassay_id;
>>  bioassay_id | count
>> -------------+-------
>>         5153 | 20000
>>         9712 |   120
>
> That's pretty interesting :-(.  What PG version is this?  Is there an
> index on bioassay_id, and if so is the query using it?  REINDEXing
> the index might fix it.

aha yes... this is 7.4.12 by the way. I was confused as loading last
nights backup into a duplicate database made the problem go away. But as
you suggested running a REINDEX on the offending index fixed the problem!

thanks very much for your help Tom

adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

tech@leatherlink.net wrote:
> On Mon June 5 2006 1:31 am, Adam Witney wrote:
>> Tom Lane wrote:
>>> Adam Witney <awitney@sgul.ac.uk> writes:
>>>> bugasbase2=# SELECT bioassay_id, count(*) from mba_data_base where
>>>> bioassay_id = 5153 group by bioassay_id;
>>>>  bioassay_id | count
>>>> -------------+-------
>>>>         5153 | 20000
>>>>         9712 |   120
>>> That's pretty interesting :-(.  What PG version is this?  Is there an
>>> index on bioassay_id, and if so is the query using it?  REINDEXing
>>> the index might fix it.
>> aha yes... this is 7.4.12 by the way. I was confused as loading last
>> nights backup into a duplicate database made the problem go away. But as
>> you suggested running a REINDEX on the offending index fixed the problem!
>>
>
> That is interesting. What explains this behaviour? Should we be running
> REINDEX regularly?

I don't know, so posting your question to the list. I meant to ask last
night in fact, should i be worried as to why this occurred?

thanks

adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney@sgul.ac.uk> writes:
> I don't know, so posting your question to the list. I meant to ask last
> night in fact, should i be worried as to why this occurred?

Yeah, you should, but since you hadn't given us any context about the
problem I figured you only cared about a quick fix.  Otherwise I would
have suggested saving a physical copy of the broken index and table
for analysis.

The symptoms imply that some of the index entries were pointing to rows
that didn't belong to them (ie, rows with a different bioassay_id value
than what the index entry said).  The only ways I know of to explain
that involve database or system crashes ... you have any recently?

            regards, tom lane

Tom Lane wrote:
> Adam Witney <awitney@sgul.ac.uk> writes:
>> I don't know, so posting your question to the list. I meant to ask last
>> night in fact, should i be worried as to why this occurred?
>
> Yeah, you should, but since you hadn't given us any context about the
> problem I figured you only cared about a quick fix.  Otherwise I would
> have suggested saving a physical copy of the broken index and table
> for analysis.
>
> The symptoms imply that some of the index entries were pointing to rows
> that didn't belong to them (ie, rows with a different bioassay_id value
> than what the index entry said).  The only ways I know of to explain
> that involve database or system crashes ... you have any recently?

Hi, yes, i was a bit worried about it, and hence went for the fix first!

I don't think there have been any crashes, the uptime is showing 189
days and i don't see anything obvious in the PostgreSQL log. The data
rows that were incorrectly being pointed to were only uploaded on friday
and there had been zero user connections since friday evening. i do run
a Vacuum Analyse and a full dump every night, i don't know if that makes
a difference though. I have had problems with this table before (you
probably don't remember) that required me (with your help Tom) to zero
out blocks and reload some data.

Anyway i will try to get a copy of the index if it happens again

thanks again for your help

adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.