[s.cherkashin@postgrespro.ru]

This patch adds verification of the connection with the client during 
the execution of the SQL query. The feature enables using the GUC 
variable ‘client_connection_check_interval’. The default check interval 
is 1 second. If you set the value of ‘client_connection_check_interval’ 
to 0, then the check will not be performed.
The feature will be useful in cases when, during the execution of a very 
long query, the client suddenly terminates the connection - this will 
allow backend to cancel further execution of the query and free server 
resources.

Attachments:

Add_client_connection_check.patch (text/x-diff, 11.3K) download
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c4effa034c..83c662de0f 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7677,6 +7677,27 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-client-connection-check-interval" xreflabel="client_connection_check_interval">
+      <term><varname>client_connection_check_interval</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>client_connection_check_interval</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        This parameter sets a time interval (in milliseconds) between periodic 
+        verification of the connection with the client during the execution
+        of the query. In case when the client aborts the connection,
+        the execution of the query will be terminated.
+       </para>
+       <para>
+        If value is -1, then this option is disabled, and the backend will
+        detect client disconnection only when trying to send him a response
+        to the query. Zero selects a suitable default value (1 second).
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
     </sect2>
    </sect1>
diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c
index 0c9593d4cc..dd1917efe1 100644
--- a/src/backend/libpq/pqcomm.c
+++ b/src/backend/libpq/pqcomm.c
@@ -120,6 +120,7 @@
  */
 int			Unix_socket_permissions;
 char	   *Unix_socket_group;
+int 		client_connection_check_interval;
 
 /* Where the Unix socket files are (list of palloc'd strings) */
 static List *sock_paths = NIL;
@@ -1926,3 +1927,25 @@ pq_setkeepalivescount(int count, Port *port)
 
 	return STATUS_OK;
 }
+
+bool pq_is_client_connected(void)
+{
+	CheckClientConnectionPending = false;
+	if (IsUnderPostmaster &&
+		MyProcPort != NULL && !PqCommReadingMsg && !PqCommBusy)
+	{
+		char nextbyte;
+		int r;
+
+		r = recv(MyProcPort->sock, &nextbyte, 1, MSG_PEEK | MSG_DONTWAIT);
+
+		if (r == 0 || (r == -1 &&
+			errno != EAGAIN && errno != EWOULDBLOCK && errno != EINTR))
+		{
+			ClientConnectionLost = true;
+			InterruptPending = true;
+			return false;
+		}
+	}
+	return true;
+}
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index a3b9757565..2243b672ef 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3017,6 +3017,13 @@ ProcessInterrupts(void)
 					(errcode(ERRCODE_ADMIN_SHUTDOWN),
 					 errmsg("terminating connection due to administrator command")));
 	}
