On Sat, Nov 30, 2002 at 05:20:57PM +0100, tgl@sss.pgh.pa.us wrote:
>
> "Han Holl" <han.holl@prismant.nl> writes:
> > So it looks like using the pure SQL version here is not a good idea.
> > I'm quite willing to produce an example of this behaviour, but I
> > suspect that it's 'known behaviour' to experts.
>
> No, not to me anyway. I would not be surprised if there's some leak in
> the SQL-function code, but could I trouble you for a complete example,
> rather than having to reverse-engineer one?
Of course. I hope you have a Linux like system, otherwise the following
might not run.
If I rub this shellscript, I see with tom in a different terminal that
the postmaster process grows to about 28Mb, and then collapses to the
initial 1.5 Mb.
To avoid quoting problems, I;ll attach the shellscript as well
Cheers
Han Holl
#!/bin/bash
PGLIB=/usr/local/pgsql/lib
INC=/usr/src/postgresql-7.3rc2/src/include
TABLE=/tmp/demotable.tbl
MAPPING=/tmp/mapping.tbl
SQL=/tmp/demo.sql
set -e
[ -f $TABLE ] || {
perl -e 'for ($i = 0 ; $i < 300000 ; $i++) { printf("%s%d\n",chr(65 + ($i % 4)), $i); }' >$TABLE
}
[ -f $MAPPING ] || {
for sr in A B C D
do
for i in 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
do
echo -e "$sr\tstring$i\t$i"
done
done >$MAPPING
}
cat >$SQL <<EOF
drop table demotable cascade;
create table demotable ( column1 text );
drop table demorub cascade;
create table demorub (
srto char,
titel text,
rnum integer
);
copy demotable from '$TABLE';
copy demorub from '$MAPPING';
create or replace function mapping (char, text) returns int as '
select rnum from demorub where srto = \$1 and titel = \$2
' language 'sql';
select count(mapping(substring(column1,1,1), 'string2')) from demotable;
EOF
psql <$SQL