Disparity in search_path SHOW and SET
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I'm trying to figure out a way to modify the search_path temporarily, but
the "$user" construct is making this difficult. I need to prepend a schema
to the path. This works fine:
SELECT set_config('search_path', '$schema,' || current_setting('search_path'), true);
...but does not last outside of a transaction.
The problem is really that the output of "SHOW search_path" cannot be fed
back into "SET search_path" if the search_path contains the string "$user".
My only option appears to be to have the application parse the string returned
from SHOW search_path, quote the dollar-values, and rebuild the string. Is
there an easier way?
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200512201205
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFDqDvOvJuQZxSWSsgRAgUKAKDrRJoCfe8M7Fe2mi+/KFlEKKn+fQCgthpw
jPV95zpbejZsaRvIBeLd8rM=
=xRza
-----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
This works fine:
SELECT set_config('search_path', '$schema,' || current_setting('search_path'), true);
...but does not last outside of a transaction.
Well, sure, because you told it to. Why isn't the last parameter
"false"?
regards, tom lane
-----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
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200512201227
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFDqD83vJuQZxSWSsgRAj1gAKDIRGqzD7zORJQwrxLM+oKWOiAPKgCg9/xK
OGZIoWEnLdw+Qi71lKbCg0g=
=0dBJ
-----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
Still, would it make more sense for SHOW search_path
to return this:
"$user",public
Can't get excited about it. SHOW is meant for human consumption,
not programs ...
regards, tom lane
Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1Well, 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 19073Attachments:
/pgpatches/search_pathtext/plainDownload
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 19073Bruce 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 19073Christopher 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 19073Import Notes
Reply to msg id not found: 43AB5C0D.9050209@familyhealth.com.au | Resolved by subject fallback
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