Full Text search differences from 8.3 to 8.4.6

Поиск
Список
Период
Сортировка
От Achilleas Mantzios
Тема Full Text search differences from 8.3 to 8.4.6
Дата
Msg-id 201012171219.11610.achill@matrix.gatewaynet.com
обсуждение исходный текст
Список 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Chris Ruprecht
Дата:
Сообщение: Create/update trigger auto-populate fields.
Следующее
От: serviciotdf
Дата:
Сообщение: Re: Translate Function PL/pgSQL to SQL92