Обсуждение: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR:syntax error at or near "$" .
ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR:syntax error at or near "$" .
От
Achilleas Mantzios
Дата:
This is with PostgreSQL 10.4. How to reproduce : postgres@smadev:~% psql psql (10.4) Type "help" for help. Alter the role for search path : dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public; ALTER ROLE dynacom=# Verify : postgres@smadev:~% psql -U amura3 Password for user amura3: psql (10.4) Type "help" for help. dynacom=> show search_path ; search_path --------------------------- $user, amuragents, public (1 row) dynacom=> pg_dumpall's output : ALTER ROLE amura3 SET search_path TO $user, amuragents, public; psql -f pg_dumpall_out.sql : dynacom=# ALTER ROLE amura3 SET search_path TO $user, amuragents, public; ERROR: syntax error at or near "$" Is this a bug or am I missing something ? -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
On 07/30/2018 05:57 AM, Achilleas Mantzios wrote: > This is with PostgreSQL 10.4. > How to reproduce : > postgres@smadev:~% psql > psql (10.4) > Type "help" for help. > > Alter the role for search path : > dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public; > ALTER ROLE > dynacom=# > > Verify : > postgres@smadev:~% psql -U amura3 > Password for user amura3: > psql (10.4) > Type "help" for help. > > dynacom=> show search_path ; > search_path > --------------------------- > $user, amuragents, public > (1 row) > > dynacom=> > > pg_dumpall's output : > ALTER ROLE amura3 SET search_path TO $user, amuragents, public; > > psql -f pg_dumpall_out.sql : > dynacom=# ALTER ROLE amura3 SET search_path TO $user, amuragents, public; > ERROR: syntax error at or near "$" Well the above does not have $user double quoted. Was that hand entered or was that like that in the pg_dumpall_out.sql file? > > Is this a bug or am I missing something ? I could not replicate: create role sp_test; alter role sp_test SET search_path TO "$user", public; pg_dumpall -g -U postgres > sp_test.sql In sp_test.sql CREATE ROLE sp_test; ALTER ROLE sp_test SET search_path TO "$user", public; drop role sp_test ; psql -d test -U postgres -f sp_test.sql \du sp_test | Cannot login | {} \drds List of settings Role | Database | Settings ---------+----------+----------------------------- sp_test | | search_path="$user", public > -- Adrian Klaver adrian.klaver@aklaver.com
Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR:syntax error at or near "$" .
От
Achilleas Mantzios
Дата:
On 30/07/2018 16:23, Adrian Klaver wrote: > On 07/30/2018 05:57 AM, Achilleas Mantzios wrote: >> This is with PostgreSQL 10.4. >> How to reproduce : >> postgres@smadev:~% psql >> psql (10.4) >> Type "help" for help. >> >> Alter the role for search path : >> dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public; >> ALTER ROLE >> dynacom=# >> >> Verify : >> postgres@smadev:~% psql -U amura3 >> Password for user amura3: >> psql (10.4) >> Type "help" for help. >> >> dynacom=> show search_path ; >> search_path >> --------------------------- >> $user, amuragents, public >> (1 row) >> >> dynacom=> >> >> pg_dumpall's output : >> ALTER ROLE amura3 SET search_path TO $user, amuragents, public; >> >> psql -f pg_dumpall_out.sql : >> dynacom=# ALTER ROLE amura3 SET search_path TO $user, amuragents, public; >> ERROR: syntax error at or near "$" > > Well the above does not have $user double quoted. Was that hand entered or was that like that in the pg_dumpall_out.sqlfile? > >> >> Is this a bug or am I missing something ? > > I could not replicate: You are absolutely right, I apologize for the noise :( I had forgotten that we had to tweak src/backend/utils/misc/guc.c in order for search_path to work with pgbouncer in transactionmode. --- /usr/local/src/postgresql-10.4/src/backend/utils/misc/guc.c.orig 2018-05-07 23:51:40.000000000 +0300 +++ /usr/local/src/postgresql-10.4/src/backend/utils/misc/guc.c 2018-06-14 16:07:29.832476000 +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", With GUC_LIST_QUOTE it kept re-quoting and it broke the app. Without GUC_REPORT it was losing the search_path. > > create role sp_test; > > alter role sp_test SET search_path TO "$user", public; > > pg_dumpall -g -U postgres > sp_test.sql > > In sp_test.sql > > CREATE ROLE sp_test; > > ALTER ROLE sp_test SET search_path TO "$user", public; > > drop role sp_test ; > > psql -d test -U postgres -f sp_test.sql > > > \du > > sp_test | Cannot login | {} > > > \drds > List of settings > Role | Database | Settings > ---------+----------+----------------------------- > sp_test | | search_path="$user", public > > >> > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > This is with PostgreSQL 10.4. > How to reproduce : > dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public; > ALTER ROLE > ... > dynacom=> show search_path ; > search_path > --------------------------- > $user, amuragents, public > (1 row) Hm, that's not happening for me: regression=# alter user joe set search_path to "$user", public; ... regression=> show search_path ; search_path ----------------- "$user", public (1 row) and then pg_dumpall produces ALTER ROLE joe SET search_path TO "$user", public; There was a relevant bug fix in March (commit 742869946) ... are you certain this is a 10.4 server, and not 10.3 or older? regards, tom lane
Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR:syntax error at or near "$" .
От
Achilleas Mantzios
Дата:
On 30/07/2018 16:51, Tom Lane wrote: > Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: >> This is with PostgreSQL 10.4. >> How to reproduce : >> dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public; >> ALTER ROLE >> ... >> dynacom=> show search_path ; >> search_path >> --------------------------- >> $user, amuragents, public >> (1 row) > 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. > > regression=# alter user joe set search_path to "$user", public; > ... > regression=> show search_path ; > search_path > ----------------- > "$user", public > (1 row) > > and then pg_dumpall produces > > ALTER ROLE joe SET search_path TO "$user", public; > > There was a relevant bug fix in March (commit 742869946) ... > are you certain this is a 10.4 server, and not 10.3 or older? > > regards, tom lane > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
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 ... regards, tom lane
Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR:syntax error at or near "$" .
От
Achilleas Mantzios
Дата:
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
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes: > On 30/07/2018 17:00, Tom Lane wrote: >> 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 ... > On pgsql 10.4 , I reverted (undid) this patch/hack : > - GUC_LIST_INPUT | GUC_LIST_QUOTE > + GUC_LIST_INPUT | GUC_REPORT What I meant was you should be able to undo the removal of GUC_LIST_QUOTE. The other change is independent, and it seems to be about working around a bug in pgbouncer. You ought to discuss that with the pgbouncer folk. > 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. This seems like it might be a bug in pgbouncer as well; or at least an oversight, in that it's not expecting any GUC_REPORT parameters to need GUC_LIST_QUOTE treatment. regards, tom lane