Re: Protocol Question

--089e0158aee8b455200500779aa3
Content-Type: text/plain; charset=UTF-8

I tried sending P/D/S...B/E/S...E/S... but the second Execute fails because
the portal no longer exists.

So I need a BEGIN/COMMIT for a SELECT in order to read in multiple steps?

I can process the data on-the-fly, my motivation for using Execute with a
limit was to introduce some kind of control on how fast things travel over
the wire. I'm not sure how the backend handles large results sets and
clients that can't keep up. Say I have a SELECT that returns 100k rows,
after 60k rows the clients into some sort of resource limit. Since the
backend just dumps everything on the wire I now have to continue reading
and discarding the remaining rows? I doubt I can Close the statement early
since the buffers on the backend should be full with more rows to send to
the frontend. I don't expect into these kinds of situations very often,
just want the client to "do the right thing". If I run into some sort of
hard limit I can just terminate the connection since I probably have other
things to worry about than reusing a connection.


Anyways, I might be overthinking things. The "standard" flow of
P/D/S.../B/E/S is probably the way to go then.

Regards,
/thomas

PS: The whole thing is available at https://github.com/thheller/shadow-pgsql
but be warned its very alpha.





On Wed, Aug 13, 2014 at 12:08 AM, Tom Lane <[email protected]> wrote:

> Thomas Heller <[email protected]> writes:
> > In an Extended Query Lifecycle, in order to prepare a query I send the
> > Commands
>
> > Parse('P') / Describe('D') / Sync('S')
>
> > read 1/t/T/Z then to execute
>
> > Bind('B') / Execute('E') / Flush('H')
>
> This is not a good idea.  You *need* to use Sync to terminate a command
> sequence in order to be sure of proper error recovery (because if there's
> an error during the Execute, the backend will discard subsequent messages
> until it sees Sync).
>
> > If I skip the Flush after Execute I receive no data, if I Execute and
> Sync
> > I receive the the Limit of rows and a ReadyForQuery('Z').
>
> That's probably because you're not wrapping this in a transaction so the
> Sync implicitly does a commit, discarding the open portal.  If you want
> to read from a portal in multiple steps then you should issue a BEGIN
> first and a COMMIT (or ROLLBACK) after you're done.  However, have you
> considered just processing the data on-the-fly instead of using a limit?
>
>                         regards, tom lane
>

--089e0158aee8b455200500779aa3
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I tried sending P/D/S...B/E/S...E/S... but the second Exec=
ute fails because the portal no longer exists.<div><br></div><div>So I need=
 a BEGIN/COMMIT for a SELECT in order to read in multiple steps?<div><br></=
div>
<div>I can process the data on-the-fly, my motivation for using Execute wit=
h a limit was to introduce some kind of control on how fast things travel o=
ver the wire. I&#39;m not sure how the backend handles large results sets a=
nd clients that can&#39;t keep up. Say I have a SELECT that returns 100k ro=
ws, after 60k rows the clients into some sort of resource limit. Since the =
backend just dumps everything on the wire I now have to continue reading an=
d discarding the remaining rows?=C2=A0I doubt I can Close the statement ear=
ly since the buffers on the backend should be full with more rows to send t=
o the frontend. I don&#39;t expect into these kinds of situations very ofte=
n, just want the client to &quot;do the right thing&quot;. If I run into so=
me sort of hard limit I can just terminate the connection since I probably =
have other things to worry about than reusing a connection.</div>
<div><br></div><div><br></div><div>Anyways, I might be overthinking things.=
 The &quot;standard&quot; flow of P/D/S.../B/E/S is probably the way to go =
then.</div><div><br></div><div>Regards,</div><div>/thomas</div><div><br>
</div><div>PS: The whole thing is available at=C2=A0<a href=3D"https://gith=
ub.com/thheller/shadow-pgsql">https://github.com/thheller/shadow-pgsql</a> =
but be warned its very alpha.</div><div><br></div><div><br></div><div><br><=
/div>
</div></div><div class=3D"gmail_extra"><br><br><div class=3D"gmail_quote">O=
n Wed, Aug 13, 2014 at 12:08 AM, Tom Lane <span dir=3D"ltr">&lt;<a href=3D"=
mailto:[email protected]" target=3D"_blank">[email protected]</a>&gt;</span=
> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex"><div class=3D"">Thomas Heller &lt;<a href=3D=
"mailto:[email protected]">[email protected]</a>&gt; writes:<br>
&gt; In an Extended Query Lifecycle, in order to prepare a query I send the=
<br>
&gt; Commands<br>
<br>
&gt; Parse(&#39;P&#39;) / Describe(&#39;D&#39;) / Sync(&#39;S&#39;)<br>
<br>
&gt; read 1/t/T/Z then to execute<br>
<br>
&gt; Bind(&#39;B&#39;) / Execute(&#39;E&#39;) / Flush(&#39;H&#39;)<br>
<br>
</div>This is not a good idea. =C2=A0You *need* to use Sync to terminate a =
command<br>
sequence in order to be sure of proper error recovery (because if there&#39=
;s<br>
an error during the Execute, the backend will discard subsequent messages<b=
r>
until it sees Sync).<br>
<div class=3D""><br>
&gt; If I skip the Flush after Execute I receive no data, if I Execute and =
Sync<br>
&gt; I receive the the Limit of rows and a ReadyForQuery(&#39;Z&#39;).<br>
<br>
</div>That&#39;s probably because you&#39;re not wrapping this in a transac=
tion so the<br>
Sync implicitly does a commit, discarding the open portal. =C2=A0If you wan=
t<br>
to read from a portal in multiple steps then you should issue a BEGIN<br>
first and a COMMIT (or ROLLBACK) after you&#39;re done. =C2=A0However, have=
 you<br>
considered just processing the data on-the-fly instead of using a limit?<br=
>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div><br></div>

--089e0158aee8b455200500779aa3--