Here's the patch with a somewhat expanded / improved commit message.
Jelte, can you take a look there's no silly mistake?
As mentioned previously, I plan to push this, so that if the protocol
improvements from [1] don't land in PG18 we have at least something. I
did take a brief look at the other thread, but it's hard to predict.
[1]
https://www.postgresql.org/message-id/CAGECzQTg2hcmb5GaU53uuWcdC7gCNJFLL6mnW0WNhWHgq9UTgw%40mail.gmail.com
regards
--
Tomas Vondra
From ce6705f5b851dae584a0b5929d1eaefa4adeff3f Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <jelte.fenn...@microsoft.com>
Date: Wed, 14 Aug 2024 17:28:48 +0200
Subject: [PATCH v20240814] Mark search_path as GUC_REPORT
Report search_path changes to the client. It's common for multi-tenant
applications to map tenants to separate schemas, and use search_path to
pick the tenant a given connection works with. Unfortunately, this
breaks when a connection pool (like PgBouncer) is introduced, because
the search_path may change unexpectedly.
Ideally, clients could specify which GUCs are interesting and should be
subject to this reporting. But we don't have that capability yet, and
GUC_REPORT is what connection pools use to track interesting changes.
There are other GUCs we might want reported (e.g. various timeouts), but
search_path is by far the biggest foot gun that can lead either to
puzzling failures during query execution (when objects are missing or
are defined differently), or even to accessing incorrect data.
Many existing tools modify search_path, pg_dump being a notable example.
When this change was initially proposed in 2014, one of the concerns was
impact on performance. But this was addressed by commit 2432b1a04087,
which ensures we report each GUC at most once per query, no matter how
many times it changed during execution.
Eventually, this might be replaced / superseded by allowing doing this
by making the protocol extensible in this direction, but it's unclear
when (or if) that happens. Until then, we can leverage GUC_REPORT.
Author: Alexander Kukushkin, Jelte Fennema-Nio
Discussion: https://postgr.es/m/CAFh8B=k8s7wrcqhafmyhdn1+e5lvzzi_qaydq8bkvrgjtah...@mail.gmail.com
---
src/backend/utils/misc/guc_tables.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 79ecaa4c4c2..af227b1f248 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -4331,7 +4331,7 @@ struct config_string ConfigureNamesString[] =
{"search_path", PGC_USERSET, CLIENT_CONN_STATEMENT,
gettext_noop("Sets the schema search order for names that are not schema-qualified."),
NULL,
- GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_EXPLAIN
+ GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_EXPLAIN | GUC_REPORT
},
&namespace_search_path,
"\"$user\", public",
--
2.45.2
