[Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

[Karl O. P. <ko...@me...>]

Hi,

I'm using sqlgrey 1.6.7 and Postgresql 8.1.

Please forgive me if I'm wrong.  I've not
gone into the code in depth.

Like others I'm getting memory leaks, both in the
sqlgrey process and the Postgresql process.
It almost makes sqlgrey unusable.

The suggestion was that there's a problem in
the perl dbi prepare_cached call.  Research says
that there was a problem with a memory leak in
perl DBI fixed in 1.51 (IIRC), but I'm running
1.53 so that's not where the problem is.

So, I looked at the code.  I'm not a perl wiz,
but the problem appears to be obvious. Sqlgrey
seems to be repeatedly creating prepared queries,
each of which differs from the last.  The whole
point of prepared queries is to have _invariant_
queries, substituted parameters excepted.

Here's one example from 1.6.7, there are others:

sub is_in_from_awl {
     my ($self, $sender_name, $sender_domain, $host) = @_;

     # last_seen less than $self->{sqlgrey}{awl_age} days ago
     my $sth = $self->prepare("SELECT 1 FROM $from_awl " .
                              'WHERE sender_name = ? ' .
                              'AND sender_domain = ? ' .
                              'AND src = ? ' .
                              'AND last_seen > ' .
                               
$self->past_tstamp($self->{sqlgrey}{awl_age},
                                                 'DAY')
                             );
     if (!defined $sth or !$sth->execute($sender_name, $sender_domain,  
$host)) {

The time stamp is going to be different every time.  It should
be a parameter, just like $sender_name, etc.

Like I say, I'm no perl wiz, but it seems to me that this
would be sure to cause both the client side (sqlgrey) and
server side (Postgresql) to require forever increasing
amounts of memory.

I'd love to see this fixed.

Thanks for the program.

Regards,


Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein