Hi all, Well, we have migrated our server from 8.3.3 to 8.3.4. The server isbased on Red Hat and an instans it deals with insalled on RAMFS. db_online=> select version();version----------------------------------------------------------------------------------------PostgreSQL 8.3.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)4.1.2 20071124 (Red Hat 4.1.2-42)(1 row) There is a table: Table "public.person_online" Column | Type |Modifiers----------------------------+--------------------------+--------------------------------- obj_id | bigint | not null obj_status_did | smallint |... po_since | timestamp with time zone | not null default now() po_geo_point | point | not nullIndexes: "pk_person_online" PRIMARY KEY, btree (obj_id) "i_person_online__geo_point" gist (box(po_geo_point,po_geo_point)) WHERE obj_status_did = 1 "i_person_online__since" btree (po_since)Triggers: t_person_online_since_bu BEFORE UPDATE ON person_online FOR EACHROW EXECUTE PROCEDURE t_person_online_since() Pay attention to i_person_online__geo_point index. After migration we did initdb, installed btree_gist contrib (it wasinstaled on 8.3.3 too) and created the table and the index. Later wenoticed strange behaviour of our application and that is what we havemanaged to find: db_online=> select obj_status_did, count(1) from person_onlinewhere obj_status_did = 1 group by obj_status_did; obj_status_did | count----------------+------- 1 | 711(1 row) db_online=> select obj_status_did, count(1) from person_onlinegroup by obj_status_did; obj_status_did | count----------------+------- 5 | 2 1 | 1930(2 rows) explain showed that first query do index scan and second one sec scan. db_online=> We did reindex i_person_online__geo_point and situation seemed being solved: db_online=> select obj_status_did, count(1) from person_onlinewhere obj_status_did = 1 group by obj_status_did; obj_status_did | count----------------+------- 1 | 2071(1 row)db_online=> select obj_status_did, count(1) from person_onlinegroup by obj_status_did; obj_status_did | count----------------+------- 5 | 2 1 | 2071(2 rows) But after our application had done some inserts/updates/deletes thequeries showed different count of rows with obj_status_did = 1 again.We tried to drop-create the index but it did not help either. Then we created a twin-table and a twin-index and did a lot of insertsand random deletes using different connections. Everything wasalright. We renamed original table to person_online_tmp and thetwin-table to person_online. Few minutes later we saw wrong behaviouragain. Will somebody explain what has happened and how to solve the problem please? --Regards,Sergey Konoplev--PostgreSQL articles in english & russianhttp://gray-hemp.blogspot.com/search/label/postgresql/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