+	if (client_connection_check_interval > 0 && CheckClientConnectionPending)
+	{
+		if (pq_is_client_connected())
+			enable_timeout_after(SKIP_CLIENT_CHECK_TIMEOUT,
+								 client_connection_check_interval);
+
+	}
 	if (ClientConnectionLost)
 	{
 		QueryCancelPending = false; /* lost connection trumps QueryCancel */
@@ -4195,6 +4202,9 @@ PostgresMain(int argc, char *argv[],
 		 */
 		CHECK_FOR_INTERRUPTS();
 		DoingCommandRead = false;
+		if (client_connection_check_interval)
+			enable_timeout_after(SKIP_CLIENT_CHECK_TIMEOUT,
+								 client_connection_check_interval);
 
 		/*
 		 * (5) turn off the idle-in-transaction timeout
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index c6939779b9..96d44a15e8 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -30,6 +30,7 @@ ProtocolVersion FrontendProtocol;
 volatile sig_atomic_t InterruptPending = false;
 volatile sig_atomic_t QueryCancelPending = false;
 volatile sig_atomic_t ProcDiePending = false;
+volatile sig_atomic_t CheckClientConnectionPending = false;
 volatile sig_atomic_t ClientConnectionLost = false;
 volatile sig_atomic_t IdleInTransactionSessionTimeoutPending = false;
 volatile sig_atomic_t ConfigReloadPending = false;
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 4f1d2a0d28..1dfdfe8b12 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -33,6 +33,7 @@
 #include "catalog/pg_db_role_setting.h"
 #include "catalog/pg_tablespace.h"
 #include "libpq/auth.h"
+#include "libpq/libpq.h"
 #include "libpq/libpq-be.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
@@ -72,6 +73,7 @@ static void ShutdownPostgres(int code, Datum arg);
 static void StatementTimeoutHandler(void);
 static void LockTimeoutHandler(void);
 static void IdleInTransactionSessionTimeoutHandler(void);
+static void ClientCheckTimeoutHandler(void);
 static bool ThereIsAtLeastOneRole(void);
 static void process_startup_options(Port *port, bool am_superuser);
 static void process_settings(Oid databaseid, Oid roleid);
@@ -628,6 +630,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
 		RegisterTimeout(LOCK_TIMEOUT, LockTimeoutHandler);
 		RegisterTimeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
 						IdleInTransactionSessionTimeoutHandler);
+		RegisterTimeout(SKIP_CLIENT_CHECK_TIMEOUT, ClientCheckTimeoutHandler);
 	}
 
 	/*
@@ -1239,6 +1242,13 @@ IdleInTransactionSessionTimeoutHandler(void)
 	SetLatch(MyLatch);
 }
 
+static void
+ClientCheckTimeoutHandler(void)
+{
+	CheckClientConnectionPending = true;
+	InterruptPending = true;
+}
+
 /*
  * Returns true if at least one role is defined in this database cluster.
  */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 514595699b..11c6d5bf71 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -3072,6 +3072,16 @@ static struct config_int ConfigureNamesInt[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"client_connection_check_interval", PGC_USERSET, CLIENT_CONN_OTHER,
+			gettext_noop("Sets the time interval for checking connection with the client."),
+			gettext_noop("A value of -1 disables this feature. Zero selects a suitable default value."),
+			GUC_UNIT_MS
+		},
+		&client_connection_check_interval,
+		1000, 0, INT_MAX,
+		NULL, NULL, NULL
+	},
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ab063dae41..40a7610a77 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -623,6 +623,9 @@
 
 #dynamic_library_path = '$libdir'
 
+#client_connection_check_interval = 1000	# set time interval between
+											# connection checks, in ms
+											# 0 is disabled
 
 #------------------------------------------------------------------------------
 # LOCK MANAGEMENT
diff --git a/src/include/libpq/libpq.h b/src/include/libpq/libpq.h
index c7762f68a6..0bf681500e 100644
--- a/src/include/libpq/libpq.h
+++ b/src/include/libpq/libpq.h
@@ -71,6 +71,7 @@ extern int	pq_getbyte(void);
 extern int	pq_peekbyte(void);
 extern int	pq_getbyte_if_available(unsigned char *c);
 extern int	pq_putbytes(const char *s, size_t len);
+extern bool pq_is_client_connected(void);
 
 /*
  * prototypes for functions in be-secure.c
@@ -102,6 +103,7 @@ extern WaitEventSet *FeBeWaitSet;
 extern char *SSLCipherSuites;
 extern char *SSLECDHCurve;
 extern bool SSLPreferServerCiphers;
+extern int client_connection_check_interval;
 
 /*
  * prototypes for functions in be-secure-common.c
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index d6b32c070c..8c828059fc 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -83,7 +83,7 @@ extern PGDLLIMPORT volatile sig_atomic_t QueryCancelPending;
 extern PGDLLIMPORT volatile sig_atomic_t ProcDiePending;
 extern PGDLLIMPORT volatile sig_atomic_t IdleInTransactionSessionTimeoutPending;
 extern PGDLLIMPORT volatile sig_atomic_t ConfigReloadPending;
-
+extern PGDLLIMPORT volatile sig_atomic_t CheckClientConnectionPending;
 extern PGDLLIMPORT volatile sig_atomic_t ClientConnectionLost;
 
 /* these are marked volatile because they are examined by signal handlers: */
diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h
index dcc7307c16..e19a3976e3 100644
--- a/src/include/utils/timeout.h
+++ b/src/include/utils/timeout.h
@@ -31,6 +31,7 @@ typedef enum TimeoutId
 	STANDBY_TIMEOUT,
 	STANDBY_LOCK_TIMEOUT,
 	IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
+	SKIP_CLIENT_CHECK_TIMEOUT,
 	/* First user-definable timeout reason */
 	USER_TIMEOUT,
 	/* Maximum number of timeout reasons */
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 19d60a506e..526a5149cf 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -7,6 +7,7 @@ include $(top_builddir)/src/Makefile.global
 SUBDIRS = \
 		  brin \
 		  commit_ts \
+		  connection \
 		  dummy_seclabel \
 		  snapshot_too_old \
 		  test_bloomfilter \
diff --git a/src/test/modules/connection/Makefile b/src/test/modules/connection/Makefile
new file mode 100644
index 0000000000..2ec706fd56
--- /dev/null
+++ b/src/test/modules/connection/Makefile
@@ -0,0 +1,14 @@
+# src/test/modules/connection/Makefile
+
+subdir = src/test/modules/connection
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+
+check:
+	$(prove_check)
+
+installcheck:
+	$(prove_installcheck)
+
+clean distclean maintainer-clean:
+	rm -rf tmp_check
diff --git a/src/test/modules/connection/t/001_close_connection.pl b/src/test/modules/connection/t/001_close_connection.pl
new file mode 100644
index 0000000000..9d1e7d5990
--- /dev/null
+++ b/src/test/modules/connection/t/001_close_connection.pl
@@ -0,0 +1,61 @@
+# Check if backend stopped after client disconnection
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More tests => 2;
+
+my $long_query = q{
+DO
+$$
+DECLARE row_data RECORD;
+BEGIN
+EXECUTE 'CREATE TABLE IF NOT EXISTS keep_alive_test AS SELECT generate_series(0,100000) AS tt';
+FOR row_data IN
+    SELECT tt
+    FROM keep_alive_test
+LOOP
+    EXECUTE 'SELECT count(*) FROM keep_alive_test';
+END LOOP;
+END$$;
+};
+my $set_guc = q{
+    SET client_connection_check_interval = 1000;
+};
+
+my $node = get_new_node('node');
+my ($pid, $timed_out);
+$node->init;
+$node->start;
+
+#########################################################
+# TEST 1: GUC client_connection_check_interval: enabled #
+#########################################################
+
+# Set GUC options, get backend pid and run a long time query
+$node->psql('postgres', "$set_guc SELECT pg_backend_pid(); $long_query",
+            stdout => \$pid, timeout => 2, timed_out => \$timed_out);
+
+# Give time to the backend to detect client disconnected
+sleep 3;
+# Check if backend is still alive
+my $is_alive = $node->safe_psql('postgres', "SELECT count(*) FROM pg_stat_activity where pid = $pid;");
+is($is_alive, '0', 'Test: client_connection_check_interval enable');
+$node->stop;
+
+##########################################################
+# TEST 2: GUC client_connection_check_interval: disabled #
+##########################################################
+
+$node->start;
+$set_guc = q{
+    SET client_connection_check_interval = 0;
+};
+$node->psql('postgres', "$set_guc SELECT pg_backend_pid(); $long_query",
+            stdout => \$pid, timeout => 2, timed_out => \$timed_out);
+# Give time to the client to disconnect
+sleep 3;
+# Check if backend is still alive
+$is_alive = $node->safe_psql('postgres', "SELECT count(*) FROM pg_stat_activity where pid = $pid;");
+is($is_alive, '1', 'Test: client_connection_check_interval disable');
+$node->stop;