SEO

Hi ,

We recently upgraded from PostgreSQL 8.1.5 to PostgreSQL 8.2.0.
looks like rank_cd function is giving problem .

tradein_clients=> CREATE TABLE test (name text , name_vec tsvector);
CREATE TABLE
tradein_clients=> INSERT INTO test (name ,name_vec) values ('hello
world' , to_tsvector('hello world'));
INSERT 0 1
tradein_clients=> SELECT name from test where name_vec @@
to_tsquery('hello') ;
+-------------+
| name |
+-------------+
| hello world |
+-------------+
(1 row)

tradein_clients=> SELECT name, rank_cd(1,name_vec,
to_tsquery('hello') ) as rank from test where name_vec @@
to_tsquery('hello') ;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
tradein_clients=>

Analysis of core dump: (not sure though if its the right way of doing it)


$ gdb /opt/usr/local/pgsql/bin/postgres core.2807

GNU gdb 5.3-25mdk (Mandrake Linux)
Copyright 2002 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i586-mandrake-linux-gnu"...
Core was generated by `postgres: tradein tradein_clients 192.168.0.11(52876'.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /lib/libcrypt.so.1...done.
Loaded symbols for /lib/libcrypt.so.1
Reading symbols from /lib/libdl.so.2...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/i686/libm.so.6...done.
Loaded symbols for /lib/i686/libm.so.6
Reading symbols from /lib/i686/libc.so.6...done.
Loaded symbols for /lib/i686/libc.so.6
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libnss_files.so.2...done.
Loaded symbols for /lib/libnss_files.so.2
Reading symbols from
/mnt/disk3/opt/usr/local/postgresql820/lib/tsearch2.so...done.
Loaded symbols for /mnt/disk3/opt/usr/local/postgresql820/lib/tsearch2.so
#0 pg_detoast_datum (datum=0x1) at fmgr.c:1964
1964 if (VARATT_IS_EXTENDED(datum))
(gdb) bt
#0 pg_detoast_datum (datum=0x1) at fmgr.c:1964
#1 0x40c2a961 in rank_cd (fcinfo=0xbfffeda0) at rank.c:731
#2 0x0815948c in ExecMakeFunctionResult (fcache=0x8423c40, econtext=0x84239a8,
isNull=0x8424c85 "\177~\177\177\177\177\177\034\210@\b\b",
isDone=0x8424c9c) at execQual.c:1147
#3 0x0815d373 in ExecTargetList (targetlist=0x8423c08,
econtext=0x84239a8, values=0x8424c70, isnull=0x8424c84 "",
itemIsDone=0x8424c98, isDone=0xbffff068) at execQual.c:3981
#4 0x0815d672 in ExecProject (projInfo=0x8424bac, isDone=0xbffff068)
at execQual.c:4182
#5 0x0815d785 in ExecScan (node=0x8423b00, accessMtd=0x8169290
<SeqNext>) at execScan.c:143
#6 0x08169364 in ExecSeqScan (node=0x8423b00) at nodeSeqscan.c:130
#7 0x08157cb1 in ExecProcNode (node=0x8423b00) at execProcnode.c:349
#8 0x08155d5c in ExecutePlan (estate=0x842391c, planstate=0x8423b00,
operation=CMD_SELECT, numberTuples=0,
direction=ForwardScanDirection, dest=0x83edfbc) at execMain.c:1081
#9 0x08154fbe in ExecutorRun (queryDesc=0x8423b00,
direction=ForwardScanDirection, count=0) at execMain.c:241
#10 0x081e5ee1 in PortalRunSelect (portal=0x840f96c, forward=1 '\001',
count=0, dest=0x83edfbc) at pquery.c:831
#11 0x081e5a91 in PortalRun (portal=0x840f96c, count=2147483647,
dest=0x83edfbc, altdest=0x83edfbc,
completionTag=0xbffff320 "") at pquery.c:684
#12 0x081e1368 in exec_simple_query (
query_string=0x83ed064 "SELECT name, rank_cd(1,name_vec,
to_tsquery('hello') ) as rank from test where name_vec @@
to_tsquery('hello') ;") at postgres.c:939
#13 0x081e4932 in PostgresMain (argc=4, argv=0x83b09f4,
username=0x83b09c4 "tradein") at postgres.c:3419
#14 0x081bb396 in BackendRun (port=0x839e9a8) at postmaster.c:2926
#15 0x081babde in BackendStartup (port=0x839e9a8) at postmaster.c:2553
#16 0x081b8db7 in ServerLoop () at postmaster.c:1206
#17 0x081b822d in PostmasterMain (argc=1, argv=0x83937d8) at postmaster.c:958
#18 0x08177117 in main (argc=1, argv=0x1) at main.c:188
#19 0x40085c57 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

You need to read documentation ! rank_cd accepts the same args as rank()
function.

Oleg
On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:

> Hi ,
>
> We recently upgraded from PostgreSQL 8.1.5 to PostgreSQL 8.2.0.
> looks like rank_cd function is giving problem .
>
> tradein_clients=> CREATE TABLE test (name text , name_vec tsvector);
> CREATE TABLE
> tradein_clients=> INSERT INTO test (name ,name_vec) values ('hello
> world' , to_tsvector('hello world'));
> INSERT 0 1
> tradein_clients=> SELECT name from test where name_vec @@
> to_tsquery('hello') ;
> +-------------+
> | name |
> +-------------+
> | hello world |
> +-------------+
> (1 row)
>
> tradein_clients=> SELECT name, rank_cd(1,name_vec,
> to_tsquery('hello') ) as rank from test where name_vec @@
> to_tsquery('hello') ;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Succeeded.
> tradein_clients=>
>
> Analysis of core dump: (not sure though if its the right way of doing it)
>
>
> $ gdb /opt/usr/local/pgsql/bin/postgres core.2807
>
> GNU gdb 5.3-25mdk (Mandrake Linux)
> Copyright 2002 Free Software Foundation, Inc.
> GDB is free software, covered by the GNU General Public License, and you are
> welcome to change it and/or distribute copies of it under certain conditions.
> Type "show copying" to see the conditions.
> There is absolutely no warranty for GDB. Type "show warranty" for details.
> This GDB was configured as "i586-mandrake-linux-gnu"...
> Core was generated by `postgres: tradein tradein_clients 192.168.0.11(52876'.
> Program terminated with signal 11, Segmentation fault.
> Reading symbols from /lib/libcrypt.so.1...done.
> Loaded symbols for /lib/libcrypt.so.1
> Reading symbols from /lib/libdl.so.2...done.
> Loaded symbols for /lib/libdl.so.2
> Reading symbols from /lib/i686/libm.so.6...done.
> Loaded symbols for /lib/i686/libm.so.6
> Reading symbols from /lib/i686/libc.so.6...done.
> Loaded symbols for /lib/i686/libc.so.6
> Reading symbols from /lib/ld-linux.so.2...done.
> Loaded symbols for /lib/ld-linux.so.2
> Reading symbols from /lib/libnss_files.so.2...done.
> Loaded symbols for /lib/libnss_files.so.2
> Reading symbols from
> /mnt/disk3/opt/usr/local/postgresql820/lib/tsearch2.so...done.
> Loaded symbols for /mnt/disk3/opt/usr/local/postgresql820/lib/tsearch2.so
> #0 pg_detoast_datum (datum=0x1) at fmgr.c:1964
> 1964 if (VARATT_IS_EXTENDED(datum))
> (gdb) bt
> #0 pg_detoast_datum (datum=0x1) at fmgr.c:1964
> #1 0x40c2a961 in rank_cd (fcinfo=0xbfffeda0) at rank.c:731
> #2 0x0815948c in ExecMakeFunctionResult (fcache=0x8423c40,
> econtext=0x84239a8,
> isNull=0x8424c85 "\177~\177\177\177\177\177\034\210@\b\b",
> isDone=0x8424c9c) at execQual.c:1147
> #3 0x0815d373 in ExecTargetList (targetlist=0x8423c08,
> econtext=0x84239a8, values=0x8424c70, isnull=0x8424c84 "",
> itemIsDone=0x8424c98, isDone=0xbffff068) at execQual.c:3981
> #4 0x0815d672 in ExecProject (projInfo=0x8424bac, isDone=0xbffff068)
> at execQual.c:4182
> #5 0x0815d785 in ExecScan (node=0x8423b00, accessMtd=0x8169290
> <SeqNext>) at execScan.c:143
> #6 0x08169364 in ExecSeqScan (node=0x8423b00) at nodeSeqscan.c:130
> #7 0x08157cb1 in ExecProcNode (node=0x8423b00) at execProcnode.c:349
> #8 0x08155d5c in ExecutePlan (estate=0x842391c, planstate=0x8423b00,
> operation=CMD_SELECT, numberTuples=0,
> direction=ForwardScanDirection, dest=0x83edfbc) at execMain.c:1081
> #9 0x08154fbe in ExecutorRun (queryDesc=0x8423b00,
> direction=ForwardScanDirection, count=0) at execMain.c:241
> #10 0x081e5ee1 in PortalRunSelect (portal=0x840f96c, forward=1 '\001',
> count=0, dest=0x83edfbc) at pquery.c:831
> #11 0x081e5a91 in PortalRun (portal=0x840f96c, count=2147483647,
> dest=0x83edfbc, altdest=0x83edfbc,
> completionTag=0xbffff320 "") at pquery.c:684
> #12 0x081e1368 in exec_simple_query (
> query_string=0x83ed064 "SELECT name, rank_cd(1,name_vec,
> to_tsquery('hello') ) as rank from test where name_vec @@
> to_tsquery('hello') ;") at postgres.c:939
> #13 0x081e4932 in PostgresMain (argc=4, argv=0x83b09f4,
> username=0x83b09c4 "tradein") at postgres.c:3419
> #14 0x081bb396 in BackendRun (port=0x839e9a8) at postmaster.c:2926
> #15 0x081babde in BackendStartup (port=0x839e9a8) at postmaster.c:2553
> #16 0x081b8db7 in ServerLoop () at postmaster.c:1206
> #17 0x081b822d in PostmasterMain (argc=1, argv=0x83937d8) at postmaster.c:958
> #18 0x08177117 in main (argc=1, argv=0x1) at main.c:188
> #19 0x40085c57 in __libc_start_main () from /lib/i686/libc.so.6
> (gdb)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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 ?

