default result formats setting

From 30d3118e3f104fe1eef50c5782ab0fb5c2fb2b55 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@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-03f938aacdae@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

From cfe184cbb45134360713405bb4d39b674a298672 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 25 Nov 2020 07:46:25 +0100
Subject: [PATCH v3] Add result_format_auto_binary_types 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 result_format_auto_binary_types = int2,int4,int8;

To request result formats based on this setting, send format code
-1 (instead of 0 or 1) in the Bind message.

Discussion: https://www.postgresql.org/message-id/flat/40cbb35d-774f-23ed-3079-03f938aacdae@2ndquadrant.com
---
 doc/src/sgml/config.sgml     |  53 ++++++++
 doc/src/sgml/libpq.sgml      |  10 +-
 doc/src/sgml/protocol.sgml   |   7 +-
 src/backend/tcop/pquery.c    | 246 ++++++++++++++++++++++++++++++++++-
 src/backend/utils/misc/guc.c |  12 ++
 src/include/tcop/pquery.h    |   5 +
 6 files changed, 323 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3795c57004..20049fe7df 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8530,6 +8530,59 @@ <title>Statement Behavior</title>
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-result-format-auto-binary-types" xreflabel="result_format_auto_binary_types">
+      <term><varname>result_format_auto_binary_types</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>result_format_auto_binary_types</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        This parameter specifies which data types are to be sent from the
+        server in binary format 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 comma-separated list of type names.  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>int2,int4,int8</literal> or
+        <literal>smallint,int,bigint</literal>.  Type names may also be
+        double-quoted, as in SQL syntax.  The parsing of this value is run
+        with a temporary search path that only contains the
+        <literal>pg_catalog</literal> schema.  Therefore, if data types in
+        other schemas are to be included, they must be fully schema-qualified.
+       </para>
+
+       <para>
+        A non-existing type is ignored (but a message is written to the server
+        log).  This allows using the same value for different databases that
+        might have different extensions installed.  Because validating this
+        setting requires system catalog access, invalid settings are not
+        diagnosed at the time the value is set but only when the value is used
+        the first time, which happens when the first extended query protocol
+        result is returned to the client.
+       </para>
+
+       <para>
+        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 9d4b6ab4a8..2a4ec7ff63 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-result-format-auto-binary-types"/> 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 cee28889e1..d0e447803a 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -3567,7 +3567,7 @@ <title>Message Formats</title>
 </varlistentry>
 
 
-<varlistentry>
+<varlistentry id="protocol-message-Bind">
 <term>
 Bind (F)
 </term>
@@ -3709,8 +3709,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-result-format-auto-binary-types"/>.
 </para>
 </listitem>
 </varlistentry>
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 96ea74f118..a06d5f3635 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -18,14 +18,19 @@
 #include <limits.h>
 
 #include "access/xact.h"
+#include "catalog/namespace.h"
 #include "commands/prepare.h"
 #include "executor/tstoreReceiver.h"
 #include "miscadmin.h"
+#include "parser/parse_type.h"
+#include "parser/scansup.h"
 #include "pg_trace.h"
 #include "tcop/pquery.h"
 #include "tcop/utility.h"
+#include "utils/inval.h"
 #include "utils/memutils.h"
 #include "utils/snapmgr.h"
