On 2020-11-05 22:03, Peter Eisentraut wrote:
Independently of that, how would you implement "says otherwise" here,
ie do a single-query override of the session's prevailing setting?
Maybe the right thing for that is to define -1 all the way down to the
protocol level as meaning "use the session's per-type default", and
then if you don't want that you can pass 0 or 1. An advantage of that
is that you couldn't accidentally break an application that wasn't
ready for this feature, because it would not be the default to use it.
Yeah, that sounds a lot better. I'll look into that.
Here is a new patch updated to work that way. Feels better now.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
From 30d3118e3f104fe1eef50c5782ab0fb5c2fb2b55 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 9 Nov 2020 11:06:07 +0100
Subject: [PATCH v2] Add default_result_formats setting
The current way binary results are requested in the extended query
protocol is too cumbersome for some practical uses. Some clients, for
example the JDBC driver, have built-in support for handling specific
data types in binary. Such a client would always have to request a
result row description (Describe statement) before sending a Bind
message, in order to be able to pick out the result columns it 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 solution is to allow a client to register for a session which
types it wants to always get in binary. This is done by a new GUC
setting. 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.
To request result formats based on this setting, send format code
-1 (instead of 0 or 1) in the Bind message.
This format satisfies the current requirements of the JDBC
driver (which knows about types by their OID). 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.
Discussion:
https://www.postgresql.org/message-id/flat/40cbb35d-774f-23ed-3079-03f938aac...@2ndquadrant.com
---
doc/src/sgml/config.sgml | 43 +++++++++++++
doc/src/sgml/libpq.sgml | 10 +--
doc/src/sgml/protocol.sgml | 7 ++-
src/backend/tcop/pquery.c | 119 ++++++++++++++++++++++++++++++++++-
src/backend/utils/misc/guc.c | 12 ++++
src/include/tcop/pquery.h | 5 ++
6 files changed, 186 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f043433e31..8703c06538 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8530,6 +8530,49 @@ <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 result format code
+ -1 is specified in the <link linkend="protocol-message-Bind">Bind
+ message</link>. It is intended to be used by client libraries that
+ prefer to handle specific, but not all, 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 9ce32fb39b..14560c271f 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2707,10 +2707,12 @@ <title>Main Functions</title>
<term><parameter>resultFormat</parameter></term>
<listitem>
<para>
- Specify zero to obtain results in text format, or one to obtain
- 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.)
+ Specify 0 to obtain results in text format, or 1 to obtain results
+ in binary format, or -1 to have the setting of <xref
+ linkend="guc-default-result-formats"/> be applied by the server.
+ (There is not currently a provision to obtain different result
+ columns in different formats, although that is possible in the
+ underlying protocol.)
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 9a95d7b734..4e18ab7817 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -3565,7 +3565,7 @@ <title>Message Formats</title>
</varlistentry>
-<varlistentry>
+<varlistentry id="protocol-message-Bind">
<term>
Bind (F)
</term>
@@ -3707,8 +3707,9 @@ <title>Message Formats</title>
</term>
<listitem>
<para>
- The result-column format codes. Each must presently be
- zero (text) or one (binary).
+ The result-column format codes. Each must be 0 for text, or 1
+ for binary, or -1 to apply the setting of <xref
+ linkend="guc-default-result-formats"/>.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 96ea74f118..2560f116a5 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,116 @@ 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);
+}
+
+/*
+ * Convenience routine for PortalSetResultFormat(): Return format code,
+ * resolving code -1 by using default_result_formats setting.
+ */
+static int16
+resolve_result_format(int16 format, Form_pg_attribute attr)
+{
+ if (format == -1)
+ {
+ if (list_member_oid(default_result_formats_binary,
attr->atttypid))
+ return 1;
+ else
+ return 0;
+ }
+ else
+ return format;
+}
+
/*
* PortalSetResultFormat
* Select the format codes for a portal's output.
@@ -628,7 +739,9 @@ PortalSetResultFormat(Portal portal, int nFormats, int16
*formats)
(errcode(ERRCODE_PROTOCOL_VIOLATION),
errmsg("bind message has %d result
formats but query has %d columns",
nFormats, natts)));
- memcpy(portal->formats, formats, natts * sizeof(int16));
+
+ for (i = 0; i < natts; i++)
+ portal->formats[i] = resolve_result_format(formats[i],
TupleDescAttr(portal->tupDesc, i));
}
else if (nFormats > 0)
{
@@ -636,11 +749,11 @@ PortalSetResultFormat(Portal portal, int nFormats, int16
*formats)
int16 format1 = formats[0];
for (i = 0; i < natts; i++)
- portal->formats[i] = format1;
+ portal->formats[i] = resolve_result_format(format1,
TupleDescAttr(portal->tupDesc, i));
}
else
{
- /* use default format for all columns */
+ /* by default use text format for all columns */
for (i = 0; i < natts; i++)
portal->formats[i] = 0;
}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index bb34630e8e..b6ebcf40aa 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);
base-commit: ef60de67ebde6dfd1ea09b4d08166736bf05698c
--
2.29.1
