Re: Protocol Question

--001a11c1e550aa749305008f90ab
Content-Type: text/plain; charset=UTF-8

Why wouldn't TCP be sufficient to throttle the data rate from the server?
If the client stops/slows consuming bytes from its socket, TCP will make
the server slow its sending.


On 13 August 2014 12:09, Thomas Heller <[email protected]> wrote:

> 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
>>
>
>

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

<div dir=3D"ltr">Why wouldn&#39;t TCP be sufficient to throttle the data ra=
te from the server? If the client stops/slows consuming bytes from its sock=
et, TCP will make the server slow its sending.</div><div class=3D"gmail_ext=
ra">
<br><br><div class=3D"gmail_quote">On 13 August 2014 12:09, Thomas Heller <=
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:1px #ccc solid;padding-left:1ex">
<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" target=3D"_blank">https://github.com/thheller=
/shadow-pgsql</a> but be warned its very alpha.</div><div><br></div><div><b=
r></div>
<div><br></div>
</div></div><div class=3D"HOEnZb"><div class=3D"h5"><div class=3D"gmail_ext=
ra"><br><br><div class=3D"gmail_quote">On Wed, Aug 13, 2014 at 12:08 AM, To=
m 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>Thomas Heller &lt;<a href=3D"mailto:inf=
[email protected]" target=3D"_blank">[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><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>
</div></div></blockquote></div><br></div>

--001a11c1e550aa749305008f90ab--