Full Text search differences from 8.3 to 8.4.6 - Mailing list pgsql-sql
From | Achilleas Mantzios |
---|---|
Subject | Full Text search differences from 8.3 to 8.4.6 |
Date | |
Msg-id | 201012171219.11610.achill@matrix.gatewaynet.com Whole thread Raw |
List | pgsql-sql |
Hello, i am currently evaluating the migration cost from 8.3 -> 9.x and in order to do that, i think i must have a confident view of the situation of my app with 8.4.6 as an intermediate stage. I noticed a few minor incompatibilities, but also a change in behaviour in full text search: Table "public.vmanews"Column | Type | Modifiers ---------+----------+---------------------------------------------------------------------id | integer | not null defaultnextval(('public.vmanews_id_seq'::text)::regclass)newid | integer | not nulldate | date | not null defaultnow()vslid | integer | not nullcat | text | not nullappname | text | not nullheader | text | notnullcontent | text | not nullidxfti | tsvector | Indexes: "vmanews_pkey" PRIMARY KEY, btree (id) "vmanews_vslid_newid" UNIQUE, btree (vslid, newid) "vmanews_idxfti_idx"gist (idxfti) "vmanews_vslid" btree (vslid) Foreign-key constraints: "$1" FOREIGN KEY (vslid) REFERENCES vessels(id) Triggers: vmanews_tsvectorupdate BEFORE INSERT OR UPDATE ON vmanews FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('idxfti','pg_catalog.english', 'header', 'content') in 8.3 : postgres@dynacom=# SELECT idxfti from vmanews where id=28717; idxfti ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'8':25 'ad':12,34'job':9,30 'pms':62 'vma':61 'attn':3 'fuel':20 'hour':27 'kind':43 'main':1,16 'note':6 'pump':21 'sinc':28 'check':18,47'engin':2,17 'everi':24 'first':53 'pleas':5 'shock':22,50 'absorb':23,51 'report':55 'result':58 'vessel':14,38'vma-pm':60 'inspect':57 'request':44 'respect':49 'deliveri':36 'opportun':54 'overlook':41 'tech.dept':64'tks/brgds':63 '000running':26 'master/cheng':4 (1 row) (here is the important command) postgres@dynacom=# SELECT idxfti @@ to_tsquery('overlooked') from vmanews where id=28717;?column? ----------t (1 row) while in 8.4.6 : SELECT idxfti from vmanews where id=28717; idxfti ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'000running':26 '8':25'absorb':23,51 'ad':12,34 'attn':3 'check':18,47 'deliveri':36 'engin':2,17 'everi':24 'first':53 'fuel':20 'hour':27'inspect':57 'job':9,30 'kind':43 'main':1,16 'master/cheng':4 'note':6 'opportun':54 'overlook':41 'pleas':5 'pms':62'pump':21 'report':55 'request':44 'respect':49 'result':58 'shock':22,50 'sinc':28 'tech.dept':64 'tks/brgds':63'vessel':14,38 'vma':61 'vma-pm':60 (1 row) and (the important command in 8.4.6) dynacom=# SELECT idxfti @@ to_tsquery('overlooked') from vmanews where id=28717;?column? ----------f (1 row) So, searching for "overlooked" which the original word in the text, in 8.3 idxfti @@ to_tsquery('overlooked') returns true while in 8.4.6 idxfti @@ to_tsquery('overlooked') returns false. Is there anything i am missing? -- Achilleas Mantzios