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?

Chris Dryburgh wrote

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

This is a known problem, not only for postgres.

The problem is the open transaction for an open dataset: committing the transaction (what you would normally do) will close the dataset.

The solution for which I have code in place is a flag which tells the transaction that a connected dataset should not be closed when the transaction is committed. The transaction can then be committed or rollbacked as soon as the data is fetched.

I have code for this in place that works for all connection types. But it still needs to be checked through the testsuite.

Michael.

The MSEgui version of TSQLQuery has two transaction properties, "transactionwrite" which is used for write operations and "transaction" used for read operations and for write operations if "transactionwrite" is not assigned. So one can use different transactions and transaction isolation levels for reading and writing. The MSEgui version of TSQLTransaction has the flag "tao_fake" which omits sending "BEGIN", "COMMIT" and "ROLLBACK" in order to use implicit transactions if the server supports it.
Additional it is possible to "disconnect" an open query dataset from database and transaction, they can be closed after disconnect. Later it is possible to "reconnect" the still open dataset.
I suggest that you implement a "tao_fake"-like functionality yourself, maybe a patch will be accepted. I already suggested it several times because especially MySQL users don't like and often even don't know transactions. :-) If you like to know how it is done in MSEgui:

https://gitorious.org/mseide-msegui

Martin

Sounds good. The bottom line is that the Delphi model where db controls hold a connection etc. open for an extended period is not a good fit on top of a database server which implements connection pools etc.

Another issue is that once a connection has been established to a named server, there's a single point of failure if it tries to reopen it but finds that the nameserver is unavailable. A facility to temporarily cache the IP address, or possibly an application-supplied list of pool names/addresses, would be useful.

I'm aware of the MSEgui approach and have viewed the source code for how it handles transactions. I had trouble seeing how to integrate it with the PostgreSQL code. With my approach the programmer does not have to specify a transaction type. Multiple statements run faster in a single transaction batch and their read-only or read-write actions do not matter. The current MSEgui approach does not allow batch read-only queries which results in server overhead if there are multiple queries run together. It is also read-only transaction idiot proof which is a good thing. Michael's approach of allowing for closing a transaction without closing a still in use dataset looks like a better approach. It will still cause issues for users "that don't like and often even don't know transactions".

I like Michael's approach. If I understand it correctly after opening a query it could be immediately committed and keep the dataset. Currently I use CommitRetaining after opening a query to free up any server locks that may have been created. Using PostgreSQL these would change to Commit which would close the transaction and return the connection to the connection pool. I don't know what would happen for other database engines.

Pretty much the same.
I'll see about getting this code in trunk.

Michael.

On Wednesday 05 November 2014 19:19:41 Chris Dryburgh wrote: [...]> run together. It is also read-only transaction idiot proof which is a

good thing. Michael's approach of allowing for closing a transaction without closing a still in use dataset looks like a better approach. It will still cause issues for users "that don't like and often even don't know transactions".

Just for clarification, the mentioned MSEgui dataset "disconnect"/"reconnect" functionality provides that possibility. Activate
tmsesqlquery.contoller.options "dso_offline" and it will be handled automatically.

Martin

I understand that but I don't think your approach allows for a series of read-only queries to be run in a single transaction batch. It is a simple trade off of security vs possible performance gain.

Chris Dryburgh wrote

I understand that but I don't think your approach allows for a series of read-only queries to be run in a single transaction batch.

Why not? Activate the datasets with dso_offline, the activation of the first dataset will start the transaction. Rollback or commit the transaction after loading all datasets. If one later calls "applyupdates" the dataset automatically starts a new transaction either by "transactionwrite" if assigned or the regular "transaction" property. If dso_autocommit is set there will be also an automatic commit.
Transaction option tao_fake is off of course.

Martin

OK I suppose I had not gotten to the point of fully understanding how it worked. Well Michael's patch should work for all databases and is close to being put into trunk. Unless something happens porting the MSEgui transaction handling approach to other databases should not be needed.

I probably was not clear. MSEgui has two approaches:

1. transaction.options tao_fake, works for all databases except Firebird which does not support implicit transactions.
2. Dataset option dso_offline, works for all databases and probably is equivalent to the FPC patch.

1. is important for users who come from Zeos or MySQL users who update from an older FPC version where MySQL transactions where not supported (normally they switch to Zeos...) or Sqlite3 where explicit transactions sometimes are problematic.

Martin