Обсуждение: Help with search_path setting - can't create objects
Hello:
Brand new postgresql user. Long time Oracle user. Looking to migrate... ;-)
I have created a user (sbowner) and a database (pas) owned by that user. I have created a schema sbowner with the same name as the user. I have created it with:
When I do this, it works:
Note: I have tried:
When I did that, postgresql wouldn't start. Clearly it was a syntax error of some kind.
Why is this happening? How do I fix the system search_path so that it just "works"?
Thanks in advance...
Eric Raskin
eraskin at paslists dot com
Brand new postgresql user. Long time Oracle user. Looking to migrate... ;-)
I have created a user (sbowner) and a database (pas) owned by that user. I have created a schema sbowner with the same name as the user. I have created it with:
create schema authorization sbowner;When I connect as the user, my search_path looks like this:
$ psql -U sbowner -d pas -h postgres Password for user sbowner: psql (9.4.4) Type "help" for help. pas=> show search_path; search_path -------------------"sbowner, public" (1 row) pas=> \dn List of schemas Name | Owner --------------+----------dbms_alert | postgresdbms_assert | postgresdbms_output | postgresdbms_pipe | postgresdbms_random | postgresdbms_utility | postgresoracle | postgresplunit | postgresplvchr | postgresplvdate | postgresplvlex | postgresplvstr | postgresplvsubst | postgrespublic | postgressbowner | sbownerutl_file | postgres (16 rows) pas=> create table test(a1 char); ERROR: no schema has been selected to create inSo, it says the schema does not exist or it doesn't know where to create the table. It clearly does exist, according to the \dn listing, so the search_path must be wrong somehow.
When I do this, it works:
pas=> set search_path to sbowner, public; SET pas=> show search_path; search_path -----------------sbowner, public (1 row) pas=> create table test (a1 char); CREATE TABLE pas=> \dt List of relationsSchema | Name | Type | Owner ---------+------+-------+---------sbowner | test | table | sbowner (1 row)So, the difference I see is that the default search_path when I start the database has quotes around it, and when I reset it the quotes are gone.
Note: I have tried:
pas=# alter database pas set search_path = "$user", public;That did not help. My postgresql.conf has:
#------------------------------------------------------------------------------ # CLIENT CONNECTION DEFAULTS #------------------------------------------------------------------------------ # - Statement Behavior - #search_path = '"$user",public' # schema names #default_tablespace = '' # a tablespace name, '' uses the default #temp_tablespaces = '' # a list of tablespace names, '' uses # only default tablespaceAs you can see, the default setting is in use (the line is commented out). I see that the default setting has quotes around it. I tried setting it like this:
search_path = "$user",public # schema names
When I did that, postgresql wouldn't start. Clearly it was a syntax error of some kind.
Why is this happening? How do I fix the system search_path so that it just "works"?
Thanks in advance...
Eric Raskin
eraskin at paslists dot com
--
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
Eric H. Raskin | 914-765-0500 x120 |
Professional Advertising Systems Inc. | 914-765-0503 fax |
200 Business Park Dr Suite 304 | eraskin@paslists.com |
Armonk, NY 10504 | http://www.paslists.com |
Eric Raskin <eraskin@paslists.com> writes: > When I connect as the user, my search_path looks like this: > pas=> show search_path; > search_path > ------------------- > "sbowner, public" > (1 row) That's not right ... those quotes shouldn't be there. (They mean that you have just one entry in the list, and it is "sbowner, public", which of course doesn't match anything.) How did you set that value exactly? > When I do this, it works: > pas=> set search_path to sbowner, public; Yes, that is correct syntax. > #search_path = '"$user",public' # schema names This would be correct too (note the quoting rules in postgresql.conf are not the same as in SQL). > Why is this happening? How do I fix the system search_path so that it > just "works"? There is evidently a broken setting of search_path that is overriding the default. Perhaps you applied it with ALTER USER SET or ALTER DATABASE SET or ALTER SYSTEM SET. Looking into the pg_settings view for the entry about search_path should tell you where the active setting came from. regards, tom lane
Thank you. There was a mistake in the user account search_path. I'm not sure when I messed it up, but:
fixed the problem.
alter user sbowner set search_path = sbowner, public;
fixed the problem.
On 08/18/2015 03:54 PM, Tom Lane wrote:
Eric Raskin <eraskin@paslists.com> writes:When I connect as the user, my search_path looks like this:pas=> show search_path; search_path -------------------"sbowner, public" (1 row)That's not right ... those quotes shouldn't be there. (They mean that you have just one entry in the list, and it is "sbowner, public", which of course doesn't match anything.) How did you set that value exactly?When I do this, it works:pas=> set search_path to sbowner, public;Yes, that is correct syntax.#search_path = '"$user",public' # schema namesThis would be correct too (note the quoting rules in postgresql.conf are not the same as in SQL).Why is this happening? How do I fix the system search_path so that it just "works"?There is evidently a broken setting of search_path that is overriding the default. Perhaps you applied it with ALTER USER SET or ALTER DATABASE SET or ALTER SYSTEM SET. Looking into the pg_settings view for the entry about search_path should tell you where the active setting came from. regards, tom lane
--
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
Eric H. Raskin | 914-765-0500 x120 |
Professional Advertising Systems Inc. | 914-765-0503 fax |
200 Business Park Dr Suite 304 | eraskin@paslists.com |
Armonk, NY 10504 | http://www.paslists.com |