Re: PQsendQuery+PQgetResult+PQsetSingleRowMode limitations and support

On Fri, Apr 1, 2016 at 7:53 PM, Karl O. Pinc <kop(at)meme(dot)com> wrote:
>
> On Fri, 1 Apr 2016 05:57:33 +0200
> "Shulgin, Oleksandr" <oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
>
> > On Apr 1, 2016 02:57, "Karl O. Pinc" <kop(at)meme(dot)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