Thread: BUG #4479: Incorrect TSearch2 results when inserting after deleting

The following bug has been logged online:

Bug reference:      4479
Logged by:          Dan Fabulich
Email address:      dan@fabulich.com
PostgreSQL version: 8.3.4
Operating system:   OS X 10.5.5
Description:        Incorrect TSearch2 results when inserting after deleting
Details:

Run the following psql script on Postgres 8.3.4.  I've reproduced this on
Centos 5.2 and RhodiumToad on IRC reproduced the problem as well.

EXPECTED: The last two SELECT statements should return the same results (1
row)
ACTUAL: The final SELECT statement returns 0 results.  The SELECT statement
just before it returns 1 result.

DROP DATABASE search_bug;
CREATE DATABASE search_bug;
\c search_bug

CREATE TABLE search_test (id bigserial PRIMARY KEY, name_search tsvector
default NULL, name_keywords varchar(255) default NULL);
CREATE INDEX name_search_index ON search_test USING gist(name_search);
create trigger name_search_update before update or insert on search_test for
each row execute procedure tsvector_update_trigger(name_search,
'pg_catalog.english', name_keywords);
SELECT * from search_test where name_search @@ to_tsquery('Noe');
-- should and does return nothing

INSERT INTO search_test (name_keywords) VALUES ('Noe Valley');

SELECT * from search_test where name_search @@ to_tsquery('Noe');
-- should and does return 1 row

DELETE from search_test where id = 1;

INSERT INTO search_test (name_keywords) VALUES ('Noe Valley');

SELECT * from search_test where name_search @@ to_tsquery('Noe');
-- should and does return 1 row

SELECT * from search_test where name_search @@ to_tsquery('Noe');
-- should return 1 row BUT RETURNS 0 ROWS; BUG!

"Dan Fabulich" <dan@fabulich.com> writes:
> Description:        Incorrect TSearch2 results when inserting after deleting

> EXPECTED: The last two SELECT statements should return the same results (1
> row)
> ACTUAL: The final SELECT statement returns 0 results.  The SELECT statement
> just before it returns 1 result.

Hmm.  I can reproduce this with the given script on 8.3 current, but
*not* in CVS HEAD.  Also, the script results in an indexscan plan,
but if you set enable_indexscan off (resulting in a bitmap scan of the
same index), the correct result is returned.  Fascinating.

Teodor, will you look at this?

            regards, tom lane

> Teodor, will you look at this?

Yes, Of course
--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

Fixed, the reason is the same as for
http://archives.postgresql.org/pgsql-general/2008-10/msg00845.php

Patch:
http://archives.postgresql.org/pgsql-committers/2008-10/msg00187.php
--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

I want to say many thanks to Teodor for really quick bug fixing.

Currently we are waiting for decision when official bugfix will be issued
(we had to make some workarounds for production environment and thinking,
what to do -- rollback to 8.3.3 or apply the patch).

So the question is when 8.3.5 will be released? I suspect many DBAs need it.

On Wed, Oct 22, 2008 at 5:20 PM, Teodor Sigaev <teodor@sigaev.ru> wrote:

> Fixed, the reason is the same as for
> http://archives.postgresql.org/pgsql-general/2008-10/msg00845.php
>
> Patch:
> http://archives.postgresql.org/pgsql-committers/2008-10/msg00187.php
> --
> Teodor Sigaev                                   E-mail: teodor@sigaev.ru
>                                                   WWW:
> http://www.sigaev.ru/
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>



--
Sincerely yours,
Nikolay Samokhvalov
Postgresmen LLC, http://postgresmen.ru