Re: Perl function leading to out of memory error - Mailing list pgsql-general

On 19.02.2013 12:41, Tom Lane wrote:
> Jan Strube <js@deriva.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