Really closing database connections lazarus / 16-Databases ---------------------------------------------------------------------- SymbolicFrank · 2023-04-19 08:08 Even after Free-ing the connection, it stays active until the application exits (at least with PostgreSQL). How do I remove it? I tried this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     MyConnection.CloseTransactions;    MyConnection.CloseDataSets;    MyConnection.Free; That makes no difference. ---------------------------------------------------------------------- MarkMLl · 2023-04-19 08:26 Commit/rollback transactions and set any queries etc. inactive before setting the connection false. I think it's good practice to go through that sequence methodically, rather than just deleting components. MarkMLl ---------------------------------------------------------------------- SymbolicFrank · 2023-04-19 08:35 Yes, that's what I do, it makes no difference. ---------------------------------------------------------------------- MarkMLl · 2023-04-19 08:38 I've definitely not seen that behaviour with PostgreSQL, in fact I lean heavily towards closing connections as soon as they're no longer needed to make apps tolerant of server reboots etc. MarkMLl ---------------------------------------------------------------------- SymbolicFrank · 2023-04-19 09:11 Ok, to explain it a bit more: there are many actions that won't work when you have an active transaction. Which you normally always have with an active connection. That's why there are special functions, like CreateDB, that try to execute a command with only a "clean" connection. For Postgres, that is done by the protected function TPQConnection.ExecuteDirectPG: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TPQConnection.ExecuteDirectPG(const Query: String); var  AHandle : TPGHandle; begin  CheckDisConnected;{$IfDef LinkDynamically}  InitialisePostgres3;{$EndIf}  aHandle:=TPGHandle.Create(Self,'template1');  try    aHandle.Connect;    aHandle.Exec(Query,True,'Error executing query');  finally    aHandle.Free;  end;{$IfDef LinkDynamically}  ReleasePostgres3;{$EndIf}end; The thing is, that connection still keeps counting against your connection limit as long as the application is active. So, even if I create a function like that, it doesn't work. What I'm trying to do is use the COPY functions from the library, which basically requires its own, dedicated connection for each iteration, as they're asynchronous. Reusing the connection is not stable. And then I try to insert thousands of tables, each of which requires at least three transactions, together with that dedicated connection for the COPY command itself. It should work, but I haven't gotten it to work reliably. ---------------------------------------------------------------------- Zvoni · 2023-04-19 09:14 On which side does the connection stay open? Client (Your App) or Server (PostGres)? ---------------------------------------------------------------------- SymbolicFrank · 2023-04-19 09:18 Quote from: Zvoni on April 19, 2023, 11:14:16 amOn which side does the connection stay open? Client (Your App) or Server (PostGres)? I have no idea. I'm sure it is disconnected and Free'd from my side, but I get this error: Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Connection to database failed (PostgreSQL: connection to server at "localhost" (::1), port 5432 failed: FATAL:  sorry, too many clients already) With ~1000 tables, 5000 max_connections is not nearly enough. ---------------------------------------------------------------------- Zvoni · 2023-04-19 09:27 Quote from: SymbolicFrank on April 19, 2023, 11:18:07 amQuote from: Zvoni on April 19, 2023, 11:14:16 amOn which side does the connection stay open? Client (Your App) or Server (PostGres)? I have no idea. I'm sure it is disconnected and Free'd from my side, but I get this error: Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Connection to database failed (PostgreSQL: connection to server at "localhost" (::1), port 5432 failed: FATAL:  sorry, too many clients already) With ~1000 tables, 5000 max_connections is not nearly enough. So it's probably server-side. I just saw in the source-code of TPQConnection, that it uses a Connection-Pool. And that thing is a TThreadList! Have you checked with HeapTrc if it maybe leaks? Maybe it gets swamped instead of reusing "dead" connections ---------------------------------------------------------------------- SymbolicFrank · 2023-04-19 09:45 Quote from: Zvoni on April 19, 2023, 11:27:54 amSo it's probably server-side. I just saw in the source-code of TPQConnection, that it uses a Connection-Pool. And that thing is a TThreadList! Have you checked with HeapTrc if it maybe leaks? Maybe it gets swamped instead of reusing "dead" connections When reusing the same connection, I either get an "external" exception (not useful for debugging), or packets get dropped, probably because it reuses a previous connection that is still busy processing the last transaction. Well, not all the times, sometimes it works... Not production ready. But everything I tried to improve it (ie. do it myself, low-level), only results in more errors. Probably because then there are two different mechanisms, with overlap. Having to do it all myself would be a bummer (and a lot of work). I'll check the heap. ---------------------------------------------------------------------- SymbolicFrank · 2023-04-19 12:07 Heap trace: Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 401072739 memory blocks allocated : 26749298540/28535316080401071599 memory blocks freed     : 26749237799/285352515841140 unfreed memory blocks : 60741 True heap size : 1540096 (576 used in System startup)True free heap : 1224896Should be : 1256144 Call trace for block $000000000ABD9820 size 24  $00000001000116B2  GETMEM,  line 286 of ../inc/heap.inc  $000000010000EE56  NEWINSTANCE,  line 441 of ../inc/objpas.inc  $000000010000EC51  CREATE,  line 324 of ../inc/objpas.inc  $00000001000446D5  CREATE,  line 622 of ../objpas/classes/lists.inc  $0000000100044C5D  CREATE,  line 957 of ../objpas/classes/lists.inc  $00000001001B8610  CREATE,  line 271 of fcl-db/src/sqldb/postgres/pqconnection.pp  $00000001001B9658  DOINTERNALCONNECT,  line 671 of fcl- db/src/sqldb/postgres/pqconnection.pp  $00000001001CF932  DOCONNECT,  line 37 of fcl-db/src/base/database.inc  $00000001001AB6A9  DOCONNECT,  line 14  $00000001001AB6A9  DOCONNECT,    $00000001001D0CA2  SETCONNECTED,  line 689 of fcl- db/src/base/database.inc  $00000001001D1010  OPEN,  line 775 of fcl- db/src/base/database.inc  $00000001001A93E5  PostgresCopy,  line 2106 of ../Modules/tablefunctions.pas  $0000000100031E79  ImportCSS,  line 238 of main.pas  $000000010003264C  ExportGMS,  line 362 of main.pas  $0000000100031630  StartButtonClick,  line 164 of main.pas  $0000000100136251  CLICK,  line 2968 of include/control.inc Call trace for block $000000000ABD9720 size 32  $00000001000116B2  GETMEM,  line 286 of ../inc/heap.inc  $000000010000EE56  NEWINSTANCE,  line 441 of ../inc/objpas.inc  $00000001000446A1  CREATE,  line 620 of ../objpas/classes/lists.inc  $0000000100044C5D  CREATE,  line 957 of ../objpas/classes/lists.inc  $00000001001B8610  CREATE,  line 271 of fcl-db/src/sqldb/postgres/pqconnection.pp  $00000001001B9658  DOINTERNALCONNECT,  line 671 of fcl- db/src/sqldb/postgres/pqconnection.pp  $00000001001CF932  DOCONNECT, $00000001001CF932  DOCONNECT,  line 37 o  $00000001001AB6A9  DOCONNECT,  line 1436 of fcl-db/src/sqldb/sqldb.pp  $00000001001D0CA2  SETCONNECTED,  line 689 of fcl-db/src/base/database.inc  $00000001001D1010  OPEN,  line 775 of fcl-db/src/base/database.inc  $00000001001A93E5  PostgresCopy,  line 2106 of ../Modules/tablefunctions.pas  $0000000100031E79  ImportCSS,  line 238 of main.pas  $000000010003264C  ExportGMS,  line 362 of main.pas  $0000000100031630  StartButtonClick,  line 164 of main.pas  $0000000100136251  CLICK,  line 2968 of include/control.inc  $00000001001516DC  CLICK,  line 56 of include/buttoncontrol.inc Call trace for block $0000000006364FB0 size 64  $00000001000116B2  GETMEM,  line 286 of ../inc/heap.inc  $000000010000EE56  NEWINSTANCE,  line 441 of ../inc/objpas.inc  $0000000100044C11  CREATE,  line 951 of ../objpas/classes/lists.inc  $00000001001B8610  CREATE,  line 271 of fcl-db/src/sqldb/postgres/pqconnection.pp  $00000001001B9658  DOINTERNALCONNECT,  line 671   $00000001001CF932  DOCONNECT,  line 37 o  $00000001001AB6A9  DOCONNECT,  line 1436 of fcl- db/src/sqldb/sqldb.pp  $00000001001D0CA2  SETCONNECTED,  line 689 of fcl-db/src/base/database.inc  $00000001001D1010  OPEN,  line 775 of fcl-db/src/base/database.inc  $00000001001A93E5  PostgresCopy,  line 2106 of ../Modules/tablefunctions.pas  $0000000100031E79  ImportCSS,  line 238 of main.pas  $000000010003264C  ExportGMS,  line 362 of main.pas  $0000000100031630  StartButtonClick,  line 164 of main.pas  $0000000100136251  CLICK,  line 2968 of include/control.inc  $00000001001516DC  CLICK,  line 56 of include/buttoncontrol.inc  So, it seems that some connections are not closed. I checked, but as far as I can see, all the ones I create are freed in a try .. finally block. And while running, no exceptions occurred. (That was why it took so long: it ran for half an hour with the heaptrace enabled, and I needed it not to crash.) Edit: yes, there was no file mentioned in the last line. Edit2: Ah there was more, I added two more blocks. It seems to be the same connection every time? ---------------------------------------------------------------------- MarkMLl · 2023-04-19 12:37 You are making the unjustified assumption that freeing a connection closes all sockets etc. MarkMLl ---------------------------------------------------------------------- Zvoni · 2023-04-19 12:45 Have you tried explicitely closing the connection before freeing? btw: i'd assign a testing-procedure to Event "BeforeDisconnect" just to see if it fires ---------------------------------------------------------------------- SymbolicFrank · 2023-04-19 13:37 Yes, I'll have to make a testprog. That will take some time. ---------------------------------------------------------------------- SymbolicFrank · 2023-04-25 13:04 Testprog! (You need to add the dll/so libraries yourself, otherwise it goes over the size limit). There is a SourceConnector and a DestinationConnector, change their properties to valid databases: the second one needs to be a PostgreSQL one. And yes, it crashes often, if you copy multiple tables. Also, the large tables end up empty, even when the data is inserted and the transaction committed. ---------------------------------------------------------------------- SymbolicFrank · 2023-04-25 14:14 This one is stable (main.pas only), but still doesn't copy all data. ---------------------------------------------------------------------- SymbolicFrank · 2023-04-26 10:20 This one works 1. Use a dedicated, native connection and don't use any high-level functions of the connection (or transaction) you extracted it from. 2. Try all transactions until they succeed, or a fatal error is returned. 3. Read the statusses until nil; 4. Don't forget to collect the error message. And, finally: format the fields correctly (I had a comma as decimal separator in my floats).