The following function:

create function text_to_words(text)
returns setof text as $_$
my %stopwords =3D ( 'i' =3D> 'i','me' =3D> 'me','my' =3D> 'my','myself'=
=3D> 'myself','we' =3D> 'we','our' =3D> 'our','ours' =3D> 'ours','ourselve=
s' =3D> 'ourselves','you' =3D> 'you','your' =3D> 'your','yours' =3D> 'yours=
','yourself' =3D> 'yourself','yourselves' =3D> 'yourselves','he' =3D> 'he',=
'him' =3D> 'him','his' =3D> 'his','himself' =3D> 'himself','she' =3D> 'she'=
,'her' =3D> 'her','hers' =3D> 'hers','herself' =3D> 'herself','it' =3D> 'it=
','its' =3D> 'its','itself' =3D> 'itself','they' =3D> 'they','them' =3D> 't=
hem','their' =3D> 'their','theirs' =3D> 'theirs','themselves' =3D> 'themsel=
ves','what' =3D> 'what','which' =3D> 'which','who' =3D> 'who','whom' =3D> '=
whom','this' =3D> 'this','that' =3D> 'that','these' =3D> 'these','those' =
=3D> 'those','am' =3D> 'am','is' =3D> 'is','are' =3D> 'are','was' =3D> 'was=
','were' =3D> 'were','be' =3D> 'be','been' =3D> 'been','being' =3D> 'being'=
,'have' =3D> 'have','has' =3D> 'has','had' =3D> 'had','having' =3D> 'having=
','do' =3D> 'do','does' =3D> 'does','did' =3D> 'did','doing' =3D> 'doing','=
a' =3D> 'a','an' =3D> 'an','the' =3D> 'the','and' =3D> 'and','but' =3D> 'bu=
t','if' =3D> 'if','or' =3D> 'or','because' =3D> 'because','as' =3D> 'as','u=
ntil' =3D> 'until','while' =3D> 'while','of' =3D> 'of','at' =3D> 'at','by' =
=3D> 'by','for' =3D> 'for','with' =3D> 'with','about' =3D> 'about','against=
' =3D> 'against','between' =3D> 'between','into' =3D> 'into','through' =3D>=
'through','during' =3D> 'during','before' =3D> 'before','after' =3D> 'afte=
r','above' =3D> 'above','below' =3D> 'below','to' =3D> 'to','from' =3D> 'fr=
om','up' =3D> 'up','down' =3D> 'down','in' =3D> 'in','out' =3D> 'out','on' =
=3D> 'on','off' =3D> 'off','over' =3D> 'over','under' =3D> 'under','again' =
=3D> 'again','further' =3D> 'further','then' =3D> 'then','once' =3D> 'once'=
,'here' =3D> 'here','there' =3D> 'there','when' =3D> 'when','where' =3D> 'w=
here','why' =3D> 'why','how' =3D> 'how','all' =3D> 'all','any' =3D> 'any','=
both' =3D> 'both','each' =3D> 'each','few' =3D> 'few','more' =3D> 'more','m=
ost' =3D> 'most','other' =3D> 'other','some' =3D> 'some','such' =3D> 'such'=
,'no' =3D> 'no','nor' =3D> 'nor','not' =3D> 'not','only' =3D> 'only','own' =
=3D> 'own','same' =3D> 'same','so' =3D> 'so','than' =3D> 'than','too' =3D> =
'too','very' =3D> 'very','s' =3D> 's','t' =3D> 't','can' =3D> 'can','will' =
=3D> 'will','just' =3D> 'just','don' =3D> 'don','should' =3D> 'should','now=
' =3D> 'now' );
my $textstring =3D $_[0];
$textstring =3D~ s/[\`\`\~\!\@\#\$\%\^\&\*\(\)\_\+\-\=3D\{\}\[\]\\\|=
\:\;\"\"\'\'\<\>\,\.\?\/]+/ /gi; my @words =3D split /\ /,$textstring;
my $res =3D [];
=20
foreach my $word (@words) {
$word =3D~ s/([A-Z])/lc($1)/ge;
if($word ne $stopwords{$word} && $word ne '') {
push @$res,$word;
}
}
return $res;
$_$ language plperl immutable strict;

creashes the backend, UNLESS it's executed in the same session where it was=
created.

Kind regards,

John


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

On Wed, Nov 17, 2004 at 02:49:15PM +1100, John Hansen wrote:

I can reproduce this crash on the following platform:

Solaris 9
PostgreSQL 8.0.0beta4 (CVS)
Perl 5.8.5
gcc 3.4.2

I can NOT reproduce the crash on the following platform:

FreeBSD 4.10-STABLE
PostgreSQL 8.0.0beta4 (CVS - same copy of source as above)
Perl 5.8.6-RC1
gcc 2.95.4

The server log on the Solaris box shows this when it crashes
(signal 10 is SIGBUS):

Can't return outside a subroutine at (eval 4) line 13.
LOG: server process (PID 25681) was terminated by signal 10

Adding a few elog lines to the function shows that it's crashing
at the "return $res;" line. One of my gdb sessions showed the
following, although haven't been able to get this same output again:

Program received signal SIGSEGV, Segmentation fault.
0xfec8c1e4 in Perl_av_fetch () from /usr/local/lib/libperl.so
(gdb) bt
#0 0xfec8c1e4 in Perl_av_fetch () from /usr/local/lib/libperl.so
#1 0xfec689f4 in S_pad_findlex () from /usr/local/lib/libperl.so
#2 0xfec68968 in Perl_pad_findmy () from /usr/local/lib/libperl.so

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

I've come up with a simpler test case:

CREATE OR REPLACE FUNCTION foo(INTEGER) RETURNS INTEGER AS $$
my @a = 1..$_[0];
elog INFO, "array has $_[0] elements";
return $_[0];
$$ LANGUAGE plperl;

Here's the Solaris 9 failure mode:

test=> select foo(131); -- works consistently
INFO: array has 131 elements
foo
-----
131
(1 row)

test=> select foo(132); -- fails consistently
INFO: array has 132 elements
server closed the connection unexpectedly

This test also fails on FreeBSD 4.10, but at a higher resource usage
than on Solaris:

test=> select foo(260); -- works consistently
INFO: array has 260 elements
foo
-----
260
(1 row)

test=> select foo(261); -- fails consistently
INFO: array has 261 elements
server closed the connection unexpectedly

It looks like some resource is being exhausted that has a higher
setting on my FreeBSD box than on my Solaris box. Interestingly,
the elog output shows that the crash doesn't happen until the
function returns. Here's the gdb output from Solaris:

% sudo -u postgres gdb /usr/local/pgsql/bin/postgres
...
(gdb) run -D/usr/local/pgsql/data test
...
PostgreSQL stand-alone backend 8.0.0beta4
backend> select foo(132);
1: foo (typeid = 23, len = 4, typmod = -1, byval = t)
----

Program received signal SIGSEGV, Segmentation fault.
0xfecc3378 in Perl_pop_return () from /usr/local/lib/libperl.so
(gdb) bt
#0 0xfecc3378 in Perl_pop_return () from /usr/local/lib/libperl.so
#1 0xfec295f8 in Perl_call_sv () from /usr/local/lib/libperl.so
#2 0xfeda413c in plperl_call_perl_func (desc=0xffbff178, fcinfo=0x0) at plperl.c:810

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

John Hansen <john (AT) geeknet (DOT) com.au> writes:
Got it ...

RCS file: /cvsroot/pgsql/src/pl/plperl/plperl.c,v
***************
*** 659,665 ****
--- 655,664 ----
int count;

if (trusted && !plperl_safe_init_done)
+ {
plperl_safe_init();
+ SPAGAIN;
+ }

ENTER;
SAVETMPS;
***************

Man, that was painful to find.

regards, tom lane

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

On Thu, Nov 18, 2004 at 04:37:42PM -0500, Tom Lane wrote:

Excellent -- I get no crashes with the test cases. Thanks.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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