Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHWOz-0000p0-Mo for psycopg@arkaria.postgresql.org; Tue, 30 Oct 2018 15:57:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gHWOx-0003Tt-W3 for psycopg@arkaria.postgresql.org; Tue, 30 Oct 2018 15:57:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHWOx-0003R3-J9 for psycopg@lists.postgresql.org; Tue, 30 Oct 2018 15:57:55 +0000 Received: from mail-qt1-x82d.google.com ([2607:f8b0:4864:20::82d]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gHWOu-0003d5-Fq for psycopg@postgresql.org; Tue, 30 Oct 2018 15:57:54 +0000 Received: by mail-qt1-x82d.google.com with SMTP id u34-v6so14054174qth.3 for ; Tue, 30 Oct 2018 08:57:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=mc0vzCBmfnogDkQvilZqo/euBtZXq7q6puLNGgRMAb0=; b=DIq7myyqvytqh68hd78l+ekHvWbMhPod29tdi7REojELcly2lTbPDZIBLdnQ9Iyv/3 7U2HZ6KildAdpxQuhblRS1P9T4BlDsTpuCpxit99wPxyMpvrhEao6ZLFo/GO1eaK2bgE 75Xc+DFwQTg9Of9c1xrYoc5BXnWVd51cmPYTesLCX3JPrLB9b3R2fX0O5qHYyaMhTiA2 NsAD5sCMUCvxitds4Uevb1rzKgKLpvO9Ab+ixRiPmh5SX1msFO8J/UWOTbvWN637eQYB 3M2fFyFSWf78Tn6voZhx1vUkxoWzW/0DgDPi6VVeg4a38m6OwA14lXw9yNARFDZmLQL7 XYEQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=mc0vzCBmfnogDkQvilZqo/euBtZXq7q6puLNGgRMAb0=; b=qJu5No2HLgCba5oYBUrNvQVkVuxi/CVJqzfVxU2TjiY0qpXGDSoBjdjWYLLiuIwZVR llC61dphPn9IFCUzhDjDrdHzEA+DM1Be8im9vygdxcX0vAoCIJelyOg1eQLa3RKb1tKy WoQv86r/hcrNO571mevmd+s+sW15kyThwRA2NP6s39EG0nn+YZ12Yh//hnXZ0v0Rxniw tCuRzBSEu1+J5aF3/vMITpTc3xhNzsozmj7Ea9mte68im4MK0r/7Quo6eG15rekuqndZ GpQW8RnKnUabcrohQwyW2BfvRc88vrZKuhoZKC2LsfrYIRj5N3U3ro4MUKBTvxFD1vyv XphA== X-Gm-Message-State: AGRZ1gKfkiAPaBF/S8n0kU+0BZlmKvGE5XTvRaHtO9MTlO+HW1+PuYR6 l9R+gTYjvAYSwrVbqnyKSmGGUMhHXeXOqrU4e7SdDucQ X-Google-Smtp-Source: AJdET5e6sadiTY6p9hjAc0kgI8tO06+aJn3DON7A10v2Bj5aUtGP88GWpdavDUUH122nkWv+BgBcf1DQcnrCDQmJnME= X-Received: by 2002:ac8:3466:: with SMTP id v35mr2731597qtb.231.1540915071233; Tue, 30 Oct 2018 08:57:51 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Akshay Joshi Date: Tue, 30 Oct 2018 21:27:39 +0530 Message-ID: Subject: Re: psycopg2 conn.poll() hangs when used with sshtunnel To: Daniele Varrazzo Cc: psycopg@postgresql.org Content-Type: multipart/alternative; boundary="000000000000733d2a0579743f1d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000733d2a0579743f1d Content-Type: text/plain; charset="UTF-8" I have one more observation when i run this application one out of 10 times it works. I have logged one more issue as COPY command hangs, so thought it is the problem in psycopg2 On Tue, 30 Oct 2018, 21:22 Akshay Joshi > > On Tue, 30 Oct 2018, 21:06 Daniele Varrazzo wrote: > >> 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! >> > > I have send the sample application and created an issue to sshtunnel > git hub. Not sure where is the problem, as poll() function hangs so i > thought its issue in psycopg2. > >> >> -- Daniele >> >>> --000000000000733d2a0579743f1d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I have one more observation when i run this application o= ne out of 10 times it works. I have logged one more issue as COPY command h= angs, so thought it is the problem in psycopg2=C2=A0

On Tue, 30 Oct 2018, 21:22 Akshay Joshi <= akshay.joshi@enterprisedb.= com wrote:


On Tue, 30 Oct 2018,= 21:06 Daniele Varrazzo <daniele.varrazzo@gmail.com wrote:
On Tue, Oct 30, 2018 at 1:55 PM Akshay Joshi <aks= hay.joshi@enterprisedb.com> wrote:
=C2=A0
=C2=A0 =C2=A0 What changes should I made to fix this? Or it should be f= ixed in psycopg2 itself.

Actual= ly, I have another observation. I was surprised to see that the whole test = suite passed running the connection through an ssh tunnel: there are defini= tely failing=C2=A0queries in the test suite. So I made other tests and it s= eems the bad condition only happens using the `sshtunnel` module: If I open= a tunnel manually with something like:

=C2=A0 =C2= =A0=C2=A0ssh -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 sui= te through the tunnel open by the sshtunnel module. It doesn't hang, bu= t the tunnel breaks in a test with the following reported on the sshtunnel = side:

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

and the following tests fail to run as the c= onnection 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(c= onnectionObject *conn)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|| status =3D=3D PGRES_COPY_IN) {
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0break;
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0}
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 if (PQisBusy(conn-&g= t;pgconn)) {
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 /* This h= appens connecting through ssl tunnel
+=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0* TODO: just kill this function. The loop should happe= n within
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* the a= sync/green machinery. */
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Dprintf("pq_get_last_result: we are busy");
+=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 break;
+=C2=A0 =C2=A0 =C2=A0 = =C2=A0 }
=C2=A0 =C2=A0 =C2=A0}
=C2=A0
=C2=A0 = =C2=A0 =C2=A0return result;
```

do= esn't work, and failing queries leave the connection in an inconsistent= state.


So, wrapping up, I think th= ere 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 (o= r configure sshtunnel to behave so) you shouldn't hit the problem. On o= ur side I don't think we can fix #801 with a quick band aid, and we sho= uld 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 betwee= n the module and the tunnel via `ssh -L`, thank you!

=C2=A0 =C2=A0 I have send the sample application and created an i= ssue to sshtunnel git hub. Not sure where is the problem, as poll() functio= n hangs so i thought its issue in psycopg2.
=
-- Daniele
--000000000000733d2a0579743f1d--