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