During the discussion on dynamic result sets[0], it became apparent that
the current way binary results are requested in the extended query
protocol is too cumbersome for some practical uses, and keeping that
style around would also make the proposed protocol extensions very
complicated.
The premise here is that a client library has hard-coded knowledge on
how to deal with binary format for certain, but not all, data types.
(Most client libraries process everything in text, and some client
libraries process everything in binary. Neither of these extremes are
of concern here.) Such a client always has to request a result row
description (Describe statement) before sending a Bind message, in order
to be able to pick out the result columns in should request in binary.
The feedback was that this extra round trip is often not worth it in
terms of performance, and so it is not done and binary format is not
used when it could be.
The conceptual solution is to allow a client to register for a session
which types it wants to always get in binary, unless it says otherwise.
In the discussion in [0], I pondered a new protocol message for that,
but after further thought, a GUC setting would do just as well.
The attached patch implements this. For example, to get int2, int4,
int8 in binary by default, you could set
SET default_result_formats = '21=1,23=1,20=1';
This is a list of oid=format pairs.
I think this format satisfies the current requirements of the JDBC
driver. But the format could also be extended in the future to allow
type names to be listed or some other ways of identifying the types.
In order to be able to test this via libpq, I had to add a little hack.
Currently, PQexecParams() and similar functions can only pass exactly
one result format code, which per protocol is then applied to all result
columns. There is no support for sending zero result format codes to
make the session default apply. I enabled this by allowing -1 to be
passed as the format code. I'm not sure if we want to make this part of
the official API, but it would be useful to have something like this
somehow.
[0]:
https://www.postgresql.org/message-id/flat/6e747f98-835f-2e05-cde5-86ee444a7140%402ndquadrant.com
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From fa0e6968985cbe1f100746ea562b7f7712baf646 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 26 Oct 2020 09:10:43 +0100
Subject: [PATCH v1] Add default_result_formats setting
---
doc/src/sgml/config.sgml | 42 ++++++++++++++
doc/src/sgml/libpq.sgml | 3 +
doc/src/sgml/protocol.sgml | 2 +-
src/backend/tcop/pquery.c | 100 ++++++++++++++++++++++++++++++++-
src/backend/utils/misc/guc.c | 12 ++++
src/include/tcop/pquery.h | 5 ++
src/interfaces/libpq/fe-exec.c | 14 ++++-
7 files changed, 173 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f043433e31..6009e13899 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8530,6 +8530,48 @@ <title>Statement Behavior</title>
</listitem>
</varlistentry>
+ <varlistentry id="guc-default-result-formats"
xreflabel="default_result_formats">
+ <term><varname>default_result_formats</varname> (<type>string</type>)
+ <indexterm>
+ <primary><varname>default_result_formats</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ This parameter specifies the default result formats by data type for
+ rows returned in the extended query protocol when no result formats
+ are specified in the Bind message. It is intended to be used by
+ client libraries that prefer to handle certain data types in binary
+ format. The typical usage would be that the client library sets this
+ value when it starts a connection. (A client library that wants to
+ handle <emphasis>all</emphasis> types in binary doesn't need to use
+ this because it can just specify the format code for all types at once
+ in the protocol message.)
+ </para>
+ <para>
+ The value is a list of
+ <replaceable>typeoid</replaceable>=<replaceable>format</replaceable>,
+ separated by commas. <replaceable>typeoid</replaceable> is the OID of
+ a type, from the <structname>pg_type</structname> system catalog.
+ <replaceable>format</replaceable> is the format code, currently 0 for
+ text and 1 for binary. 0 is the default for all types, so only 1
+ needs to be specified explicitly. For example, if you want to
+ automatically get values of the types <type>int2</type>,
+ <type>int4</type>, and <type>int8</type> in binary while leaving the
+ rest in text, an appropriate setting would be
+ <literal>21=1,23=1,20=1</literal>.
+ </para>
+ <para>
+ Invalid format codes are an error. Nonexistent type OIDs are not
+ diagnosed. This setting applies only to result rows from the extended
+ query protocol, so it does not affect usage of
+ <application>psql</application> or <application>pg_dump</application>
+ for example. Also, it does not affect the format of query parameters.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
<sect2 id="runtime-config-client-format">
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index de60281fcb..6361d5a93b 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2711,6 +2711,9 @@ <title>Main Functions</title>
results in binary format. (There is not currently a provision
to obtain different result columns in different formats,
although that is possible in the underlying protocol.)
+
+ XXX Specify -1 to send no result formats, so that <xref
+ linkend="guc-default-result-formats"/> can take effect.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 3a64db6f55..e98f92ee78 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -3694,7 +3694,7 @@ <title>Message Formats</title>
(denoted <replaceable>R</replaceable> below).
This can be zero to indicate that there are no result columns
or that the result columns should all use the default format
- (text);
+ (usually text, but see <xref
linkend="guc-default-result-formats"/>);
or one, in which case the specified format code is applied
to all result columns (if any); or it can equal the actual
number of result columns of the query.
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 96ea74f118..1dff772d1e 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -26,6 +26,7 @@
#include "tcop/utility.h"
#include "utils/memutils.h"
#include "utils/snapmgr.h"
+#include "utils/varlena.h"
/*
@@ -597,6 +598,98 @@ PortalStart(Portal portal, ParamListInfo params,
portal->status = PORTAL_READY;
}
+char *default_result_formats;
+
+bool
+check_default_result_formats(char **newval, void **extra, GucSource source)
+{
+ char *rawstring;
+ List *elemlist;
+ ListCell *lc;
+
+ rawstring = pstrdup(*newval);
+ if (!SplitGUCList(rawstring, ',', &elemlist))
+ {
+ GUC_check_errdetail("List syntax is invalid.");
+ pfree(rawstring);
+ list_free(elemlist);
+ return false;
+ }
+
+ foreach(lc, elemlist)
+ {
+ char *str = lfirst(lc);
+ Oid oid;
+ short int format;
+
+ if (sscanf(str, "%u=%hd", &oid, &format) != 2)
+ {
+ GUC_check_errdetail("Invalid list entry: %s", str);
+ pfree(rawstring);
+ list_free(elemlist);
+ return false;
+ }
+
+ if (format !=0 && format != 1)
+ {
+ GUC_check_errdetail("Invalid format code: %d", format);
+ pfree(rawstring);
+ list_free(elemlist);
+ return false;
+ }
+ }
+
+ return true;
+}
+
+List *default_result_formats_binary = NIL;
+
+void
+assign_default_result_formats(const char *newval, void *extra)
+{
+ char *rawstring;
+ List *elemlist;
+ ListCell *lc;
+
+ rawstring = pstrdup(newval);
+ if (!SplitGUCList(rawstring, ',', &elemlist))
+ {
+ pfree(rawstring);
+ list_free(elemlist);
+ return;
+ }
+
+ foreach(lc, elemlist)
+ {
+ char *str = lfirst(lc);
+ Oid oid;
+ short int format;
+ MemoryContext oldcontext;
+
+ if (sscanf(str, "%u=%hd", &oid, &format) != 2)
+ {
+ pfree(rawstring);
+ list_free(elemlist);
+ return;
+ }
+
+ oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+ switch (format)
+ {
+ case 0:
+ default_result_formats_binary =
list_delete_oid(default_result_formats_binary, oid);
+ break;
+ case 1:
+ default_result_formats_binary =
list_append_unique_oid(default_result_formats_binary, oid);
+ break;
+ }
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ pfree(rawstring);
+ list_free(elemlist);
+}
+
/*
* PortalSetResultFormat
* Select the format codes for a portal's output.
@@ -642,7 +735,12 @@ PortalSetResultFormat(Portal portal, int nFormats, int16
*formats)
{
/* use default format for all columns */
for (i = 0; i < natts; i++)
- portal->formats[i] = 0;
+ {
+ if (list_member_oid(default_result_formats_binary,
TupleDescAttr(portal->tupDesc, i)->atttypid))
+ portal->formats[i] = 1;
+ else
+ portal->formats[i] = 0;
+ }
}
}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a62d64eaa4..8d4cd50055 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -83,6 +83,7 @@
#include "storage/predicate.h"
#include "storage/proc.h"
#include "storage/standby.h"
+#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tsearch/ts_cache.h"
#include "utils/acl.h"
@@ -4448,6 +4449,17 @@ static struct config_string ConfigureNamesString[] =
check_backtrace_functions, assign_backtrace_functions, NULL
},
+ {
+ {"default_result_formats", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Which format codes to use for types if
nothing else is specified in the protocol."),
+ NULL,
+ GUC_LIST_INPUT | GUC_NOT_IN_SAMPLE |
GUC_DISALLOW_IN_FILE
+ },
+ &default_result_formats,
+ "",
+ check_default_result_formats, assign_default_result_formats,
NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, NULL, NULL, NULL, NULL
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index 437642cc72..1d431fe013 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -15,10 +15,12 @@
#define PQUERY_H
#include "nodes/parsenodes.h"
+#include "utils/guc.h"
#include "utils/portal.h"
extern PGDLLIMPORT Portal ActivePortal;
+extern char *default_result_formats;
extern PortalStrategy ChoosePortalStrategy(List *stmts);
@@ -30,6 +32,9 @@ extern List *FetchStatementTargetList(Node *stmt);
extern void PortalStart(Portal portal, ParamListInfo params,
int eflags, Snapshot snapshot);
+extern bool check_default_result_formats(char **newval, void **extra,
GucSource source);
+extern void assign_default_result_formats(const char *newval, void *extra);
+
extern void PortalSetResultFormat(Portal portal, int nFormats,
int16
*formats);
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c
index eea0237c3a..e41216e2a1 100644
--- a/src/interfaces/libpq/fe-exec.c
+++ b/src/interfaces/libpq/fe-exec.c
@@ -1617,9 +1617,17 @@ PQsendQueryGuts(PGconn *conn,
goto sendFailed;
}
}
- if (pqPutInt(1, 2, conn) < 0 ||
- pqPutInt(resultFormat, 2, conn))
- goto sendFailed;
+ if (resultFormat >= 0)
+ {
+ if (pqPutInt(1, 2, conn) < 0 ||
+ pqPutInt(resultFormat, 2, conn))
+ goto sendFailed;
+ }
+ else
+ {
+ if (pqPutInt(0, 2, conn) < 0)
+ goto sendFailed;
+ }
if (pqPutMsgEnd(conn) < 0)
goto sendFailed;
--
2.28.0
