PostgreSQL SQLdb transactions
lazarus / 16-Databases

----------------------------------------------------------------------
goodname · 2014-10-30 18:20

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.
Currently (FPC 2.6.4) a open connection has a open transaction that
remains open for as long as a dataset is open. This happens when
queries are read-only and the server does not need a transaction.
Transactions are only needed in read-write situations. I propose only
sending a ‘begin’ transaction command when the statementType is not a
Select. This would mean changes to the pqconnection file.
Code: [Select]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
before the statement is run if the query is not a select. 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.

Code: [Select] procedure TPQConnection.PrepareStatement(cursor:
TSQLCursor;ATransaction : TSQLTransaction;buf : string; 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 happen when
writing. Users would likely commit these transactions quickly to save
data to the database. Multiple select-queries will run faster inside a
single transaction than they will individually but I think this is a
small drawback compared to the problems that can be caused by long
running idle transactions. What do others here think?

----------------------------------------------------------------------
BigChimp · 2014-11-04 08:24

Note: there is no more development in the FPC 2.6.x branch.

Please discuss your proposed changes on the fpc or fpc-devel mailing
lists; not all FPC devs read this forum.

----------------------------------------------------------------------
goodname · 2014-11-04 14:06

Thanks big chimp.

----------------------------------------------------------------------
goodname · 2014-11-06 20:50

FYI. Looks like there will be a patch going into SQLdb that will allow
a dataset to remain open when a transaction commit is sent. This will
be applicable to all database connection types and will solve the
problem of long running idle transactions.

For MSEgui users read-only queries can be run in tao_fake mode which
will have the server automatically open and close transactions for
each query. The tao_fake behaviour can be ported to other databases
except Firebird which apparently does not support implicit
transactions.