Horton
Menu

Thread

  1. Disparity in search_path SHOW and SET

    Greg Sabino Mullane <greg@turnstep.com> — 2005-12-20T17:15:46Z

    -----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-----

  2. Re: Disparity in search_path SHOW and SET

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-12-20T17:21:20Z

    "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

  3. Re: Disparity in search_path SHOW and SET

    Greg Sabino Mullane <greg@turnstep.com> — 2005-12-20T17:30:07Z

    -----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-----

  4. Re: Disparity in search_path SHOW and SET

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-12-20T17:44:35Z

    "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

  5. Re: Disparity in search_path SHOW and SET

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-12-22T23:57:19Z

    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

  6. Re: Disparity in search_path SHOW and SET

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-12-23T00:03:57Z

    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

  7. Re: Disparity in search_path SHOW and SET

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-12-23T00:12:06Z

    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

  8. Re: Disparity in search_path SHOW and SET

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-12-23T00:26:36Z

    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

  9. Re: Disparity in search_path SHOW and SET

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-12-23T00:38:28Z

    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

  10. Re: Disparity in search_path SHOW and SET

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-12-23T02:04:40Z

    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

  11. Re: Disparity in search_path SHOW and SET

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-12-23T02:08:13Z

    > 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