From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Full Text search differences from 8.3 to 8.4.6
Date: Fri, 17 Dec 2010 13:19:11 +0300
Message-ID: <201012171219.11610.achill@matrix.gatewaynet.com> (raw)
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 default nextval(('public.vmanews_id_seq'::text)::regclass)
newid | integer | not null
date | date | not null default now()
vslid | integer | not null
cat | text | not null
appname | text | not null
header | text | not null
content | text | not null
idxfti | 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: Full Text search differences from 8.3 to 8.4.6
In-Reply-To: <201012171219.11610.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox