Thread: SET search_path = value
Hi developers!
Just created a ticket concerning a minor error in the reverse engineered
SQL code for roles
Besides the example ...
ALTER ROLE SET search_path = test, public;
--> ALTER DATABASE test SET search_path='test, public'; -- quotes are
wrong
... there are at least two more occurrances:
ALTER FUNCTION ...
ALTER DATABASE ...
_If_ you wanted to quote the schema names, it would have to be quotes
around _each_ schema in the list, like so:
ALTER DATABASE test SET search_path = 'test', 'public';
But that would lead to another pitfall. This does not work as intended:
ALTER DATABASE test SET search_path = '"$user", public';
So, best not to add single quotes at all:
ALTER DATABASE test SET search_path ="$user", public;
The official docs on the matter are also misleading, IMO.
http://www.postgresql.org/docs/8.4/interactive/runtime-config-client.html
I've sent a comment on the website.
Regards
Erwin
Le 19/04/2010 04:43, Erwin Brandstetter a écrit : > Hi developers! > > Just created a ticket concerning a minor error in the reverse engineered > SQL code for roles > > Besides the example ... > ALTER ROLE SET search_path = test, public; > --> ALTER DATABASE test SET search_path='test, public'; -- quotes are > wrong > ... there are at least two more occurrances: > ALTER FUNCTION ... > ALTER DATABASE ... > > _If_ you wanted to quote the schema names, it would have to be quotes > around _each_ schema in the list, like so: > ALTER DATABASE test SET search_path = 'test', 'public'; > But that would lead to another pitfall. This does not work as intended: > ALTER DATABASE test SET search_path = '"$user", public'; > So, best not to add single quotes at all: > ALTER DATABASE test SET search_path ="$user", public; > > The official docs on the matter are also misleading, IMO. > http://www.postgresql.org/docs/8.4/interactive/runtime-config-client.html > I've sent a comment on the website. > pgAdmin shouldn't use quotes on some parameters: search_path, temp_tablespaces. Perhaps others. I don't find a way to pick them automatically. For example, their vartype are "string". Which is quite ambiguous with others "string" parameters like archive_command. Of course, we can use a specific code for them. It would be really easy, but I would prefer to find another way. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Guillaume Lelarge escreveu: > pgAdmin shouldn't use quotes on some parameters: search_path, > temp_tablespaces. Perhaps others. I don't find a way to pick them > automatically. For example, their vartype are "string". Which is quite > ambiguous with others "string" parameters like archive_command. Of > course, we can use a specific code for them. It would be really easy, > but I would prefer to find another way. > I don't see an easy way. :( Perhaps the plan could be map all of these parameters (because in the future legacy versions will use this code path) and then implement a way to identify those strings without quotes in PostgreSQL. -- Euler Taveira de Oliveira http://www.timbira.com/
Le 20/04/2010 05:28, Euler Taveira de Oliveira a écrit : > Guillaume Lelarge escreveu: >> pgAdmin shouldn't use quotes on some parameters: search_path, >> temp_tablespaces. Perhaps others. I don't find a way to pick them >> automatically. For example, their vartype are "string". Which is quite >> ambiguous with others "string" parameters like archive_command. Of >> course, we can use a specific code for them. It would be really easy, >> but I would prefer to find another way. >> > I don't see an easy way. :( Perhaps the plan could be map all of these > parameters (because in the future legacy versions will use this code path) and > then implement a way to identify those strings without quotes in PostgreSQL. Yeah, I don't find a better way to fix this right now. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Le 20/04/2010 08:54, Guillaume Lelarge a écrit : > Le 20/04/2010 05:28, Euler Taveira de Oliveira a écrit : >> Guillaume Lelarge escreveu: >>> pgAdmin shouldn't use quotes on some parameters: search_path, >>> temp_tablespaces. Perhaps others. I don't find a way to pick them >>> automatically. For example, their vartype are "string". Which is quite >>> ambiguous with others "string" parameters like archive_command. Of >>> course, we can use a specific code for them. It would be really easy, >>> but I would prefer to find another way. >>> >> I don't see an easy way. :( Perhaps the plan could be map all of these >> parameters (because in the future legacy versions will use this code path) and >> then implement a way to identify those strings without quotes in PostgreSQL. > > Yeah, I don't find a better way to fix this right now. > Fix commited for 1.10. Still needs to work on the trunk patch. Will do tomorrow. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Le 21/04/2010 00:24, Guillaume Lelarge a écrit : > Le 20/04/2010 08:54, Guillaume Lelarge a écrit : >> Le 20/04/2010 05:28, Euler Taveira de Oliveira a écrit : >>> Guillaume Lelarge escreveu: >>>> pgAdmin shouldn't use quotes on some parameters: search_path, >>>> temp_tablespaces. Perhaps others. I don't find a way to pick them >>>> automatically. For example, their vartype are "string". Which is quite >>>> ambiguous with others "string" parameters like archive_command. Of >>>> course, we can use a specific code for them. It would be really easy, >>>> but I would prefer to find another way. >>>> >>> I don't see an easy way. :( Perhaps the plan could be map all of these >>> parameters (because in the future legacy versions will use this code path) and >>> then implement a way to identify those strings without quotes in PostgreSQL. >> >> Yeah, I don't find a better way to fix this right now. >> > > Fix commited for 1.10. Still needs to work on the trunk patch. Will do > tomorrow. > Fixed in trunk too, with another issue. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com