Re: Enabling connection pooling by default

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