Re: [fpc-devel] PostgreSQL SQLdb transactions
On 06/11/14 01:31 AM, Martin Schreiber wrote: 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 ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel 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. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
On Thursday 06 November 2014 15:43:03 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 ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
On 06/11/14 10:22 AM, Martin Schreiber wrote: On Thursday 06 November 2014 15:43:03 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 ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel 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. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
On Thursday 06 November 2014 17:10:13 Chris Dryburgh wrote: 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 ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
On Tue, 4 Nov 2014, 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. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
On Tuesday 04 November 2014 19:33:22 Chris Dryburgh wrote: 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? 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 ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
Michael Van Canneyt wrote: On Tue, 4 Nov 2014, 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. 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. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
On 05/11/14 03:04 AM, Martin Schreiber wrote: On Tuesday 04 November 2014 19:33:22 Chris Dryburgh wrote: 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? 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 ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel 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. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
On 05/11/14 05:03 AM, Mark Morgan Lloyd wrote: Michael Van Canneyt wrote: On Tue, 4 Nov 2014, 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. 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 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. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
On Wed, 5 Nov 2014, Chris Dryburgh wrote: 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 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. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] PostgreSQL SQLdb transactions
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 ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
[fpc-devel] PostgreSQL SQLdb transactions
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 - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
