View Issue Details

IDProjectCategoryView StatusLast Update
0025477FPCDatabasepublic2014-01-23 20:49
ReporterMichałAssigned ToBigChimp 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionno change required 
PlatformIntelOSWindowsOS Version7 64b/8.1 64b
Product VersionProduct Build43446 
Target Version2.7.1Fixed in Version 
Summary0025477: Memory leak in TSQLQuery (TSQLQuery.next)
DescriptionI'm trying to export data to a *.csv file using a TSQLQuery (connected to a postgres database). Everything works fine for a small results. When the result is bigger (for example 80k rows, each with 6 fields of varchar 255 - which isn't of course huge amount of data), application exhausts all possible memory (more than 1,5GB).

FIRST TEST (saving data to a file)

BEGIN CODE ----->

  try
            AssignFile(plik, Utf8ToAnsi(SaveDialog.FileName));
            Rewrite(plik);
            Form1.QueryReports.First;

            Count := Form1.QueryReports.FieldCount - 1;
            pg.Max := Form1.QueryReports.RowsAffected;
            pg.Position := 0;

            Form1.Datasource1.Enabled := False;
            Form1.Datasource1.DataSet := nil;

            //header
            for i := 0 to Count do
              begin
                if i = Count then
                  Writeln(plik, '"' + Form1.QueryReports.FieldDefs[i].DisplayName + '"')
                else
                  Write(plik, '"' + Form1.QueryReports.FieldDefs[i].DisplayName + '"' + ',');
              end;

            //content
            while not Form1.QueryReports.EOF do
              begin
                for i := 0 to Count do
                  begin
                    if i = Count then
                      begin
                        tmp_string:= '"' + StringReplace(Form1.QueryReports.Fields[i].AsString, '"', '^', [rfIgnoreCase, rfReplaceAll]) + '"';
                        tmp_string:= StringReplace(tmp_string, 0000013, ' ', [rfIgnoreCase, rfReplaceAll]);
                        tmp_string:= StringReplace(tmp_string, 0000010, ' ', [rfIgnoreCase, rfReplaceAll]);
                        Writeln(plik, tmp_string);
                      end
                    else
                      begin
                        tmp_string:= '"' + StringReplace(Form1.QueryReports.Fields[i].AsString, '"', '^', [rfIgnoreCase, rfReplaceAll]) + '"' + ',';
                        tmp_string:= StringReplace(tmp_string, 0000013, ' ', [rfIgnoreCase, rfReplaceAll]);
                        tmp_string:= StringReplace(tmp_string, 0000010, ' ', [rfIgnoreCase, rfReplaceAll]);
                        Write(plik, tmp_string);
                      end;
                  end;
                Form1.QueryReports.Next;
                pg.Position := pg.Position + 1;
              end;

<----- END CODE

SECOND TEST
I turned off writing to a file, keeping simply iterating through SQLQuery by using '.next').
I made a DumpHeap after each 10000 records. You can see results below.

### After first 10000 records: ###

Heap dump by heaptrc unit
1002361 memory blocks allocated : 886168757/890603048
944126 memory blocks freed : 452070306/456341968
58235 unfreed memory blocks : 434098451
True heap size : 448954368 (112 used in System startup)
True free heap : 10104368
Should be : 10966136
Call trace for block $087554F8 size 28
  $004CABEA TFORMA_RAPORTY__MENUITEM14CLICK (comment: procedure code is above), line 200 of form_raporty.pas
  $00601568 TMENUITEM__CLICK, line 83 of ./include/menuitem.inc
  $00601B26 TMENUITEM__DOCLICKED, line 278 of ./include/menuitem.inc
  $0040C0F6
  $75877694
  $75878BAA
  $75878468
  $75879C40
Call trace for block $087A07B8 size 14
  $004CABBD
  $00601568
  $00601B26
  $0040C0F6
  $75877694
  $75878BAA
  $75878468
  $75879C40
Call trace for block $4140A270 size 42764
[...]


### On 20000 records: ###

Heap dump by heaptrc unit
1828722 memory blocks allocated : 1735404672/1743811016
1760487 memory blocks freed : 873666230/881869944
68235 unfreed memory blocks : 861738442
True heap size : 885948416 (112 used in System startup)
True free heap : 18618432
Should be : 19640192
[...]


### On 30000 records: ###

Heap dump by heaptrc unit
2660904 memory blocks allocated : 2585303174/2597715304
2582669 memory blocks freed : 1295924723/1308094224
78235 unfreed memory blocks : 1289378451
True heap size : 1322942464 (112 used in System startup)
True free heap : 27132464
Should be : 28314232
[...]


After a while: RunError(203), EOutOfMemory
Steps To Reproduce1. Connect throught TPQConnection to PostgreSQL database
2. Use the code above to retrieve data
Additional InformationI made tests on Windows 7 64b, Win 8.1.
The application was compiled on Lazarus (stable) 1.0.10 and 1.0.14 (stable).
TagsNo tags attached.
Fixed in Revision
FPCOldBugId
FPCTarget
Attached Files

Activities

Reinier Olislagers

2014-01-01 12:05

developer   ~0072149

Last edited: 2014-01-01 13:03

View 2 revisions

Please post a compilable example project that demonstrates the problem, including DDL etc. See http://wiki.lazarus.freepascal.org/Database_bug_reporting

Otherwise we cannot reproduce this bug and we cannot help you.

Please indicate your FPC version (2.6.2?)
If you think this is a bug in TSQLQuery: please provide an FPC program, not a Lazarus program.

Thanks.

Michał

2014-01-01 13:59

reporter  

bugtest.zip (1,669,182 bytes)

Michał

2014-01-01 14:04

reporter   ~0072152

Last edited: 2014-01-01 14:12

View 2 revisions

Please find sample lazarus application attached. DDL is inside.
By default- writing to a file is commented so there is simple iterating by using sqlquery.next (it exhausts my memory after about 450k records).

Reinier Olislagers

2014-01-01 14:52

developer  

pqerror.lpr (2,135 bytes)

Reinier Olislagers

2014-01-01 14:56

developer   ~0072154

Ok, thanks; confirmed on FPC 2.6.2 windows
... simplified it to an FPC program which also demonstrates the bug (see attached pqerror.lpr). Please verify.

Problem also occurs on FPC current trunk/2.7.1.

@Devs: please reassign to FPC project/database; thanks

Reinier Olislagers

2014-01-01 15:29

developer   ~0072157

Got this on fpc trunk, linux x64, compiled with -g -gh -gl :
pascaldev@debianlaz:~$ ./pqerror
An unhandled exception occurred at $000000000041C3AB:
EOutOfMemory: Out of memory
  $000000000041C3AB line 861 of ../inc/heap.inc

Heap dump by heaptrc unit
535635 memory blocks allocated : 1655988342/1656524512
535634 memory blocks freed : 1655985207/1656521376
1 unfreed memory blocks : 3135
True heap size : 393216
True free heap : 393024
Should be : 389952
Call trace for block $00007F3B6FC1A3C0 size 40
  $0000000000416142 line 132 of ../inc/except.inc
  $00000000004165E2 line 223 of ../inc/except.inc
  $0000000000474082 line 415 of ../objpas/sysutils/sysutils.inc
  $000000000041A5EA line 1062 of ../inc/system.inc
  $000000000041A6A1 line 1079 of ../inc/system.inc
  $000000000041A71F line 1099 of ../inc/system.inc
  $000000000041C3AB line 861 of ../inc/heap.inc
  $000000000041C908 line 1034 of ../inc/heap.inc

Michał

2014-01-01 15:37

reporter   ~0072158

I checked simplified FPC program. The same error occurred.

LacaK

2014-01-02 09:15

developer   ~0072167

Last edited: 2014-01-02 22:08

View 2 revisions

Can it be reproduced also with other type of TSQLConnection (f.e. SQLite3Connection) ?

Can you try and set UniDirectional:=True for TSQLQuery ?
(something changes?)

Michał

2014-01-02 22:24

reporter   ~0072182

Last edited: 2014-01-02 22:32

View 3 revisions

LacaK,
If I set UniDirectional to true, export doesn't work at all. (Just nothing happens; no errors or messages).
Other DB I will try on the weekend.

Michał

2014-01-04 21:53

reporter   ~0072205

The same error is on SQLite3 database;

LacaK

2014-01-05 15:26

developer   ~0072222

Yes. But let's look again at the problem:

