[fpc-devel] PostgreSQL SQLdb transactions

[Mark Morgan Lloyd]

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]