MIME-Version: 1.0
References: 
 <CANxoLDc4AosChXUhArLh2ufdon1BRwhCNRgGjyeCsYf9vHTmTw@mail.gmail.com>
 <CANxoLDf3ra5UaqFw_6A824_hAi5XPykySr+DJre2ZbprbxTCVA@mail.gmail.com>
 <CANxoLDesBkV9=qpg22-9nG=JYgvUuo3Xm+iDxSWwLVReTiEC7g@mail.gmail.com>
 <CA+mi_8ZBYkPm7Kou8smEnDfXPPjVByw7o3M-nY3PvVEK_TN9nA@mail.gmail.com>
 <CA+mi_8boc=n4jZnX+20-UYwZYd4wse+wrnZWf6zG-1nMhDLmnA@mail.gmail.com>
 <CANxoLDfDw=9oAePwZk0Su9PLB3ni_NQdJ5Lj1k21GzYe07sY8Q@mail.gmail.com>
In-Reply-To: 
 <CANxoLDfDw=9oAePwZk0Su9PLB3ni_NQdJ5Lj1k21GzYe07sY8Q@mail.gmail.com>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Tue, 30 Oct 2018 15:36:01 +0000
Message-ID: 
 <CA+mi_8ZfYM9Mqp18z7cTC5Hh8TEPxS+LrotTmWTnS3=wmcMJnA@mail.gmail.com>
Subject: Re: psycopg2 conn.poll() hangs when used with sshtunnel
To: Akshay Joshi <akshay.joshi@enterprisedb.com>
Cc: psycopg@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000017d91b057973f291"
Precedence: bulk

--00000000000017d91b057973f291
Content-Type: text/plain; charset="UTF-8"

On Tue, Oct 30, 2018 at 1:55 PM Akshay Joshi <akshay.joshi@enterprisedb.com>
wrote:


>     What changes should I made to fix this? Or it should be fixed in
> psycopg2 itself.
>

Actually, I have another observation. I was surprised to see that the whole
test suite passed running the connection through an ssh tunnel: there are
definitely failing queries in the test suite. So I made other tests and it
seems the bad condition only happens using the `sshtunnel` module: If I
open a tunnel manually with something like:

    ssh -L 36421:localhost:5432 -N localhost

and point your script to port 36421 everything works ok.


Conversely, I don't seem able to run the test suite through the tunnel open
by the sshtunnel module. It doesn't hang, but the tunnel breaks in a test
with the following reported on the sshtunnel side:

    2018-10-30 15:24:25,550| ERROR   | Socket exception: Bad file
descriptor (9)
    2018-10-30 15:24:25,551| ERROR   | Could not establish connection from
('127.0.0.1', 33743) to remote side of the tunnel

and the following tests fail to run as the connection is broken.


Con-conversely, the patch I had in mind to fix #801, which very brutally is:

```
@@ -1136,6 +1136,13 @@ pq_get_last_result(connectionObject *conn)
                 || status == PGRES_COPY_IN) {
             break;
         }
+        if (PQisBusy(conn->pgconn)) {
+            /* This happens connecting through ssl tunnel
+             * TODO: just kill this function. The loop should happen within
+             * the async/green machinery. */
+            Dprintf("pq_get_last_result: we are busy");
+            break;
+        }
     }

     return result;
```

doesn't work, and failing queries leave the connection in an inconsistent
state.


So, wrapping up, I think there is something you can do on your side,
checking why the sshtunnel module behaves differently from a normal ssh
tunnel, and if you use the latter (or configure sshtunnel to behave so) you
shouldn't hit the problem. On our side I don't think we can fix #801 with a
quick band aid, and we should rather do #802, but as things stands now I
don't trust sshtunnel to do the right thing, and have less of an urgency to
do so.

Please let us know if you understand what is the difference between the
module and the tunnel via `ssh -L`, thank you!

-- Daniele

>

--00000000000017d91b057973f291
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div cl=
ass=3D"gmail_quote"><div dir=3D"ltr">On Tue, Oct 30, 2018 at 1:55 PM Akshay=
 Joshi &lt;<a href=3D"mailto:akshay.joshi@enterprisedb.com">akshay.joshi@en=
terprisedb.com</a>&gt; wrote:<br></div><div>=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"auto"><div dir=3D"auto">=C2=A0=
 =C2=A0 What changes should I made to fix this? Or it should be fixed in ps=
ycopg2 itself.</div></div></blockquote><div><br></div><div>Actually, I have=
 another observation. I was surprised to see that the whole test suite pass=
ed running the connection through an ssh tunnel: there are definitely faili=
ng=C2=A0queries in the test suite. So I made other tests and it seems the b=
ad condition only happens using the `sshtunnel` module: If I open a tunnel =
manually with something like:</div><div><br></div><div>=C2=A0 =C2=A0=C2=A0s=
sh -L 36421:localhost:5432 -N localhost</div><div><br></div><div>and point =
your script to port 36421 everything works ok.</div><div><br></div><div><br=
></div><div>Conversely, I don&#39;t seem able to run the test suite through=
 the tunnel open by the sshtunnel module. It doesn&#39;t hang, but the tunn=
el breaks in a test with the following reported on the sshtunnel side:</div=
><div><br></div><div><div>=C2=A0 =C2=A0 2018-10-30 15:24:25,550| ERROR=C2=
=A0 =C2=A0| Socket exception: Bad file descriptor (9)</div><div>=C2=A0 =C2=
=A0 2018-10-30 15:24:25,551| ERROR=C2=A0 =C2=A0| Could not establish connec=
tion from (&#39;127.0.0.1&#39;, 33743) to remote side of the tunnel</div></=
div><div><br></div><div>and the following tests fail to run as the connecti=
on is broken.</div><div><br></div><div><br></div><div>Con-conversely, the p=
atch I had in mind to fix #801, which very brutally is:</div><div><br></div=
><div>```</div><div><div>@@ -1136,6 +1136,13 @@ pq_get_last_result(connecti=
onObject *conn)</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0|| status =3D=3D PGRES_COPY_IN) {</div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0break;</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0}</div><div>+=C2=A0 =C2=A0 =C2=A0 =C2=A0 if (PQisBusy(conn-&gt;pgcon=
n)) {</div><div>+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 /* This happens =
connecting through ssl tunnel</div><div>+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0* TODO: just kill this function. The loop should happen withi=
n</div><div>+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* the async/gr=
een machinery. */</div><div>+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Dpri=
ntf(&quot;pq_get_last_result: we are busy&quot;);</div><div>+=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 break;</div><div>+=C2=A0 =C2=A0 =C2=A0 =C2=A0 }=
</div><div>=C2=A0 =C2=A0 =C2=A0}</div><div>=C2=A0</div><div>=C2=A0 =C2=A0 =
=C2=A0return result;</div></div><div>```</div><div><br></div><div>doesn&#39=
;t work, and failing queries leave the connection in an inconsistent state.=
</div><div><br></div><div><br></div><div>So, wrapping up, I think there is =
something you can do on your side, checking why the sshtunnel module behave=
s differently from a normal ssh tunnel, and if you use the latter (or confi=
gure sshtunnel to behave so) you shouldn&#39;t hit the problem. On our side=
 I don&#39;t think we can fix #801 with a quick band aid, and we should rat=
her do #802, but as things stands now I don&#39;t trust sshtunnel to do the=
 right thing, and have less of an urgency to do so.</div><div><br></div><di=
v>Please let us know if you understand what is the difference between the m=
odule and the tunnel via `ssh -L`, thank you!</div><div><br></div><div>-- D=
aniele</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"a=
uto"><div dir=3D"auto"><div class=3D"gmail_quote"><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div class=3D"gmail_quote"><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div class=3D"gmail_quote"><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">
</blockquote></div>
</blockquote></div>
</blockquote></div></div></div>
</blockquote></div></div></div></div></div>

--00000000000017d91b057973f291--