public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hayato Kuroda (Fujitsu) <[email protected]>
To: 'Ted Yu' <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: vignesh C <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Önder Kalacı <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Kyotaro Horiguchi <[email protected]>
Subject: RE: [Proposal] Add foreign-server health checks infrastructure
Date: Wed, 11 Jan 2023 07:37:00 +0000
Message-ID: <TYAPR01MB5866413814861A5016839506F5FC9@TYAPR01MB5866.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CALte62zppS-YxEhes5jSsHppQsRrgHBw-krViS1HJJPSsV9onw@mail.gmail.com>
References: <CACawEhW_R=6mKsB24QW3WpCZTQgtxAPH7J0q8yedKkCQY2xT0w@mail.gmail.com>
<TYAPR01MB5866F419C4261177578AC1CCF55B9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<CACawEhXAy4gjJ4G3-59Xv_yb8Xbtn1VMYP8SOj-0C9dG2zbAcQ@mail.gmail.com>
<TYAPR01MB5866ACB4D3B845B4B2ED121BF55A9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<CACawEhW19nPfbFpvfke9eidFDxAy+ic36wmY0s936T=xzxgHog@mail.gmail.com>
<TYAPR01MB5866CFD6BAE6DDF01A27CBF1F5299@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<CACawEhUzpqYJ8mQmSjYgX0ePtPpvb2u9Onjf6pCjUGkoZ=-xSg@mail.gmail.com>
<TYAPR01MB58668728393648C2F7DC7C85F5399@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<CACawEhW56PHQ83Q59x4U5zpi0rVJ6=0Vn-FeYSLZ13Y0yasebQ@mail.gmail.com>
<TYAPR01MB5866AAA4EB0632B4001AA2C5F50E9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<[email protected]>
<TYAPR01MB586635DB5F1C5CE4660175FEF51A9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<TYAPR01MB58660C8F72746C2A8C6381F2F5EA9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<TYAPR01MB586613F662443D5A3005CD17F5FF9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<[email protected]>
<TYAPR01MB586612A29BAF468042F04B20F5FF9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<CALte62zppS-YxEhes5jSsHppQsRrgHBw-krViS1HJJPSsV9onw@mail.gmail.com>
Dear Ted,
Thank you for reviewing! PSA new version.
> + /* quick exit if connection cache has been not initialized yet. */
>
> been not initialized -> not been initialized
Fixed.
> + (errcode(ERRCODE_CONNECTION_FAILURE),
> + errmsg("could not connect to server \"%s\"",
>
> Currently each server which is not connected would log a warning.
> Is it better to concatenate names for such servers and log one line ? This would be cleaner when there are multiple such servers.
Sounds good, fixed as you said. The following shows the case that two disconnections
are detected by postgres_fdw_verify_connection_states_all().
```
postgres=*# select postgres_fdw_verify_connection_states_all ();
WARNING: could not connect to server "my_external_server2", "my_external_server"
DETAIL: Socket close is detected.
HINT: Plsease check the health of server.
postgres_fdw_verify_connection_states_all
-------------------------------------------
f
(1 row)
```
Currently, the name of servers is concatenated without doing unique checks. IIUC
a backend process cannot connect to the same foreign server by using different
user mapping, so there is no possibility that the same name appears twice.
If the user mapping is altered in the transaction, the cache entry is invalidated
and will not be checked.
Best Regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v24-0001-Add-PQConncheck-and-PQCanConncheck-to-libpq.patch (5.1K, ../TYAPR01MB5866413814861A5016839506F5FC9@TYAPR01MB5866.jpnprd01.prod.outlook.com/2-v24-0001-Add-PQConncheck-and-PQCanConncheck-to-libpq.patch)
download | inline diff:
From 600d450274a2f4aa3f3e29c4eb0e1d0e128de2ff Mon Sep 17 00:00:00 2001
From: "kuroda.hayato%40jp.fujitsu.com" <[email protected]>
Date: Tue, 1 Nov 2022 09:13:20 +0000
Subject: [PATCH v24 1/3] Add PQConncheck and PQCanConncheck to libpq
PQConncheck() function allows to check the status of socket by polling
the socket. This function is currently available only on systems that
support the non-standard POLLRDHUP extension to the poll system call,
including Linux.
PQCanConncheck() checks whether above function is available or not.
---
doc/src/sgml/libpq.sgml | 44 ++++++++++++++++++++++++
src/interfaces/libpq/exports.txt | 2 ++
src/interfaces/libpq/fe-misc.c | 57 ++++++++++++++++++++++++++++++++
src/interfaces/libpq/libpq-fe.h | 4 +++
4 files changed, 107 insertions(+)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 0e7ae70c70..f7cff16f7f 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2679,6 +2679,50 @@ void *PQgetssl(const PGconn *conn);
</listitem>
</varlistentry>
+ <varlistentry id="libpq-PQConncheck">
+ <term><function>PQConncheck</function><indexterm><primary>PQConncheck</primary></indexterm></term>
+ <listitem>
+ <para>
+ Returns the status of the socket.
+
+<synopsis>
+int PQConncheck(PGconn *conn);
+</synopsis>
+ </para>
+
+ <para>
+ Unlike <xref linkend="libpq-PQstatus"/>, this function checks socket
+ health. This check is performed by polling the socket. This function is
+ currently available only on systems that support the non-standard
+ <symbol>POLLRDHUP</symbol> extension to the <symbol>poll</symbol> system
+ call, including Linux. <xref linkend="libpq-PQConncheck"/> returns <literal>1</literal>
+ if the remote peer seems to be closed, returns <literal>0</literal> if
+ the socket is valid, and returns <literal>-1</literal> if the connection
+ has been already invalid.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="libpq-PQCanConncheck">
+ <term><function>PQCanConncheck</function><indexterm><primary>PQCanConncheck</primary></indexterm></term>
+ <listitem>
+ <para>
+ Returns the status of the socket.
+
+<synopsis>
+int PQCanConncheck(void);
+</synopsis>
+ </para>
+
+ <para>
+ This function checks whether <xref linkend="libpq-PQConncheck"/> is
+ available or not on this platform. <xref linkend="libpq-PQCanConncheck"/>
+ returns <literal>0</literal> if the function is supported, otherwise
+ returns <literal>-1</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
index e8bcc88370..b90d178047 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -186,3 +186,5 @@ PQpipelineStatus 183
PQsetTraceFlags 184
PQmblenBounded 185
PQsendFlushRequest 186
+PQConncheck 187
+PQCanConncheck 188
diff --git a/src/interfaces/libpq/fe-misc.c b/src/interfaces/libpq/fe-misc.c
index 3653a1a8a6..2888b94c0d 100644
--- a/src/interfaces/libpq/fe-misc.c
+++ b/src/interfaces/libpq/fe-misc.c
@@ -1218,6 +1218,63 @@ PQenv2encoding(void)
return encoding;
}
+/*
+ * Helper function for PQconncheck().
+ *
+ * Return >0 if opposite side seems to be disconnected.
+ */
+static int
+pqConncheck_internal(int sock)
+{
+#if (defined(HAVE_POLL) && defined(POLLRDHUP))
+ struct pollfd input_fd;
+ int errflags = POLLHUP | POLLERR | POLLNVAL;
+
+ input_fd.fd = sock;
+ input_fd.events = POLLRDHUP | errflags;
+ input_fd.revents = 0;
+
+ poll(&input_fd, 1, 0);
+
+ return input_fd.revents;
+#else
+ /* Do not support socket checking on this platform, return 0 */
+ return 0;
+#endif
+}
+
+/*
+ * Check whether the socket peer closed connection or not.
+ *
+ * Returns >0 if remote peer seems to be closed, 0 if it is valid,
+ * -1 if the input connection is bad.
+ */
+int
+PQConncheck(PGconn *conn)
+{
+ /* quick exit if invalid connection has come */
+ if (conn == NULL ||
+ conn->sock == PGINVALID_SOCKET ||
+ conn->status != CONNECTION_OK)
+ return -1;
+
+ return pqConncheck_internal(conn->sock);
+}
+
+/*
+ * Check whether PQConncheck() can work well on this platform.
+ *
+ * Returns 0 if this can use PQConncheck(), otherwise -1.
+ */
+int
+PQCanConncheck(void)
+{
+#if (defined(HAVE_POLL) && defined(POLLRDHUP))
+ return 0;
+#else
+ return -1;
+#endif
+}
#ifdef ENABLE_NLS
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
index f3d9220496..4771ee1124 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -648,6 +648,10 @@ extern int PQdsplen(const char *s, int encoding);
/* Get encoding id from environment variable PGCLIENTENCODING */
extern int PQenv2encoding(void);
+/* Check whether the postgres server is still alive or not */
+extern int PQConncheck(PGconn *conn);
+extern int PQCanConncheck(void);
+
/* === in fe-auth.c === */
extern char *PQencryptPassword(const char *passwd, const char *user);
--
2.27.0
[application/octet-stream] v24-0002-postgres_fdw-add-postgres_fdw_verify_connection_.patch (9.5K, ../TYAPR01MB5866413814861A5016839506F5FC9@TYAPR01MB5866.jpnprd01.prod.outlook.com/3-v24-0002-postgres_fdw-add-postgres_fdw_verify_connection_.patch)
download | inline diff:
From 3005525d67175deec5cbb01af7565cc7690a5097 Mon Sep 17 00:00:00 2001
From: "kuroda.hayato%40jp.fujitsu.com" <[email protected]>
Date: Tue, 1 Nov 2022 09:13:42 +0000
Subject: [PATCH v24 2/3] postgres_fdw: add
postgres_fdw_verify_connection_states
This function can verify the status of connections that are establieshed by
postgres_fdw. This check wil be done by PQConncheck(), which means this is
available only on systems that support the non-standard POLLRDHUP extension
to the poll system call, including Linux.
This returns true if checked connection is still valid, or the checking is
not supported on this platform. False is returned if the connection seems
to be closed.
fdw: concat string
---
contrib/postgres_fdw/connection.c | 124 ++++++++++++++++++
.../postgres_fdw/postgres_fdw--1.0--1.1.sql | 15 +++
doc/src/sgml/postgres-fdw.sgml | 68 ++++++++++
3 files changed, 207 insertions(+)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index ed75ce3f79..5f345ca170 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -86,6 +86,9 @@ static bool xact_got_connection = false;
PG_FUNCTION_INFO_V1(postgres_fdw_get_connections);
PG_FUNCTION_INFO_V1(postgres_fdw_disconnect);
PG_FUNCTION_INFO_V1(postgres_fdw_disconnect_all);
+PG_FUNCTION_INFO_V1(postgres_fdw_verify_connection_states);
+PG_FUNCTION_INFO_V1(postgres_fdw_verify_connection_states_all);
+PG_FUNCTION_INFO_V1(postgres_fdw_can_verify_connection_states);
/* prototypes of private functions */
static void make_new_connection(ConnCacheEntry *entry, UserMapping *user);
@@ -116,6 +119,7 @@ static void pgfdw_finish_pre_subcommit_cleanup(List *pending_entries,
int curlevel);
static bool UserMappingPasswordRequired(UserMapping *user);
static bool disconnect_cached_connections(Oid serverid);
+static bool verify_cached_connections(Oid serverid);
/*
* Get a PGconn which can be used to execute queries on the remote PostgreSQL
@@ -1862,3 +1866,123 @@ disconnect_cached_connections(Oid serverid)
return result;
}
+
+/*
+ * Workhorse to verify cached connections.
+ *
+ * This function scans all the connection cache entries and verifies the
+ * connections whose foreign server OID matches with the specified one. If
+ * InvalidOid is specified, it verifies all the cached connections.
+ *
+ * This function emits warnings if a disconnection is found, and this returns
+ * false only when the lastly verified server seems to be disconnected.
+ *
+ * Note that the verification can be used on some limited platforms. If this
+ * server does not support it, this function alwayse returns true.
+ */
+static bool
+verify_cached_connections(Oid serverid)
+{
+ HASH_SEQ_STATUS scan;
+ ConnCacheEntry *entry;
+ bool all = !OidIsValid(serverid);
+ bool result = true;
+ StringInfoData str;
+
+ /* quick exit if connection cache has not been initialized yet. */
+ if (!ConnectionHash)
+ return true;
+
+ hash_seq_init(&scan, ConnectionHash);
+ while ((entry = (ConnCacheEntry *) hash_seq_search(&scan)))
+ {
+ /* Ignore cache entry if no open connection right now. */
+ if (!entry->conn)
+ continue;
+
+ /* Skip if the entry is invalidated. */
+ if (entry->invalidated)
+ continue;
+
+ if (all || entry->serverid == serverid)
+ {
+ if (PQConncheck(entry->conn))
+ {
+ /* A foreign server might be down, so construct a message. */
+ ForeignServer *server = GetForeignServer(entry->serverid);
+
+ if (result)
+ {
+ /*
+ * Initialize and add a prefix if this is the first
+ * disconnection we found.
+ */
+ initStringInfo(&str);
+ appendStringInfo(&str, "could not connect to server ");
+
+ result = false;
+ }
+ else
+ appendStringInfo(&str, ", ");
+
+ appendStringInfo(&str, "\"%s\"", server->servername);
+ }
+ }
+ }
+
+ /* Raise a warning if disconnections are found. */
+ if (!result)
+ {
+ Assert(str.len);
+ ereport(WARNING,
+ (errcode(ERRCODE_CONNECTION_FAILURE),
+ errmsg("%s", str.data),
+ errdetail("Socket close is detected."),
+ errhint("Plsease check the health of server.")));
+ pfree(str.data);
+ }
+
+ return result;
+}
+
+/*
+ * Verify the specified cached connections.
+ *
+ * This function verifies the connections that are established by postgres_fdw
+ * from the local session to the foreign server with the given name.
+ *
+ * This function emits a warning if a disconnection is found, and this returns
+ * false only when the verified server seems to be disconnected.
+ *
+ * Note that the verification can be used on some limited platforms. If this
+ * server does not support it, this function alwayse returns true.
+ */
+Datum
+postgres_fdw_verify_connection_states(PG_FUNCTION_ARGS)
+{
+ ForeignServer *server;
+ char *servername;
+
+ servername = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ server = GetForeignServerByName(servername, false);
+
+ PG_RETURN_BOOL(verify_cached_connections(server->serverid));
+}
+
+/*
+ * Verify all the cached connections.
+ *
+ * This function verifies all the connections that are established by postgres_fdw
+ * from the local session to the foreign servers.
+ */
+Datum
+postgres_fdw_verify_connection_states_all(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_BOOL(verify_cached_connections(InvalidOid));
+}
+
+Datum
+postgres_fdw_can_verify_connection_states(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_BOOL(!PQCanConncheck());
+}
diff --git a/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql
index ed4ca378d4..b337760bfa 100644
--- a/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql
+++ b/contrib/postgres_fdw/postgres_fdw--1.0--1.1.sql
@@ -18,3 +18,18 @@ CREATE FUNCTION postgres_fdw_disconnect_all ()
RETURNS bool
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT PARALLEL RESTRICTED;
+
+CREATE FUNCTION postgres_fdw_verify_connection_states (text)
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT PARALLEL RESTRICTED;
+
+CREATE FUNCTION postgres_fdw_verify_connection_states_all ()
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT PARALLEL RESTRICTED;
+
+CREATE FUNCTION postgres_fdw_can_verify_connection_states ()
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 78f2d7d8d5..bfd1dbae93 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -825,6 +825,74 @@ postgres=# SELECT postgres_fdw_disconnect_all();
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><function>postgres_fdw_verify_connection_states(server_name text) returns boolean</function></term>
+ <listitem>
+ <para>
+ This function checks the status of remote connections that are established
+ by <filename>postgres_fdw</filename> from the local session to the foreign
+ server with the given name. This check is performed by polling the socket
+ and allows long-running transactions to be aborted sooner if the kernel
+ reports that the connection is closed. This function is currently
+ available only on systems that support the non-standard <symbol>POLLRDHUP</symbol>
+ extension to the <symbol>poll</symbol> system call, including Linux. This
+ returns <literal>true</literal> if a checked connections are still valid,
+ or the checking is not supported on this platform. <literal>false</literal>
+ is returned if the local session seems to be disconnected from other
+ servers. Example usage of the function:
+<screen>
+postgres=# SELECT postgres_fdw_verify_foreign_servers(false);
+ postgres_fdw_verify_connection_states
+---------------------------------------
+ t
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><function>postgres_fdw_verify_connection_states_all() returns boolean</function></term>
+ <listitem>
+ <para>
+ This function checks the status of remote connections that are established
+ by <filename>postgres_fdw</filename> from the local session to the foreign
+ servers. This check is performed by polling the socket and allows
+ long-running transactions to be aborted sooner if the kernel reports that
+ the connection is closed. This function is currently available only on
+ systems that support the non-standard <symbol>POLLRDHUP</symbol> extension
+ to the <symbol>poll</symbol> system call, including Linux. This returns
+ <literal>true</literal> if a lastly checked connections are still valid,
+ or the checking is not supported on this platform. <literal>false</literal>
+ is returned if the local session seems to be disconnected from other
+ servers. Example usage of the function:
+<screen>
+postgres=# SELECT postgres_fdw_verify_foreign_servers(false);
+ postgres_fdw_verify_connection_states_all
+-------------------------------------------
+ t
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><function>postgres_fdw_can_verify_connection_states() returns boolean</function></term>
+ <listitem>
+ <para>
+ This function checks whether functions the health of remote connectio
+ work well or not. This returns <literal>true</literal> if it can be used,
+ otherwise returns <literal>false</literal>. Example usage of the function:
+
+<screen>
+postgres=# SELECT postgres_fdw_verify_connection_states_all();
+ postgres_fdw_verify_connection_states_all
+-------------------------------------------
+ t
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
--
2.27.0
[application/octet-stream] v24-0003-add-test.patch (4.6K, ../TYAPR01MB5866413814861A5016839506F5FC9@TYAPR01MB5866.jpnprd01.prod.outlook.com/4-v24-0003-add-test.patch)
download | inline diff:
From 4a2178608b9cce47fe2c90649e40d004ffdb34b0 Mon Sep 17 00:00:00 2001
From: "kuroda.hayato%40jp.fujitsu.com" <[email protected]>
Date: Wed, 21 Sep 2022 06:52:23 +0000
Subject: [PATCH v24 3/3] add test
---
.../postgres_fdw/expected/postgres_fdw.out | 54 +++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 53 ++++++++++++++++++
2 files changed, 107 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c0267a99d2..ee1c0eb96b 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11804,3 +11804,57 @@ ANALYZE analyze_table;
-- cleanup
DROP FOREIGN TABLE analyze_ftable;
DROP TABLE analyze_table;
+-- ===================================================================
+-- test for postgres_fdw_verify_foreign_servers function
+-- ===================================================================
+-- Disable debug_discard_caches in order to manage remote connections
+SET debug_discard_caches TO '0';
+-- -- The text of the error might vary across platforms, so only show SQLSTATE.
+\set VERBOSITY sqlstate
+-- Disconnect once and set application_name to an arbitrary value
+SELECT 1 FROM postgres_fdw_disconnect_all();
+ ?column?
+----------
+ 1
+(1 row)
+
+ALTER SERVER loopback OPTIONS (SET application_name 'healthcheck');
+DO $$
+DECLARE
+ can_verify boolean;
+ result boolean;
+BEGIN
+ PERFORM 1 FROM ft1 LIMIT 1;
+
+ -- Terminate the remote backend process
+ PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
+ WHERE application_name = 'healthcheck';
+
+ -- Check whether we can do health check on this platform
+ SELECT INTO can_verify postgres_fdw_can_verify_connection_states();
+
+ -- If the checking can be done on this platform, call it
+ IF can_verify IS TRUE THEN
+ -- Set client_min_messages to ERROR temporary because the following
+ -- function only throws a WARNING on the supported platform.
+ SET LOCAL client_min_messages TO ERROR;
+
+ SELECT INTO result postgres_fdw_verify_connection_states('loopback');
+
+ RESET client_min_messages;
+ ELSE
+ result = false;
+ END IF;
+
+ -- If result is FALSE, we succeeded to detect the disconnection or it could
+ -- not be done on this platform. Raise an message.
+ IF result IS FALSE THEN
+ RAISE INFO 'postgres_fdw_verify_connection_states() could detect the disconnection, or health check cannot be used on this platform';
+ END IF;
+END;
+$$;
+INFO: 00000
+ERROR: 08006
+-- Clean up
+\set VERBOSITY default
+RESET debug_discard_caches;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index c37aa80383..49042eb8c1 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3974,3 +3974,56 @@ ANALYZE analyze_table;
-- cleanup
DROP FOREIGN TABLE analyze_ftable;
DROP TABLE analyze_table;
+
+-- ===================================================================
+-- test for postgres_fdw_verify_foreign_servers function
+-- ===================================================================
+
+-- Disable debug_discard_caches in order to manage remote connections
+SET debug_discard_caches TO '0';
+
+-- -- The text of the error might vary across platforms, so only show SQLSTATE.
+\set VERBOSITY sqlstate
+
+-- Disconnect once and set application_name to an arbitrary value
+SELECT 1 FROM postgres_fdw_disconnect_all();
+ALTER SERVER loopback OPTIONS (SET application_name 'healthcheck');
+
+DO $$
+DECLARE
+ can_verify boolean;
+ result boolean;
+BEGIN
+ PERFORM 1 FROM ft1 LIMIT 1;
+
+ -- Terminate the remote backend process
+ PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
+ WHERE application_name = 'healthcheck';
+
+ -- Check whether we can do health check on this platform
+ SELECT INTO can_verify postgres_fdw_can_verify_connection_states();
+
+ -- If the checking can be done on this platform, call it
+ IF can_verify IS TRUE THEN
+ -- Set client_min_messages to ERROR temporary because the following
+ -- function only throws a WARNING on the supported platform.
+ SET LOCAL client_min_messages TO ERROR;
+
+ SELECT INTO result postgres_fdw_verify_connection_states('loopback');
+
+ RESET client_min_messages;
+ ELSE
+ result = false;
+ END IF;
+
+ -- If result is FALSE, we succeeded to detect the disconnection or it could
+ -- not be done on this platform. Raise an message.
+ IF result IS FALSE THEN
+ RAISE INFO 'postgres_fdw_verify_connection_states() could detect the disconnection, or health check cannot be used on this platform';
+ END IF;
+END;
+$$;
+
+-- Clean up
+\set VERBOSITY default
+RESET debug_discard_caches;
--
2.27.0
view thread (19+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: [Proposal] Add foreign-server health checks infrastructure
In-Reply-To: <TYAPR01MB5866413814861A5016839506F5FC9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox