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?
_______________________________________________
fpc-devel maillist - [email protected]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
