Memory Leak with dbgrid an postgresql query ?
lazarus / 16-Databases

----------------------------------------------------------------------
mingodad · 2008-03-13 20:26

Using Lazarus 0.9.24

Hello I'm trying convert one application to lazarus and I supect that
could have a memory leak using dbgrid and postgresql query, I've done
a test with StringGrid and connecting to postgresql with the raw
interface and no memory leak observed, maybe I'm missing some step
when remaking a query.

Bellow is the main code that I use:

procedure TFormPartnersList.btnSearchClick(Sender: TObject);
var
   sql, search_str : String;
begin
     sql := 'select id, name, contact, phone from partners where 1=1
';
     search_str := EditSearch.Text;
     if Length(search_str) > 0 then
     begin
          sql := sql + ' and name ilike ''';
          if AnsiStartsStr(' ', search_str) then
             sql := sql +  EscapeSqlStr(Trim(search_str)) + '%'''
          else
             sql := sql + '%' + EscapeSqlStr(Trim(search_str)) +
'%''';
     end;
     sql := sql + ' order by 2 limit ' +
IntToStr(StrToIntDef(EditSearchLimit.Text, 100));
    
     with DBGridDBList.DataSource do
     begin
          DataSet.Active:=False;
          TSQLQuery(DataSet).SQL.Text := sql;
          DataSet.Active:=True;
     end
end;

With the above code almost every time this procedure is called the
memory comsumption increases a bit, and when using dbfields in another
form the memory comsumption increases a lot more.

Now the StringGrid raw connection that doesn't leak:

procedure TFormProductsList.btnSearchClick(Sender: TObject);
var
 rc, rx, i       : Integer;
 sql , fldv, search_str     : string;
 vm       : Pointer;
begin
          sql := 'select p.id as id, case when p.reference isnull then
'' '' else p.reference end as reference,' +
              'cast('' '' as text) as selected,' +
         'p.description_short as description_short,' +
         'cast(case when p.kit isnull then 0 else kit end as text) as
kit, ' +
         'p.main_price as main_price,' +
         'case when p.kit > 0 then p.kit_onhand else p.onhand end as
onhand, ' +
         '(p.main_price * (1 + (pv.percentage/100))) as
price_with_vat, active, image ' +
         'from products_sb_onhand p left join products_vat pv ' +
         'on p.vat_id = pv.id where 1=1 ';

     search_str := EditSearch.Text;
     if Length(search_str) > 0 then
     begin
          sql := sql + ' and description_short ilike ''';
          if AnsiStartsStr(' ', search_str) then
             sql := sql +  EscapeSqlStr(Trim(search_str)) + '%'''
          else
             sql := sql + '%' + EscapeSqlStr(Trim(search_str)) +
'%''';
     end;

             sql := sql + ' order by 4 ' +
              ' limit 100;';
          res := PQexec(conn,Pchar(sql));
          //ShowMessage(sql);
          if (PQresultStatus(res) = PGRES_TUPLES_OK) then
          begin
          //ShowMessage(sql);
               rx := 1;
               for i := 0 to PQntuples(res)-1 do
               begin
                    StringGridList.RowCount := rx+1;
                    fldv := PQgetvalue(res, i,0);
                    StringGridList.Cells[0,rx] := fldv;
                    fldv := PQgetvalue(res, i,1);
                    StringGridList.Cells[1,rx] := fldv;
                    fldv := PQgetvalue(res, i,3);
                    StringGridList.Cells[2,rx] := fldv;
                    fldv := PQgetvalue(res, i,4);
                    StringGridList.Cells[3,rx] := fldv;
                    fldv := PQgetvalue(res, i,5);
                    StringGridList.Cells[4,rx] := fldv;
                    fldv := PQgetvalue(res, i,6);
                    StringGridList.Cells[5,rx] := fldv;
                    inc(rx);
      end;
          end;
          PQclear(res);
end;

Am I missing something in the first example or there is a bug ?

Thanks in advance for any help !

----------------------------------------------------------------------
mingodad · 2008-03-20 08:42

I'm investigating the problem and I found that the cursor used by
TSQLQuery is never released, that causes successives calls to the same
query to leave a memory leak, but at the end of the program it gets
released, the problem is while the program runs, because it eats more
and more memory while executing.

Here is a modified version of testsqldb.pp I'm using to see the
problem:

program testsqldb;

{ A very simple example for sqldb, written by Joost van der Sluis
(2004)

  The following parameters are used, in given order:

    parameter1 = databasetype (mysql,interbase,postgresql - case
sensitive)
    parameter2 = databasename
    parameter3 = tablename
    parameter4 = username, optional
    parameter5 = password, optional

  This example will only display the data for each record in the given
table.
  Examples:

  ./testsqldb postgresql testdb fpdev
  ./testsqldb interbase /home/firebird/dbtest.fdb fpdev sysdba 123456
}

{$mode objfpc}{$H+}

uses
  Classes,
  pqconnection,
  //mysql4conn,
  //IBConnection,
  sqldb, sysutils;

//  postgres3dyn;

var

   connection  : tSQLConnection;
    transaction : tSQLTransaction;
    query       : tSQLQuery;

    tel, i         : integer;
    dbtype      : string;

begin
dbtype := paramstr(1);
if dbtype = 'postgresql' then connection := tpqConnection.Create(nil);
  if not assigned(connection) then exit; // probably an invalid
database type given

  connection.DatabaseName := paramstr(2);
  connection.UserName := paramstr(4);
  connection.Password := paramstr(5);
  transaction := tsqltransaction.create(nil);
  query := tsqlquery.Create(nil);
  transaction.database := connection;
  query.DataBase := connection;
  query.transaction := transaction;

for i := 0 to 10 do
begin
  connection.open;
  transaction.database := connection;
  query.DataBase := connection;
  query.transaction := transaction;
      query.SQL.clear;
      // use a query that returns a 3 or 4 megas of memory results
      query.sql.add('select * from ' + paramstr(3));
      query.ReadOnly := True; // If the query is writeable, a
transaction must be assigned
                        // to the database.
  query.open;
  //do abreaking point above and bellow and look at the program memory
comsumption
  query.close;
  connection.close;
end;
end.

----------------------------------------------------------------------
Vincent Snijders · 2008-03-20 09:07

Please, create a FPC bug report.