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 results of 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 statements to 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 get the 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 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 statements to server failed: %s\n",
PQerrorMessage(conn));
exit_nicely(conn);
}
/* Loop through the results of our statements. */
while (res = PQgetResult(conn))
{
/* Produce a "nice" header" */
printf("\n%s\nResults of 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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?
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.
--
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Apr 11, 2016 at 7:15 PM, Karl O. Pinc <kop@meme.com> wrote:
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?
Pretty much.
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.
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 is per query, where query == "argument to PQsendQuery()" :-)
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.
--
Regards,
Alex
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers