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

[Lionel B. <lio...@bo...>]

Karl O. Pinc wrote the following on 05.08.2007 18:26 :
> Hi,
>
> 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.
>
>   

Not exactly. It's only a problem when you cache the prepared statements.

> 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 " .
>   

Note that SQLgrey uses prepare and not prepare_cached here: the prepared 
statement ($sth) will be reclaimed when it goes out of scope (unless 
there is a bug in DBI or the driver...).

> I'd love to see this fixed.
>
>   

In fact you made me think of a performance problem. If we do what you 
ask for we won't solve any leaks (or if we do, it will be because we 
don't hit a bug in DBI anymore) but we will allow the database to 
optimize our queries a little more (unless we run on DBs where prepared 
statement are emulated in the driver, but that's another matter).

I'll take a look at that...

Lionel.