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@matrix.gatewaynet.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@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) ,
pgbouncerclient 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
doesnot 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
expenseof the nuisance at pg_dumpall and search paths.
 


>
>             regards, tom lane
>

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