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

[Michael Van Canneyt]

On Sat, 1 Jan 2011, Mark Morgan Lloyd wrote:

> 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).

The name lookup is not done by FPC itself, but by the Postgresql client library.
This is outside of FPC's control; FPC just asks a connection on a certain IP 
address, it doesn't even know if the adress is an actual IP addres or a DNS name.

You can always do the address lookup yourself, if you want, and pass that to the 
connection component.

Michael.