Re: Resetting libpq connections after an app error

On Sat, Jul 21, 2012 at 5:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)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