Thread: Resetting libpq connections after an app error

Hello,

apologize for bumping the question to -hackers but I got no answer
from -general. If there is a better ML to post it let me know.

In a libpq application, if there is an application error between
PQsendQuery and PQgetResult, is there a way to revert a connection
back to an usable state (i.e. from transaction status ACTIVE to IDLE)
without using the network in a blocking way? We are currently doing
   while (NULL != (res = PQgetResult(conn->pgconn))) {       PQclear(res);   }

but this is blocking, and if the error had been caused by the network
down, we'll just get stuck in a poll() waiting for a timeout.

Thank you very much.

-- Daniele

On Sat, Jul 21, 2012 at 01:08:58AM +0100, Daniele Varrazzo wrote:
> Hello,
>
> apologize for bumping the question to -hackers but I got no answer
> from -general. If there is a better ML to post it let me know.
>
> In a libpq application, if there is an application error between
> PQsendQuery and PQgetResult, is there a way to revert a connection
> back to an usable state (i.e. from transaction status ACTIVE to IDLE)
> without using the network in a blocking way? We are currently doing
>
>     while (NULL != (res = PQgetResult(conn->pgconn))) {
>         PQclear(res);
>     }
>
> but this is blocking, and if the error had been caused by the network
> down, we'll just get stuck in a poll() waiting for a timeout.

There is PQreset(), which also exists in a non-blocking variant.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.  -- Arthur Schopenhauer

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sat, Jul 21, 2012 at 01:08:58AM +0100, Daniele Varrazzo wrote:
>> In a libpq application, if there is an application error between
>> PQsendQuery and PQgetResult, is there a way to revert a connection
>> back to an usable state (i.e. from transaction status ACTIVE to IDLE)
>> without using the network in a blocking way? We are currently doing

> There is PQreset(), which also exists in a non-blocking variant.

Note that PQreset essentially kills the connection and establishes a new
one, which might not be what Daniele is looking for.  The alternative
approach is to issue PQcancel and then just let the query flush out as
you normally would in an async application, ie PQisBusy/PQconsumeInput
until ready, then PQgetResult (which you throw away), repeat until you
get NULL.
        regards, tom lane

On Sat, Jul 21, 2012 at 5:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>> On Sat, Jul 21, 2012 at 01:08:58AM +0100, Daniele Varrazzo wrote:
>>> In a libpq application, if there is an application error between
>>> PQsendQuery and PQgetResult, is there a way to revert a connection
>>> back to an usable state (i.e. from transaction status ACTIVE to IDLE)
>>> without using the network in a blocking way? We are currently doing
>
>> There is PQreset(), which also exists in a non-blocking variant.
>
> Note that PQreset essentially kills the connection and establishes a new
> one, which might not be what Daniele is looking for.  The alternative
> approach is to issue PQcancel and then just let the query flush out as
> you normally would in an async application, ie PQisBusy/PQconsumeInput
> until ready, then PQgetResult (which you throw away), repeat until you
> get NULL.

I'm back on this issue. I've tested the PQcancel approach, but blocks
as well on send()/recv(), and given the constraint resources it is
bound to use (to be called from a signal handler) I assume there is no
workaround for it.

The PQreset approach has the shortcoming of discarding the session
configuration that somebody may have created in Pythonland: a state
with a connection made but not configured would be something a client
program is probably not prepared to handle.

I think a plain disconnection would be much easier to handle for
long-running python program: a long-running one should probably
already cope with a broken connection, a short-lived one would benefit
of working SIGINT.

If you have other suggestions I'd be glad to know, thank you.


-- Daniele