+#include "utils/syscache.h"
 
 
 /*
@@ -597,6 +602,235 @@ PortalStart(Portal portal, ParamListInfo params,
 	portal->status = PORTAL_READY;
 }
 
+/*
+ * Support for result_format_auto_binary_types setting
+ *
+ * Interpreting this setting requires catalog access, so we cannot do most of
+ * the work in the usual check or assign hooks.  Instead, we do it the first
+ * time it is used.
+ */
+
+/* GUC variable */
+char *result_format_auto_binary_types;
+
+/* remembers whether the internal representation is up to date */
+static bool result_format_auto_binary_types_internal_valid = false;
+
+/*
+ * SplitGUCTypeList() --- parse a list of types
+ *
+ * Similar to SplitIdentifierString() etc., but does not strip quotes or alter
+ * the list elements in any way, since this is done by parseTypeString()
+ * later.  It only checks for comma as a separator and keeps track of balanced
+ * double quotes.
+ */
+static bool
+SplitGUCTypeList(char *rawstring, List **namelist)
+{
+	const char separator = ',';
+	char	   *nextp = rawstring;
+	bool		done = false;
+
+	*namelist = NIL;
+
+	while (scanner_isspace(*nextp))
+		nextp++;				/* skip leading whitespace */
+
+	if (*nextp == '\0')
+		return true;			/* allow empty string */
+
+	/* At the top of the loop, we are at start of a new list element. */
+	do
+	{
+		char	   *curname;
+		char	   *endp;
+		bool		in_quote = false;
+
+		curname = nextp;
+
+		while (*nextp)
+		{
+			if (!*nextp)
+				break;
+
+			if (*nextp == '"')
+				in_quote = !in_quote;
+
+			if (*nextp == separator && !in_quote)
+				break;
+
+			nextp++;
+		}
+
+		endp = nextp;
+		if (curname == nextp)
+			return false;	/* empty element not allowed */
+
+		if (*nextp == separator)
+			nextp++;
+		else if (*nextp == '\0')
+		{
+			if (in_quote)
+				return false;
+			done = true;
+		}
+		else
+			return false;		/* invalid syntax */
+
+		/* Now safe to overwrite separator with a null */
+		*endp = '\0';
+
+		/*
+		 * Finished isolating current name --- add it to list
+		 */
+		*namelist = lappend(*namelist, curname);
+
+		/* Loop back if we didn't reach end of string */
+	} while (!done);
+
+	return true;
+}
+
+bool
+check_result_format_auto_binary_types(char **newval, void **extra, GucSource source)
+{
+	char       *rawstring;
+	List       *elemlist;
+
+	rawstring = pstrdup(*newval);
+	if (!SplitGUCTypeList(rawstring, &elemlist))
+	{
+		GUC_check_errdetail("List syntax is invalid.");
+		pfree(rawstring);
+		list_free(elemlist);
+		return false;
+	}
+
+	pfree(rawstring);
+	list_free(elemlist);
+
+	return true;
+}
+
+/*
+ * GUC assign hook invalidates internal representation when the setting changes
+ */
+void
+assign_result_format_auto_binary_types(const char *newval, void *extra)
+{
+	result_format_auto_binary_types_internal_valid = false;
+}
+
+/*
+ * Invalidate when anything relevant in the system catalogs changes
+ */
+static void
+invalidate_result_format_auto_binary_types_internal(Datum arg, int cacheid, uint32 hashvalue)
+{
+	result_format_auto_binary_types_internal_valid = false;
+}
+
+/*
+ * Error context callback, so the error messages are clearer, since they
+ * happen as part of query processing, not GUC processing.
+ */
+static void
+_parse_rfabt_error_callback(void *arg)
+{
+	const char *value = (const char *) arg;
+
+	errcontext("parsing %s element \"%s\"",
+			   "result_format_auto_binary_types",
+			   value);
+}
+
+/*
+ * Subroutine for PortalSetResultFormat(): Return format code for type by
+ * using result_format_auto_binary_types setting.
+ */
+static int16
+resolve_result_format(Oid typeid)
+{
+	static List *result_format_auto_binary_types_internal = NIL;	/* type OID list */
+	static bool syscache_callback_set = false;
+
+	if (!syscache_callback_set)
+	{
+		CacheRegisterSyscacheCallback(TYPEOID, invalidate_result_format_auto_binary_types_internal, (Datum) 0);
+		syscache_callback_set = true;
+	}
+
+	if (!result_format_auto_binary_types_internal_valid)
+	{
+		MemoryContext oldcontext;
+		char	   *rawstring;
+		List	   *elemlist;
+		ListCell   *lc;
+
+		rawstring = pstrdup(result_format_auto_binary_types);
+		if (!SplitGUCTypeList(rawstring, &elemlist))
+		{
+			pfree(rawstring);
+			list_free(elemlist);
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("invalid list syntax in parameter \"%s\"",
+							"result_format_auto_binary_types")));
+		}
+
+		oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+
+		list_free(result_format_auto_binary_types_internal);
+		result_format_auto_binary_types_internal = NIL;
+
+		foreach(lc, elemlist)
+		{
+			char	   *str = lfirst(lc);
+			ErrorContextCallback myerrcontext;
+			OverrideSearchPath temppath = { .addCatalog = true };
+			Oid			typeid;
+
+			myerrcontext.callback = _parse_rfabt_error_callback;
+			myerrcontext.arg = str;
+			myerrcontext.previous = error_context_stack;
+			error_context_stack = &myerrcontext;
+
+			/* use pg_catalog-only search_path */
+			PushOverrideSearchPath(&temppath);
+
+			parseTypeString(str, &typeid, NULL, true);
+
+			PopOverrideSearchPath();
+
+			if (typeid)
+				result_format_auto_binary_types_internal =
+					list_append_unique_oid(result_format_auto_binary_types_internal,
+										   typeid);
+			else
+				/*
+				 * We ignore nonexisting types, so that one setting can be
+				 * shared between different databases that might have
+				 * different extensions installed.  But we should diagnose
+				 * that we are ignoring a type, otherwise typos and similar
+				 * might never get noticed.
+				 */
+				ereport(LOG,
+						errmsg("type %s does not exist", str));
+
+			error_context_stack = myerrcontext.previous;
+		}
+
+		MemoryContextSwitchTo(oldcontext);
+
+		pfree(rawstring);
+		list_free(elemlist);
+
+		result_format_auto_binary_types_internal_valid = true;
+	}
+
+	return list_member_oid(result_format_auto_binary_types_internal, typeid) ? 1 : 0;
+}
+
 /*
  * PortalSetResultFormat
  *		Select the format codes for a portal's output.
@@ -628,7 +862,11 @@ 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] = (formats[i] == -1
+								  ? resolve_result_format(TupleDescAttr(portal->tupDesc, i)->atttypid)
+								  : formats[i]);
 	}
 	else if (nFormats > 0)
 	{
@@ -636,11 +874,13 @@ PortalSetResultFormat(Portal portal, int nFormats, int16 *formats)
 		int16		format1 = formats[0];
 
 		for (i = 0; i < natts; i++)
-			portal->formats[i] = format1;
+			portal->formats[i] = (format1 == -1
+								  ? resolve_result_format(TupleDescAttr(portal->tupDesc, i)->atttypid)
+								  : format1);
 	}
 	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..79491857fa 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
 	},
 
+	{
+		{"result_format_auto_binary_types", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Which data types to send in binary for format -1 in the extended query protocol."),
+			NULL,
+			GUC_LIST_INPUT | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+		},
+		&result_format_auto_binary_types,
+		"",
+		check_result_format_auto_binary_types, assign_result_format_auto_binary_types, 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..bceca24d2d 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 *result_format_auto_binary_types;
 
 
 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_result_format_auto_binary_types(char **newval, void **extra, GucSource source);
+extern void assign_result_format_auto_binary_types(const char *newval, void *extra);
+
 extern void PortalSetResultFormat(Portal portal, int nFormats,
 								  int16 *formats);
 

base-commit: a7e65dc88b6f088fc2fcf5a660d866de644b1300
-- 
2.29.2

From 3410ac02bc34b60b79c99ad96505dabe4362570f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Thu, 18 Mar 2021 21:07:42 +0100
Subject: [PATCH v4] Add result_format_auto_binary_types 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 result_format_auto_binary_types = 21,23,20;

To request result formats based on this setting, send format code
-1 (instead of 0 or 1) in the Bind message.

Discussion: https://www.postgresql.org/message-id/flat/40cbb35d-774f-23ed-3079-03f938aacdae@2ndquadrant.com
---
 doc/src/sgml/config.sgml                      | 40 ++++++++
 doc/src/sgml/libpq.sgml                       | 10 +-
 doc/src/sgml/protocol.sgml                    |  7 +-
 src/backend/access/common/printtup.c          | 94 +++++++++++++++++++
 src/backend/utils/misc/guc.c                  | 12 +++
 src/include/access/printtup.h                 |  5 +
 src/test/modules/Makefile                     |  1 +
 src/test/modules/libpq_extended/.gitignore    |  6 ++
 src/test/modules/libpq_extended/Makefile      | 20 ++++
 src/test/modules/libpq_extended/README        |  1 +
 .../libpq_extended/t/001_result_format.pl     | 33 +++++++
 11 files changed, 222 insertions(+), 7 deletions(-)
 create mode 100644 src/test/modules/libpq_extended/.gitignore
 create mode 100644 src/test/modules/libpq_extended/Makefile
 create mode 100644 src/test/modules/libpq_extended/README
 create mode 100644 src/test/modules/libpq_extended/t/001_result_format.pl

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8603cf3f94..0fed8abfd2 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8671,6 +8671,46 @@ <title>Statement Behavior</title>
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-result-format-auto-binary-types" xreflabel="result_format_auto_binary_types">
+      <term><varname>result_format_auto_binary_types</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>result_format_auto_binary_types</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        This parameter specifies which data types are to be sent from the
+        server in binary format 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 comma-separated list of type OIDs.  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,23,20</literal>.  A non-existing type OIDs are ignored.
+        This allows using the same value for different databases that might
+        have different extensions installed.
+       </para>
+
+       <para>
+        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 be674fbaa9..ef55f09487 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2834,10 +2834,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-result-format-auto-binary-types"/> 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 43092fe62a..e3a614e80e 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -3587,7 +3587,7 @@ <title>Message Formats</title>
 </varlistentry>
 
 
-<varlistentry>
+<varlistentry id="protocol-message-Bind">
 <term>
 Bind (F)
 </term>
@@ -3729,8 +3729,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-result-format-auto-binary-types"/>.
 </para>
 </listitem>
 </varlistentry>
diff --git a/src/backend/access/common/printtup.c b/src/backend/access/common/printtup.c
index 54b539f6fb..0754ec7e9b 100644
--- a/src/backend/access/common/printtup.c
+++ b/src/backend/access/common/printtup.c
@@ -22,6 +22,7 @@
 #include "utils/lsyscache.h"
 #include "utils/memdebug.h"
 #include "utils/memutils.h"
+#include "utils/varlena.h"
 
 
 static void printtup_startup(DestReceiver *self, int operation,
@@ -30,6 +31,13 @@ static bool printtup(TupleTableSlot *slot, DestReceiver *self);
 static void printtup_shutdown(DestReceiver *self);
 static void printtup_destroy(DestReceiver *self);
 
+/* GUC variable */
+char *result_format_auto_binary_types;
+
+/* internal parsed representation */
+static List *result_format_auto_binary_types_internal = NIL;	/* type OID list */
+
+
 /* ----------------------------------------------------------------
  *		printtup / debugtup support
  * ----------------------------------------------------------------
@@ -231,6 +239,9 @@ SendRowDescriptionMessage(StringInfo buf, TupleDesc typeinfo,
 		else
 			format = 0;
 
+		if (format == -1)
+			format = list_member_oid(result_format_auto_binary_types_internal, atttypid) ? 1 : 0;
+
 		pq_writestring(buf, NameStr(att->attname));
 		pq_writeint32(buf, resorigtbl);
 		pq_writeint16(buf, resorigcol);
@@ -271,6 +282,9 @@ printtup_prepare_info(DR_printtup *myState, TupleDesc typeinfo, int numAttrs)
 		int16		format = (formats ? formats[i] : 0);
 		Form_pg_attribute attr = TupleDescAttr(typeinfo, i);
 
+		if (format == -1)
+			format = list_member_oid(result_format_auto_binary_types_internal, attr->atttypid) ? 1 : 0;
+
 		thisState->format = format;
 		if (format == 0)
 		{
@@ -483,3 +497,83 @@ debugtup(TupleTableSlot *slot, DestReceiver *self)
 
 	return true;
 }
+
+
+/*
+ * Support for result_format_auto_binary_types setting
+ */
+
+bool
+check_result_format_auto_binary_types(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);
+
+		if (atooid(str) == 0)
+		{
+			GUC_check_errdetail("Invalid list entry: %s", str);
+			pfree(rawstring);
+			list_free(elemlist);
+			return false;
+		}
+	}
+
+	pfree(rawstring);
+	list_free(elemlist);
+
+	return true;
+}
+
+void
+assign_result_format_auto_binary_types(const char *newval, void *extra)
+{
+	char	   *rawstring;
+	List	   *elemlist;
+	ListCell   *lc;
+
+	rawstring = pstrdup(newval);
+	if (!SplitGUCList(rawstring, ',', &elemlist))
+	{
+		pfree(rawstring);
+		list_free(elemlist);
+		return;
+	}
+
+	list_free(result_format_auto_binary_types_internal);
+	result_format_auto_binary_types_internal = NIL;
+
+	foreach(lc, elemlist)
+	{
+		char	   *str = lfirst(lc);
+		Oid			oid;
+		MemoryContext oldcontext;
+
+		if ((oid = atooid(str)) == 0)
+		{
+			pfree(rawstring);
+			list_free(elemlist);
+			return;
+		}
+
+		oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+		result_format_auto_binary_types_internal = list_append_unique_oid(result_format_auto_binary_types_internal, oid);
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	pfree(rawstring);
+	list_free(elemlist);
+}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index b263e3493b..4d5447b09a 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -31,6 +31,7 @@
 
 #include "access/commit_ts.h"
 #include "access/gin.h"
+#include "access/printtup.h"
 #include "access/rmgr.h"
 #include "access/tableam.h"
 #include "access/transam.h"
@@ -4514,6 +4515,17 @@ static struct config_string ConfigureNamesString[] =
 		check_backtrace_functions, assign_backtrace_functions, NULL
 	},
 
