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
>