>
> Oleg
> On Fri, 8 Dec 2006, Rajesh Kumar Mallah wrote:
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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)


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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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
>

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Oleg Bartunov <oleg@sai.msu.su> writes:
> You need to read documentation ! rank_cd accepts the same args as rank()
> function.

Nonetheless, dumping core on bad input is not acceptable behavior ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On 12/8/06, Oleg Bartunov <oleg@sai.msu.su> wrote:
> On Fri, 8 Dec 2006, Tom Lane wrote:
>
> > Oleg Bartunov <oleg@sai.msu.su> writes:
> >> You need to read documentation ! rank_cd accepts the same args as rank()
> >> function.
> >
> > Nonetheless, dumping core on bad input is not acceptable behavior ...
>
> we already resolved the situation. This is mostly problem of
> missing release notes.

Sir,

if the old functions which are present in system catalogs and are missing
in new tsearch2.so file and are not dropped from database then the
database seems to
be crashing when they are invoked. Should' upgrade scritps not drop the
old functions. just a thought , this situation could arise in general not just
tsearch2.so .

Regds
mallah.

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Joshua D. Drake" <jd@commandprompt.com> writes:
> On Fri, 2006-12-08 at 10:47 -0500, Tom Lane wrote:
>> Nonetheless, dumping core on bad input is not acceptable behavior ...