+	{
+		{"result_format_auto_binary_types", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Which data types to send in binary for format -1 in the extended query protocol."),
+			NULL,
+			GUC_LIST_INPUT | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+		},
+		&result_format_auto_binary_types,
+		"",
+		check_result_format_auto_binary_types, assign_result_format_auto_binary_types, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, NULL, NULL, NULL, NULL
diff --git a/src/include/access/printtup.h b/src/include/access/printtup.h
index c9b37538a0..9ceaf4cd82 100644
--- a/src/include/access/printtup.h
+++ b/src/include/access/printtup.h
@@ -14,6 +14,7 @@
 #ifndef PRINTTUP_H
 #define PRINTTUP_H
 
+#include "utils/guc.h"
 #include "utils/portal.h"
 
 extern DestReceiver *printtup_create_DR(CommandDest dest);
@@ -27,6 +28,10 @@ extern void debugStartup(DestReceiver *self, int operation,
 						 TupleDesc typeinfo);
 extern bool debugtup(TupleTableSlot *slot, DestReceiver *self);
 
+extern char *result_format_auto_binary_types;
+extern bool check_result_format_auto_binary_types(char **newval, void **extra, GucSource source);
+extern void assign_result_format_auto_binary_types(const char *newval, void *extra);
+
 /* XXX these are really in executor/spi.c */
 extern void spi_dest_startup(DestReceiver *self, int operation,
 							 TupleDesc typeinfo);
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 93e7829c67..65dcbd2a09 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -10,6 +10,7 @@ SUBDIRS = \
 		  delay_execution \
 		  dummy_index_am \
 		  dummy_seclabel \
+		  libpq_extended \
 		  libpq_pipeline \
 		  plsample \
 		  snapshot_too_old \
diff --git a/src/test/modules/libpq_extended/.gitignore b/src/test/modules/libpq_extended/.gitignore
new file mode 100644
index 0000000000..6fb386d93b
--- /dev/null
+++ b/src/test/modules/libpq_extended/.gitignore
@@ -0,0 +1,6 @@
+/test-*
+
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/libpq_extended/Makefile b/src/test/modules/libpq_extended/Makefile
new file mode 100644
index 0000000000..eb6f308206
--- /dev/null
+++ b/src/test/modules/libpq_extended/Makefile
@@ -0,0 +1,20 @@
+# src/test/modules/libpq_extended/Makefile
+
+PROGRAM = test-result-format
+OBJS = test-result-format.o
+
+PG_CPPFLAGS = -I$(libpq_srcdir)
+PG_LIBS_INTERNAL += $(libpq_pgport)
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/libpq_pipeline
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/libpq_extended/README b/src/test/modules/libpq_extended/README
new file mode 100644
index 0000000000..842c02d4f0
--- /dev/null
+++ b/src/test/modules/libpq_extended/README
@@ -0,0 +1 @@
+Tests for libpq extended query protocol support
diff --git a/src/test/modules/libpq_extended/t/001_result_format.pl b/src/test/modules/libpq_extended/t/001_result_format.pl
new file mode 100644
index 0000000000..0ceea44bcc
--- /dev/null
+++ b/src/test/modules/libpq_extended/t/001_result_format.pl
@@ -0,0 +1,33 @@
+use strict;
+use warnings;
+
+use PostgresNode;
+use TestLib;
+use Test::More tests => 4;
+use Cwd;
+
+my $node = get_new_node('main');
+$node->init;
+$node->start;
+
+$ENV{PATH} = "$ENV{PATH}:" . getcwd();
+
+# int2,int4,int8
+$ENV{PGOPTIONS} = '-c result_format_auto_binary_types=21,23,20,20000';
+$node->command_like([
+	'test-result-format',
+	$node->connstr('postgres'),
+	"SELECT 1::int4, 2::int8, 'abc'::text",
+	],
+	qr/0->1 1->1 2->0/,
+	"binary format is applied, nonexisting OID ignored");
+
+$ENV{PGOPTIONS} = '-c result_format_auto_binary_types=foo,bar';
+$node->command_fails([
+	'test-result-format',
+	$node->connstr('postgres'),
+	"SELECT 1::int4, 2::int8, 'abc'::text",
+	],
+	"invalid setting is an error");
+
+$node->stop('fast');

base-commit: ed62d3737c1b823f796d974060b1d0295a3dd831
-- 
2.30.2

From 61796d1077bd146daa6952dae799819539c05a96 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 22 Mar 2021 13:36:35 +0100
Subject: [PATCH v5] Add result_format_auto_binary_types 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 result_format_auto_binary_types = 21,23,20;

To request result formats based on this setting, send format code
-1 (instead of 0 or 1) in the Bind message.

Discussion: https://www.postgresql.org/message-id/flat/40cbb35d-774f-23ed-3079-03f938aacdae@2ndquadrant.com
---
 doc/src/sgml/config.sgml                      | 40 ++++++++
 doc/src/sgml/libpq.sgml                       | 10 +-
 doc/src/sgml/protocol.sgml                    |  7 +-
 src/backend/access/common/printtup.c          | 94 +++++++++++++++++++
 src/backend/utils/misc/guc.c                  | 12 +++
 src/include/access/printtup.h                 |  5 +
 src/test/modules/Makefile                     |  1 +
 src/test/modules/libpq_extended/.gitignore    |  6 ++
 src/test/modules/libpq_extended/Makefile      | 20 ++++
 src/test/modules/libpq_extended/README        |  1 +
 .../libpq_extended/t/001_result_format.pl     | 33 +++++++
 .../libpq_extended/test-result-format.c       | 39 ++++++++
 12 files changed, 261 insertions(+), 7 deletions(-)
 create mode 100644 src/test/modules/libpq_extended/.gitignore
 create mode 100644 src/test/modules/libpq_extended/Makefile
 create mode 100644 src/test/modules/libpq_extended/README
 create mode 100644 src/test/modules/libpq_extended/t/001_result_format.pl
 create mode 100644 src/test/modules/libpq_extended/test-result-format.c

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ee4925d6d9..0c81a96170 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8671,6 +8671,46 @@ <title>Statement Behavior</title>
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-result-format-auto-binary-types" xreflabel="result_format_auto_binary_types">
+      <term><varname>result_format_auto_binary_types</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>result_format_auto_binary_types</varname></primary>
+       <secondary>configuration parameter</secondary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        This parameter specifies which data types are to be sent from the
+        server in binary format 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 comma-separated list of type OIDs.  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,23,20</literal>.  A non-existing type OIDs are ignored.
+        This allows using the same value for different databases that might
+        have different extensions installed.
+       </para>
+
+       <para>
+        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 be674fbaa9..ef55f09487 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2834,10 +2834,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-result-format-auto-binary-types"/> 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 43092fe62a..e3a614e80e 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -3587,7 +3587,7 @@ <title>Message Formats</title>
 </varlistentry>
 
 
-<varlistentry>
+<varlistentry id="protocol-message-Bind">
 <term>
 Bind (F)
 </term>
@@ -3729,8 +3729,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-result-format-auto-binary-types"/>.
 </para>
 </listitem>
 </varlistentry>
diff --git a/src/backend/access/common/printtup.c b/src/backend/access/common/printtup.c
index 54b539f6fb..0754ec7e9b 100644
--- a/src/backend/access/common/printtup.c
+++ b/src/backend/access/common/printtup.c
@@ -22,6 +22,7 @@
 #include "utils/lsyscache.h"
 #include "utils/memdebug.h"
 #include "utils/memutils.h"
+#include "utils/varlena.h"
 
 
 static void printtup_startup(DestReceiver *self, int operation,
@@ -30,6 +31,13 @@ static bool printtup(TupleTableSlot *slot, DestReceiver *self);
 static void printtup_shutdown(DestReceiver *self);
 static void printtup_destroy(DestReceiver *self);
 
+/* GUC variable */
+char *result_format_auto_binary_types;
+
+/* internal parsed representation */
+static List *result_format_auto_binary_types_internal = NIL;	/* type OID list */
+
+
 /* ----------------------------------------------------------------
  *		printtup / debugtup support
  * ----------------------------------------------------------------
@@ -231,6 +239,9 @@ SendRowDescriptionMessage(StringInfo buf, TupleDesc typeinfo,
 		else
 			format = 0;
 
+		if (format == -1)
+			format = list_member_oid(result_format_auto_binary_types_internal, atttypid) ? 1 : 0;
+
 		pq_writestring(buf, NameStr(att->attname));
 		pq_writeint32(buf, resorigtbl);
 		pq_writeint16(buf, resorigcol);
@@ -271,6 +282,9 @@ printtup_prepare_info(DR_printtup *myState, TupleDesc typeinfo, int numAttrs)
 		int16		format = (formats ? formats[i] : 0);
 		Form_pg_attribute attr = TupleDescAttr(typeinfo, i);
 
+		if (format == -1)
+			format = list_member_oid(result_format_auto_binary_types_internal, attr->atttypid) ? 1 : 0;
+
 		thisState->format = format;
 		if (format == 0)
 		{
@@ -483,3 +497,83 @@ debugtup(TupleTableSlot *slot, DestReceiver *self)
 
 	return true;
 }
+
+
+/*
+ * Support for result_format_auto_binary_types setting
+ */
+
+bool
+check_result_format_auto_binary_types(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);
+
+		if (atooid(str) == 0)
+		{
+			GUC_check_errdetail("Invalid list entry: %s", str);
+			pfree(rawstring);
+			list_free(elemlist);
+			return false;
+		}
+	}
+
+	pfree(rawstring);
+	list_free(elemlist);
+
+	return true;
+}
+
+void
+assign_result_format_auto_binary_types(const char *newval, void *extra)
+{
+	char	   *rawstring;
+	List	   *elemlist;
+	ListCell   *lc;
+
+	rawstring = pstrdup(newval);
+	if (!SplitGUCList(rawstring, ',', &elemlist))
+	{
+		pfree(rawstring);
+		list_free(elemlist);
+		return;
+	}
+
+	list_free(result_format_auto_binary_types_internal);
+	result_format_auto_binary_types_internal = NIL;
+
+	foreach(lc, elemlist)
+	{
+		char	   *str = lfirst(lc);
+		Oid			oid;
+		MemoryContext oldcontext;
+
+		if ((oid = atooid(str)) == 0)
+		{
+			pfree(rawstring);
+			list_free(elemlist);
+			return;
+		}
+
+		oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+		result_format_auto_binary_types_internal = list_append_unique_oid(result_format_auto_binary_types_internal, oid);
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	pfree(rawstring);
+	list_free(elemlist);
+}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 3b36a31a47..a1fb787782 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -31,6 +31,7 @@
 
 #include "access/commit_ts.h"
 #include "access/gin.h"
+#include "access/printtup.h"
 #include "access/rmgr.h"
 #include "access/tableam.h"
 #include "access/toast_compression.h"
@@ -4543,6 +4544,17 @@ static struct config_string ConfigureNamesString[] =
 		check_backtrace_functions, assign_backtrace_functions, NULL
 	},
 
