Re: Insane behaviour in 8.3.3 - Mailing list pgsql-sql

On Παρ 15 Ιουν 2012 09:34:16 Richard Huxton wrote:
> On 14/06/12 09:39, Achilleas Mantzios wrote:
> > dynacom=# SELECT id from items_tmp WHERE id=1261319 AND xid=61972;
> >
> >     id
> >
> > ---------
> >
> >   1261319
> >
> > (1 row)
> > dynacom=# -- ok this is how it should be
> > dynacom=# SELECT id from items_tmp WHERE id=1261319 AND
> > xid=currval('xadmin_xid_seq');
> >
> >   id
> >
> > ----
> > (0 rows)
> > dynacom=# -- THIS IS INSANE
>
> Perhaps just do an EXPLAIN ANALYSE on both of those. If for some reason
> one is using the index and the other isn't then it could be down to a
> corrupted index. Seems unlikely though.

Hello Richard,
I had the same thought, and did the EPXLAIN ANALYZE and it gave results which looked pretty much
like the below (unfortunately i didn't keep the original exact output, cause i was in a hurry to solve the problem):

dynacom=# EXPLAIN ANALYZE SELECT id from items_tmp where id=1261319 AND xid=62035;
                                                           QUERY PLAN
   

---------------------------------------------------------------------------------------------------------------------Index
Scanusing it_tmp_pk on items_tmp  (cost=0.00..8.28 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)  Index
Cond:((id = 1261319) AND (xid = 62035))Total runtime: 0.042 ms 
(3 rows)

dynacom=#
dynacom=# EXPLAIN ANALYZE SELECT id from items_tmp where id=1261319 AND xid=currval('xadmin_xid_seq');
                                 QUERY PLAN                                                      

--------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on items_tmp  (cost=4.53..120.32 rows=1 width=4) (actual time=58.212..58.212 rows=1 loops=1)  Recheck Cond:
(id= 1261319)  Filter: (xid = currval('xadmin_xid_seq'::regclass))  ->  Bitmap Index Scan on it_tmp_pk
(cost=0.00..4.53rows=37 width=0) (actual time=0.021..0.021 rows=39 loops=1)        Index Cond: (id = 1261319)Total
runtime:58.235 ms 
(6 rows)

dynacom=#

After that, i tried to REINDEX items_tmp, which succeeded, and also made the last select return correctly one row.
Being suspicious of the general condition of the database,I then tried to REINDEX DATABASE the whole db, which failed
at some point because of corrupted data, but i didn't indicate which table had the corruption. I then wrote a script to

make more verbose what table was being reindexed at any time and this time i got no errors. I also re-issued the batch
REINDEX DATABASE command again with no errors. So it was indeed an index/data corruption problem.

Thanx to Richard and Adrian

-
Achilleas Mantzios
IT DEPT