Re: Enabling connection pooling by default

[Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>]

Andrus wrote:

> If application is idle for a some time, ADSL modems or something other 
> in wire closes connection automatically.
> To prevent this I think I need to close connection after every major 
> transaction is finished in my application.
> In some cases major transactions are called rapidly. In this case 
> automatic connection pool with 60 sec time should be useful to eliminate 
> time
> to establish new  connection.

The automatic close is probably a TCP/IP connection timeout or (much 
more likely) a home user's NAT router expiring connection tracking entries.

Continued activity on the connection should prevent this. See these 
configuration directives, which you can set in postgresql.conf or 
per-connection using the SET command:

tcp_keepalives_idle = 0		# TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 0		# TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 0		# TCP_KEEPCNT;

Of course, your code must still be prepared to handle broken connections 
where it re-establishes the connection and retries the transaction. You 
need to be prepared for transaction failures due to other transient 
errors, too.

> I have no idea how to implement this with multiple connections.
> Maybe to create login table. When user log-in appl inserts record to it, 
> log-off removes record.

This won't work, because an unexpected connection break won't remove the 
record.

You could use an advisory lock on some imaginary resource, but that'd be 
pretty ugly.

--
Craig Ringer