Thread: BUG #5328: GIN index with fastupdates=on provide wrong result on bitmap scan

The following bug has been logged online:

Bug reference:      5328
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 8.4.1
Operating system:   linux kernel v 2.6.18
Description:        GIN index with fastupdates=on provide wrong result on
bitmap scan
Details:

I cannot create self-containing test case because fresh loaded table from
dump don't have such effect.
Bug itself is returning some extra unrelated tuples from bitmap scan over
complex GIN index.
Bug not random server issue (same problem exists on the master database and
on both londiste slaves).

Here is query which show bug:
SELECT file_type_did FROM
            "file"
WHERE
    "file".file_type_did='69000003057'::bigint
    AND "file".obj_status_did = 1
    AND ("file".obj_tsvector @@ (make_tsquery('(море)',
'utf8_russian')))
offset 0
) AS t
where t.file_type_did<>'69000003057'::bigint;
---------------
   69000006314
   69000002260
   69000006314
   69000006314
   69000002260
   69000002260
...
   69000002260
   69000006314
(83 rows)

E.g. inner query returned 83 rows with complete unrelated file_type_did

Now let me show explain of an inner query:
db=#      EXPLAIN SELECT file_type_did FROM
db-#             "file"
db-# WHERE
db-#     "file".file_type_did='69000003057'::bigint
db-#     AND "file".obj_status_did = 1
db-#     AND ("file".obj_tsvector @@ (make_tsquery('(море)',
'utf8_russian')))
db-# offset 0;
                                                            QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
 Limit  (cost=58.47..722.08 rows=5253 width=8)
   ->  Bitmap Heap Scan on file  (cost=58.47..722.08 rows=5253 width=8)
         Recheck Cond: ((file_type_did = 69000003057::bigint) AND
(obj_tsvector @@ '''море'''::tsquery) AND (obj_status_did = 1))
         ->  Bitmap Index Scan on i_file__photo_search  (cost=0.00..57.16
rows=5253 width=0)
               Index Cond: ((file_type_did = 69000003057::bigint) AND
(obj_tsvector @@ '''море'''::tsquery))


Description of used GIN index:
"i_file__photo_search" gin (file_type_did, photo_vislvl, obj_tsvector) WHERE
obj_status_did = 1

where is related table fields is:
 file_type_did              | bigint                   | not null
 photo_vislvl               | smallint                 | not null default 9
 obj_tsvector               | tsvector                 | not null default
''::tsvector

Additional information:
1)doing reindex index i_file__photo_search; remove an problem for a short
time (1-60 minutes depend user=update activity on the table).
2)VACUUM files; have the same effect for the same time.
3)8.3 server don't have such issues

My idea is bug related to GIN fastupdate engine which was implemented in 8.4
version.

I will do ALTER INDEX i_file__photo_search set (fastupdate=off); and VACUUM
file; on one of slaves and check results in next 24 hours to confirm or
contradict my idea.

"Maxim Boguk" <maxim.boguk@gmail.com> writes:
> Bug reference:      5328
> Logged by:          Maxim Boguk
> Email address:      maxim.boguk@gmail.com
> PostgreSQL version: 8.4.1
> Operating system:   linux kernel v 2.6.18
> Description:        GIN index with fastupdates=on provide wrong result on
> bitmap scan

The 8.4.2 release notes mention

* Fix wrong search results for a multi-column GIN index with fastupdate enabled (Teodor)

which seems to match your complaint.  Please update and see if the
problem is resolved.  You might need to reindex the GIN index after
updating to make things good.

            regards, tom lane