From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: Insane behaviour in 8.3.3
Date: Fri, 15 Jun 2012 10:25:32 +0300
User-Agent: KMail/1.13.7 (FreeBSD/8.3-RELEASE; KDE/4.7.3; amd64; ; )
References: <201206141139.35601.achill@matrix.gatewaynet.com>
	<4FDAD768.4040409@archonet.com>
In-Reply-To: <4FDAD768.4040409@archonet.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201206151025.32973.achill@matrix.gatewaynet.com>

On =CE=A0=CE=B1=CF=81 15 =CE=99=CE=BF=CF=85=CE=BD 2012 09:34:16 Richard Hux=
ton wrote:
> On 14/06/12 09:39, Achilleas Mantzios wrote:
> > dynacom=3D# SELECT id from items_tmp WHERE id=3D1261319 AND xid=3D61972;
> >=20
> >     id
> >=20
> > ---------
> >=20
> >   1261319
> >=20
> > (1 row)
> > dynacom=3D# -- ok this is how it should be
> > dynacom=3D# SELECT id from items_tmp WHERE id=3D1261319 AND
> > xid=3Dcurrval('xadmin_xid_seq');
> >=20
> >   id
> >=20
> > ----
> > (0 rows)
> > dynacom=3D# -- THIS IS INSANE
>=20
> 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 whi=
ch looked pretty much
like the below (unfortunately i didn't keep the original exact output, caus=
e i was in a hurry to solve the problem):

dynacom=3D# EXPLAIN ANALYZE SELECT id from items_tmp where id=3D1261319 AND=
 xid=3D62035;                                             =20
                                                     QUERY PLAN            =
                                         =20
=2D------------------------------------------------------------------------=
=2D-------------------------------------------
 Index Scan using it_tmp_pk on items_tmp  (cost=3D0.00..8.28 rows=3D1 width=
=3D4) (actual time=3D0.017..0.018 rows=3D1 loops=3D1)
   Index Cond: ((id =3D 1261319) AND (xid =3D 62035))
 Total runtime: 0.042 ms
(3 rows)

dynacom=3D#=20
dynacom=3D# EXPLAIN ANALYZE SELECT id from items_tmp where id=3D1261319 AND=
 xid=3Dcurrval('xadmin_xid_seq');
                                                     QUERY PLAN            =
                                        =20
=2D------------------------------------------------------------------------=
=2D------------------------------------------
 Bitmap Heap Scan on items_tmp  (cost=3D4.53..120.32 rows=3D1 width=3D4) (a=
ctual time=3D58.212..58.212 rows=3D1 loops=3D1)
   Recheck Cond: (id =3D 1261319)
   Filter: (xid =3D currval('xadmin_xid_seq'::regclass))
   ->  Bitmap Index Scan on it_tmp_pk  (cost=3D0.00..4.53 rows=3D37 width=
=3D0) (actual time=3D0.021..0.021 rows=3D39 loops=3D1)
         Index Cond: (id =3D 1261319)
 Total runtime: 58.235 ms
(6 rows)

dynacom=3D#=20

After that, i tried to REINDEX items_tmp, which succeeded, and also made th=
e last select return correctly one row.
Being suspicious of the general condition of the database,I then tried to R=
EINDEX DATABASE the whole db, which failed=20
at some point because of corrupted data, but i didn't indicate which table =
had the corruption. I then wrote a script to=20
make more verbose what table was being reindexed at any time and this time =
i got no errors. I also re-issued the batch=20
REINDEX DATABASE command again with no errors. So it was indeed an index/da=
ta corruption problem.

Thanx to Richard and Adrian

=2D
Achilleas Mantzios
IT DEPT