Thread: Re: Disparity in search_path SHOW and SET
Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > Well, sure, because you told it to. Why isn't the last parameter "false"?
>
> Thanks. I knew I was overlooking something. I've obviously been staring at
> the code too long. :) Still, would it make more sense for SHOW search_path
> to return this:
>
> "$user",public
Agreed. I have gotten confused on how to set $user in the past. I have
developed the following patch that sets the default with the double
quotes around it, and it works fine. The patch also contains updated
documentation.
I just never realized that dollar signs have to be double-quoted, but I
it makes sense now that I see it:
test=> select lanname as $user from pg_language;
ERROR: syntax error at or near "$" at character 19
LINE 1: select lanname as $user from pg_language;
^
test=> select lanname as "$user" from pg_language;
$user
----------
internal
c
sql
(3 rows)
Are the quotes an improvement?
search_path
----------------
"$user",public
(1 row)
test=> set search_path = "$user",public;
SET
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.39
diff -c -c -r1.39 config.sgml
*** doc/src/sgml/config.sgml 20 Dec 2005 02:30:35 -0000 1.39
--- doc/src/sgml/config.sgml 22 Dec 2005 23:42:13 -0000
***************
*** 39,45 ****
# This is a comment
log_connections = yes
log_destination = 'syslog'
! search_path = '$user, public'
</programlisting>
One parameter is specified per line. The equal sign between name and
value is optional. Whitespace is insignificant and blank lines are
--- 39,45 ----
# This is a comment
log_connections = yes
log_destination = 'syslog'
! search_path = '"$user", public'
</programlisting>
One parameter is specified per line. The equal sign between name and
value is optional. Whitespace is insignificant and blank lines are
***************
*** 3117,3123 ****
<para>
The default value for this parameter is
! <literal>'$user, public'</literal> (where the second part will be
ignored if there is no schema named <literal>public</>).
This supports shared use of a database (where no users
have private schemas, and all share use of <literal>public</>),
--- 3117,3123 ----
<para>
The default value for this parameter is
! <literal>'"$user", public'</literal> (where the second part will be
ignored if there is no schema named <literal>public</>).
This supports shared use of a database (where no users
have private schemas, and all share use of <literal>public</>),
Index: doc/src/sgml/ddl.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v
retrieving revision 1.50
diff -c -c -r1.50 ddl.sgml
*** doc/src/sgml/ddl.sgml 4 Nov 2005 23:53:18 -0000 1.50
--- doc/src/sgml/ddl.sgml 22 Dec 2005 23:42:14 -0000
***************
*** 1650,1656 ****
<screen>
search_path
--------------
! $user,public
</screen>
The first element specifies that a schema with the same name as
the current user is to be searched. If no such schema exists,
--- 1650,1656 ----
<screen>
search_path
--------------
! "$user",public
</screen>
The first element specifies that a schema with the same name as
the current user is to be searched. If no such schema exists,
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.302
diff -c -c -r1.302 guc.c
*** src/backend/utils/misc/guc.c 20 Dec 2005 02:30:36 -0000 1.302
--- src/backend/utils/misc/guc.c 22 Dec 2005 23:42:17 -0000
***************
*** 1902,1908 ****
GUC_LIST_INPUT | GUC_LIST_QUOTE
},
&namespace_search_path,
! "$user,public", assign_search_path, NULL
},
{
--- 1902,1908 ----
GUC_LIST_INPUT | GUC_LIST_QUOTE
},
&namespace_search_path,
! "\"$user\",public", assign_search_path, NULL
},
{
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.171
diff -c -c -r1.171 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample 17 Nov 2005 22:14:54 -0000 1.171
--- src/backend/utils/misc/postgresql.conf.sample 22 Dec 2005 23:42:19 -0000
***************
*** 364,370 ****
# - Statement Behavior -
! #search_path = '$user,public' # schema names
#default_tablespace = '' # a tablespace name, '' uses
# the default
#check_function_bodies = on
--- 364,370 ----
# - Statement Behavior -
! #search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses
# the default
#check_function_bodies = on
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Agreed. I have gotten confused on how to set $user in the past. I have
> developed the following patch that sets the default with the double
> quotes around it, and it works fine. The patch also contains updated
> documentation.
This is really entirely irrelevant to Greg's complaint. To respond to
that, you'd have to modify the behavior of SHOW.
Actually, it seems that this exposes a bug in the search_path code: if
I wrote what you wrote, I'd really expect that it refers to a schema
named exactly $user --- the quoting ought to suppress the substitution,
one would think. Not sure how hard or easy that might be to implement
though ...
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Agreed. I have gotten confused on how to set $user in the past. I have
> > developed the following patch that sets the default with the double
> > quotes around it, and it works fine. The patch also contains updated
> > documentation.
>
> This is really entirely irrelevant to Greg's complaint. To respond to
> that, you'd have to modify the behavior of SHOW.
Uh, SHOW does show the quotes:
test=> show search_path;
search_path
----------------
"$user",public
(1 row)
and that can be fed right into SET:
test=> set search_path = "$user",public;
SET
I thought that was the goal.
> Actually, it seems that this exposes a bug in the search_path code: if
> I wrote what you wrote, I'd really expect that it refers to a schema
> named exactly $user --- the quoting ought to suppress the substitution,
> one would think. Not sure how hard or easy that might be to implement
> though ...
I am unsure if the quotes are suppose to still allow dollar expansion.
It does in shell scripts. Actually this is kind of unusual:
test=> set search_path = '$user', public;
SET
test=> show search_path;
search_path
-----------------
"$user", public
(1 row)
It converts the single quotes to double.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Uh, SHOW does show the quotes:
> test=> show search_path;
> search_path
> ----------------
> "$user",public
> (1 row)
Hmm ... you're right, it does, so the current default is actually a
value that you can't get into the variable by a normal SET.
Interesting. (We are doing the "smart" stuff during SET not SHOW,
it appears.)
regression=# show search_path ;
search_path
--------------
$user,public
(1 row)
regression=# set search_path = '$user',public;
SET
regression=# show search_path ;
search_path
-----------------
"$user", public
(1 row)
Given that, I agree with changing the default string. It should look
the same as a value that you could actually assign ...
regards, tom lane
OK, applied. I have _not_ backpatched this. --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Uh, SHOW does show the quotes: > > > test=> show search_path; > > search_path > > ---------------- > > "$user",public > > (1 row) > > Hmm ... you're right, it does, so the current default is actually a > value that you can't get into the variable by a normal SET. > Interesting. (We are doing the "smart" stuff during SET not SHOW, > it appears.) > > regression=# show search_path ; > search_path > -------------- > $user,public > (1 row) > > regression=# set search_path = '$user',public; > SET > regression=# show search_path ; > search_path > ----------------- > "$user", public > (1 row) > > Given that, I agree with changing the default string. It should look > the same as a value that you could actually assign ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> Agreed. I have gotten confused on how to set $user in the past. I have > developed the following patch that sets the default with the double > quotes around it, and it works fine. The patch also contains updated > documentation. Just be careful about pg_dump's special handling of search_path in user and db variables... Make sure you haven't broken it. Chris
Christopher Kings-Lynne wrote: > > Agreed. I have gotten confused on how to set $user in the past. I have > > developed the following patch that sets the default with the double > > quotes around it, and it works fine. The patch also contains updated > > documentation. > > Just be careful about pg_dump's special handling of search_path in user > and db variables... > > Make sure you haven't broken it. Uh, could you provide a test I can do? The code is already in CVS. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073