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!
