On Fri, Apr 1, 2016 at 7:53 PM, Karl O. Pinc <
kop@meme.com> wrote:
>
> On Fri, 1 Apr 2016 05:57:33 +0200
> "Shulgin, Oleksandr" <
oleksandr.shulgin@zalando.de> wrote:
>
> > On Apr 1, 2016 02:57, "Karl O. Pinc" <
kop@meme.com> wrote:
> > >
> > > I assume there are no questions about supporting a
> > > similar functionality only without PQsetSingleRowMode,
> > > as follows:
> >
> > Sorry, but I don't see what is your actual question here?
>
> The question is whether or not the functionality of the first
> script is supported. I ask since Bruce was surprised to see
> this working and questioned whether PG was intended to behave
> this way.
Well, according to the docs it should work, though I don't recall if I have really tried that at least once. Not sure about the part where you call PQsetSingleRowMode() again after seeing PGRES_TUPLES_OK: doesn't look to me like you need or want to do that. You should only call it immediately after PQsendQuery().
> > Both code examples are going to compile and work, AFAICS. The
> > difference is that the latter will try to fetch the whole result set
> > into client's memory before returning you a PGresult.
>
> Thanks for the clarification. For some reason I recently
> got it into my head that the libpq buffering was on the server side,
> which is really strange since I long ago determined it was
> client side.
There are also a number of cases where the caching will happen on the server side: using ORDER BY without an index available to fetch the records in the required order is the most obvious one.
Less obvious is when you have a set-returning-function and use it like "SELECT * FROM srffunc()", this will cause the intermediate result to be materialized in a tuple store on the server side before it will be streamed to the client. On the other hand, if you use the same function as "SELECT srffunc()" you are going to get the same results streamed to the client. I've seen this a number of times already and I doesn't look like a fundamental limitation of the execution engine to me, rather an implementation deficiency.
Another plausible approach to get the results row by row is invoking COPY protocol with the query: "COPY (SELECT ...) TO STDOUT". This way you lose the type information of course, but it still might be appropriate for some use cases.
--
Regards,
Alex