[fpc-pascal] Lifetime of a database (PostgreSQL) connection

[Mark Morgan Lloyd]

Michael Van Canneyt wrote:
> On Fri, 31 Dec 2010, Mark Morgan Lloyd wrote:
> 
>> I've just taken our name server down for maintenance, and noted that a 
>> db-aware app I'd left running ground to a halt despite the fact that 
>> it was only activating queries, not establishing new (named) connections.
>>
>> The connection was being made by host name rather than by dotted-quad 
>> address, resulting in a long-term handle associated with the 
>> TPQConnection object. Whenever a TSQLQuery is activated, it results in 
>> the opening of a short-term handle, if I'm reading things correctly 
>> associated with a TSQLTransaction object which relies on the host 
>> information from the connection object.
>>
>> My expectation is that these short-term handles should not mandate a 
>> name lookup, since the host name or address cannot be specified at 
>> this point. Am I being unreasonable?
> 
> In order to support multiple transaction, a connection is created for 
> each transaction. So if you application creates transactions at regular 
> intervals, then it will perform a name lookup each time a transaction is 
> created.

The program is not creating explicit transactions and only has read 
access to the table. I'm establishing a connection during program 
initialisation and then alternating between two query objects, it's the 
activation of the query object which is causing a short-term handle to 
be opened.

Please excuse me for not having looked at the sources yet, but I presume 
that the client library is performing a name lookup if the initial 
connection was by name (hopefully it doesn't do this if the connection 
was by address). I'd suggest that the IP address could usefully be 
cached in case of name server failure, in the same way that other 
parameters are cached somewhere (for example, the password in the 
connection object can be wiped without affecting operation).

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]