[fpc-devel] PostgreSQL SQLdb transactions

[Chris Dryburgh]

Hi

In PostgreSQL it is considered poor practice to have long running idle 
transactions.
https://encrypted.google.com/#q=postgresql+idle+in+transaction

Long running idle transactions are common with SQLdb and PostgreSQL. In 
FPC 2.6.4 and I think the development version a open connection has a 
open transaction that remains open for as long as a dataset is open. 
Transactions are only needed in read-write situations that require 
multiple queries. Single queries automatically start and commit there 
own transaction. I propose only sending a ‘begin’ transaction command 
when a query is sent or when a non-select query is sent. This would mean 
changes to the pqconnection.pp file.

|TPQConnection.StartdbTransaction, TPQConnection.RollBackRetaining,
and TPQConnection.CommitRetaining
...
-   res := PQexec(tr.PGConn, ‘BEGIN’);
-   CheckResultError(res,tr.PGConn,sErrTransactionFailed);
-   PQclear(res);

|

The ‘begin’ transaction command can now be sent in PrepareStatement or 
Execute before a query is run. According to PostgreSQL documentation 
beginning a transaction when there is already one running or committing 
one when one is not running generates a warning but causes no harm. If 
this warning causes an issue then I'm sure a serverTransactionRunning 
flag can be created.

If transactions are opened for all queries.

|  procedure TPQConnection.PrepareStatement(cursor: TSQLCursor;ATransaction : TSQLTransaction;buf : string; AParams : TParams);
or procedure TPQConnection.Execute(cursor: TSQLCursor;atransaction:tSQLtransaction;AParams : TParams);
...
      if LogEvent(detPrepare) then
         Log(detPrepare,S);
+     res := PQexec(tr.PGConn, 'BEGIN');
+     CheckResultError(res,tr.PGConn,sErrTransactionFailed);
+     PQclear(res);
       res := PQexec(tr.PGConn,pchar(s));
       CheckResultError(res,nil,SErrPrepareFailed);
|


If transactions are limited to write only situations.

|  procedure TPQConnection.PrepareStatement(cursor: TSQLCursor;ATransaction : TSQLTransaction;buf : string; AParams : TParams);
|||or procedure TPQConnection.Execute(cursor: TSQLCursor;atransaction:tSQLtransaction;AParams : TParams);
|
...
      if LogEvent(detPrepare) then
         Log(detPrepare,S);
+      if (FStatementType in [stInsert,stUpdate,stDelete]) then
+       begin
+         res := PQexec(tr.PGConn, 'BEGIN');
+         CheckResultError(res,tr.PGConn,sErrTransactionFailed);
+         PQclear(res);
+        end;
       res := PQexec(tr.PGConn,pchar(s));
       CheckResultError(res,nil,SErrPrepareFailed);
|


The end result should be that server transactions only open when needed. 
Users would likely commit write transactions quickly to save data to the 
database. Read-only transactions might be left in a open state but can 
be closed by an aware developer when not needed. To eliminate open 
read-only transactions an option is to never open a transaction for 
select queries which would mean a overhead for the server opening and 
closing transactions for each query. What do others here think?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-devel/attachments/20141104/14523500/attachment.html>