MIME-Version: 1.0
References: 
 <CANxoLDekyavcxjh1yWk1hhB1ER+YvxUkg1ZP2DOYev+Mr1FULw@mail.gmail.com>
In-Reply-To: 
 <CANxoLDekyavcxjh1yWk1hhB1ER+YvxUkg1ZP2DOYev+Mr1FULw@mail.gmail.com>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Thu, 4 Oct 2018 11:26:11 +0100
Message-ID: 
 <CA+mi_8Z0EYtyayOYzerwjZww2zFGE=f0oQdvW7P92ZOUvdRhQA@mail.gmail.com>
Subject: Re: Copy command hangs in poll() function
To: akshay.joshi@enterprisedb.com
Cc: psycopg@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000001cda680577649623"
Precedence: bulk

--0000000000001cda680577649623
Content-Type: text/plain; charset="UTF-8"

On Thu, Oct 4, 2018 at 6:35 AM Akshay Joshi <akshay.joshi@enterprisedb.com>
wrote:

> Hello
>
> As per documentation http://initd.org/psycopg/docs/advanced.html Copy
> commands are not supported in asynchronous mode, but this will be probably
> implemented in a future release. In pgAdmin4 (open source) we have used
> async connection to run the SQL query on PostgreSQL database and uses
> poll() function to poll the results. When user runs any *COPY *command
> application hangs at poll() function.
>

> If copy command not supported then at least it should not hang the
> application. poll() function should return the proper error message.
>
> Can you please provide that fix until support for copy command won't come.
>

Uhm, I see:

    In [2]: cnn = psycopg2.connect("")
    In [3]: cur = cnn.cursor()
    In [4]: cur.execute("copy (select 1 as x) to stdout")

---------------------------------------------------------------------------
    ProgrammingError                          Traceback (most recent call
last)
    <ipython-input-4-62e3af3d121e> in <module>()
    ----> 1 cur.execute("copy (select 1 as x) to stdout")
    ProgrammingError: can't execute COPY TO: use the copy_to() method
instead

but:

    In [6]: cnn = psycopg2.connect("", async=True)
    In [7]: cnn.poll()
    Out[7]: 2
    In [8]: cnn.poll()
    Out[8]: 1
    In [9]: cnn.poll()
    Out[9]: 0

    In [12]: cur.execute("copy (select 1 as x) to stdout")
    In [14]: cnn.poll()
    ^C -- hung

So yes, I'll take a look if there is a way to detect we get in this state.
But I'm not sure it's possible, because we might not have received a result
yet that tells we are in a state not allowed (in a COPY).

Observation: maybe EnterpriseDB has resources enough to help us debugging
and fixing this issue, or maybe even implement async copy?

-- Daniele

--0000000000001cda680577649623
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 Thu, Oct 4, 2018 at 6:35 AM Akshay =
Joshi &lt;<a href=3D"mailto:akshay.joshi@enterprisedb.com">akshay.joshi@ent=
erprisedb.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hello=C2=A0=
<div><br></div><div>As per documentation <a href=3D"http://initd.org/psycop=
g/docs/advanced.html" target=3D"_blank">http://<span id=3D"gmail-m_-2043510=
383649116845:2c6.25">initd</span>.org/<span id=3D"gmail-m_-2043510383649116=
845:2c6.26">psycopg</span>/docs/advanced.html</a>=C2=A0Copy commands are no=
t supported in asynchronous mode, but this will be probably implemented in =
a future release. In pgAdmin4 (open source) we have used <span id=3D"gmail-=
m_-2043510383649116845:2c6.27">async</span> connection to run the <span id=
=3D"gmail-m_-2043510383649116845:2c6.28">SQL</span> query on <span id=3D"gm=
ail-m_-2043510383649116845:2c6.29">PostgreSQL</span> database and uses poll=
() function to poll the results. When user runs any <b>COPY </b>command app=
lication hangs at poll() function.=C2=A0</div></div></div></div></blockquot=
e><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div d=
ir=3D"ltr"><div dir=3D"ltr"><div><br></div><div>If copy command not support=
ed then at least it should not hang the application. poll() function should=
 return the proper error message.=C2=A0</div><div><br></div><div>Can you pl=
ease provide that fix until support for copy command won&#39;t come.</div><=
/div></div></div></blockquote><div>=C2=A0<br></div><div>Uhm, I see:</div><d=
iv><br></div><div><div>=C2=A0 =C2=A0 In [2]: cnn =3D psycopg2.connect(&quot=
;&quot;)<br></div><div>=C2=A0 =C2=A0 In [3]: cur =3D cnn.cursor()<br></div>=
<div>=C2=A0 =C2=A0 In [4]: cur.execute(&quot;copy (select 1 as x) to stdout=
&quot;)<br></div><div>=C2=A0 =C2=A0 ---------------------------------------=
------------------------------------</div><div>=C2=A0 =C2=A0 ProgrammingErr=
or=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Traceback (most recent call last)</div><div>=C2=A0 =C2=A0=
 &lt;ipython-input-4-62e3af3d121e&gt; in &lt;module&gt;()</div><div>=C2=A0 =
=C2=A0 ----&gt; 1 cur.execute(&quot;copy (select 1 as x) to stdout&quot;)</=
div><div>=C2=A0 =C2=A0 ProgrammingError: can&#39;t execute COPY TO: use the=
 copy_to() method instead</div><div><br></div></div><div>but:</div><div><br=
></div><div><div>=C2=A0 =C2=A0 In [6]: cnn =3D psycopg2.connect(&quot;&quot=
;, async=3DTrue)<br></div><div>=C2=A0 =C2=A0 In [7]: cnn.poll()<br></div><d=
iv>=C2=A0 =C2=A0 Out[7]: 2</div><div>=C2=A0 =C2=A0 In [8]: cnn.poll()</div>=
<div>=C2=A0 =C2=A0 Out[8]: 1</div><div>=C2=A0 =C2=A0 In [9]: cnn.poll()</di=
v><div>=C2=A0 =C2=A0 Out[9]: 0</div><div><br></div><div><div>=C2=A0 =C2=A0 =
In [12]: cur.execute(&quot;copy (select 1 as x) to stdout&quot;)</div><div>=
=C2=A0 =C2=A0 In [14]: cnn.poll()<br></div></div></div><div>=C2=A0 =C2=A0 ^=
C -- hung</div><div><br></div><div>So yes, I&#39;ll take a look if there is=
 a way to detect we get in this state. But I&#39;m not sure it&#39;s possib=
le, because we might not have received a result yet that tells we are in a =
state not allowed (in a COPY).</div><div><br></div><div>Observation: maybe =
EnterpriseDB has resources enough to help us debugging and fixing this issu=
e, or maybe even implement async copy?</div><div><br></div><div>-- Daniele<=
/div><div><br></div></div></div></div></div></div>

--0000000000001cda680577649623--