Thread: PQsendQuery+PQgetResult+PQsetSingleRowMode limitations and support

Hi,

Bruce Momjian suggested I write and ask about using libpq
to submit multiple SQL statements to the backend, and
then get results for each of the submitted statements,
row-by-row without server-side caching of the results.

Bruce wrote:
> I think this would be good
> to post to hackers to get a general discussion of the limitations of
> this approach and allow to communicate if this is something we want
> those interfaces to support.  My guess is this never used to work, but
> now it does.

As I read the documentation this functionality is supported.
(Although I do believe that the wording could be more clear.)

http://www.postgresql.org/docs/9.5/static/libpq-single-row-mode.html

And (I suppose):
http://www.postgresql.org/docs/9.5/static/libpq-async.html

FWIW, I would use such functionality to support an
interactive interface for users wishing to write SQL
and query the db directly.  Like psql does, only not
from the command line.

The following example program exhibits this functionality.
It runs on Debian Jesse (8.3) postgresql 9.4 (from the
Debian repos).

----------------------------<snip>------------------
/** byrow.c**      Test that libpq, the PostgreSQL frontend library, can be given*      multiple statements and get the
resultsof executing each,*      row-by-row without server side buffering.*/
 
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int         stmtcnt = 0;

static void
exit_nicely(PGconn *conn)
{ PQfinish(conn); exit(1);
}

static void
setting_failed(PGconn *conn)
{ fprintf(stderr, "Unable to enter single row mode: %s\n",         PQerrorMessage(conn)); exit_nicely(conn);
}

