Re: [fpc-devel] _wcsicmp
On 04/12/14 07:16 AM, Jonas Maebe wrote: The closest equivalent is probably sysutils.UnicodeCompareText(). It uses CompareStringW on Windows though, and on Unix it converts both strings to uppercase, locale-sensitive-wise, and compares those. As a general rule it is better to convert to lower case before comparing Unicode characters. Accents can get lost in conversion to uppercase. Accent loss does not happen or is much more rare in conversion to lower case. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
Re: [fpc-devel] Implicit Transactions Postgres
On 25/11/14 03:47 AM, Michael Van Canneyt wrote: On Mon, 24 Nov 2014, Chris Dryburgh wrote: Found this bug report from 2010 on using implicit transactions in PostgreSQL. As far as I can tell nothing has happened since. Using the TSQLTransaction.Action setting looks like a good approach to implementing implicit transactions. It would mean warning users about setting action as it currently does nothing and is likely ignored by many programmers. http://bugs.freepascal.org/view.php?id=17713 There is another reason to use implicit transactions. When creating or dropping a database the query must be run in a implicit transaction. Currently createDB and dropDB use implicit transactions but do not allow for options beyond the required database name. Implementing implicit transactions will compliment the transaction patch from Michael. His patch allows automatically opened transactions to be closed without closing datasets which allows for user managed transaction handling. Please check the sources, I have additionally implemented toUseImplicit. I tested it using postgres (as firebird does not support it). Michael. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel It looks like implicit transaction code went in a couple of days ago. Seams to only apply to Postgres currently but the frame work is there to apply to other databases. I see the other transaction patch TSQLTransaction.AllowClose in there as well. This is good news for SQLdb and with talk of a branch happening soon just in time. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel
[fpc-devel] Implicit Transactions Postgres
Found this bug report from 2010 on using implicit transactions in PostgreSQL. As far as I can tell nothing has happened since. Using the TSQLTransaction.Action setting looks like a good approach to implementing implicit transactions. It would mean warning users about setting action as it currently does nothing and is likely ignored by many programmers. http://bugs.freepascal.org/view.php?id=17713 There is another reason to use implicit transactions. When creating or dropping a database the query must be run in a implicit transaction. Currently createDB and dropDB use implicit transactions but do not allow for options beyond the required database name. Implementing implicit transactions will compliment the transaction patch from Michael. His patch allows automatically opened transactions to be closed without closing datasets which allows for user managed transaction handling. ___ 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 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 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 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
[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
