Обсуждение: dblnk_is_busy returns 1 for dead connecitons

Hackers,

I have a situation that I am observing where dblink_is_busy returns 1
even though the connection is long gone.   tcp keepalives are on and
the connection has been dead for several hours. Looking at the call
for dblink_is_busy, I see that  it is a thin wrapper to PQusBusy().
If I attempt to call dblink_get_result(), the result comes back with
an error mesage, 'invalid socket'. This however is not helpful since
there is no way to probe for dead connections in dblink that appears
to be 100% reliable.  My workaround that I had been relying on was to
call dblink_get_notify twice, which for some weird reason forced the
connection error to the surface.  However for whatever reason, that is
not working here.

In cases the connection was cancelled via dblink_cancel_query(), so in
some scenarios connections cancelled that way seem to become 'stuck'.
Any thoughts on this?

merlin

On Sun, Aug 2, 2020 at 7:18 PM Merlin Moncure <mmoncure@gmail.com> wrote:
>
> Hackers,
>
> I have a situation that I am observing where dblink_is_busy returns 1
> even though the connection is long gone.   tcp keepalives are on and
> the connection has been dead for several hours. Looking at the call
> for dblink_is_busy, I see that  it is a thin wrapper to PQusBusy().
> If I attempt to call dblink_get_result(), the result comes back with
> an error mesage, 'invalid socket'. This however is not helpful since
> there is no way to probe for dead connections in dblink that appears
> to be 100% reliable.  My workaround that I had been relying on was to
> call dblink_get_notify twice, which for some weird reason forced the
> connection error to the surface.  However for whatever reason, that is
> not working here.
>
> In cases the connection was cancelled via dblink_cancel_query(), so in
> some scenarios connections cancelled that way seem to become 'stuck'.
> Any thoughts on this?

Correction, keepalives are probably not on, because dblink does not
have an option to set them.  Also, it looks like dblink_is_busy()
calls pqConsumeInput without checking the error code.  Is that safe?

merlin

On Sun, Aug 2, 2020 at 9:55 PM Merlin Moncure <mmoncure@gmail.com> wrote:
>
> On Sun, Aug 2, 2020 at 7:18 PM Merlin Moncure <mmoncure@gmail.com> wrote:
> >
> > Hackers,
> >
> > I have a situation that I am observing where dblink_is_busy returns 1
> > even though the connection is long gone.   tcp keepalives are on and
> > the connection has been dead for several hours. Looking at the call
> > for dblink_is_busy, I see that  it is a thin wrapper to PQusBusy().
> > If I attempt to call dblink_get_result(), the result comes back with
> > an error mesage, 'invalid socket'. This however is not helpful since
> > there is no way to probe for dead connections in dblink that appears
> > to be 100% reliable.  My workaround that I had been relying on was to
> > call dblink_get_notify twice, which for some weird reason forced the
> > connection error to the surface.  However for whatever reason, that is
> > not working here.
> >
> > In cases the connection was cancelled via dblink_cancel_query(), so in
> > some scenarios connections cancelled that way seem to become 'stuck'.
> > Any thoughts on this?
>
> Correction, keepalives are probably not on, because dblink does not
> have an option to set them.  Also, it looks like dblink_is_busy()
> calls pqConsumeInput without checking the error code.  Is that safe?

I could not reproduce this with application external test script (see
attached if curious).  I alos noticed you can set keepalives in the
libpq connection string, so I'll do that and see if it helps, and
report back for posterity.  Thanks, sorry for the noise.

merlin