Re: Perl function leading to out of memory error

On 19.02.2013 12:41, Tom Lane wrote:
> Jan Strube <js(at)deriva(dot)de> writes:
>> we have a Java daemon that´s repeatedly calling a Perl function inside
>> our database (version 9.1.8). The function is called about 200 times per
>> second. While the Java program is running you can watch the memory usage
>> of the postmaster grow continuously until after a few hours we get an
>> out of memory error from Postgres. In the log you see a lot of
>> "ExprContext..." messages.
> I tried to reproduce this, without much success. Can you extract a
> self-contained test case?
after some investigation it seems that the error has to do with a domain
type that we have defined in our database. We have defined the following
helper functions:

CREATE OR REPLACE FUNCTION isin_pz(text) RETURNS integer AS $$
DECLARE
c char;
s text := '';
l integer;
d integer;
w integer;
sum integer := 0;
BEGIN
IF char_length($1) != 11 THEN
RETURN null;
END IF;

IF substr($1, 1, 2) < 'AA' OR substr($1, 1, 2) > 'ZZ' THEN
RETURN null;
END IF;

FOR pos IN 1 .. 11 LOOP
c := substr($1, pos, 1);
IF c >= '0' AND c <= '9' THEN
s := s || c;
ELSE
IF c >= 'A' AND c <= 'Z' THEN
s := s || to_char(ascii(c) - 55, 'FM99');
ELSE
RETURN null;
END IF;
END IF;
END LOOP;

l := char_length(s);
FOR pos IN 1 .. l LOOP
d := to_number(substr(s, pos, 1), '0');
w := ((l-pos+1) % 2) + 1;
IF w * d >= 10 THEN
sum := sum + (w * d) % 10 + 1;
ELSE
sum := sum + (w * d);
END IF;
END LOOP;

RETURN (10 - (sum % 10)) % 10;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION isin_ok(text) RETURNS boolean AS $$
DECLARE
pz integer;
BEGIN
IF char_length($1) != 12 OR substr($1, 1, 2) < 'AA' OR substr($1,
1, 2) > 'ZZ' THEN
RETURN false;
END IF;

pz := public.isin_pz(substr($1, 1, 11));
IF pz IS NULL THEN
RETURN false;
END IF;

RETURN to_char(pz, 'FM9') = substr($1, 12, 1);
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

They are used to define the domain type "isin" as follows:

CREATE DOMAIN isin AS char(12) CHECK (isin_ok(value));

Now we can create our test case. Create the following table:

CREATE TABLE foo (isin char(12) NOT NULL);

And this function:

CREATE OR REPLACE FUNCTION foo(isin char(12)) RETURNS void AS $$
my ($isin) = @_;

my $stmt = spi_prepare('
INSERT INTO foo (isin)
VALUES ($1)', 'isin');
spi_exec_prepared($stmt, $isin);
spi_freeplan($stmt);
$$ LANGUAGE plperl VOLATILE STRICT;

If we now repeatedly call this function (e.g. using a Perl script) we
can see the memory consumption rise continuously until the out of memory
error occurs.

Interestingly, if we change the type specification in the call to
"spi_prepare" from "isin" to "char(12)" the problem no longer occurs.

Can you explain this behavior?

Regards,
Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen

Amtsgericht Göttingen | HRB 3240
Geschäftsführer: Dirk Baule, Christian Schröder
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer