Re: opened connection

Dear List,

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.

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?

Is it wise to terminate the connection each time the PQexec() finished?
Should I keep the connection for say 1hour, then terminate it?
Is there a common practice?

Any guides are welcome.

Thank you,
Levente

--
Levente Kovacs
CTO, CSO
http://levente.logonex.eu

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

I think a general best practice is to keep a database connection open for as
short a time as possible, where that doesn't adversely impact your performance;
so, for example, close it if you don't expect to be using it for the next few
minutes, and then reopen it. Open connections tie up resources and are wasted
if you aren't actively doing something.

Even more important, though, is having transactions open for as short a time as
possible. A general rule of thumb is that a transaction should be as short as
possible, eg not more than a few seconds, and only group together statements
that actually should be mutually atomic, as this gives you the greatest amount
of concurrency while keeping consistency.

A main exception to the latter is if you are doing some batch operation such as
inserting large numbers of records, in which case you want to make your
transactions quite large, including large numbers of records (at least thousands
in a batch), as this has a huge impact on performance.

Unless you have very unique needs, keeping an open connection for days is just
wrong anyway; if its for the sake of some user GUI or shell, there probably
should be safeguards there to encourage users to not keep long-running
transactions or connections.

-- Darren Duncan

Levente Kovacs wrote:
> Dear List,
>
>
> 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.
>
> 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?
>
> Is it wise to terminate the connection each time the PQexec() finished?
> Should I keep the connection for say 1hour, then terminate it?
> Is there a common practice?
>
> Any guides are welcome.
>
> Thank you,
> Levente
>

On Sun, Sep 30, 2012 at 5:41 PM, Nathan Wagner <nw(at)hydaspes(dot)if(dot)org> wrote:
> 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've had problems with ssl connections dying on me. For slony
replication I had to make sure the connections were NOT ssl or they'd
die and subscriptions would just keep repeating after getting 80%
through and getting a connect error. This was with 8.4 on debian
lenny.

On Sun, 30 Sep 2012 20:24:47 -0700
Darren Duncan <darren(at)darrenduncan(dot)net> wrote:

> Unless you have very unique needs, keeping an open connection for
> days is just wrong anyway; if its for the sake of some user GUI or
> shell, there probably should be safeguards there to encourage users
> to not keep long-running transactions or connections.

Okay. Thanks for the answers. I implemented an alarm for the timeout, and I
close the connection when it is not needed in certain amount of time.

However, I have an other question.
Calling PQfinish() on an already closed connection makes my program
segfault. Is this normal?

Thank you,
Levente

--
Levente Kovacs
CTO, CSO
http://levente.logonex.eu

On 2012-10-01, Levente Kovacs <leventelist(at)gmail(dot)com> wrote:
> On Sun, 30 Sep 2012 20:24:47 -0700
> Darren Duncan <darren(at)darrenduncan(dot)net> wrote:
>
>
>> Unless you have very unique needs, keeping an open connection for
>> days is just wrong anyway; if its for the sake of some user GUI or
>> shell, there probably should be safeguards there to encourage users
>> to not keep long-running transactions or connections.
>
> Okay. Thanks for the answers. I implemented an alarm for the timeout, and I
> close the connection when it is not needed in certain amount of time.
>
> However, I have an other question.
> Calling PQfinish() on an already closed connection makes my program
> segfault. Is this normal?

Absolutely. Calling PQfinish on any other pointer to unallocated
heap is likely to cause a segfault too. same as calling fclose()
on a closed, (or unopened), FILE*.

--
⚂⚃ 100% natural

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> I've had problems with ssl connections dying on me. For slony
> replication I had to make sure the connections were NOT ssl or they'd
> die and subscriptions would just keep repeating after getting 80%
> through and getting a connect error. This was with 8.4 on debian
> lenny.

That sounds like an artifact of the kluge solution some vendors used for
the SSL renegotiation security bug a couple years back: their patched
openssl libraries would simply kill the connection when a key
renegotiation was requested, which PG would do after transferring a
couple hundred megabytes.

We put in a workaround whereby you could prevent that by setting a GUC
variable to disable the renegotiation requests ... but if you're still
seeing such a problem today, you really need to complain to your distro
vendor. Nobody should still be shipping such lobotomized libraries.

regards, tom lane