Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR: syntax error at or near "$" .

On 30/07/2018 17:00, Tom Lane wrote:
> Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
>> On 30/07/2018 16:51, Tom Lane wrote:
>>> Hm, that's not happening for me:
>> You (and Adrian) are right. This is due to our own tweaking (which I had forgotten). Sorry for the false alarm.
> It looks like your hack was to work around the bug that was fixed
> properly in 742869946. You should be able to undo that now ...
Hello Tom,
On pgsql 10.4 , I reverted (undid) this patch/hack :

root(at)smadev:/usr/local/src/postgresql-10.4# diff -u ./src/backend/utils/misc/guc.c ./src/backend/utils/misc/guc.c.hack
--- ./src/backend/utils/misc/guc.c      2018-08-01 16:22:30.901629000 +0300
+++ ./src/backend/utils/misc/guc.c.hack 2018-08-01 15:45:15.893348000 +0300
@@ -3266,7 +3266,7 @@
                {"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_LIST_INPUT | GUC_REPORT
                },
                &namespace_search_path,
                "\"$user\", public",

And (i.e. with stock 10.4) I tested with pgbouncer again in transaction mode, and still, (i.e. without GUC_REPORT) , pgbouncer client does not get from server and does not store the search path after
the first server connection, and therefore, after server disconnects , the second time the client tries to connect it does not set the search_path .
I tested with :
GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_REPORT
as well, but then I get the re-quote problem I was telling about :
At first connection the search path is set correctly : bdynacom,epaybdynacom,epay,"$user", public
but after server disconnection and re-connection the search path becomes : "bdynacom,epaybdynacom,epay,""$user"", public"
which is wrong.

So in order to retain all the benefits of transaction mode in pgbouncer I had to re-apply the first patch/hack, at the expense of the nuisance at pg_dumpall and search paths.

>
> regards, tom lane
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt