Re: Protocol Question
Thomas Heller <[email protected]> Thu, 14 Aug 2014 10:00:27 +0200
| Newsgroups | gmane.comp.db.postgresql.interfaces |
|---|---|
| Message-ID | <CAGTxmOug+0mheOYfxiZX65Vno3E641EBwubRCmhezBD7LKnQbg@mail.gmail.com> |
--089e014932c035703e0500924cfd Content-Type: text/plain; charset=UTF-8 Yeah, I went with that and let the server send me everything. If more control is required, a cursor can be used. Thanks for the feedback. Onto implementing the Types. I assume the only place the binary encoding is documented is the source? Can't find anything anywhere else. Regards, /thomas On Thu, Aug 14, 2014 at 6:44 AM, Alistair Bayley <[email protected]> wrote: > 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 >>> >> >> > --089e014932c035703e0500924cfd Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Yeah, I went with that and let the server send me everythi= ng. If more control is required, a cursor can be used.<div><br><div>Thanks = for the feedback.</div></div><div><br></div><div>Onto implementing the Type= s. I assume the only place the binary encoding is documented is the source?= Can't find anything anywhere else.</div> <div><br></div><div>Regards,</div><div>/thomas</div><div><br></div></div><d= iv class=3D"gmail_extra"><br><br><div class=3D"gmail_quote">On Thu, Aug 14,= 2014 at 6:44 AM, Alistair Bayley <span dir=3D"ltr"><<a href=3D"mailto:a= [email protected]" target=3D"_blank">[email protected]</a>></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 dir=3D"ltr">Why wouldn't TCP be suf= ficient to throttle the data rate from the server? If the client stops/slow= s consuming bytes from its socket, TCP will make the server slow its sendin= g.</div> <div class=3D"HOEnZb"><div class=3D"h5"><div class=3D"gmail_extra"> <br><br><div class=3D"gmail_quote">On 13 August 2014 12:09, Thomas Heller <= span dir=3D"ltr"><<a href=3D"mailto:[email protected]" target=3D"_blank">= [email protected]</a>></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'm not sure how the backend handles large results sets a= nd clients that can'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't expect into these kinds of situations very ofte= n, just want the client to "do the right thing". 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 "standard" 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><div><div class=3D"gmail_extra"><br><br><div class=3D"gmai= l_quote">On Wed, Aug 13, 2014 at 12:08 AM, Tom Lane <span dir=3D"ltr"><<= a href=3D"mailto:[email protected]" target=3D"_blank">[email protected]</a>= ></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 <<a href=3D"mailto:inf= [email protected]" target=3D"_blank">[email protected]</a>> writes:<br> > In an Extended Query Lifecycle, in order to prepare a query I send the= <br> > Commands<br> <br> > Parse('P') / Describe('D') / Sync('S')<br> <br> > read 1/t/T/Z then to execute<br> <br> > Bind('B') / Execute('E') / Flush('H')<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'= ;s<br> an error during the Execute, the backend will discard subsequent messages<b= r> until it sees Sync).<br> <div><br> > If I skip the Flush after Execute I receive no data, if I Execute and = Sync<br> > I receive the the Limit of rows and a ReadyForQuery('Z').<br> <br> </div>That's probably because you'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'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> </div></div></blockquote></div><br></div> --089e014932c035703e0500924cfd--