Re: PQsendQuery+PQgetResult+PQsetSingleRowMode limitations and support - Mailing list pgsql-hackers

On Mon, 11 Apr 2016 15:55:53 +0200
"Shulgin, Oleksandr" <oleksandr.shulgin@zalando.de> wrote:

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

Well, the code does work.  (Mostly, see below.)

Should I submit a regression test or something to ensure
that this usage is officially supported?  (A grep for
PQsetSingleRowMode in src/test/ finds no hits.)
Can I assume because it's documented it'll continue to work?
Where do I go from here?

> 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().

You're quite right.  All but the first PQsetSingleRowMode()
calls fail.

This seems unfortunate.   What if I submit several SQL statements
with one PQsendQuery() call and I only want some of the statements
executed in single row mode?  I'm not sure what the use case
would be but it seems sad that PQsetSingleRowMode() is per
libpq call and not per sql statement.  It seems a little late
to change the API now.  (On the other hand, fewer calls = less
overhead, especially on the network.  So maybe it's just as well
and any deficiencies are best left for future work.)


For the record, here is where I got confused:

I find the docs unclear.  (I've plans to send in a patch, but
I think I'll wait until after finishing as a reviewer for
somebody else's patch.  That is in process now.)

The docs say:

"To enter single-row mode, call PQsetSingleRowMode immediately after a
successful call of PQsendQuery (or a sibling function). This mode
selection is effective only for the currently executing query."
(http://www.postgresql.org/docs/devel/static/libpq-single-row-mode.html)

Now, if the mode selection is effective only for the currently
executing query then if you call PQSetSingleRowMode() only
once after PQsendQuery() then single row mode will only be on
for the first query, when multiple queries are supplied in
the string passed to PQsendQuery().  The other queries won't
be executed in single row mode.

When the docs here say "query" what they really mean is "set of
statements submitted in a single libpq call".


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

<snip>

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

That is very interesting.  Thanks.

Regards,

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."                -- Robert A. Heinlein