int
main(int argc, char **argv)
{ const char *conninfo; PGconn     *conn; PGresult   *res; int         first = 1; int         nFields; int         i,
j;
 /* Construct some statements to execute. */ char *stmts = "select * from pg_database;\n"               "select * from
pg_roles;\n"              "select count(*) from pg_tables;\n";
 
 /*  * If the user supplies a parameter on the command line, use it as  * the conninfo string; otherwise default to
setting * dbname=postgres and using environment variables or defaults for  * all other connection parameters.  */ if
(argc> 1)   conninfo = argv[1]; else   conninfo = "dbname = postgres";
 
 /* Make a connection to the database */ conn = PQconnectdb(conninfo);
 /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK)   {
fprintf(stderr,"Connection to database failed: %s",             PQerrorMessage(conn));     exit_nicely(conn);   }
 
 /* Send our statements off to the server. */ if (!PQsendQuery(conn, stmts))   {     fprintf(stderr, "Sending
statementsto server failed: %s\n",             PQerrorMessage(conn));     exit_nicely(conn);   }  /* We want results
row-by-row.*/ if (!PQsetSingleRowMode(conn))   {     setting_failed(conn);   }
 
 /* Loop through the results of our statements. */ while (res = PQgetResult(conn))   {
     switch (PQresultStatus(res))       {       case PGRES_TUPLES_OK:  /* No more rows from current query. */         {
         /* We want the next statement's results row-by-row also. */           if (!PQsetSingleRowMode(conn))
 {               PQclear(res);               setting_failed(conn);             }           first = 1;           break;
      }
 
       case PGRES_SINGLE_TUPLE:         {           if (first)             {               /* Produce a "nice" header"
*/              printf("\n%s\nResults of statement number %d:\n\n",
"-----------------------------"                     "-----------------------------",                      stmtcnt++);
 
               /* print out the attribute names */               nFields = PQnfields(res);               for (i = 0; i
<nFields; i++)                 printf("%-15s", PQfname(res, i));               printf("\n\n");
   first = 0;             }
 
           /* print out the row */           for (j = 0; j < nFields; j++)             printf("%-15s", PQgetvalue(res,
0,j));           printf("\n");
 
           break;         }       default:         /* Always call PQgetResult until it returns null, even on          *
error.*/         {           fprintf(stderr,                   "Query execution failed: %s", PQerrorMessage(conn));
   }       }
 
     PQclear(res);   }
 /* close the connection to the database and cleanup */ PQfinish(conn);
 return 0;
}
----------------------------<snip>------------------

(You may recognize much of the code above because it was cribbed
from the libpq docs example #1.)


I assume there are no questions about supporting a
similar functionality only without PQsetSingleRowMode,
as follows:
----------------------------<snip>------------------
/** testmultistmt.c**      Test that libpq, the PostgreSQL frontend library, can be given*      multiple statements and
getthe results of executing each.*      (Not just results from the last statement executed.)*/
 
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

static void
exit_nicely(PGconn *conn)
{ PQfinish(conn); exit(1);
}

int
main(int argc, char **argv)
{ const char *conninfo; PGconn     *conn; PGresult   *res; int         stmtcnt = 0; int         nFields; int         i,
 j;
 
 /* Construct some statements to execute. */ char *stmts = "select * from pg_database;\n"               "select * from
pg_roles;\n"              "select count(*) from pg_tables;\n";
 
 /*  * If the user supplies a parameter on the command line, use it as  * the conninfo string; otherwise default to
settingdbname=postgres  * and using environment variables or defaults for all other  * connection parameters.  */ if
(argc> 1)   conninfo = argv[1]; else   conninfo = "dbname = postgres";
 
 /* Make a connection to the database */ conn = PQconnectdb(conninfo);
 /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK)   {
fprintf(stderr,"Connection to database failed: %s",             PQerrorMessage(conn));     exit_nicely(conn);   }
 
 /* Send our statements off to the server. */ if (!PQsendQuery(conn, stmts))   {     fprintf(stderr, "Sending
statementsto server failed: %s\n",             PQerrorMessage(conn));     exit_nicely(conn);   }  /* Loop through the
resultsof our statements. */ while (res = PQgetResult(conn))   {     /* Produce a "nice" header" */
printf("\n%s\nResultsof statement number %d:\n\n",            "------------------------------------------------------",
          stmtcnt++);
 
     if (PQresultStatus(res) == PGRES_TUPLES_OK)       {
         /* first, print out the attribute names */         nFields = PQnfields(res);         for (i = 0; i < nFields;
i++)          printf("%-15s", PQfname(res, i));         printf("\n\n");                  /* next, print out the rows */
       for (i = 0; i < PQntuples(res); i++)           {             for (j = 0; j < nFields; j++)
printf("%-15s",PQgetvalue(res, i, j));             printf("\n");           }
 
       }     else       /* Always call PQgetResult until it returns null, even on        * error. */       {
fprintf(stderr,                "Query execution failed: %s", PQerrorMessage(conn));       }
 
     PQclear(res);   }
 /* close the connection to the database and cleanup */ PQfinish(conn);
 return 0;
}
----------------------------<snip>------------------

Regards,

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

<p dir="ltr">On Apr 1, 2016 02:57, "Karl O. Pinc" <<a href="mailto:kop@meme.com">kop@meme.com</a>> wrote:<br />
><br/> > I assume there are no questions about supporting a<br /> > similar functionality only without
PQsetSingleRowMode,<br/> > as follows:<p dir="ltr">Sorry, but I don't see what is your actual question here?<p
dir="ltr">Bothcode examples are going to compile and work, AFAICS. The difference is that the latter will try to fetch
thewhole result set into client's memory before returning you a PGresult.<p dir="ltr">--<br /> Alex 

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.

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




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

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

On Mon, 11 Apr 2016 19:25:20 +0200
"Shulgin, Oleksandr" <oleksandr.shulgin@zalando.de> wrote:

> On Mon, Apr 11, 2016 at 7:15 PM, Karl O. Pinc <kop@meme.com> wrote:

> > > 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 would assume that if you know for which of the statements you want
> the single row mode, then you as well can submit them as separate
> PQsendQuery() calls.

Agreed.  Although I suppose it's possible to know which statements
you want in single row mode but not know how to parse those
statements out of some big string of queries.  Not my problem.  ;-)

> > When the docs here say "query" what they really mean is "set of
> > statements submitted in a single libpq call".  
> 
> Which are the same things more or less, I'm not sure that the extended
> explanation you suggest makes it less confusing.

I'll try to remember to cc-you if and when I send in a doc patch
so you can see if there's any improvement.

Regards,

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