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
> >
>

Re: tsearch2 problem rank_cd() (possibly) crashing postgres

From
Oleg Bartunov
Date:
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

Re: tsearch2 problem rank_cd() (possibly) crashing postgres

From
"Rajesh Kumar Mallah"
Date:
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
>

Re: tsearch2 problem rank_cd() (possibly) crashing postgres

From
Oleg Bartunov
Date:
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

Re: tsearch2 problem rank_cd() (possibly) crashing postgres

From
"Rajesh Kumar Mallah"
Date:
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.