> Is it time to require test cases for contrib modules?

tsearch2 *has* a regression test. ATM it sounds like the problem is
that the OP tried to use a new library with old pg_proc entries that
defined different parameter sets for the same-named C functions; a
situation that no regression test would have exercised anyway.

That change was probably unwise on Oleg and Teodor's part, but what's
done is done. My point is that now that we know the failure mode, we
need to add some defenses. The OP is certainly not the last DBA who
will make this mistake during 8.1->8.2 upgrade.

It looks to me like the problem is that with the old pg_proc entries,
an "int4" will get passed where the code is expecting "float4[]",
so it tries to dereference the int and crashes. There doesn't seem
to be any real cheap defense --- we might have to add a
get_fn_expr_argtype() call into rank_cd, and anything else that's been
changed similarly. But I don't think this is negotiable. Backend
crashes are bad.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Fri, 2006-12-08 at 11:12 -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > On Fri, 2006-12-08 at 10:47 -0500, Tom Lane wrote:
> >> Nonetheless, dumping core on bad input is not acceptable behavior ...
>
> > Is it time to require test cases for contrib modules?
>
> tsearch2 *has* a regression test. ATM it sounds like the problem is
> that the OP tried to use a new library with old pg_proc entries that
> defined different parameter sets for the same-named C functions; a
> situation that no regression test would have exercised anyway.

Hmmmm, is there a way that we could have library version requirements
for all C based functions? So if within PGLIB version 3 was present but
the function was calling version 2... It would politely fail with a
mismatched version warning?

Sincerely,

Joshua D. Drake


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 8 Dec 2006, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
>> You need to read documentation ! rank_cd accepts the same args as rank()
>> function.
>
> Nonetheless, dumping core on bad input is not acceptable behavior ...

we already resolved the situation. This is mostly problem of
missing release notes.

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend