Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id BD7A59FA0B7 for ; Fri, 8 Dec 2006 09:05:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 15190-06 for ; Fri, 8 Dec 2006 09:04:55 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id CE2029FA28E for ; Fri, 8 Dec 2006 09:04:59 -0400 (AST) Received: by py-out-1112.google.com with SMTP id d32so430704pye for ; Fri, 08 Dec 2006 05:04:58 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:mime-version:content-type:content-transfer-encoding:content-disposition; b=kIva1yKF/uBbfZx5kRsvLuzWF2VNZftTAMzLLvZaLsWcvdbLaP0aD9krb6wtEC0y38e826J9hbl9JWMO9BklVmswVmcLulcpSfj1O6BKHsurcxGWZC+r5RA5qeluWFFR0Y6YG3Fkibjmtpe3Ob6BbjGZN3BaLwjewVVox3mf7FE= Received: by 10.35.69.11 with SMTP id w11mr3635990pyk.1165583098450; Fri, 08 Dec 2006 05:04:58 -0800 (PST) Received: by 10.35.80.8 with HTTP; Fri, 8 Dec 2006 05:04:58 -0800 (PST) Message-ID: Date: Fri, 8 Dec 2006 18:34:58 +0530 From: "Rajesh Kumar Mallah" To: "Oleg Bartunov" Subject: Re: tsearch2 problem rank_cd() (possibly) crashing postgres [Update] Cc: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/71 X-Sequence-Number: 23863 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 wrote: > On 12/8/06, Oleg Bartunov wrote: > > On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote: > > > > > On 12/8/06, Oleg Bartunov 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 > > >