TSQLQuery uses TCustomBufDataset in the background, so all fetched records are cached and stored (buffered) by BufDataset.
Now look at memory requirements:
- One record requires at least 3061 bytes + extra bytes for NullFlags, Bookmark, Record pointers etc. so we need at least 3079 bytes of memory for one record
- if we have in table million records then we need at client side to chache all record: 3 079 000 000 bytes of memory ... so here is IMHO source of EOutOfMemory

Solution is set: UniDirectional:=True (before Open dataset)
Which causes, that records will not be cached, but only last fettched will be available.
For me it works (UniDirectional:=Tru) as expected.

So IMO no bug here?

Michał

2014-01-05 23:34

reporter   ~0072239

I fixed my code and finally I can export data by using TSQLQuery with the UniDirectional set to true.
Thanks for that advice.

However, I had the same problem in another a little more complicated sql query just after 30k-40k of records. (More complicated is not equal to more data in this case).
The same SQL Query in pgadmin (admin tool) I was able to successfully run, view data in grid and then export to a file. The csv file (result) had about 6 MBytes size.
It's hard to belive for me that a 6MB result file can take more than 1,5GB of RAM as a DataSet (80k records, 6 fields varchar 255 each, lots of null).

Anyway, thank you again for your help.

LacaK

2014-01-06 09:18

developer   ~0072243

Can you calculate memory requirements for your second case ?
One varchar column with length of 255 occupies 255+1 bytes of memory.
(may be that it is more depending of used CharSet; f.e. if UTF-8 is used then may be that PQ client libray reports length * 3 (or 4) I am not sure of it reports "byte length" or "character length")
TBufDataSet uses fixed length record layout, so even if columns are empty or null here is still allocated maximum allowed length.

So we have 256*6 = 1536 bytes for one record + cca 18 extra bytes
80 000 * 1554 = 124 320 000 bytes

So here I do not expect Out of memory exception ?
If you can prepare test case, which will reproduce error we can look at it ...

Reinier Olislagers

2014-01-07 14:09

developer   ~0072284

@Lacak: I suppose you're right that there's nothing much we can do about bufdataset.

Have added some docs
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Out_of_memory_errors

Closing this issue.
@Michał: please close if ok; otherwise reopen. Dziekuje!

Michał

2014-01-23 20:49

reporter   ~0072621

I agree. Thank you!

Issue History

Date Modified Username Field Change
2014-01-01 11:46 Michał New Issue
2014-01-01 12:05 Reinier Olislagers Note Added: 0072149
2014-01-01 13:03 Reinier Olislagers Note Edited: 0072149 View Revisions
2014-01-01 13:59 Michał File Added: bugtest.zip
2014-01-01 14:04 Michał Note Added: 0072152
2014-01-01 14:12 Michał Note Edited: 0072152 View Revisions
2014-01-01 14:52 Reinier Olislagers File Added: pqerror.lpr
2014-01-01 14:56 Reinier Olislagers Note Added: 0072154
2014-01-01 15:29 Reinier Olislagers Note Added: 0072157
2014-01-01 15:37 Michał Note Added: 0072158
2014-01-02 09:15 LacaK Note Added: 0072167
2014-01-02 14:29 Maxim Ganetsky Project Packages => FPC
2014-01-02 22:08 LacaK Note Edited: 0072167 View Revisions
2014-01-02 22:24 Michał Note Added: 0072182
2014-01-02 22:31 Michał Note Edited: 0072182 View Revisions
2014-01-02 22:32 Michał Note Edited: 0072182 View Revisions
2014-01-04 21:53 Michał Note Added: 0072205
2014-01-05 15:26 LacaK Note Added: 0072222
2014-01-05 23:34 Michał Note Added: 0072239
2014-01-06 09:18 LacaK Note Added: 0072243
2014-01-07 14:09 Reinier Olislagers Note Added: 0072284
2014-01-07 14:09 Reinier Olislagers Status new => resolved
2014-01-07 14:09 Reinier Olislagers Resolution open => no change required
2014-01-07 14:09 Reinier Olislagers Assigned To => Reinier Olislagers
2014-01-07 14:09 Reinier Olislagers Target Version => 2.7.1
2014-01-23 20:49 Michał Note Added: 0072621
2014-01-23 20:49 Michał Status resolved => closed