Extend libpq to support mixed text and binary results
The Frontend/Backend protocol's "bind" message allows to
specify mixed text and binary result formats, but there
is not support in the C API for that.
I'd like to add support for that to libpq for these reasons:
- I think that every feature of the line protocol should
be exposed in the C API.
- This is quite useful if you select bytea and other
fields in one query (less overhead and data traffic
for the bytea).
- I've seen at least one case on the list where
somebody would have liked to have that feature:
http://archives.postgresql.org/pgsql-general/2012-08/msg00761.php
Attached is a WIP patch that adds four new functions
to libpq:
PQexecParams2
PQexecPrepared2
PQsendQueryParams2
PQsendQueryPrepared2
If that idea meets with approval, I'd add documentation and
submit it to the next commitfest.
I am aware that these names are rather ugly and welcome
better ideas.
Maybe it is not necessary to add four new functions.
You could get the full functionality just with
PQsendQueryPrepared2, the rest is just for ease of use.
Yours,
Laurenz Albe
Attachments:
result-formats.patchapplication/octet-stream; name=result-formats.patchDownload
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
new file mode 100644
index 56d0bb8..d1b3136
*** a/src/interfaces/libpq/exports.txt
--- b/src/interfaces/libpq/exports.txt
*************** PQsetSingleRowMode 161
*** 164,166 ****
--- 164,170 ----
lo_lseek64 162
lo_tell64 163
lo_truncate64 164
+ PQexecParams2 165
+ PQexecPrepared2 166
+ PQsendQueryParams2 167
+ PQsendQueryPrepared2 168
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
new file mode 100644
index 77124ef..51f7c9c
*** a/src/interfaces/libpq/fe-exec.c
--- b/src/interfaces/libpq/fe-exec.c
*************** static int PQsendQueryGuts(PGconn *conn,
*** 61,67 ****
const char *const * paramValues,
const int *paramLengths,
const int *paramFormats,
! int resultFormat);
static void parseInput(PGconn *conn);
static bool PQexecStart(PGconn *conn);
static PGresult *PQexecFinish(PGconn *conn);
--- 61,68 ----
const char *const * paramValues,
const int *paramLengths,
const int *paramFormats,
! int nResults,
! const int *resultFormats);
static void parseInput(PGconn *conn);
static bool PQexecStart(PGconn *conn);
static PGresult *PQexecFinish(PGconn *conn);
*************** PQsendQueryParams(PGconn *conn,
*** 1168,1173 ****
--- 1169,1201 ----
const int *paramFormats,
int resultFormat)
{
+ return PQsendQueryParams2(conn,
+ command,
+ nParams,
+ paramTypes,
+ paramValues,
+ paramLengths,
+ paramFormats,
+ 1,
+ &resultFormat);
+ }
+
+ /*
+ * PQsendQueryParams2
+ * Like PQsendQueryParams, but allows to pass an array
+ * of result formats
+ */
+ int
+ PQsendQueryParams2(PGconn *conn,
+ const char *command,
+ int nParams,
+ const Oid *paramTypes,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int nResults,
+ const int *resultFormats)
+ {
if (!PQsendQueryStart(conn))
return 0;
*************** PQsendQueryParams(PGconn *conn,
*** 1184,1189 ****
--- 1212,1223 ----
libpq_gettext("number of parameters must be between 0 and 65535\n"));
return 0;
}
+ if (nResults < 0 || nResults > 65535)
+ {
+ printfPQExpBuffer(&conn->errorMessage,
+ libpq_gettext("number of result formats must be between 0 and 65535\n"));
+ return 0;
+ }
return PQsendQueryGuts(conn,
command,
*************** PQsendQueryParams(PGconn *conn,
*** 1193,1199 ****
paramValues,
paramLengths,
paramFormats,
! resultFormat);
}
/*
--- 1227,1234 ----
paramValues,
paramLengths,
paramFormats,
! nResults,
! resultFormats);
}
/*
*************** PQsendQueryPrepared(PGconn *conn,
*** 1309,1314 ****
--- 1344,1374 ----
const int *paramFormats,
int resultFormat)
{
+ return PQsendQueryPrepared2(conn,
+ stmtName,
+ nParams,
+ paramValues,
+ paramLengths,
+ paramFormats,
+ 1,
+ &resultFormat);
+ }
+
+ /*
+ * PQsendQueryPrepared2
+ * Like PQsendQueryPrepared, but allows to pass an array
+ * of result formats
+ */
+ int
+ PQsendQueryPrepared2(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int nResults,
+ const int *resultFormats)
+ {
if (!PQsendQueryStart(conn))
return 0;
*************** PQsendQueryPrepared(PGconn *conn,
*** 1325,1330 ****
--- 1385,1396 ----
libpq_gettext("number of parameters must be between 0 and 65535\n"));
return 0;
}
+ if (nResults < 0 || nResults > 65535)
+ {
+ printfPQExpBuffer(&conn->errorMessage,
+ libpq_gettext("number of result formats must be between 0 and 65535\n"));
+ return 0;
+ }
return PQsendQueryGuts(conn,
NULL, /* no command to parse */
*************** PQsendQueryPrepared(PGconn *conn,
*** 1334,1340 ****
paramValues,
paramLengths,
paramFormats,
! resultFormat);
}
/*
--- 1400,1407 ----
paramValues,
paramLengths,
paramFormats,
! nResults,
! resultFormats);
}
/*
*************** PQsendQueryGuts(PGconn *conn,
*** 1391,1397 ****
const char *const * paramValues,
const int *paramLengths,
const int *paramFormats,
! int resultFormat)
{
int i;
--- 1458,1465 ----
const char *const * paramValues,
const int *paramLengths,
const int *paramFormats,
! int nResults,
! const int *resultFormats)
{
int i;
*************** PQsendQueryGuts(PGconn *conn,
*** 1495,1503 ****
goto sendFailed;
}
}
! if (pqPutInt(1, 2, conn) < 0 ||
! pqPutInt(resultFormat, 2, conn))
goto sendFailed;
if (pqPutMsgEnd(conn) < 0)
goto sendFailed;
--- 1563,1576 ----
goto sendFailed;
}
}
!
! /* send result formats */
! if (pqPutInt(nResults, 2, conn) < 0)
goto sendFailed;
+ for (i = 0; i < nResults; ++i)
+ if (pqPutInt(resultFormats[i], 2, conn))
+ goto sendFailed;
+
if (pqPutMsgEnd(conn) < 0)
goto sendFailed;
*************** PQexecParams(PGconn *conn,
*** 1822,1832 ****
const int *paramFormats,
int resultFormat)
{
if (!PQexecStart(conn))
return NULL;
! if (!PQsendQueryParams(conn, command,
nParams, paramTypes, paramValues, paramLengths,
! paramFormats, resultFormat))
return NULL;
return PQexecFinish(conn);
}
--- 1895,1932 ----
const int *paramFormats,
int resultFormat)
{
+ return PQexecParams2(conn,
+ command,
+ nParams,
+ paramTypes,
+ paramValues,
+ paramLengths,
+ paramFormats,
+ 1,
+ &resultFormat);
+ }
+
+ /*
+ * PQexecParams2
+ * Like PQexecParams, but allows to pass an array
+ * of result formats
+ */
+ PGresult *
+ PQexecParams2(PGconn *conn,
+ const char *command,
+ int nParams,
+ const Oid *paramTypes,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int nResults,
+ const int *resultFormats)
+ {
if (!PQexecStart(conn))
return NULL;
! if (!PQsendQueryParams2(conn, command,
nParams, paramTypes, paramValues, paramLengths,
! paramFormats, nResults, resultFormats))
return NULL;
return PQexecFinish(conn);
}
*************** PQexecPrepared(PGconn *conn,
*** 1868,1878 ****
const int *paramFormats,
int resultFormat)
{
if (!PQexecStart(conn))
return NULL;
! if (!PQsendQueryPrepared(conn, stmtName,
nParams, paramValues, paramLengths,
! paramFormats, resultFormat))
return NULL;
return PQexecFinish(conn);
}
--- 1968,2003 ----
const int *paramFormats,
int resultFormat)
{
+ return PQexecPrepared2(conn,
+ stmtName,
+ nParams,
+ paramValues,
+ paramLengths,
+ paramFormats,
+ 1,
+ &resultFormat);
+ }
+
+ /*
+ * PQexecPrepared2
+ * Like PQexecPrepared, but allows to pass an array
+ * of result formats
+ */
+ PGresult *
+ PQexecPrepared2(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int nResults,
+ const int *resultFormats)
+ {
if (!PQexecStart(conn))
return NULL;
! if (!PQsendQueryPrepared2(conn, stmtName,
nParams, paramValues, paramLengths,
! paramFormats, nResults, resultFormats))
return NULL;
return PQexecFinish(conn);
}
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
new file mode 100644
index 0b8d9a6..8df0051
*** a/src/interfaces/libpq/libpq-fe.h
--- b/src/interfaces/libpq/libpq-fe.h
*************** extern PGresult *PQexecParams(PGconn *co
*** 358,363 ****
--- 358,372 ----
const int *paramLengths,
const int *paramFormats,
int resultFormat);
+ extern PGresult *PQexecParams2(PGconn *conn,
+ const char *command,
+ int nParams,
+ const Oid *paramTypes,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int nResults,
+ const int *resultFormats);
extern PGresult *PQprepare(PGconn *conn, const char *stmtName,
const char *query, int nParams,
const Oid *paramTypes);
*************** extern PGresult *PQexecPrepared(PGconn *
*** 368,373 ****
--- 377,390 ----
const int *paramLengths,
const int *paramFormats,
int resultFormat);
+ extern PGresult *PQexecPrepared2(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int nResults,
+ const int *resultFormats);
/* Interface for multiple-result or asynchronous queries */
extern int PQsendQuery(PGconn *conn, const char *query);
*************** extern int PQsendQueryParams(PGconn *con
*** 379,384 ****
--- 396,410 ----
const int *paramLengths,
const int *paramFormats,
int resultFormat);
+ extern int PQsendQueryParams2(PGconn *conn,
+ const char *command,
+ int nParams,
+ const Oid *paramTypes,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int nResults,
+ const int *resultFormats);
extern int PQsendPrepare(PGconn *conn, const char *stmtName,
const char *query, int nParams,
const Oid *paramTypes);
*************** extern int PQsendQueryPrepared(PGconn *c
*** 389,394 ****
--- 415,428 ----
const int *paramLengths,
const int *paramFormats,
int resultFormat);
+ extern int PQsendQueryPrepared2(PGconn *conn,
+ const char *stmtName,
+ int nParams,
+ const char *const * paramValues,
+ const int *paramLengths,
+ const int *paramFormats,
+ int nResults,
+ const int *resultFormats);
extern int PQsetSingleRowMode(PGconn *conn);
extern PGresult *PQgetResult(PGconn *conn);
On 7 November 2012 13:08, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
- I think that every feature of the line protocol should
be exposed in the C API.
Exposing every possible bug in ther underlying protocol isn't the best
plan though, especially when doing so complicates the API just to
support this.
Are those the only reasons you want this? They seem pretty thin set of
requirements to me that will lead to dead and hard to test code.
Or perhaps associated measurements are needed to show clear benefit?
i.e. please convince somre more.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Nov 7, 2012 at 7:08 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
The Frontend/Backend protocol's "bind" message allows to
specify mixed text and binary result formats, but there
is not support in the C API for that.I'd like to add support for that to libpq for these reasons:
- I think that every feature of the line protocol should
be exposed in the C API.- This is quite useful if you select bytea and other
fields in one query (less overhead and data traffic
for the bytea).- I've seen at least one case on the list where
somebody would have liked to have that feature:
http://archives.postgresql.org/pgsql-general/2012-08/msg00761.phpAttached is a WIP patch that adds four new functions
to libpq:
PQexecParams2
PQexecPrepared2
PQsendQueryParams2
PQsendQueryPrepared2If that idea meets with approval, I'd add documentation and
submit it to the next commitfest.I am aware that these names are rather ugly and welcome
better ideas.Maybe it is not necessary to add four new functions.
You could get the full functionality just with
PQsendQueryPrepared2, the rest is just for ease of use.
How would this compare to libpqtypes which I might in-humbly suggest
is the gold standard in terms of dealing with libpq binary formats: it
pulls everything in binary and puts the complicated stuff in structs
so you don't have to parse.
merlin
Simon Riggs <simon@2ndQuadrant.com> writes:
On 7 November 2012 13:08, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
- I think that every feature of the line protocol should
be exposed in the C API.
My recollection is that there are several things in the V3 protocol that
were put there to satisfy JDBC, without any expectation that they'd
necessarily be useful to libpq. So I don't buy the above argument as-is;
it requires some clear use-cases for libpq.
Exposing every possible bug in ther underlying protocol isn't the best
plan though, especially when doing so complicates the API just to
support this.
The fact that you couldn't think of any function names more mnemonic
than "PQexecParams2" etc strongly suggests that this is mostly
complication :-(
The referenced thread from August doesn't strike me as sufficient
justification. In the first place, that's only one request, and in the
second place, that user was going through Perl DBI. I don't really
foresee enough changes being made to the DBI/DBD stack to be able to use
this facility effectively. The key point there (which is a deficiency
the JDBC folk have already complained about) is that this design only
helps you if you know *in advance of running the query* which result
columns are bytea. While that's not a serious problem for applications
running hard-coded queries, client-side libraries aren't going to know
that unless they incur extra network round-trips to ask the server to
Describe the query result.
So what I think would be considerably more useful than this is some sort
of session-level setting saying "please send results of such-and-such
data type(s) in binary/text automatically". Not sure we can have that
without a protocol break though.
regards, tom lane
Simon Riggs wrote:
- I think that every feature of the line protocol should
be exposed in the C API.Exposing every possible bug in ther underlying protocol isn't the best
plan though, especially when doing so complicates the API just to
support this.
Well, I wouldn't call this a bug, but I got enough good
points against the idea that I consider it dead.
Yours,
Laurenz Albe