From bc3880ad2ef1a76448c10cf9c6dc9a1b00cd659d Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Tue, 15 Jul 2025 22:38:25 +0530
Subject: [PATCH v2] Add custom PQsetNoticeProcessor handlers for dblink,
 postgres_fdw, and subscribers

This patch introduces a custom notice processor for libpq-based connections in
dblink, postgres_fdw, and logical replication subscribers. The notice processor
captures messages from remote PostgreSQL servers and routes them through
ereport(), making them visible in local logs with a prefix making it
easy for diagnosis.
---
 contrib/dblink/dblink.c                       | 29 +++++++++++++++++++
 contrib/postgres_fdw/connection.c             | 26 +++++++++++++++++
 .../libpqwalreceiver/libpqwalreceiver.c       | 26 +++++++++++++++++
 3 files changed, 81 insertions(+)

diff --git a/contrib/dblink/dblink.c b/contrib/dblink/dblink.c
index 8a0b112a7ff..f80c5a196f2 100644
--- a/contrib/dblink/dblink.c
+++ b/contrib/dblink/dblink.c
@@ -177,6 +177,30 @@ xpstrdup(const char *in)
 	return pstrdup(in);
 }
 
+/*
+ * notice_processor
+ *
+ * Custom notice processor for libpq connections used by dblink.
+ * This function captures NOTICE messages sent by the remote server and
+ * redirects them to PostgreSQL's logging facility using ereport().
+ *
+ * Strips trailing newline characters from messages for cleaner logs.
+ */
+static void
+notice_processor(void *arg, const char *message)
+{
+	/* Trim trailing newline for cleaner logs */
+	size_t		len = strlen(message);
+
+	if (len > 0 && message[len - 1] == '\n')
+		ereport(LOG,
+				errmsg("Received message from remote server: %.*s",
+					   (int) (len - 1), message));
+	else
+		ereport(LOG,
+				errmsg("Received message from remote server: %s", message));
+}
+
 pg_noreturn static void
 dblink_res_internalerror(PGconn *conn, PGresult *res, const char *p2)
 {
@@ -240,6 +264,9 @@ dblink_get_conn(char *conname_or_str,
 					 errmsg("could not establish connection"),
 					 errdetail_internal("%s", msg)));
 		}
+
+		PQsetNoticeProcessor(conn, notice_processor, NULL);
+
 		dblink_security_check(conn, NULL, connstr);
 		if (PQclientEncoding(conn) != GetDatabaseEncoding())
 			PQsetClientEncoding(conn, GetDatabaseEncodingName());
@@ -338,6 +365,8 @@ dblink_connect(PG_FUNCTION_ARGS)
 				 errdetail_internal("%s", msg)));
 	}
 
+	PQsetNoticeProcessor(conn, notice_processor, NULL);
+
 	/* check password actually used if not superuser */
 	dblink_security_check(conn, connname, connstr);
 
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 304f3c20f83..53df8fab394 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -472,6 +472,30 @@ pgfdw_security_check(const char **keywords, const char **values, UserMapping *us
 			 errhint("Target server's authentication method must be changed or password_required=false set in the user mapping attributes.")));
 }
 
+/*
+ * notice_processor
+ *
+ * Custom notice processor for libpq connections used by postgres_fdw.
+ * This function captures NOTICE messages sent by the remote server and
+ * redirects them to PostgreSQL's logging facility using ereport().
+ *
+ * Strips trailing newline characters from messages for cleaner logs.
+ */
+static void
+notice_processor(void *arg, const char *message)
+{
+	/* Trim trailing newline for cleaner logs */
+	size_t		len = strlen(message);
+
+	if (len > 0 && message[len - 1] == '\n')
+		ereport(LOG,
+				errmsg("Received message from remote server: %.*s",
+					   (int) (len - 1), message));
+	else
+		ereport(LOG,
+				errmsg("Received message from remote server: %s", message));
+}
+
 /*
  * Connect to remote server using specified server and user mapping properties.
  */
@@ -625,6 +649,8 @@ connect_pg_server(ForeignServer *server, UserMapping *user)
 							server->servername),
 					 errdetail_internal("%s", pchomp(PQerrorMessage(conn)))));
 
+		PQsetNoticeProcessor(conn, notice_processor, NULL);
+
 		/* Perform post-connection security checks. */
 		pgfdw_security_check(keywords, values, user, conn);
 
diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
index f7b5d093681..40979287799 100644
--- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
+++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
@@ -127,6 +127,30 @@ _PG_init(void)
 	WalReceiverFunctions = &PQWalReceiverFunctions;
 }
 
+/*
+ * notice_processor
+ *
+ * Custom notice processor for libpq connections used by subscriber.
+ * This function captures NOTICE messages sent by the publisher and
+ * redirects them to logging facility using ereport().
+ *
+ * Strips trailing newline characters from messages for cleaner logs.
+ */
+static void
+notice_processor(void *arg, const char *message)
+{
+	/* Trim trailing newline for cleaner logs */
+	size_t		len = strlen(message);
+
+	if (len > 0 && message[len - 1] == '\n')
+		ereport(LOG,
+				errmsg("Received message from publisher: %.*s",
+					   (int) (len - 1), message));
+	else
+		ereport(LOG,
+				errmsg("Received message from publisher: %s", message));
+}
+
 /*
  * Establish the connection to the primary server.
  *
@@ -232,6 +256,8 @@ libpqrcv_connect(const char *conninfo, bool replication, bool logical,
 				 errhint("Target server's authentication method must be changed, or set password_required=false in the subscription parameters.")));
 	}
 
+	PQsetNoticeProcessor(conn->streamConn, notice_processor, NULL);
+
 	/*
 	 * Set always-secure search path for the cases where the connection is
 	 * used to run SQL queries, so malicious users can't get control.
-- 
2.43.0