Memory leaks when fetching array columns

[Krystian Samp]

Hello DBD-Pg!

I've been struggling now for a few days with leaking memory in my long running Perl script. Step by step I narrowed the source of the leak. And it turned out to be the fetching of array ( text[] ) column from a postgresql db. I tried: using fetchall_hashref( {} ), other variations of fetchall, all variations of fetchrow_*, prepare vs prepare_cached, prepare+fetch_* vs selectall_*. The result is always the same. If I remove the array column from SELECT then the memory leak goes away. I detect the memory leak with Devel::Monitor by comparing number of new SVs before and after the queries - and I do it multiple times which shows that this number is only increasing over time.

I found few thread on the internet reporting some leaks related to fetchall_* etc. but these didn't suggest any working solution.

Is this a known problem? Am I doing something wrong? Would you have advice how to fix it?

Thank you in advance,

Kris

[Ben Tilly]

Honestly my best advice? Create a short stand alone script that can
duplicate it.

Create a temporary table, populate it, query it over and over again,
and demonstrate the memory leak. With a clear test case, you're more
likely to motivate someone to fix it.

[Krystian Samp]

Hey,

Below is a small test case demonstrating the issue. It selects “arr” column ( text[] ) from the “test” table. And you’ll see how the counter of SVs is constantly increasing. If you just change “arr” to “id” (i.e., if you stop retrieving the “arr” column from the table) you will see how the counter increases and decreases (as a result of recycling) staying at the same level.

you need to have a “test” database and initialise it as so:

create table test ( id text, arr text[] );
insert into test values ( ‘a', '{"a","b","c"}' );
insert into test values ( ‘b', '{"a","b","c"}' );
insert into test values ( ‘c', '{"a","b","c"}' );

then the script itself:

<cut>
use strict;
use warnings;
use DBI;
use Devel::Leak;

my $dbh = DBI->connect( "dbi:Pg:dbname=test;host=localhost;port=5432", "postgres", "" );

sub work {
my $sth = $dbh->prepare( "SELECT arr FROM test" );
$sth->execute();
my $r = $sth->fetchall_arrayref( {} );
}

while ( 1 ) {
my $handle;
my $count1 = Devel::Leak::NoteSV( $handle );
work();
my $count2 = Devel::Leak::NoteSV( $handle );
#print "diff: " . ( $count2 - $count1 ) . "\n";
print "c2: " . $count2 . "\n";
#sleep( 1 );
}
<cut>

Thank you,
K

[Greg Sabino Mullane]

On Sun, Nov 02, 2014 at 07:07:47PM +0000, Krystian Samp wrote:
> Below is a small test case demonstrating the issue.

Thank you for the report and test case. I modified it a hair
and added to the repo in the testme.tmp.pl file, and was
able to verify the leak. I made a small change in commit
36463bd3e72c0f274488be2cbdde61365335b
that fixes it some - we now only leak 6 SVs per round. I will
take a closer look when I am fully caffeinated and my mind
is clear, as XS mortalization is a real bugbear. If anyone
else wants to take a look, the problem is somewhere in
pg_destringify_array function inside of dbdimp.c

I verified that the problem is in that block by setting:

$dbh->{pg_expand_array} = 0;

If that is set, no leak is seen in the test case.


--
Greg Sabino Mullane gr...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8

[Krystian Samp]

Thank you Greg, I’m sending you a virtual coffee with lots of good caffeine. Thanks again!

K

[Lamprecht]

Am 04.11.2014 um 16:32 schrieb Greg Sabino Mullane:

> I verified that the problem is in that block by setting:
>
> $dbh->{pg_expand_array} = 0;
>
> If that is set, no leak is seen in the test case.
>
>

I tried to reproduce with Ubuntu 14.04 / perl 5.18.2 / PG 9.3.5
DBD::Pg 3.4.2

I set $dbh->{pg_expand_array} = 0;

No matter if I select the text or the array column, I still get an inc of 1 SV
per round.
But what makes it much worse is a growth of process memory: It increases by 0.4
MB each round. I doubt that this is all lost in the one SV that D::L reports...

Cheers, Christoph

[Lamprecht]

Checked with perlbrew installs of perl-5.16.3 and perl-5.21.5 - got the same
results.
Unfortunately Git checkout commit 18b5b8f0d4f82fdc4a0 fails tests on all the
three perls I tried:


t/01connect.t ....... 1/15 #
# DBI Version 1.63
# DBD::Pg Version 3.4.2
# Perl Version 5.18.2
# OS linux
# PostgreSQL (compiled) 90305
# PostgreSQL (target) 90305
# PostgreSQL (reported) PostgreSQL 9.3.5 on i686-pc-linux-gnu, compiled by
gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 32-bit
# Default port 5432
# DBI_DSN dbi:Pg:;client_encoding=utf8
# DBI_USER postgres
# Test schema dbd_pg_testschema
# LANG de_DE.UTF-8
# array_nulls on
# backslash_quote safe_encoding
# client_encoding UTF8
# server_encoding UTF8
# standard_conforming_strings on
# Adjusted: DBI_DSN
t/01connect.t ....... ok
t/01constants.t ..... ok
t/02attribs.t ....... 254/260 (in cleanup) DBD::Pg::st DESTROY failed: no
connection to the server at t/02attribs.t line 1622.
t/02attribs.t ....... ok
t/03dbmethod.t ...... 1/547
# Failed test 'DB handle method "do" works properly with passed-in array with
undefined entries'
# at t/03dbmethod.t line 133.
# Structures begin differing at:
# $got->[1][2] = 'ARRAY(0x8728480)'
# $expected->[1][2] = 'asasa'
t/03dbmethod.t ...... 158/547 # Looks like you failed 1 test of 547.

...

Test Summary Report
-------------------
t/03dbmethod.t (Wstat: 256 Tests: 547 Failed: 1)
Failed test: 11
Non-zero exit status: 1
t/03smethod.t (Wstat: 65280 Tests: 90 Failed: 0)
Non-zero exit status: 255
Parse errors: Bad plan. You planned 122 tests but ran 90.
t/09arrays.t (Wstat: 65280 Tests: 144 Failed: 46)
Failed tests: 5, 7, 10, 12, 15, 17, 20, 22, 25, 27, 46
48, 51, 53, 56, 58, 61, 63, 66, 68, 71
73, 76, 78, 81, 83, 86, 88, 91, 93, 96
98, 101, 103, 106, 108, 111, 113, 116, 118
121, 123, 126, 128, 141, 143
Non-zero exit status: 255
Parse errors: Bad plan. You planned 200 tests but ran 144.
t/30unicode.t (Wstat: 65280 Tests: 32 Failed: 0)
Non-zero exit status: 255
Parse errors: No plan found in TAP output
Files=21, Tests=1719, 32 wallclock secs ( 0.73 usr 0.09 sys + 8.44 cusr 0.90
csys = 10.16 CPU)
Result: FAIL
Failed 4/21 test programs. 47/1719 subtests failed.

Christoph

[Lamprecht]

Am 07.11.2014 um 16:10 schrieb Lamprecht:

The memory growth is caused by Devel::Leak, so please ignore.
When I move the prepare out of the the loop so the statement gets prepared only
once, the SV count is constant.

Christoph