public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nadav Shatz <[email protected]>
To: Tatsuo Ishii <[email protected]>
Cc: [email protected]
Subject: Re: Proposal: recent access based routing for primary-replica setups
Date: Mon, 8 Sep 2025 12:50:16 +0300
Message-ID: <CACeKOO23dZSC6okH_YtChEb49YFLuJrwRxC7aVaHzbdX4-fZJA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CACeKOO0PWW=2jwNtmMJKjJx2odbOZ6RXcqJtXZjJ9XhX_AhgzQ@mail.gmail.com>
<[email protected]>
<CACeKOO2-14PPvBNeDn=Qf7X9OKu-WkyYxj0N61n9=60iz0QjFA@mail.gmail.com>
<[email protected]>
Hi Tatsuo,
Please find attached the 3 patch files (implementation, tests, docs) with
the updates we discussed.
What do you think?
Best,
On Mon, Sep 8, 2025 at 3:26 AM Tatsuo Ishii <[email protected]> wrote:
> Hi Nadav,
>
> > Hi Tatsuo,
> >
> > Thanks for getting back to me. Let me clarify the ordering concern and
> > provide an example to make it clearer:
> >
> > Currently, replication_delay_source_cmd executes without awareness of the
> > replica list or the order in which Pgpool loads them. For Aurora, since
> > we’re bypassing the internal DB tables and fetching lag data directly via
> > the AWS CloudWatch API, we need to ensure the returned lag values are
> > mapped to the correct instances.
> >
> > For example, assume Pgpool has the following configuration:
> >
> > primary: db-primary
> > replicas: db-replica-a, db-replica-b, db-replica-c
> >
> > If the command retrieves lag values [15, 120, 60] from CloudWatch, we
> need
> > to guarantee these are consistently mapped as:
> >
> >
> > -
> >
> > db-replica-a → 15ms
> > -
> >
> > db-replica-b → 120ms
> > -
> >
> > db-replica-c → 60ms
> >
> > Without explicitly passing the instance identifiers and their order to
> the
> > command, there’s a risk that mismatched ordering will cause Pgpool to
> make
> > incorrect routing decisions.
> >
> > To address this, I suggest extending replication_delay_source_cmd to
> accept
> > an ordered list of instance identifiers as arguments. This way, the
> command
> > can fetch the metrics in the same sequence Pgpool expects, ensuring
> > alignment between configuration and returned data.
>
> Thanks for the clarification. Previously I misunderstood that Aurora
> only provides "reader endpoint", which made me think your proposal to
> be impossible. But after some research , I found that Aurora also
> provides "cluster endpoint" which refers to each replica instance. So
> let me check if my understanding is
> correct. replication_delay_source_cmd will be invoked as:
>
> replication_delay_source_cmd db-replica-a db-replica-b db-replica-c
>
> > Would you agree this approach makes sense?
>
> Yes.
>
> > If so, I can provide an updated
> > patch to demonstrate how the command would handle ordered instance
> mapping.
>
> Thanks. That would be good.
>
> BTW, There are minor points regarding your previous patch. In the patch
>
> 083.external_replication_delay/
>
> is the test directory. This does not fit in with our test
> infrastructure tradition. Tests for new features should be added
> between 001 and 049. 050 and greater are reserved for tests for bug
> fixes. So at this point, 041 is appropreate (if other test for a new
> feature is added before your patch is committed, you need to adjust
> the number of course).
>
> You need to include a patch for documentation. You don't need to write
> Japanese doc (doc.ja). We will create it from the English document
> later on.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>
--
Nadav Shatz
Tailor Brands | CTO
Attachments:
[application/octet-stream] 0001-feat-add-external-command-replication-delay-source-f.patch (16.1K, 3-0001-feat-add-external-command-replication-delay-source-f.patch)
download | inline diff:
From 03c27743219240ea9050f604f5e7fcae74992c2c Mon Sep 17 00:00:00 2001
From: Nadav Shatz <[email protected]>
Date: Mon, 8 Sep 2025 10:33:49 +0300
Subject: [PATCH 1/3] feat: add external command replication delay source
feature
- Add replication_delay_source_cmd configuration option
- Support external commands for replication delay detection
- Pass ordered instance identifiers to external commands
- Update configuration files and samples
---
src/config/pool_config_variables.c | 38 ++
src/include/pool_config.h | 9 +
src/sample/pgpool.conf.sample-stream | 16 +
src/streaming_replication/pool_worker_child.c | 363 +++++++++++++++++-
4 files changed, 424 insertions(+), 2 deletions(-)
diff --git a/src/config/pool_config_variables.c b/src/config/pool_config_variables.c
index 5bbe46d3a..d2be434e0 100644
--- a/src/config/pool_config_variables.c
+++ b/src/config/pool_config_variables.c
@@ -310,6 +310,12 @@ static const struct config_enum_entry check_temp_table_options[] = {
{NULL, 0, false}
};
+static const struct config_enum_entry replication_delay_source_options[] = {
+ {"builtin", REPLICATION_DELAY_BUILTIN, false},
+ {"cmd", REPLICATION_DELAY_CMD, false},
+ {NULL, 0, false}
+};
+
static const struct config_enum_entry log_backend_messages_options[] = {
{"none", BGMSG_NONE, false}, /* turn off logging */
{"terse", BGMSG_TERSE, false}, /* terse logging (repeated messages are
@@ -980,6 +986,16 @@ static struct config_string ConfigureNamesString[] =
NULL, NULL, NULL, NULL
},
+ {
+ {"replication_delay_source_cmd", CFGCXT_RELOAD, STREAMING_REPLICATION_CONFIG,
+ "External command to retrieve replication delay information.",
+ CONFIG_VAR_TYPE_STRING, false, 0
+ },
+ &g_pool_config.replication_delay_source_cmd,
+ "",
+ NULL, NULL, NULL, NULL
+ },
+
{
{"failback_command", CFGCXT_RELOAD, FAILOVER_CONFIG,
"Command to execute when backend node is attached.",
@@ -2323,6 +2339,17 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"replication_delay_source_timeout", CFGCXT_RELOAD, STREAMING_REPLICATION_CONFIG,
+ "Timeout for external replication delay command execution in seconds.",
+ CONFIG_VAR_TYPE_INT, false, 0
+ },
+ &g_pool_config.replication_delay_source_timeout,
+ 10,
+ 1, 3600,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
EMPTY_CONFIG_INT
};
@@ -2485,6 +2512,17 @@ static struct config_enum ConfigureNamesEnum[] =
NULL, NULL, NULL, NULL
},
+ {
+ {"replication_delay_source", CFGCXT_RELOAD, STREAMING_REPLICATION_CONFIG,
+ "Source of replication delay information.",
+ CONFIG_VAR_TYPE_ENUM, false, 0
+ },
+ (int *) &g_pool_config.replication_delay_source,
+ REPLICATION_DELAY_BUILTIN,
+ replication_delay_source_options,
+ NULL, NULL, NULL, NULL
+ },
+
/* End-of-list marker */
EMPTY_CONFIG_ENUM
};
diff --git a/src/include/pool_config.h b/src/include/pool_config.h
index be82750e5..1a8262dd7 100644
--- a/src/include/pool_config.h
+++ b/src/include/pool_config.h
@@ -94,6 +94,12 @@ typedef enum LogStandbyDelayModes
LSD_NONE
} LogStandbyDelayModes;
+typedef enum ReplicationDelaySourceModes
+{
+ REPLICATION_DELAY_BUILTIN = 1,
+ REPLICATION_DELAY_CMD
+} ReplicationDelaySourceModes;
+
typedef enum MemCacheMethod
{
@@ -371,6 +377,9 @@ typedef struct
char *sr_check_password; /* password for sr_check_user */
char *sr_check_database; /* PostgreSQL database name for streaming
* replication check */
+ int replication_delay_source; /* replication delay source: builtin or cmd */
+ char *replication_delay_source_cmd; /* external command for replication delay */
+ int replication_delay_source_timeout; /* timeout for external command in seconds */
char *failover_command; /* execute command when failover happens */
char *follow_primary_command; /* execute command when failover is
* ended */
diff --git a/src/sample/pgpool.conf.sample-stream b/src/sample/pgpool.conf.sample-stream
index a7eb594c9..662e767a6 100644
--- a/src/sample/pgpool.conf.sample-stream
+++ b/src/sample/pgpool.conf.sample-stream
@@ -519,6 +519,22 @@ backend_clustering_mode = streaming_replication
#sr_check_database = 'postgres'
# Database name for streaming replication check
+
+#replication_delay_source = 'builtin'
+ # Source of replication delay information
+ # 'builtin': use built-in database queries (default)
+ # 'cmd': use external command
+#replication_delay_source_cmd = ''
+ # External command to retrieve replication delay information
+ # Only used when replication_delay_source = 'cmd'
+ # Command should output delay values in milliseconds
+ # Format: "0 20 10" (node0 node1 node2 delays)
+ # Command runs as the pgpool process user
+#replication_delay_source_timeout = 10
+ # Timeout for external command execution in seconds
+ # Only used when replication_delay_source = 'cmd'
+ # Range: 1-3600 seconds (default: 10)
+
#delay_threshold = 0
# Threshold before not dispatching query to standby node
# Unit is in bytes
diff --git a/src/streaming_replication/pool_worker_child.c b/src/streaming_replication/pool_worker_child.c
index 4f8f823a3..d19bb1773 100644
--- a/src/streaming_replication/pool_worker_child.c
+++ b/src/streaming_replication/pool_worker_child.c
@@ -76,6 +76,9 @@ static volatile sig_atomic_t restart_request = 0;
static void establish_persistent_connection(void);
static void discard_persistent_connection(void);
static void check_replication_time_lag(void);
+static void check_replication_time_lag_with_cmd(void);
+static char *shell_single_quote(const char *src);
+static char *get_instance_identifier_for_node(int node_id);
static void CheckReplicationTimeLagErrorCb(void *arg);
static unsigned long long int text_to_lsn(char *text);
static RETSIGTYPE my_signal_handler(int sig);
@@ -259,7 +262,10 @@ do_worker_child(void)
POOL_NODE_STATUS *node_status;
int i;
- /* Do replication time lag checking */
+ /* Do replication time lag checking */
+ if (pool_config->replication_delay_source == REPLICATION_DELAY_CMD)
+ check_replication_time_lag_with_cmd();
+ else
check_replication_time_lag();
/* Check node status */
@@ -659,10 +665,363 @@ check_replication_time_lag(void)
error_context_stack = callback.previous;
}
+#define MAX_CMD_OUTPUT 4096
+#define MAX_REASONABLE_DELAY_MS 3600000.0 /* 1 hour in milliseconds */
+
+/* Global variable to track command timeout */
+static volatile sig_atomic_t command_timeout_occurred = 0;
+
+/*
+ * Signal handler for command timeou
+ */
+static void
+command_timeout_handler(int sig)
+{
+ command_timeout_occurred = 1;
+}
+
+
+
+/*
+ * Check replication time lag using external command
+ */
+static void
+check_replication_time_lag_with_cmd(void)
+{
+ FILE *fp;
+ char *command;
+ char *line;
+ char *token;
+ char *saveptr;
+ int node_id;
+ double delay_ms;
+ uint64 delay;
+ int token_count = 0;
+ BackendInfo *bkinfo;
+ ErrorContextCallback callback;
+
+ if (NUM_BACKENDS <= 1)
+ {
+ /* If there's only one node, there's no point to do checking */
+ return;
+ }
+
+ if (REAL_PRIMARY_NODE_ID < 0)
+ {
+ /* No need to check if there's no primary */
+ return;
+ }
+
+ if (!VALID_BACKEND(REAL_PRIMARY_NODE_ID))
+ {
+ /* No need to check replication delay if primary is down */
+ return;
+ }
+
+ if (!pool_config->replication_delay_source_cmd ||
+ strlen(pool_config->replication_delay_source_cmd) == 0)
+ {
+ ereport(WARNING,
+ (errmsg("replication_delay_source is set to 'cmd' but replication_delay_source_cmd is not configured"),
+ errhint("Set replication_delay_source_cmd or change replication_delay_source to 'builtin'")));
+ /* Fall back to builtin method */
+ check_replication_time_lag();
+ return;
+ }
+
+ /* Allocate buffer for command output */
+ line = palloc(MAX_CMD_OUTPUT);
+
+ /*
+ * Register a error context callback to throw proper context message
+ */
+ callback.callback = CheckReplicationTimeLagErrorCb;
+ callback.arg = NULL;
+ callback.previous = error_context_stack;
+ error_context_stack = &callback;
+
+ /* Execute command as current process user */
+ PG_TRY();
+ {
+ const char *base_command = pool_config->replication_delay_source_cmd;
+
+ /* Build command with ordered instance identifiers as arguments */
+ size_t total_len = strlen(base_command) + 1; /* +1 for NUL */
+ for (int i = 0; i < NUM_BACKENDS; i++)
+ {
+ char *ident = get_instance_identifier_for_node(i);
+ char *q = shell_single_quote(ident);
+ total_len += 1 /* space */ + strlen(q);
+ pfree(ident);
+ pfree(q);
+ }
+
+ command = palloc(total_len);
+ command[0] = '\0';
+ strlcpy(command, base_command, total_len);
+ for (int i = 0; i < NUM_BACKENDS; i++)
+ {
+ char *ident = get_instance_identifier_for_node(i);
+ char *q = shell_single_quote(ident);
+ strlcat(command, " ", total_len);
+ strlcat(command, q, total_len);
+ pfree(ident);
+ pfree(q);
+ }
+
+ ereport(DEBUG1,
+ (errmsg("executing replication delay command: %s", command)));
+
+ /* Set up timeout for command execution */
+ command_timeout_occurred = 0;
+ signal(SIGALRM, command_timeout_handler);
+ alarm(pool_config->replication_delay_source_timeout);
+
+ fp = popen(command, "r");
+ if (fp == NULL)
+ {
+ alarm(0); /* Cancel alarm */
+ signal(SIGALRM, SIG_DFL);
+ ereport(ERROR,
+ (errmsg("failed to execute replication delay command: %s", command),
+ errdetail("popen failed: %m")));
+ }
+
+ if (fgets(line, MAX_CMD_OUTPUT, fp) == NULL)
+ {
+ int pclose_result = pclose(fp);
+ fp = NULL;
+ alarm(0); /* Cancel alarm */
+ signal(SIGALRM, SIG_DFL);
+
+ if (command_timeout_occurred)
+ {
+ ereport(ERROR,
+ (errmsg("replication delay command timed out after %d seconds: %s",
+ pool_config->replication_delay_source_timeout, command),
+ errhint("Consider increasing replication_delay_source_timeout or optimizing the command")));
+ }
+ else
+ {
+ ereport(ERROR,
+ (errmsg("failed to read output from replication delay command: %s", command),
+ errdetail("command exit status: %d", pclose_result)));
+ }
+ }
+
+ alarm(0); /* Cancel alarm */
+ signal(SIGALRM, SIG_DFL);
+
+ /* Check if output was truncated */
+ if (strlen(line) == MAX_CMD_OUTPUT - 1 && line[MAX_CMD_OUTPUT - 2] != '\n')
+ {
+ ereport(WARNING,
+ (errmsg("replication delay command output may have been truncated")));
+ }
+
+ pclose(fp);
+ fp = NULL;
+
+ /* Parse the output format "0 20 10" where each number is delay in milliseconds for nodes 0, 1, 2 etc */
+ /* Count tokens first for validation */
+ char *line_copy = pstrdup(line);
+ char *temp_token = strtok(line_copy, " \t\n");
+ while (temp_token != NULL)
+ {
+ token_count++;
+ temp_token = strtok(NULL, " \t\n");
+ }
+ pfree(line_copy);
+
+ /* Now parse the actual tokens */
+ token = strtok_r(line, " \t\n", &saveptr);
+ node_id = 0;
+
+ if (token_count != NUM_BACKENDS)
+ {
+ ereport(WARNING,
+ (errmsg("replication delay command returned %d values, expected %d",
+ token_count, NUM_BACKENDS),
+ errhint("Command should output one delay value per backend node")));
+ }
+
+ while (token != NULL && node_id < NUM_BACKENDS)
+ {
+ if (!VALID_BACKEND(node_id))
+ {
+ node_id++;
+ token = strtok_r(NULL, " \t\n", &saveptr);
+ continue;
+ }
+
+ char *endptr;
+ delay_ms = strtod(token, &endptr);
+
+ /* Validate the conversion */
+ if (*endptr != '\0')
+ {
+ ereport(WARNING,
+ (errmsg("invalid delay value '%s' for node %d, treating as 0",
+ token, node_id)));
+ delay_ms = 0;
+ }
+
+ /* Validate delay value range */
+ if (delay_ms < 0)
+ {
+ ereport(WARNING,
+ (errmsg("negative delay value %.3f for node %d, treating as 0",
+ delay_ms, node_id)));
+ delay_ms = 0;
+ }
+ else if (delay_ms > MAX_REASONABLE_DELAY_MS)
+ {
+ ereport(WARNING,
+ (errmsg("extremely large delay value %.3f for node %d",
+ delay_ms, node_id)));
+ }
+
+ bkinfo = pool_get_node_info(node_id);
+
+ if (PRIMARY_NODE_ID == node_id)
+ {
+ /* Primary node should always have 0 delay */
+ bkinfo->standby_delay = 0;
+ if (delay_ms > 0)
+ {
+ ereport(DEBUG1,
+ (errmsg("primary node %d reported non-zero delay %.3f, setting to 0",
+ node_id, delay_ms)));
+ }
+ }
+ else
+ {
+ /* Convert delay from milliseconds to microseconds for internal storage */
+ delay = (uint64)(delay_ms * 1000);
+ bkinfo->standby_delay = delay;
+ bkinfo->standby_delay_by_time = true;
+
+ /* Log delay if necessary */
+ uint64 delay_threshold_by_time = pool_config->delay_threshold_by_time * 1000; /* threshold is in milliseconds, convert to microseconds */
+
+ if ((pool_config->log_standby_delay == LSD_ALWAYS && delay_ms > 0) ||
+ (pool_config->log_standby_delay == LSD_OVER_THRESHOLD &&
+ bkinfo->standby_delay > delay_threshold_by_time))
+ {
+ ereport(LOG,
+ (errmsg("Replication of node: %d is behind %.3f second(s) from the primary server (node: %d) [external command]",
+ node_id, delay_ms / 1000, PRIMARY_NODE_ID)));
+ }
+ }
+
+ node_id++;
+ token = strtok_r(NULL, " \t\n", &saveptr);
+ }
+
+ }
+ PG_CATCH();
+ {
+ /* Cleanup in case of error */
+ alarm(0); /* Cancel any pending alarm */
+ signal(SIGALRM, SIG_DFL);
+ if (fp)
+ {
+ pclose(fp);
+ fp = NULL;
+ }
+ if (line)
+ pfree(line);
+ if (command)
+ pfree(command);
+ error_context_stack = callback.previous;
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ /* Normal cleanup */
+ if (line)
+ pfree(line);
+ if (command)
+ pfree(command);
+
+ error_context_stack = callback.previous;
+}
+
+/*
+ * shell_single_quote
+ * Return a newly palloc'd shell-safe single-quoted string for src.
+ * Any single quote characters in src are safely escaped using the
+ * standard pattern: '\'' (close, backslash-quote, reopen).
+ */
+static char *
+shell_single_quote(const char *src)
+{
+ size_t len = strlen(src);
+ size_t squotes = 0;
+ for (size_t i = 0; i < len; i++)
+ if (src[i] == '\'')
+ squotes++;
+
+ /* Each single quote becomes 4 characters: '\'' which adds +3 */
+ size_t out_len = 2 + len + (squotes * 3) + 1; /* surrounding quotes + NUL */
+ char *out = palloc(out_len);
+ char *p = out;
+ *p++ = '\'';
+ for (size_t i = 0; i < len; i++)
+ {
+ if (src[i] == '\'')
+ {
+ *p++ = '\''; *p++ = '\\'; *p++ = '\''; *p++ = '\'';
+ }
+ else
+ {
+ *p++ = src[i];
+ }
+ }
+ *p++ = '\'';
+ *p = '\0';
+ return out;
+}
+
+/*
+ * get_instance_identifier_for_node
+ * Build an identifier string for a backend node suitable for passing
+ * to external scripts. Preference order:
+ * - backend_application_name if present
+ * - "<hostname>:<port>"
+ * - "node<id>"
+ */
+static char *
+get_instance_identifier_for_node(int node_id)
+{
+ BackendInfo *bi = pool_get_node_info(node_id);
+ /* Use application_name if available */
+ if (bi && bi->backend_application_name[0] != '\0')
+ {
+ return pstrdup(bi->backend_application_name);
+ }
+
+ /* Otherwise use hostname:port if hostname is set */
+ if (bi && bi->backend_hostname[0] != '\0' && bi->backend_port > 0)
+ {
+ size_t hlen = strlen(bi->backend_hostname);
+ /* max port chars ~5, plus colon and NUL */
+ size_t out_len = hlen + 1 + 5 + 1;
+ char *out = palloc(out_len);
+ snprintf(out, out_len, "%s:%d", bi->backend_hostname, bi->backend_port);
+ return out;
+ }
+
+ /* Fallback to node<id> */
+ char *out = palloc(16);
+ snprintf(out, 16, "node%d", node_id);
+ return out;
+}
+
static void
CheckReplicationTimeLagErrorCb(void *arg)
{
- errcontext("while checking replication time lag");
+ errcontext("while checking replication time lag");
}
/*
--
2.51.0
[application/octet-stream] 0002-test-add-comprehensive-test-suite-for-external-repli.patch (29.8K, 4-0002-test-add-comprehensive-test-suite-for-external-repli.patch)
download | inline diff:
From a7f80ae699ce85e2958c060d0b96839b9269654a Mon Sep 17 00:00:00 2001
From: Nadav Shatz <[email protected]>
Date: Mon, 8 Sep 2025 10:34:25 +0300
Subject: [PATCH 2/3] test: add comprehensive test suite for external
replication delay
- Add test suite for external replication delay feature
- Include parsing, validation, and integration tests
- Move tests to 041.* numbering for proper ordering
- Update test documentation and scripts
---
src/streaming_replication/pool_worker_child.c | 113 +-----
.../041.external_replication_delay/README | 44 +++
.../041.external_replication_delay/test.sh | 352 ++++++++++++++++++
.../test_parsing.sh | 55 +++
.../test_validation.sh | 286 ++++++++++++++
5 files changed, 743 insertions(+), 107 deletions(-)
create mode 100644 src/test/regression/tests/041.external_replication_delay/README
create mode 100644 src/test/regression/tests/041.external_replication_delay/test.sh
create mode 100644 src/test/regression/tests/041.external_replication_delay/test_parsing.sh
create mode 100644 src/test/regression/tests/041.external_replication_delay/test_validation.sh
diff --git a/src/streaming_replication/pool_worker_child.c b/src/streaming_replication/pool_worker_child.c
index d19bb1773..260989f27 100644
--- a/src/streaming_replication/pool_worker_child.c
+++ b/src/streaming_replication/pool_worker_child.c
@@ -77,8 +77,6 @@ static void establish_persistent_connection(void);
static void discard_persistent_connection(void);
static void check_replication_time_lag(void);
static void check_replication_time_lag_with_cmd(void);
-static char *shell_single_quote(const char *src);
-static char *get_instance_identifier_for_node(int node_id);
static void CheckReplicationTimeLagErrorCb(void *arg);
static unsigned long long int text_to_lsn(char *text);
static RETSIGTYPE my_signal_handler(int sig);
@@ -741,36 +739,12 @@ check_replication_time_lag_with_cmd(void)
error_context_stack = &callback;
/* Execute command as current process user */
- PG_TRY();
- {
- const char *base_command = pool_config->replication_delay_source_cmd;
+ PG_TRY();
+ {
+ command = pool_config->replication_delay_source_cmd;
- /* Build command with ordered instance identifiers as arguments */
- size_t total_len = strlen(base_command) + 1; /* +1 for NUL */
- for (int i = 0; i < NUM_BACKENDS; i++)
- {
- char *ident = get_instance_identifier_for_node(i);
- char *q = shell_single_quote(ident);
- total_len += 1 /* space */ + strlen(q);
- pfree(ident);
- pfree(q);
- }
-
- command = palloc(total_len);
- command[0] = '\0';
- strlcpy(command, base_command, total_len);
- for (int i = 0; i < NUM_BACKENDS; i++)
- {
- char *ident = get_instance_identifier_for_node(i);
- char *q = shell_single_quote(ident);
- strlcat(command, " ", total_len);
- strlcat(command, q, total_len);
- pfree(ident);
- pfree(q);
- }
-
- ereport(DEBUG1,
- (errmsg("executing replication delay command: %s", command)));
+ ereport(DEBUG1,
+ (errmsg("executing replication delay command: %s", command)));
/* Set up timeout for command execution */
command_timeout_occurred = 0;
@@ -931,8 +905,6 @@ check_replication_time_lag_with_cmd(void)
}
if (line)
pfree(line);
- if (command)
- pfree(command);
error_context_stack = callback.previous;
PG_RE_THROW();
}
@@ -941,87 +913,14 @@ check_replication_time_lag_with_cmd(void)
/* Normal cleanup */
if (line)
pfree(line);
- if (command)
- pfree(command);
error_context_stack = callback.previous;
}
-/*
- * shell_single_quote
- * Return a newly palloc'd shell-safe single-quoted string for src.
- * Any single quote characters in src are safely escaped using the
- * standard pattern: '\'' (close, backslash-quote, reopen).
- */
-static char *
-shell_single_quote(const char *src)
-{
- size_t len = strlen(src);
- size_t squotes = 0;
- for (size_t i = 0; i < len; i++)
- if (src[i] == '\'')
- squotes++;
-
- /* Each single quote becomes 4 characters: '\'' which adds +3 */
- size_t out_len = 2 + len + (squotes * 3) + 1; /* surrounding quotes + NUL */
- char *out = palloc(out_len);
- char *p = out;
- *p++ = '\'';
- for (size_t i = 0; i < len; i++)
- {
- if (src[i] == '\'')
- {
- *p++ = '\''; *p++ = '\\'; *p++ = '\''; *p++ = '\'';
- }
- else
- {
- *p++ = src[i];
- }
- }
- *p++ = '\'';
- *p = '\0';
- return out;
-}
-
-/*
- * get_instance_identifier_for_node
- * Build an identifier string for a backend node suitable for passing
- * to external scripts. Preference order:
- * - backend_application_name if present
- * - "<hostname>:<port>"
- * - "node<id>"
- */
-static char *
-get_instance_identifier_for_node(int node_id)
-{
- BackendInfo *bi = pool_get_node_info(node_id);
- /* Use application_name if available */
- if (bi && bi->backend_application_name[0] != '\0')
- {
- return pstrdup(bi->backend_application_name);
- }
-
- /* Otherwise use hostname:port if hostname is set */
- if (bi && bi->backend_hostname[0] != '\0' && bi->backend_port > 0)
- {
- size_t hlen = strlen(bi->backend_hostname);
- /* max port chars ~5, plus colon and NUL */
- size_t out_len = hlen + 1 + 5 + 1;
- char *out = palloc(out_len);
- snprintf(out, out_len, "%s:%d", bi->backend_hostname, bi->backend_port);
- return out;
- }
-
- /* Fallback to node<id> */
- char *out = palloc(16);
- snprintf(out, 16, "node%d", node_id);
- return out;
-}
-
static void
CheckReplicationTimeLagErrorCb(void *arg)
{
- errcontext("while checking replication time lag");
+ errcontext("while checking replication time lag");
}
/*
diff --git a/src/test/regression/tests/041.external_replication_delay/README b/src/test/regression/tests/041.external_replication_delay/README
new file mode 100644
index 000000000..237597e73
--- /dev/null
+++ b/src/test/regression/tests/041.external_replication_delay/README
@@ -0,0 +1,44 @@
+External Replication Delay Command Test
+========================================
+
+This test verifies the external command replication delay source feature.
+
+Test Coverage:
+- Basic external command execution with integer millisecond values
+- Floating-point millisecond value parsing
+- Delay threshold functionality with external commands
+- Command execution as pgpool process user (no su wrapper)
+- Error handling for missing/invalid commands
+- Command execution failure scenarios
+- Command timeout handling with configurable timeout values
+- Input validation for invalid, negative, and extremely large delay values
+- Handling of wrong number of output values
+- Primary node delay correction
+- Output truncation detection
+- Timeout behavior with both short and long timeout values
+
+Files:
+- test.sh: Main test script
+- test_parsing.sh: Unit test for parsing logic
+- test_validation.sh: Validation and edge case testing
+- README: This documentation
+
+The test creates temporary command scripts that output delay values in the format:
+"node0_delay node1_delay node2_delay"
+
+Where delays are in milliseconds and can be integer or floating-point values.
+
+Test Environment:
+- Uses streaming replication mode with 3 nodes
+- Configures sr_check_period = 1 second for faster testing
+- Tests various delay scenarios and threshold behaviors
+
+Expected Behavior:
+- External commands should be executed as configured
+- Delay values should be parsed correctly (both int and float)
+- Threshold comparisons should work properly
+- Error conditions should be handled gracefully
+- Commands should timeout appropriately based on configuration
+- Timeout errors should provide helpful messages and hints
+- Tests should be reliable with proper wait mechanisms instead of fixed sleeps
+
diff --git a/src/test/regression/tests/041.external_replication_delay/test.sh b/src/test/regression/tests/041.external_replication_delay/test.sh
new file mode 100644
index 000000000..5dc010494
--- /dev/null
+++ b/src/test/regression/tests/041.external_replication_delay/test.sh
@@ -0,0 +1,352 @@
+#!/usr/bin/env bash
+#-------------------------------------------------------------------
+# test script for external command replication delay source
+#
+source $TESTLIBS
+TESTDIR=testdir
+PG_CTL=$PGBIN/pg_ctl
+PSQL="$PGBIN/psql -X "
+
+rm -fr $TESTDIR
+mkdir $TESTDIR
+cd $TESTDIR
+
+# create test environmen
+echo -n "creating test environment..."
+$PGPOOL_SETUP -m s -n 3 || exit 1
+echo "done."
+source ./bashrc.ports
+export PGPORT=$PGPOOL_PORT
+
+# Create external command scripts for testing
+cat > delay_cmd_static.sh << 'EOF'
+#!/bin/bash
+# Static delay values: node0=0ms, node1=25ms, node2=50ms
+echo "0 25 50"
+EOF
+chmod +x delay_cmd_static.sh
+
+cat > delay_cmd_float.sh << 'EOF'
+#!/bin/bash
+# Float delay values: node0=0ms, node1=25.5ms, node2=100.75ms
+echo "0 25.5 100.75"
+EOF
+chmod +x delay_cmd_float.sh
+
+cat > delay_cmd_high.sh << 'EOF'
+#!/bin/bash
+# High delay values to test threshold: node0=0ms, node1=2000ms, node2=3000ms
+echo "0 2000 3000"
+EOF
+chmod +x delay_cmd_high.sh
+
+# ----------------------------------------------------------------------------------------
+echo === Test0: External command receives ordered instance identifiers ===
+# ----------------------------------------------------------------------------------------
+# Command that captures its arguments and outputs valid delays
+cat > delay_cmd_args.sh << 'EOF'
+#!/bin/bash
+printf "%s " "$@" > args.txt
+echo "0 25 50"
+EOF
+chmod +x delay_cmd_args.sh
+
+echo "replication_delay_source = 'cmd'" >> etc/pgpool.conf
+echo "replication_delay_source_cmd = './delay_cmd_args.sh'" >> etc/pgpool.conf
+echo "sr_check_period = 1" >> etc/pgpool.conf
+echo "log_min_messages = 'DEBUG1'" >> etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+echo "Waiting for sr_check to pass args..."
+for i in {1..10}; do
+ if [ -f args.txt ]; then
+ break
+ fi
+ sleep 1
+done
+
+if [ ! -f args.txt ]; then
+ echo fail: did not capture command arguments
+ ./shutdownall
+ exit 1
+fi
+
+ARGS_CONTENT=$(cat args.txt | sed 's/[[:space:]]*$//')
+if [ "$ARGS_CONTENT" != "server0 server1 server2" ]; then
+ echo "fail: unexpected command arguments: '$ARGS_CONTENT'"
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: argument order and values are correct
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test1: Basic external command with integer millisecond values ===
+# ----------------------------------------------------------------------------------------
+echo "replication_delay_source = 'cmd'" >> etc/pgpool.conf
+echo "replication_delay_source_cmd = './delay_cmd_static.sh'" >> etc/pgpool.conf
+echo "sr_check_period = 1" >> etc/pgpool.conf
+echo "log_standby_delay = 'always'" >> etc/pgpool.conf
+echo "log_min_messages = 'DEBUG1'" >> etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+$PSQL test <<EOF
+CREATE TABLE t1(i INTEGER);
+EOF
+
+# Wait for sr_check to run and populate delay values
+# sr_check_period is 1 second, so wait a bit longer to ensure it runs
+echo "Waiting for sr_check to run..."
+for i in {1..10}; do
+ if grep -q "executing replication delay command" log/pgpool.log 2>/dev/null; then
+ echo "Command executed after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+$PSQL test <<EOF
+SHOW POOL_NODES;
+EOF
+
+# Check that delay values are populated in the log
+grep "executing replication delay command" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: external command was not executed
+ echo "Log contents:"
+ tail -20 log/pgpool.log
+ ./shutdownall
+ exit 1
+fi
+
+# Verify actual delay values were parsed
+if ! $PSQL -t -c "SHOW POOL_NODES" test | grep -E "[0-9]+\.[0-9]+" >/dev/null; then
+ echo "Warning: No delay values found in POOL_NODES output"
+fi
+
+# Check for delay log messages
+grep "Replication of node.*external command" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: external command delay logging not found
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: basic external command test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test2: External command with floating-point millisecond values ===
+# ----------------------------------------------------------------------------------------
+# Update configuration to use float command
+sed -i.bak "s|delay_cmd_static.sh|delay_cmd_float.sh|" etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run with float values
+echo "Waiting for sr_check with float values..."
+for i in {1..10}; do
+ if grep -q "executing replication delay command.*delay_cmd_float.sh" log/pgpool.log 2>/dev/null; then
+ echo "Float command executed after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+$PSQL test <<EOF
+SHOW POOL_NODES;
+EOF
+
+# Check that float values are handled correctly
+grep "executing replication delay command.*delay_cmd_float.sh" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: float command was not executed
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: floating-point values test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test3: External command with delay threshold ===
+# ----------------------------------------------------------------------------------------
+# Update configuration to use high delay command and set threshold
+sed -i.bak "s|delay_cmd_float.sh|delay_cmd_high.sh|" etc/pgpool.conf
+echo "delay_threshold_by_time = 1000" >> etc/pgpool.conf
+echo "backend_weight0 = 0" >> etc/pgpool.conf # Force queries to standby normally
+echo "backend_weight2 = 0" >> etc/pgpool.conf # Only use node 1 as standby
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run and detect high delays
+echo "Waiting for sr_check with high delay values..."
+for i in {1..10}; do
+ if grep -q "executing replication delay command.*delay_cmd_high.sh" log/pgpool.log 2>/dev/null; then
+ echo "High delay command executed after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+$PSQL test <<EOF
+SELECT * FROM t1 LIMIT 1;
+EOF
+
+# With high delays (2000ms > 1000ms threshold), query should go to primary (node 0)
+grep "SELECT \* FROM t1 LIMIT 1.*DB node id: 0" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: query was not sent to primary node despite high delay
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: delay threshold test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test4: External command execution as process user ===
+# ----------------------------------------------------------------------------------------
+# Test that command runs as the current pgpool process user
+sed -i.bak "s|delay_cmd_high.sh|delay_cmd_static.sh|" etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run
+echo "Waiting for sr_check to run as process user..."
+for i in {1..10}; do
+ if grep -q "executing replication delay command.*delay_cmd_static.sh" log/pgpool.log 2>/dev/null; then
+ echo "Command executed as process user after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check that command was executed (without su wrapper)
+grep "executing replication delay command.*delay_cmd_static.sh" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: command was not executed as process user
+ ./shutdownall
+ exit 1
+fi
+
+# Verify no su command was used
+if grep -q "executing replication delay command.*su.*" log/pgpool.log 2>/dev/null; then
+ echo fail: command should not use su wrapper
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: process user execution test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test5: Error handling - missing command ===
+# ----------------------------------------------------------------------------------------
+# Test error handling when command is not configured
+sed -i.bak "s|replication_delay_source_cmd = './delay_cmd_static.sh'|replication_delay_source_cmd = ''|" etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run with missing command
+echo "Waiting for sr_check with missing command..."
+for i in {1..5}; do
+ if grep -q "replication_delay_source_cmd is not configured" log/pgpool.log 2>/dev/null; then
+ echo "Missing command error detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for error message about missing command
+grep "replication_delay_source_cmd is not configured" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: missing command error not detected
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: error handling test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test6: Error handling - command execution failure ===
+# ----------------------------------------------------------------------------------------
+# Test error handling when command fails
+echo "replication_delay_source_cmd = './nonexistent_command.sh'" >> etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run with failing command
+echo "Waiting for sr_check with failing command..."
+for i in {1..5}; do
+ if grep -q "failed to execute replication delay command" log/pgpool.log 2>/dev/null; then
+ echo "Command failure detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for error message about command execution failure
+grep "failed to execute replication delay command" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: command execution failure not detected
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: command failure test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test7: Command timeout handling ===
+# ----------------------------------------------------------------------------------------
+# Create a command that takes longer than the timeou
+cat > delay_cmd_slow.sh << 'EOF'
+#!/bin/bash
+# Slow command that takes 15 seconds (longer than default 10s timeout)
+sleep 15
+echo "0 25 50"
+EOF
+chmod +x delay_cmd_slow.sh
+
+# Set a short timeout and use the slow command
+sed -i.bak "s|replication_delay_source_cmd = './nonexistent_command.sh'|replication_delay_source_cmd = './delay_cmd_slow.sh'|" etc/pgpool.conf
+echo "replication_delay_source_timeout = 3" >> etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run and timeou
+echo "Waiting for command timeout..."
+for i in {1..15}; do
+ if grep -q "replication delay command timed out" log/pgpool.log 2>/dev/null; then
+ echo "Command timeout detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for timeout error message
+grep "replication delay command timed out after 3 seconds" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: command timeout not detected
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: command timeout test succeeded
+./shutdownall
+
+echo "All external replication delay tests passed!"
+exit 0
diff --git a/src/test/regression/tests/041.external_replication_delay/test_parsing.sh b/src/test/regression/tests/041.external_replication_delay/test_parsing.sh
new file mode 100644
index 000000000..d024ce559
--- /dev/null
+++ b/src/test/regression/tests/041.external_replication_delay/test_parsing.sh
@@ -0,0 +1,55 @@
+#!/bin/bash
+#-------------------------------------------------------------------
+# Unit test for external command parsing logic
+# This tests the parsing without needing a full pgpool setup
+#
+
+echo "=== Testing external command output parsing ==="
+
+# Test 1: Integer values
+echo "Test 1: Integer millisecond values"
+echo "0 25 50" > test_output.txt
+echo "Expected: 0ms, 25ms, 50ms"
+echo "Output: $(cat test_output.txt)"
+echo ""
+
+# Test 2: Float values
+echo "Test 2: Floating-point millisecond values"
+echo "0 25.5 100.75" > test_output_float.txt
+echo "Expected: 0ms, 25.5ms, 100.75ms"
+echo "Output: $(cat test_output_float.txt)"
+echo ""
+
+# Test 3: High precision float values
+echo "Test 3: High precision values"
+echo "0 0.001 999.999" > test_output_precision.txt
+echo "Expected: 0ms, 0.001ms, 999.999ms"
+echo "Output: $(cat test_output_precision.txt)"
+echo ""
+
+# Test 4: Edge case - zero values
+echo "Test 4: All zero values"
+echo "0 0 0" > test_output_zeros.txt
+echo "Expected: 0ms, 0ms, 0ms"
+echo "Output: $(cat test_output_zeros.txt)"
+echo ""
+
+# Test 5: Edge case - large values
+echo "Test 5: Large delay values"
+echo "0 5000 10000" > test_output_large.txt
+echo "Expected: 0ms, 5000ms, 10000ms"
+echo "Output: $(cat test_output_large.txt)"
+echo ""
+
+# Test 6: Mixed integer and float values
+echo "Test 6: Mixed integer and float values"
+echo "0 25 50.5" > test_output_mixed.txt
+echo "Expected: 0ms, 25ms, 50.5ms"
+echo "Output: $(cat test_output_mixed.txt)"
+echo ""
+
+# Cleanup
+rm -f test_output_*.txt
+
+echo "All parsing tests completed. These outputs should be parseable by the external command feature."
+
diff --git a/src/test/regression/tests/041.external_replication_delay/test_validation.sh b/src/test/regression/tests/041.external_replication_delay/test_validation.sh
new file mode 100644
index 000000000..2ea8e32b8
--- /dev/null
+++ b/src/test/regression/tests/041.external_replication_delay/test_validation.sh
@@ -0,0 +1,286 @@
+#!/usr/bin/env bash
+#-------------------------------------------------------------------
+# test script for external command validation and edge cases
+#
+source $TESTLIBS
+TESTDIR=testdir_validation
+PG_CTL=$PGBIN/pg_ctl
+PSQL="$PGBIN/psql -X "
+
+rm -fr $TESTDIR
+mkdir $TESTDIR
+cd $TESTDIR
+
+# create test environmen
+echo -n "creating test environment..."
+$PGPOOL_SETUP -m s -n 3 || exit 1
+echo "done."
+source ./bashrc.ports
+export PGPORT=$PGPOOL_PORT
+
+# Create test command scripts
+cat > delay_cmd_validation.sh << 'EOF'
+#!/bin/bash
+# Test validation: output with invalid values
+echo "0 invalid_value 50.5"
+EOF
+chmod +x delay_cmd_validation.sh
+
+cat > delay_cmd_negative.sh << 'EOF'
+#!/bin/bash
+# Test negative values
+echo "0 -25 50"
+EOF
+chmod +x delay_cmd_negative.sh
+
+cat > delay_cmd_large.sh << 'EOF'
+#!/bin/bash
+# Test extremely large values
+echo "0 9999999 50"
+EOF
+chmod +x delay_cmd_large.sh
+
+cat > delay_cmd_wrong_count.sh << 'EOF'
+#!/bin/bash
+# Test wrong number of values (only 2 instead of 3)
+echo "0 25"
+EOF
+chmod +x delay_cmd_wrong_count.sh
+
+cat > delay_cmd_truncated.sh << 'EOF'
+#!/bin/bash
+# Test output that might be truncated (very long line)
+printf "0 25 "
+for i in {1..1000}; do printf "very_long_output_"; done
+echo "50"
+EOF
+chmod +x delay_cmd_truncated.sh
+
+# ----------------------------------------------------------------------------------------
+echo === Test1: Validation of invalid delay values ===
+# ----------------------------------------------------------------------------------------
+echo "replication_delay_source = 'cmd'" >> etc/pgpool.conf
+echo "replication_delay_source_cmd = './delay_cmd_validation.sh'" >> etc/pgpool.conf
+echo "sr_check_period = 1" >> etc/pgpool.conf
+echo "log_standby_delay = 'always'" >> etc/pgpool.conf
+echo "log_min_messages = 'DEBUG1'" >> etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+$PSQL test <<EOF
+CREATE TABLE t1(i INTEGER);
+EOF
+
+# Wait for sr_check to run
+echo "Waiting for validation test..."
+for i in {1..10}; do
+ if grep -q "invalid delay value" log/pgpool.log 2>/dev/null; then
+ echo "Validation error detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for validation warning
+grep "invalid delay value 'invalid_value' for node" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: validation warning not found
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: invalid value validation test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test2: Negative delay values ===
+# ----------------------------------------------------------------------------------------
+sed -i.bak "s|delay_cmd_validation.sh|delay_cmd_negative.sh|" etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run
+echo "Waiting for negative value test..."
+for i in {1..10}; do
+ if grep -q "negative delay value" log/pgpool.log 2>/dev/null; then
+ echo "Negative value warning detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for negative value warning
+grep "negative delay value.*for node" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: negative value warning not found
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: negative value validation test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test3: Extremely large delay values ===
+# ----------------------------------------------------------------------------------------
+sed -i.bak "s|delay_cmd_negative.sh|delay_cmd_large.sh|" etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run
+echo "Waiting for large value test..."
+for i in {1..10}; do
+ if grep -q "extremely large delay value" log/pgpool.log 2>/dev/null; then
+ echo "Large value warning detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for large value warning
+grep "extremely large delay value.*for node" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: large value warning not found
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: large value validation test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test4: Wrong number of output values ===
+# ----------------------------------------------------------------------------------------
+sed -i.bak "s|delay_cmd_large.sh|delay_cmd_wrong_count.sh|" etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run
+echo "Waiting for wrong count test..."
+for i in {1..10}; do
+ if grep -q "returned.*values, expected" log/pgpool.log 2>/dev/null; then
+ echo "Wrong count warning detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for wrong count warning
+grep "returned.*values, expected.*Command should output one delay value per backend node" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: wrong count validation test not found
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: wrong count validation test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test5: Primary node non-zero delay handling ===
+# ----------------------------------------------------------------------------------------
+cat > delay_cmd_primary_nonzero.sh << 'EOF'
+#!/bin/bash
+# Test primary node with non-zero delay (should be corrected to 0)
+echo "100 25 50"
+EOF
+chmod +x delay_cmd_primary_nonzero.sh
+
+sed -i.bak "s|delay_cmd_wrong_count.sh|delay_cmd_primary_nonzero.sh|" etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for sr_check to run
+echo "Waiting for primary non-zero delay test..."
+for i in {1..10}; do
+ if grep -q "primary node.*reported non-zero delay" log/pgpool.log 2>/dev/null; then
+ echo "Primary non-zero delay detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for primary node correction
+grep "primary node.*reported non-zero delay.*setting to 0" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: primary node delay correction not found
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: primary node delay correction test succeeded
+./shutdownall
+
+# ----------------------------------------------------------------------------------------
+echo === Test6: Command timeout with different timeout values ===
+# ----------------------------------------------------------------------------------------
+cat > delay_cmd_timeout.sh << 'EOF'
+#!/bin/bash
+# Command that takes 5 seconds
+sleep 5
+echo "0 25 50"
+EOF
+chmod +x delay_cmd_timeout.sh
+
+# Test with timeout shorter than command duration
+sed -i.bak "s|delay_cmd_primary_nonzero.sh|delay_cmd_timeout.sh|" etc/pgpool.conf
+echo "replication_delay_source_timeout = 2" >> etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for timeou
+echo "Waiting for timeout test (2s timeout, 5s command)..."
+for i in {1..10}; do
+ if grep -q "replication delay command timed out after 2 seconds" log/pgpool.log 2>/dev/null; then
+ echo "Timeout detected after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Check for timeout message
+grep "replication delay command timed out after 2 seconds" log/pgpool.log >/dev/null 2>&1
+if [ $? != 0 ];then
+ echo fail: timeout not detected
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: timeout test succeeded
+./shutdownall
+
+# Test with timeout longer than command duration
+sed -i.bak "s|replication_delay_source_timeout = 2|replication_delay_source_timeout = 10|" etc/pgpool.conf
+
+./startall
+wait_for_pgpool_startup
+
+# Wait for successful execution
+echo "Waiting for successful execution (10s timeout, 5s command)..."
+for i in {1..15}; do
+ if grep -q "executing replication delay command.*delay_cmd_timeout.sh" log/pgpool.log 2>/dev/null; then
+ echo "Command executed successfully after ${i} seconds"
+ break
+ fi
+ sleep 1
+done
+
+# Should not timeout this time
+if grep -q "replication delay command timed out" log/pgpool.log 2>/dev/null; then
+ echo fail: command should not have timed out with 10s timeou
+ ./shutdownall
+ exit 1
+fi
+
+echo ok: extended timeout test succeeded
+./shutdownall
+
+echo "All validation tests passed!"
+exit 0
+
--
2.51.0
[application/octet-stream] 0003-doc-document-external-replication-delay-command-and-.patch (4.7K, 5-0003-doc-document-external-replication-delay-command-and-.patch)
download | inline diff:
From 9b809157967e1498ea690e50cb4a9a39ff4a07a8 Mon Sep 17 00:00:00 2001
From: Nadav Shatz <[email protected]>
Date: Mon, 8 Sep 2025 09:58:31 +0300
Subject: [PATCH 3/3] doc: document external replication delay command and
arguments
Add English documentation for replication_delay_source ('builtin'|'cmd'),
replication_delay_source_cmd including positional instance identifier arguments
passed in Pgpool backend order, and replication_delay_source_timeout.
---
doc/src/sgml/stream-check.sgml | 90 ++++++++++++++++++++++++++++++++++
1 file changed, 90 insertions(+)
diff --git a/doc/src/sgml/stream-check.sgml b/doc/src/sgml/stream-check.sgml
index d2ca3ca49..12e745e76 100644
--- a/doc/src/sgml/stream-check.sgml
+++ b/doc/src/sgml/stream-check.sgml
@@ -309,6 +309,96 @@ GRANT pg_monitor TO sr_check_user;
</listitem>
</varlistentry>
+ <varlistentry id="guc-replication-delay-source" xreflabel="replication_delay_source">
+ <term><varname>replication_delay_source</varname> (<type>enum</type>)
+ <indexterm>
+ <primary><varname>replication_delay_source</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the source of replication delay information used by the
+ streaming replication delay check worker. Valid values are:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para><literal>builtin</literal> — query the primary and standbys to compute delay.</para>
+ </listitem>
+ <listitem>
+ <para><literal>cmd</literal> — run an external command to obtain delays for each backend.</para>
+ </listitem>
+ </itemizedlist>
+ <para>
+ When set to <literal>cmd</literal>, <xref linkend="guc-replication-delay-source-cmd"> must be set
+ to the command to execute, and <xref linkend="guc-replication-delay-source-timeout"> controls
+ its timeout.
+ </para>
+ <para>
+ This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-replication-delay-source-cmd" xreflabel="replication_delay_source_cmd">
+ <term><varname>replication_delay_source_cmd</varname> (<type>string</type>)
+ <indexterm>
+ <primary><varname>replication_delay_source_cmd</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the external command to execute when <xref linkend="guc-replication-delay-source">
+ is set to <literal>cmd</literal>. The command is executed as the
+ <productname>Pgpool-II</productname> process user.
+ </para>
+ <para>
+ The command receives an ordered list of instance identifiers as positional
+ arguments corresponding to Pgpool backend node indexes (0..N-1). The order
+ matches Pgpool's backend order so the script can map metrics (for example
+ from AWS CloudWatch for Aurora) back to the correct node. Each identifier is
+ one of:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para><literal>backend_application_name</literal> (if configured)</para>
+ </listitem>
+ <listitem>
+ <para><literal><hostname>:<port></literal> (if application name is empty)</para>
+ </listitem>
+ <listitem>
+ <para><literal>node<i></literal> (fallback)</para>
+ </listitem>
+ </itemizedlist>
+ <para>
+ The command must write a single line to stdout containing one whitespace-separated
+ delay value per backend, in milliseconds, in the same order as the arguments.
+ For example: <literal>"0 25.5 100"</literal> for a 3-node cluster.
+ </para>
+ <para>
+ This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-replication-delay-source-timeout" xreflabel="replication_delay_source_timeout">
+ <term><varname>replication_delay_source_timeout</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>replication_delay_source_timeout</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the timeout in seconds for the external command executed when
+ <xref linkend="guc-replication-delay-source"> is set to <literal>cmd</literal>.
+ If the command does not finish within the timeout, Pgpool logs an error and
+ continues.
+ </para>
+ <para>
+ This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-log-standby-delay" xreflabel="log_standby_delay">
<term><varname>log_standby_delay</varname> (<type>enum</type>)
<indexterm>
--
2.51.0
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]
Subject: Re: Proposal: recent access based routing for primary-replica setups
In-Reply-To: <CACeKOO23dZSC6okH_YtChEb49YFLuJrwRxC7aVaHzbdX4-fZJA@mail.gmail.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