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 1gHWKB-0000bb-1g for psycopg@arkaria.postgresql.org; Tue, 30 Oct 2018 15:52:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gHWK9-0000KZ-FC for psycopg@arkaria.postgresql.org; Tue, 30 Oct 2018 15:52:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gHWK9-0000KR-4s for psycopg@lists.postgresql.org; Tue, 30 Oct 2018 15:52:57 +0000 Received: from mail-qt1-x832.google.com ([2607:f8b0:4864:20::832]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gHWK6-0002jI-J1 for psycopg@postgresql.org; Tue, 30 Oct 2018 15:52:56 +0000 Received: by mail-qt1-x832.google.com with SMTP id i15-v6so14062720qtr.0 for ; Tue, 30 Oct 2018 08:52:54 -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=XRDZLfeGM4tFAzh12X6hG06j0GUxC8MJPIJFWFUVsiQ=; b=zw2281dpEB8i2lB4eWziR+ID/E1T+SbBFD1IDjrgMzB8rfOZTFm8TfyLvVKhvH2ttQ CpW9BpOXawCIOU058Cyo6QXxC3eQ1K9d9MeLpBkqd2H5kOhJpi+SlbOjKapqklF7P8ZS UrPx2y0NPicUnVjNHvpP+uQ1TH7M6Lqy739A9uUVGj2FL//w5jOlhbGWprBzo9kG2hCX JYf5xthpTcGbRex8TX671QL7ZJc65YdWSCrnfkQHaYYqZRNgY0plNkF9LkOWyC/WLxpu zw1lp4DkEDOdtg9aUSugABhjBIxurj8DHjdszzZeZenxyYxLve4YOl1S7tB3l+9pNeeH tIRg== 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=XRDZLfeGM4tFAzh12X6hG06j0GUxC8MJPIJFWFUVsiQ=; b=G/KBF8v0iWnu77gPltHonvBMd+nXQmePyoH3YbWOVRleT5ahhYagDklPPuEt+KLbw1 4vjEb5BarRltf2x0q+e3/9DUCW0EnC1hsSPqHlpYlx7HHh5iuYA0wZ2Qkwmkho3Dci6R gbNg/LQxXi5rUSciTclbXrkkbfiqDEWeMsJSUATbdD8SuEkHye6Bh+z8Wul8zuLlilO1 RcMCXsGQalrnE6uV3GxYvsgYw4Xp0FQnMllmzLbvCBhkgjcWIqrA0lSa6Df4v9RNrI5V VDXF2dz8+hx9BtvJOcQiLK4HteRjVOTy3wF4CiJ1jrYIzWgDCcbNx0GCLJOctvIoBNtM NiEg== X-Gm-Message-State: AGRZ1gLf197OuknJWnDKz4tNlRcQnHda/6obmnkU2NpWDo1aYnWpOAiX jcpNzg/DRmawSm5ADxsgLd4td7Dc0eeWPWTna170Qg== X-Google-Smtp-Source: AJdET5dJQp37z1zBYydZWu2cMeUJ2V4Xt+N6nvYL4lPdGRaudfCRadG74LP0ZtTdoXWW1CqJ7g3scS8tW7lUnhIQum4= X-Received: by 2002:ac8:3466:: with SMTP id v35mr2714734qtb.231.1540914772274; Tue, 30 Oct 2018 08:52:52 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Akshay Joshi Date: Tue, 30 Oct 2018 21:22:41 +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="000000000000a180f30579742d6c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000a180f30579742d6c Content-Type: text/plain; charset="UTF-8" On Tue, 30 Oct 2018, 21:06 Daniele Varrazzo 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 > >> --000000000000a180f30579742d6c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


= 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 <akshay.joshi@enterprisedb.com> wrote:






=C2=A0 =C2=A0 2018-10-30 15:24:25,550| ERROR=C2=A0 =C2=A0| Socket e= xception: 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

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



=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|| statu= s =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->pgconn)) {
+=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 /* This happens connecting through s= sl tunnel
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* TODO= : just kill this function. The loop should happen within
+=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* the async/green machinery. */
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Dprintf("pq_get_las= t_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= ;
```

doesn't work, and failin= g queries leave the connection in an inconsistent state.


So, wrapping up, I think there is something you can d= o 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 b= ehave 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 h= ave less of an urgency to do so.

Please let us kno= w if you understand what is the difference between the module and the tunne= l via `ssh -L`, thank you!
=

=C2=A0 =C2=A0 I h= ave 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 is= sue in psycopg2.
<= div dir=3D"ltr">

-- Daniele
--000000000000a180f30579742d6c--