+	{
+		{"result_format_auto_binary_types", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Which data types to send in binary for format -1 in the extended query protocol."),
+			NULL,
+			GUC_LIST_INPUT | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+		},
+		&result_format_auto_binary_types,
+		"",
+		check_result_format_auto_binary_types, assign_result_format_auto_binary_types, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, NULL, NULL, NULL, NULL
diff --git a/src/include/access/printtup.h b/src/include/access/printtup.h
index c9b37538a0..9ceaf4cd82 100644
--- a/src/include/access/printtup.h
+++ b/src/include/access/printtup.h
@@ -14,6 +14,7 @@
 #ifndef PRINTTUP_H
 #define PRINTTUP_H
 
+#include "utils/guc.h"
 #include "utils/portal.h"
 
 extern DestReceiver *printtup_create_DR(CommandDest dest);
@@ -27,6 +28,10 @@ extern void debugStartup(DestReceiver *self, int operation,
 						 TupleDesc typeinfo);
 extern bool debugtup(TupleTableSlot *slot, DestReceiver *self);
 
+extern char *result_format_auto_binary_types;
+extern bool check_result_format_auto_binary_types(char **newval, void **extra, GucSource source);
+extern void assign_result_format_auto_binary_types(const char *newval, void *extra);
+
 /* XXX these are really in executor/spi.c */
 extern void spi_dest_startup(DestReceiver *self, int operation,
 							 TupleDesc typeinfo);
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 93e7829c67..65dcbd2a09 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -10,6 +10,7 @@ SUBDIRS = \
 		  delay_execution \
 		  dummy_index_am \
 		  dummy_seclabel \
+		  libpq_extended \
 		  libpq_pipeline \
 		  plsample \
 		  snapshot_too_old \
diff --git a/src/test/modules/libpq_extended/.gitignore b/src/test/modules/libpq_extended/.gitignore
new file mode 100644
index 0000000000..e3ad34260f
--- /dev/null
+++ b/src/test/modules/libpq_extended/.gitignore
@@ -0,0 +1,6 @@
+/test-result-format
+
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/libpq_extended/Makefile b/src/test/modules/libpq_extended/Makefile
new file mode 100644
index 0000000000..eb6f308206
--- /dev/null
+++ b/src/test/modules/libpq_extended/Makefile
@@ -0,0 +1,20 @@
+# src/test/modules/libpq_extended/Makefile
+
+PROGRAM = test-result-format
+OBJS = test-result-format.o
+
+PG_CPPFLAGS = -I$(libpq_srcdir)
+PG_LIBS_INTERNAL += $(libpq_pgport)
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/libpq_pipeline
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/libpq_extended/README b/src/test/modules/libpq_extended/README
new file mode 100644
index 0000000000..842c02d4f0
--- /dev/null
+++ b/src/test/modules/libpq_extended/README
@@ -0,0 +1 @@
+Tests for libpq extended query protocol support
diff --git a/src/test/modules/libpq_extended/t/001_result_format.pl b/src/test/modules/libpq_extended/t/001_result_format.pl
new file mode 100644
index 0000000000..0ceea44bcc
--- /dev/null
+++ b/src/test/modules/libpq_extended/t/001_result_format.pl
@@ -0,0 +1,33 @@
+use strict;
+use warnings;
+
+use PostgresNode;
+use TestLib;
+use Test::More tests => 4;
+use Cwd;
+
+my $node = get_new_node('main');
+$node->init;
+$node->start;
+
+$ENV{PATH} = "$ENV{PATH}:" . getcwd();
+
+# int2,int4,int8
+$ENV{PGOPTIONS} = '-c result_format_auto_binary_types=21,23,20,20000';
+$node->command_like([
+	'test-result-format',
+	$node->connstr('postgres'),
+	"SELECT 1::int4, 2::int8, 'abc'::text",
+	],
+	qr/0->1 1->1 2->0/,
+	"binary format is applied, nonexisting OID ignored");
+
+$ENV{PGOPTIONS} = '-c result_format_auto_binary_types=foo,bar';
+$node->command_fails([
+	'test-result-format',
+	$node->connstr('postgres'),
+	"SELECT 1::int4, 2::int8, 'abc'::text",
+	],
+	"invalid setting is an error");
+
+$node->stop('fast');
diff --git a/src/test/modules/libpq_extended/test-result-format.c b/src/test/modules/libpq_extended/test-result-format.c
new file mode 100644
index 0000000000..c3a2209fa6
--- /dev/null
+++ b/src/test/modules/libpq_extended/test-result-format.c
@@ -0,0 +1,39 @@
+#include "postgres_fe.h"
+
+#include <stdio.h>
+#include <stdlib.h>
+#include "libpq-fe.h"
+
+int
+main(int argc, char *argv[])
+{
+	PGconn	   *conn;
+	PGresult   *res;
+
+	conn = PQconnectdb("");
+
+	if (PQstatus(conn) != CONNECTION_OK)
+	{
+		fprintf(stderr, "Connection to database failed: %s",
+				PQerrorMessage(conn));
+		exit(1);
+	}
+
+	res = PQexecParams(conn, "SELECT 1::int4, 2::int8, 'abc'::text",
+					   0, NULL, NULL, NULL, NULL,
+					   -1);
+
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		fprintf(stderr, "query failed: %s", PQerrorMessage(conn));
+		exit(1);
+	}
+
+	for (int i = 0; i < PQnfields(res); i++)
+	{
+		printf("%d->%d ", i, PQfformat(res, i));
+	}
+
+	PQfinish(conn);
+	return 0;
+}

base-commit: 909b449e00fc2f71e1a38569bbddbb6457d28485
-- 
2.30.2