Thread: Problem with search_path and creating 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:

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 in

So, 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 tablespace

As 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

> On Aug 18, 2015, at 10:09 AM, Eric Raskin <eraskin@paslists.com> wrote:
>
> $ 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)
>


I think the key question is where the search_path got set to "sbowner, public". If you had the default setting in
postgresql.conf:

#search_path = '"$user",public'        # schema names

It should look just like this:

pas=> show search_path;
  search_path
----------------
 "$user",public
(1 row)


Are you sure you don't have search_path attached to a user? And you have reset the one you had for the database?

Maybe try

alter database pas set search_path to default;

alter user sbowner set search_path to default;

Then logout and back in to show the search_path.


John DeSoi, Ph.D.

Thank you!  That was it.  Somehow the search_path for the user had an extra set of quotes.  I have no idea where that happened.

I will have to pay more attention when I set up the next user! :-/

On Aug 18, 2015, at 10:09 AM, Eric Raskin <eraskin@paslists.com> wrote:

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

I think the key question is where the search_path got set to "sbowner, public". If you had the default setting in postgresql.conf:

#search_path = '"$user",public'		# schema names

It should look just like this:

pas=> show search_path; search_path   
----------------"$user",public
(1 row)


Are you sure you don't have search_path attached to a user? And you have reset the one you had for the database?

Maybe try

alter database pas set search_path to default;

alter user sbowner set search_path to default;

Then logout and back in to show the search_path.


John DeSoi, Ph.D.