[fpc-devel] PostgreSQL SQLdb transactions

[Chris Dryburgh]

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.