Re: opened connection

On Sun, Sep 30, 2012 at 06:54:35PM +0200, Levente Kovacs wrote:
> I've been using PostgreSQL for quite while, but I'd need some direction on
> how to handle an opened socket to the database in longer periods.
>
> I open the connection to my database with PQconnectdb().
>
> I access the database, everything is fine. However, if I don't access the
> database for a long time (measured in days), the connection is broken. I
> get this error message:
>
> SQL action failed: 'FATAL: terminating connection due to administrator
> command SSL connection has been closed unexpectedly.

Are you setting keepalives on the connection? They should be on
by default, but it's worth checking. Perhaps they don't
cooperate with SSL somehow.

> I tried to check the connection by PQstatus() before calling PQexec(), but
> this doesn't detect the broken connection.
>
> How can I detect this problem?

The docs for PQstatus() certainly imply that they should detect a
broken connection. You might try a query with no side effects,
say a 'select 1' and then try to PQreset() if that gives
a broken connection.

> Is it wise to terminate the connection each time the PQexec() finished?

Seems like a lot of overhead, you should be able to keep the connection
open. It would be necessary in any case to keep the connection open
to do more than one PQexec() in a single transaction.

> Should I keep the connection for say 1hour, then terminate it?

I don't see any need to do that. It's probably good practice
though to not keep a transaction open when you don't need one.

--
nw