public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: 'Fujii Masao' <[email protected]>
Cc: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Cc: 'Kyotaro Horiguchi' <[email protected]>
Subject: RE: [Proposal] Add foreign-server health checks infrastructure
Date: Wed, 21 Sep 2022 11:56:56 +0000
Message-ID: <TYAPR01MB58661CD72B69848FBE0CD466F54F9@TYAPR01MB5866.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<TYAPR01MB58661B088B6066282824ADD9F5369@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<[email protected]>
<TYAPR01MB58664E79C4728A6FFEA46F94F53B9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<[email protected]>
<TYAPR01MB5866FF28C802916242F1B492F53B9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<TYAPR01MB5866C6278B6386672B35F624F53B9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<[email protected]>
<TYAPR01MB5866FC683843ED8BD09505FEF53D9@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<TYAPR01MB5866982FC21ABE5DF3C1E3A2F5059@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<[email protected]>
Dear Fujii-san,
Thanks for checking!
> These failed to be applied to the master branch cleanly. Could you update them?
PSA rebased patches. I reviewed my myself and they contain changes.
E.g., move GUC-related code to option.c.
> + this option relies on kernel events exposed by Linux, macOS,
>
> s/this/This
Fixed.
>
> + GUC_check_errdetail("pgfdw_health_check_interval must be set
> to 0 on this platform");
>
> The actual parameter name "postgres_fdw.health_check_interval"
> should be used for the message instead of internal variable name.
Fixed.
> This registered signal handler does lots of things. But that's not acceptable
> and they should be performed outside signal handler. No?
I modified like v09 or earlier versions, which has a mechanism for registering CheckingRemoteServersCallback.
It had been removed because we want to keep core simpler, but IIUC it is needed
if the signal handler just sets some flags.
The core-side does not consider the current status of transaction and running query for simpleness.
Best Regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v15-0001-Add-an-infrastracture-for-checking-remote-server.patch (7.9K, ../TYAPR01MB58661CD72B69848FBE0CD466F54F9@TYAPR01MB5866.jpnprd01.prod.outlook.com/2-v15-0001-Add-an-infrastracture-for-checking-remote-server.patch)
download | inline diff:
From adca9c34d807d37b3226ea83800f0d6187586b73 Mon Sep 17 00:00:00 2001
From: "kuroda.hayato%40jp.fujitsu.com" <[email protected]>
Date: Wed, 21 Sep 2022 06:02:45 +0000
Subject: [PATCH v15 1/4] Add an infrastracture for checking remote servers
This patch adds a mechanism for registering callback functions.
They should be used for checking health of remote servers.
These functions will be called when an added flag is set to true.
The flag is expected that it is set from signal handlers, which is registered by FDWs.
Inside the function a signal SIGINT should be raised and a message should be set to QueryCancelMessage
if one of remote servers is disconnected.
When a query is canceled and a string is set to QueryCancelMessage,
the server will output the given message to the log instead of the normal message.
Note that QueryCancelMessage will be never pfree()'d.
Developers must use appropriate memory context.
---
src/backend/foreign/foreign.c | 57 ++++++++++++++++++++++++++++++++
src/backend/tcop/postgres.c | 26 +++++++++++++++
src/backend/utils/init/globals.c | 1 +
src/include/foreign/foreign.h | 19 +++++++++++
src/include/miscadmin.h | 2 ++
src/include/tcop/tcopprot.h | 1 +
6 files changed, 106 insertions(+)
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index 353e20a0cf..fbc943bea9 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -28,7 +28,9 @@
#include "utils/rel.h"
#include "utils/syscache.h"
#include "utils/varlena.h"
+#include "utils/timeout.h"
+static CheckingRemoteServersCallbackItem *remote_check_callbacks = NULL;
/*
* GetForeignDataWrapper - look up the foreign-data wrapper by OID.
@@ -810,3 +812,58 @@ GetExistingLocalJoinPath(RelOptInfo *joinrel)
}
return NULL;
}
+
+
+/*
+ * Register callbacks for checking remote servers.
+ *
+ * This function is intended for use by FDW extensions.
+ */
+void
+RegisterCheckingRemoteServersCallback(CheckingRemoteServersCallback callback,
+ void *arg)
+{
+ CheckingRemoteServersCallbackItem *item;
+
+ item = (CheckingRemoteServersCallbackItem *)
+ MemoryContextAlloc(TopMemoryContext,
+ sizeof(CheckingRemoteServersCallbackItem));
+ item->callback = callback;
+ item->arg = arg;
+ item->next = remote_check_callbacks;
+ remote_check_callbacks = item;
+}
+
+void
+UnRegisterCheckingRemoteServersCallback(CheckingRemoteServersCallback callback,
+ void *arg)
+{
+ CheckingRemoteServersCallbackItem *item;
+ CheckingRemoteServersCallbackItem *prev;
+
+ prev = NULL;
+ for (item = remote_check_callbacks; item; prev = item, item = item->next)
+ {
+ if (item->callback == callback && item->arg == arg)
+ {
+ if (prev)
+ prev->next = item->next;
+ else
+ remote_check_callbacks = item->next;
+ pfree(item);
+ break;
+ }
+ }
+}
+
+/*
+ * Call callbacks for checking remote servers.
+ */
+void
+CallCheckingRemoteServersCallbacks(void)
+{
+ CheckingRemoteServersCallbackItem *item;
+
+ for (item = remote_check_callbacks; item; item = item->next)
+ item->callback(item->arg);
+}
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 35eff28bd3..772a388a87 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -35,6 +35,7 @@
#include "commands/async.h"
#include "commands/prepare.h"
#include "common/pg_prng.h"
+#include "foreign/foreign.h"
#include "jit/jit.h"
#include "libpq/libpq.h"
#include "libpq/pqformat.h"
@@ -98,6 +99,9 @@ int PostAuthDelay = 0;
/* Time between checks that the client is still connected. */
int client_connection_check_interval = 0;
+/* Message string for canceling qurery caused by extensions */
+char *QueryCancelMessage = NULL;
+
/* ----------------
* private typedefs etc
* ----------------
@@ -3224,6 +3228,13 @@ ProcessInterrupts(void)
errmsg("connection to client lost")));
}
+ if (CheckingRemoteServersTimeoutPending)
+ {
+ CheckingRemoteServersTimeoutPending = false;
+
+ CallCheckingRemoteServersCallbacks();
+ }
+
/*
* If a recovery conflict happens while we are waiting for input from the
* client, the client is presumably just sitting idle in a transaction,
@@ -3328,8 +3339,20 @@ ProcessInterrupts(void)
LockErrorCleanup();
ereport(ERROR,
(errcode(ERRCODE_QUERY_CANCELED),
+ QueryCancelMessage ?
+ errmsg("%s", QueryCancelMessage) :
errmsg("canceling statement due to user request")));
}
+ else if (QueryCancelMessage != NULL)
+ {
+ /*
+ * If we reach here someone wanted to cancel query but it was skepped
+ * because connection status was idle. So re-arm Pending flags
+ * for next iteration.
+ */
+ InterruptPending = true;
+ QueryCancelPending = true;
+ }
}
if (IdleInTransactionSessionTimeoutPending)
@@ -4264,6 +4287,9 @@ PostgresMain(const char *dbname, const char *username)
/* Report the error to the client and/or server log */
EmitErrorReport();
+ /* Make sure QueryCancelMessage is reset. */
+ QueryCancelMessage = NULL;
+
/*
* Make sure debug_query_string gets reset before we possibly clobber
* the storage it points at.
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 1a5d29ac9b..bb94adfea8 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -37,6 +37,7 @@ volatile sig_atomic_t IdleSessionTimeoutPending = false;
volatile sig_atomic_t ProcSignalBarrierPending = false;
volatile sig_atomic_t LogMemoryContextPending = false;
volatile sig_atomic_t IdleStatsUpdateTimeoutPending = false;
+volatile sig_atomic_t CheckingRemoteServersTimeoutPending = false;
volatile uint32 InterruptHoldoffCount = 0;
volatile uint32 QueryCancelHoldoffCount = 0;
volatile uint32 CritSectionCount = 0;
diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h
index ac82125530..9859513ac6 100644
--- a/src/include/foreign/foreign.h
+++ b/src/include/foreign/foreign.h
@@ -82,4 +82,23 @@ extern List *GetForeignColumnOptions(Oid relid, AttrNumber attnum);
extern Oid get_foreign_data_wrapper_oid(const char *fdwname, bool missing_ok);
extern Oid get_foreign_server_oid(const char *servername, bool missing_ok);
+
+/* Functions and variables for fdw checking */
+typedef void (*CheckingRemoteServersCallback) (void *arg);
+
+typedef struct CheckingRemoteServersCallbackItem CheckingRemoteServersCallbackItem;
+
+struct CheckingRemoteServersCallbackItem
+{
+ CheckingRemoteServersCallbackItem *next;
+ CheckingRemoteServersCallback callback;
+ void *arg;
+};
+
+extern void RegisterCheckingRemoteServersCallback(CheckingRemoteServersCallback callback,
+ void *arg);
+extern void UnRegisterCheckingRemoteServersCallback(CheckingRemoteServersCallback callback,
+ void *arg);
+extern void CallCheckingRemoteServersCallbacks(void);
+
#endif /* FOREIGN_H */
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index ee48e392ed..95b7d66ce3 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -99,6 +99,8 @@ extern PGDLLIMPORT volatile sig_atomic_t IdleStatsUpdateTimeoutPending;
extern PGDLLIMPORT volatile sig_atomic_t CheckClientConnectionPending;
extern PGDLLIMPORT volatile sig_atomic_t ClientConnectionLost;
+extern PGDLLIMPORT volatile sig_atomic_t CheckingRemoteServersTimeoutPending;
+
/* these are marked volatile because they are examined by signal handlers: */
extern PGDLLIMPORT volatile uint32 InterruptHoldoffCount;
extern PGDLLIMPORT volatile uint32 QueryCancelHoldoffCount;
diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h
index 5d34978f32..3a8e8c5a19 100644
--- a/src/include/tcop/tcopprot.h
+++ b/src/include/tcop/tcopprot.h
@@ -30,6 +30,7 @@ extern PGDLLIMPORT const char *debug_query_string;
extern PGDLLIMPORT int max_stack_depth;
extern PGDLLIMPORT int PostAuthDelay;
extern PGDLLIMPORT int client_connection_check_interval;
+extern PGDLLIMPORT char* QueryCancelMessage;
/* GUC-configurable parameters */
--
2.27.0
[application/octet-stream] v15-0002-postgres_fdw-Implement-health-check-feature.patch (9.5K, ../TYAPR01MB58661CD72B69848FBE0CD466F54F9@TYAPR01MB5866.jpnprd01.prod.outlook.com/3-v15-0002-postgres_fdw-Implement-health-check-feature.patch)
download | inline diff:
From 87a482835635070347cc1c3fdf6847a4a54e5b55 Mon Sep 17 00:00:00 2001
From: "kuroda.hayato%40jp.fujitsu.com" <[email protected]>
Date: Wed, 21 Sep 2022 06:30:56 +0000
Subject: [PATCH v15 2/4] postgres_fdw: Implement health check feature
This patch adds a new GUC parameter postgres_fdw.health_check_interval.
This defines a time interval between checking remote servers.
In the checking function we use a socket event WL_SOCKET_CLOSED.
---
contrib/postgres_fdw/connection.c | 130 ++++++++++++++++++++++++++++
contrib/postgres_fdw/option.c | 63 ++++++++++++++
contrib/postgres_fdw/postgres_fdw.h | 3 +
3 files changed, 196 insertions(+)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 939d114f02..64ec57b54e 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -23,12 +23,14 @@
#include "postgres_fdw.h"
#include "storage/fd.h"
#include "storage/latch.h"
+#include "tcop/tcopprot.h"
#include "utils/builtins.h"
#include "utils/datetime.h"
#include "utils/hsearch.h"
#include "utils/inval.h"
#include "utils/memutils.h"
#include "utils/syscache.h"
+#include "utils/timeout.h"
/*
* Connection cache hash table entry
@@ -80,6 +82,9 @@ static unsigned int prep_stmt_number = 0;
/* tracks whether any work is needed in callback functions */
static bool xact_got_connection = false;
+/* Timeout identifier for health check */
+TimeoutId pgfdw_health_check_timeout = MAX_TIMEOUTS;
+
/*
* SQL functions
*/
@@ -117,6 +122,11 @@ static void pgfdw_finish_pre_subcommit_cleanup(List *pending_entries,
static bool UserMappingPasswordRequired(UserMapping *user);
static bool disconnect_cached_connections(Oid serverid);
+/* Functions for checking remote servers */
+static void pgfdw_connection_check(void *arg);
+static bool pgfdw_connection_check_internal(PGconn *conn);
+static void pgfdw_checking_remote_servers_timeout_handler(void);
+
/*
* Get a PGconn which can be used to execute queries on the remote PostgreSQL
* server with the user's authorization. A new connection is established
@@ -160,6 +170,11 @@ GetConnection(UserMapping *user, bool will_prep_stmt, PgFdwConnState **state)
pgfdw_inval_callback, (Datum) 0);
CacheRegisterSyscacheCallback(USERMAPPINGOID,
pgfdw_inval_callback, (Datum) 0);
+
+ /* Register a timeout and a callback for checking remote servers */
+ pgfdw_health_check_timeout = RegisterTimeout(USER_TIMEOUT,
+ pgfdw_checking_remote_servers_timeout_handler);
+ RegisterCheckingRemoteServersCallback(pgfdw_connection_check, NULL);
}
/* Set flag that we did GetConnection during the current transaction */
@@ -283,6 +298,12 @@ GetConnection(UserMapping *user, bool will_prep_stmt, PgFdwConnState **state)
if (state)
*state = &entry->state;
+ /* Start health-check timer if needed */
+ if (pgfdw_health_check_interval > 0 &&
+ !get_timeout_active(pgfdw_health_check_timeout))
+ enable_timeout_after(pgfdw_health_check_timeout,
+ pgfdw_health_check_interval);
+
return entry->conn;
}
@@ -1040,6 +1061,9 @@ pgfdw_xact_callback(XactEvent event, void *arg)
*/
xact_got_connection = false;
+ /* Stop timer because checking is no more needed. */
+ disable_timeout(pgfdw_health_check_timeout, false);
+
/* Also reset cursor numbering for next transaction */
cursor_number = 0;
}
@@ -1862,3 +1886,109 @@ disconnect_cached_connections(Oid serverid)
return result;
}
+
+/*
+ * Signal handler for calling callbacks
+ */
+static void
+pgfdw_checking_remote_servers_timeout_handler(void)
+{
+ CheckingRemoteServersTimeoutPending = true;
+ InterruptPending = true;
+ SetLatch(MyLatch);
+}
+
+/*
+ * Function for checking remote servers.
+ *
+ * This function searches the hash table from the beginning
+ * and performs a health-check on each entry.
+ *
+ * Raise SIGINT if someone might be down, otherwise do nothing.
+ */
+static void
+pgfdw_connection_check(void *arg)
+{
+ HASH_SEQ_STATUS scan;
+ ConnCacheEntry *entry;
+
+ Assert(ConnectionHash);
+
+ /*
+ * checking will be done by waiting WL_SOCKET_CLOSED event,
+ * so exit immediately if it cannot be used in this system.
+ */
+ if (!WaitEventSetCanReportClosed())
+ return;
+
+ /* Quick exit if QueryCancelMessage has already set. */
+ if (QueryCancelMessage != NULL)
+ return;
+
+ hash_seq_init(&scan, ConnectionHash);
+ while ((entry = (ConnCacheEntry *) hash_seq_search(&scan)))
+ {
+ if (entry->conn == NULL || entry->xact_depth == 0)
+ continue;
+ if (!pgfdw_connection_check_internal(entry->conn))
+ {
+ /*
+ * Foreign server might be down, so raise SIGINT.
+ * Note that error message is passed to QueryCancelMessage
+ * for reporting error in ProcessInterrupts().
+ */
+ char msg[31 + MAXDATELEN];
+ MemoryContext old;
+ ForeignServer *server;
+
+ /*
+ * Switch to CurTransactionContext in order to
+ * make sure that the lifetime of palloc'd is transaction.
+ */
+ old = MemoryContextSwitchTo(CurTransactionContext);
+ server = GetForeignServer(entry->serverid);
+ snprintf(msg, sizeof(msg), "Foreign Server %s might be down.", server->servername);
+ QueryCancelMessage = pstrdup(msg);
+ MemoryContextSwitchTo(old);
+
+ disconnect_pg_server(entry);
+ hash_seq_term(&scan);
+
+ raise(SIGINT);
+ break;
+ }
+ }
+
+ /* re-schedule timer if needed. */
+ if (pgfdw_health_check_interval > 0)
+ enable_timeout_after(pgfdw_health_check_timeout,
+ pgfdw_health_check_interval);
+
+ return;
+}
+
+/*
+ * Helper function for pgfdw_connection_check
+ */
+static bool
+pgfdw_connection_check_internal(PGconn *conn)
+{
+ WaitEventSet *eventset;
+ WaitEvent events;
+
+ Assert(WaitEventSetCanReportClosed());
+
+ eventset = CreateWaitEventSet(CurrentMemoryContext, 1);
+ AddWaitEventToSet(eventset, WL_SOCKET_CLOSED, PQsocket(conn), NULL, NULL);
+
+ WaitEventSetWait(eventset, 0, &events, 1, 0);
+
+ if (events.events & WL_SOCKET_CLOSED)
+ {
+ FreeWaitEventSet(eventset);
+ return false;
+ }
+ FreeWaitEventSet(eventset);
+
+ return true;
+}
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index fa80ee2a55..9cd5a32e77 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -20,6 +20,7 @@
#include "commands/extension.h"
#include "libpq/libpq-be.h"
#include "postgres_fdw.h"
+#include "storage/latch.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/varlena.h"
@@ -50,6 +51,7 @@ static PQconninfoOption *libpq_options;
* GUC parameters
*/
char *pgfdw_application_name = NULL;
+int pgfdw_health_check_interval;
/*
* Helper functions
@@ -58,6 +60,13 @@ static void InitPgFdwOptions(void);
static bool is_valid_option(const char *keyword, Oid context);
static bool is_libpq_option(const char *keyword);
+/*
+ * GUC hooks
+ */
+static bool check_pgfdw_health_check_interval(int *newval, void **extra,
+ GucSource source);
+static void assign_pgfdw_health_check_interval(int newval, void *extra);
+
#include "miscadmin.h"
/*
@@ -518,6 +527,47 @@ process_pgfdw_appname(const char *appname)
return buf.data;
}
+/*
+ * Check hook for pgfdw_health_check_interval
+ */
+static bool
+check_pgfdw_health_check_interval(int *newval, void **extra, GucSource source)
+{
+ if (!WaitEventSetCanReportClosed() && *newval != 0)
+ {
+ GUC_check_errdetail("postgres_fdw.health_check_interval must be set to 0 on this platform");
+ return false;
+ }
+ return true;
+}
+
+/*
+ * Assign hook for pgfdw_health_check_interval
+ */
+static void
+assign_pgfdw_health_check_interval(int newval, void *extra)
+{
+ /* Quick return if timeout is not registered yet. */
+ if (pgfdw_health_check_timeout == MAX_TIMEOUTS)
+ return;
+
+ if (get_timeout_active(pgfdw_health_check_timeout))
+ {
+ if (newval == 0)
+ disable_timeout(pgfdw_health_check_timeout, false);
+
+ /*
+ * we don't have to do anything because
+ * new value will be used in pgfdw_connection_check().
+ */
+ return;
+ }
+
+ /* Start timeout if wants to */
+ if (newval > 0)
+ enable_timeout_after(pgfdw_health_check_timeout, newval);
+}
+
/*
* Module load callback
*/
@@ -543,5 +593,18 @@ _PG_init(void)
NULL,
NULL);
+ DefineCustomIntVariable("postgres_fdw.health_check_interval",
+ "Sets the time interval between checks of remote servers.",
+ NULL,
+ &pgfdw_health_check_interval,
+ 0,
+ 0,
+ INT_MAX,
+ PGC_USERSET,
+ GUC_UNIT_MS,
+ check_pgfdw_health_check_interval,
+ assign_pgfdw_health_check_interval,
+ NULL);
+
MarkGUCPrefixReserved("postgres_fdw");
}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 21f2b20ce8..6b13544903 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -18,6 +18,7 @@
#include "libpq-fe.h"
#include "nodes/execnodes.h"
#include "nodes/pathnodes.h"
+#include "utils/timeout.h"
#include "utils/relcache.h"
/*
@@ -151,6 +152,7 @@ extern PGresult *pgfdw_exec_query(PGconn *conn, const char *query,
PgFdwConnState *state);
extern void pgfdw_report_error(int elevel, PGresult *res, PGconn *conn,
bool clear, const char *sql);
+extern TimeoutId pgfdw_health_check_timeout;
/* in option.c */
extern int ExtractConnectionOptions(List *defelems,
@@ -160,6 +162,7 @@ extern List *ExtractExtensionList(const char *extensionsString,
bool warnOnMissing);
extern char *process_pgfdw_appname(const char *appname);
extern char *pgfdw_application_name;
+extern int pgfdw_health_check_interval;
/* in deparse.c */
extern void classifyConditions(PlannerInfo *root,
--
2.27.0
[application/octet-stream] v15-0003-add-doc.patch (1.8K, ../TYAPR01MB58661CD72B69848FBE0CD466F54F9@TYAPR01MB5866.jpnprd01.prod.outlook.com/4-v15-0003-add-doc.patch)
download | inline diff:
From 4ffb4293db58cdc30af7962db22d95df40331d42 Mon Sep 17 00:00:00 2001
From: "kuroda.hayato%40jp.fujitsu.com" <[email protected]>
Date: Wed, 21 Sep 2022 06:47:55 +0000
Subject: [PATCH v15 3/4] add doc
This patch adds descriptions about postgres_fdw.health_check_interval
---
doc/src/sgml/postgres-fdw.sgml | 26 ++++++++++++++++++++++++++
1 file changed, 26 insertions(+)
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index bfd344cdc0..44e4f07dd9 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -1078,6 +1078,32 @@ postgres=# SELECT postgres_fdw_disconnect_all();
</listitem>
</varlistentry>
+ <varlistentry id="guc-pgfdw-health-check-interval" xreflabel="postgres_fdw.health_check_interval">
+ <term>
+ <varname>postgres_fdw.health_check_interval</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>postgres_fdw.health_check_interval</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the time interval between optional checks that remote servers
+ are still alive. When losing a remote connection is detected,
+ the running transaction is aborted. This feature is performed
+ by polling the socket.
+ </para>
+ <para>
+ This option relies on kernel events exposed by Linux, macOS,
+ illumos and the BSD family of operating systems, and is not currently available
+ on other systems.
+ </para>
+ <para>
+ If the value is specified without units, it is taken as milliseconds.
+ The default value is <literal>0</literal>, which disables connection
+ checks.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
--
2.27.0
[application/octet-stream] v15-0004-add-test.patch (3.1K, ../TYAPR01MB58661CD72B69848FBE0CD466F54F9@TYAPR01MB5866.jpnprd01.prod.outlook.com/5-v15-0004-add-test.patch)
download | inline diff:
From 5572c8468059a10dac58e74f7961528ab68f1013 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 v15 4/4] add test
---
.../postgres_fdw/expected/postgres_fdw.out | 36 +++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 26 ++++++++++++++
2 files changed, 62 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2e4e82a94f..476e4966cc 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11463,3 +11463,39 @@ SELECT * FROM prem2;
ALTER SERVER loopback OPTIONS (DROP parallel_commit);
ALTER SERVER loopback2 OPTIONS (DROP parallel_commit);
+-- ===================================================================
+-- test for health-check feature
+-- ===================================================================
+-- Disable debug_discard_caches in order to manage remote connections
+SET debug_discard_caches TO '0';
+-- 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');
+-- Set GUC for checking the health of remote servers
+SET postgres_fdw.health_check_interval TO '1s';
+BEGIN;
+SELECT 1 FROM ft1 LIMIT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+-- Terminate the remote backend process
+SELECT pg_terminate_backend(pid, 180000) FROM pg_stat_activity
+ WHERE application_name = 'healthcheck';
+ pg_terminate_backend
+----------------------
+ t
+(1 row)
+
+-- While sleeping the process down will be detected.
+SELECT pg_sleep(3);
+ERROR: Foreign Server loopback might be down.
+COMMIT;
+-- Clean up
+RESET debug_discard_caches;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e48ccd286b..1e1f30fc6c 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3682,3 +3682,29 @@ SELECT * FROM prem2;
ALTER SERVER loopback OPTIONS (DROP parallel_commit);
ALTER SERVER loopback2 OPTIONS (DROP parallel_commit);
+
+-- ===================================================================
+-- test for health-check feature
+-- ===================================================================
+
+-- Disable debug_discard_caches in order to manage remote connections
+SET debug_discard_caches TO '0';
+
+-- 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');
+
+-- Set GUC for checking the health of remote servers
+SET postgres_fdw.health_check_interval TO '1s';
+
+BEGIN;
+SELECT 1 FROM ft1 LIMIT 1;
+-- Terminate the remote backend process
+SELECT pg_terminate_backend(pid, 180000) FROM pg_stat_activity
+ WHERE application_name = 'healthcheck';
+-- While sleeping the process down will be detected.
+SELECT pg_sleep(3);
+COMMIT;
+
+-- Clean up
+RESET debug_discard_caches;
--
2.27.0
view thread (47+ 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]
Subject: RE: [Proposal] Add foreign-server health checks infrastructure
In-Reply-To: <TYAPR01MB58661CD72B69848FBE0CD466F54F9@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