Thread: Re: tsearch2 problem rank_cd() (possibly) crashing postgres [Update]
Re: tsearch2 problem rank_cd() (possibly) crashing postgres [Update]
From
"Rajesh Kumar Mallah"
Date:
Dear
Oleg,
In local development server. the old functions still exists in
postgresql catalogs. But probably they are missing in the
newer version of tsearch2.so that came with 8.2.0 . And hence
postgres is crashing. I feel this should have been informed in the
release notes.
tradein_clients=> \df *.rank_cd
List of functions
Schema Name Result data type Argument data types
------ ------- ---------------- -----------------------------------
(older ones)
public rank_cd real integer, tsvector, tsquery
public rank_cd real integer, tsvector, tsquery, integer
(new ones)
public rank_cd real real[], tsvector, tsquery
public rank_cd real real[], tsvector, tsquery, integer
public rank_cd real tsvector, tsquery
public rank_cd real tsvector, tsquery, integer
(6 rows)
On 12/8/06, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
> On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote:
> > On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:
> >
> > > On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote:
> > >> You need to read documentation ! rank_cd accepts the same args as rank()
> > >> function.
> > >
> > > Dear Oleg,
> > >
> > > Could you please elaborate a bit more if time permits.
> > > our application is old and it was working fine in 8.1.5. do i need to
> > > change the sql
> > > to use a different function ?
> >
> > from reference manual:
> >
> > CREATE FUNCTION rank_cd(
> > [ weights float4[], ] vector TSVECTOR, query TSQUERY, [ normalization int4 ]
> > ) RETURNS float4
> >
>
> Dear Oleg,
>
> thanks for the prompt help. looks like we have to modify
> our application code.
>
> i would like to point out :
>
> In our Production Database
>
> \df public.rank_cd
> +--------+---------+------------------+-------------------------------------+
> | Schema | Name | Result data type | Argument data types |
> +--------+---------+------------------+-------------------------------------+
> | public | rank_cd | real | integer, tsvector, tsquery |
> | public | rank_cd | real | integer, tsvector, tsquery, integer |
> | public | rank_cd | real | tsvector, tsquery |
> | public | rank_cd | real | tsvector, tsquery, integer |
> +--------+---------+------------------+-------------------------------------+
> (4 rows)
>
> In tsearch2.sql (with pgsql 8.2.0)
>
> $ grep "CREATE FUNCTION rank_cd" tsearch2.sql
> CREATE FUNCTION rank_cd(float4[], tsvector, tsquery)
> CREATE FUNCTION rank_cd(float4[], tsvector, tsquery, int4)
> CREATE FUNCTION rank_cd(tsvector, tsquery)
> CREATE FUNCTION rank_cd(tsvector, tsquery, int4)
>
> This means first arguments have changed from integer to float4[]
> This means all the application code needs to be changed now :-/
>
>
>
> > postgres=# SELECT name, rank_cd(name_vec,to_tsquery('hello') ) as rank from test where name_vec @@
to_tsquery('hello');
> > name | rank
> > -------------+------
> > hello world | 0.1
> > (1 row)
> >
> > or
> >
> > postgres=# SELECT name, rank_cd('{1,1,1,1}',name_vec,to_tsquery('hello') ) as rank from test where name_vec
@@to_tsquery('hello') ;
> > name | rank
> > -------------+------
> > hello world | 1
> > (1 row)
>
> BTW: above did not work for me
>
> i had to explicitly cast '{1,1,1,1}' to '{1,1,1,1}'::float4[] ,
> is anything fishy with my database ?
>
> SELECT name, rank_cd('{1,1,1,1}'::float4[]
> ,name_vec,to_tsquery('hello') ) as rank from test where name_vec
> @@ to_tsquery('hello') ;
>
>
> ( PS: thanks for the nice tsearch software we have been using it since
> "pre tsearch" era. (openfts) )
>
> Regds
> Mallah.
>
> >
> >
> > >
> > >>
> > >> Oleg
> > >> On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:
> > >>
> > >
> >
> > Regards,
> > Oleg
> > _____________________________________________________________
> > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> > Sternberg Astronomical Institute, Moscow University, Russia
> > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> > phone: +007(495)939-16-83, +007(495)939-23-83
> >
>
On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:
> Dear
> Oleg,
> In local development server. the old functions still exists in
> postgresql catalogs. But probably they are missing in the
> newer version of tsearch2.so that came with 8.2.0 . And hence
> postgres is crashing. I feel this should have been informed in the
> release notes.
yes, you're right. Someone has offered help to write this but then
silently dissapeared, so we stay without release notes. Could you
summarise your experience and write them and we add them for 8.2.1
Oleg
>
> tradein_clients=> \df *.rank_cd
> List of functions
> Schema Name Result data type Argument data types
> ------ ------- ---------------- -----------------------------------
> (older ones)
> public rank_cd real integer, tsvector, tsquery
> public rank_cd real integer, tsvector, tsquery, integer
>
> (new ones)
> public rank_cd real real[], tsvector, tsquery
> public rank_cd real real[], tsvector, tsquery, integer
>
> public rank_cd real tsvector, tsquery
> public rank_cd real tsvector, tsquery, integer
> (6 rows)
>
>
> On 12/8/06, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
>> On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote:
>> > On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:
>> >
>> > > On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote:
>> > >> You need to read documentation ! rank_cd accepts the same args as
>> rank()
>> > >> function.
>> > >
>> > > Dear Oleg,
>> > >
>> > > Could you please elaborate a bit more if time permits.
>> > > our application is old and it was working fine in 8.1.5. do i need to
>> > > change the sql
>> > > to use a different function ?
>> >
>> > from reference manual:
>> >
>> > CREATE FUNCTION rank_cd(
>> > [ weights float4[], ] vector TSVECTOR, query TSQUERY, [
>> normalization int4 ]
>> > ) RETURNS float4
>> >
>>
>> Dear Oleg,
>>
>> thanks for the prompt help. looks like we have to modify
>> our application code.
>>
>> i would like to point out :
>>
>> In our Production Database
>>
>> \df public.rank_cd
>> +--------+---------+------------------+-------------------------------------+
>> | Schema | Name | Result data type | Argument data types
>> |
>> +--------+---------+------------------+-------------------------------------+
>> | public | rank_cd | real | integer, tsvector, tsquery
>> |
>> | public | rank_cd | real | integer, tsvector, tsquery, integer
>> |
>> | public | rank_cd | real | tsvector, tsquery
>> |
>> | public | rank_cd | real | tsvector, tsquery, integer
>> |
>> +--------+---------+------------------+-------------------------------------+
>> (4 rows)
>>
>> In tsearch2.sql (with pgsql 8.2.0)
>>
>> $ grep "CREATE FUNCTION rank_cd" tsearch2.sql
>> CREATE FUNCTION rank_cd(float4[], tsvector, tsquery)
>> CREATE FUNCTION rank_cd(float4[], tsvector, tsquery, int4)
>> CREATE FUNCTION rank_cd(tsvector, tsquery)
>> CREATE FUNCTION rank_cd(tsvector, tsquery, int4)
>>
>> This means first arguments have changed from integer to float4[]
>> This means all the application code needs to be changed now :-/
>>
>>
>>
>> > postgres=# SELECT name, rank_cd(name_vec,to_tsquery('hello') ) as
>> rank from test where name_vec @@ to_tsquery('hello') ;
>> > name | rank
>> > -------------+------
>> > hello world | 0.1
>> > (1 row)
>> >
>> > or
>> >
>> > postgres=# SELECT name, rank_cd('{1,1,1,1}',name_vec,to_tsquery('hello')
>> ) as rank from test where name_vec @@ to_tsquery('hello') ;
>> > name | rank
>> > -------------+------
>> > hello world | 1
>> > (1 row)
>>
>> BTW: above did not work for me
>>
>> i had to explicitly cast '{1,1,1,1}' to '{1,1,1,1}'::float4[] ,
>> is anything fishy with my database ?
>>
>> SELECT name, rank_cd('{1,1,1,1}'::float4[]
>> ,name_vec,to_tsquery('hello') ) as rank from test where name_vec
>> @@ to_tsquery('hello') ;
>>
>>
>> ( PS: thanks for the nice tsearch software we have been using it since
>> "pre tsearch" era. (openfts) )
>>
>> Regds
>> Mallah.
>>
>> >
>> >
>> > >
>> > >>
>> > >> Oleg
>> > >> On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:
>> > >>
>> > >
>> >
>> > Regards,
>> > Oleg
>> > _____________________________________________________________
>> > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
>> > Sternberg Astronomical Institute, Moscow University, Russia
>> > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>> > phone: +007(495)939-16-83, +007(495)939-23-83
>> >
>>
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote: > On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote: > > > Dear > > Oleg, > > In local development server. the old functions still exists in > > postgresql catalogs. But probably they are missing in the > > newer version of tsearch2.so that came with 8.2.0 . And hence > > postgres is crashing. I feel this should have been informed in the > > release notes. > > yes, you're right. Someone has offered help to write this but then > silently dissapeared, so we stay without release notes. Could you > summarise your experience and write them and we add them for 8.2.1 Sir, Dont you feel the incremental features should be available as upgrade patches. eg at one point of time i found that the = ( ts_vector , ts_vector) operator is missing in my database. later i had to manually create the operator by looking at the tsearch2.sql file of later releases. this is becuse tsearch2.sql (i think) was not runnable on database having older versions of tsearch2. yes i would summarise my experience but lemme know if the above is an issue. also do i need to change my application code ? (becoz the older function is not available) Warm Regds Mallah. > > Oleg >
On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:
> On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote:
>> On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:
>>
>> > Dear
>> > Oleg,
>> > In local development server. the old functions still exists in
>> > postgresql catalogs. But probably they are missing in the
>> > newer version of tsearch2.so that came with 8.2.0 . And hence
>> > postgres is crashing. I feel this should have been informed in the
>> > release notes.
>>
>> yes, you're right. Someone has offered help to write this but then
>> silently dissapeared, so we stay without release notes. Could you
>> summarise your experience and write them and we add them for 8.2.1
>
> Sir,
>
> Dont you feel the incremental features should be available as upgrade
> patches. eg at one point of time i found that the = ( ts_vector ,
> ts_vector)
> operator is missing in my database. later i had to manually create
> the operator by looking at the tsearch2.sql file of later releases.
> this is becuse tsearch2.sql (i think) was not runnable on database
> having older versions of tsearch2.
> yes i would summarise my experience but lemme know if the above
> is an issue.
some people use separate scheme contrib to load all contrib stuff,
so upgrading is much easy.
>
>
> also do i need to change my application code ?
> (becoz the older function is not available)
from my understanding, the only function missed is the
-CREATE FUNCTION rank_cd(int4, tsvector, tsquery)
-CREATE FUNCTION rank_cd(int4, tsvector, tsquery, int4)
you need to change your application to use rank_cd()
as it described in reference manual.
-CREATE FUNCTION snb_ru_init(internal)
changed to
+CREATE FUNCTION snb_ru_init_koi8(internal)
+CREATE FUNCTION snb_ru_init_utf8(internal)
since we now support multibyte encoding (UTF-8 as well)
>
> Warm Regds
> Mallah.
>
>
>
>
>
>>
>> Oleg
>>
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote: > On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote: > > > On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote: > >> On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote: > >> > >> > Dear > >> > Oleg, > >> > In local development server. the old functions still exists in > >> > postgresql catalogs. But probably they are missing in the > >> > newer version of tsearch2.so that came with 8.2.0 . And hence > >> > postgres is crashing. I feel this should have been informed in the > >> > release notes. > >> > >> yes, you're right. Someone has offered help to write this but then > >> silently dissapeared, so we stay without release notes. Could you > >> summarise your experience and write them and we add them for 8.2.1 > > > > Sir, > > > > Dont you feel the incremental features should be available as upgrade > > patches. eg at one point of time i found that the = ( ts_vector , > > ts_vector) > > operator is missing in my database. later i had to manually create > > the operator by looking at the tsearch2.sql file of later releases. > > this is becuse tsearch2.sql (i think) was not runnable on database > > having older versions of tsearch2. > > yes i would summarise my experience but lemme know if the above > > is an issue. > > some people use separate scheme contrib to load all contrib stuff, > so upgrading is much easy. > Sir, even with a seperate schema contrib one cannot drop the stuff in contrib and reload the new version . Becoz DROPs have to be cascaded to dependencies which contain real data. Eg index and table columns that depend on existence of TYPE. Regds Mallah.