On 19.02.2013 12:41, Tom Lane wrote:
Jan Strube <js@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
