<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi<br>
<br>
In PostgreSQL it is considered poor practice to have long running
idle transactions.<br>
<a
href="https://encrypted.google.com/#q=postgresql+idle+in+transaction"
class="bbc_link" target="_blank">https://encrypted.google.com/#q=postgresql+idle+in+transaction</a><br>
<br>
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.<br>
<br>
<pre style="margin: 0; padding: 0;"><code style="overflow: scroll;" class="bbc_code">TPQConnection.StartdbTransaction, TPQConnection.RollBackRetaining,
and TPQConnection.CommitRetaining
...
- res := PQexec(tr.PGConn, ‘BEGIN’);
- CheckResultError(res,tr.PGConn,sErrTransactionFailed);
- PQclear(res);
</code>
</pre>
<font face="sans-serif">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 </font><font face="sans-serif">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.<br>
<br>
If transactions are opened for all queries.<br>
</font>
<pre style="margin: 0; padding: 0;"><code style="overflow: scroll;" class="bbc_code"> 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);
</code></pre>
<br>
If transactions are limited to write only situations.<br>
<pre style="margin: 0; padding: 0;"><code style="overflow: scroll;" class="bbc_code"> procedure TPQConnection.PrepareStatement(cursor: TSQLCursor;ATransaction : TSQLTransaction;buf : string; AParams : TParams);
</code><code style="overflow: scroll;" class="bbc_code"><code style="overflow: scroll;" class="bbc_code">or procedure TPQConnection.Execute(cursor: TSQLCursor;atransaction:tSQLtransaction;AParams : TParams);
</code>
...
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);
</code></pre>
<br>
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?<br>
</body>
</html>