Re: tsearch2 problem rank_cd() (possibly) crashing postgres [Update] - Mailing list pgsql-admin

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