Bug 123845

PostgreSQL is shipped with Solaris, so we have to make life of people, who use Sun products, easier.

From the PostgreSQL docs:

 Qualified names are tedious to write, and it's often best not to wire a particular schema name into applications
anyway. Therefore tables are often referred to by unqualified names, which consist of just the table name. The system
determines which table is meant by following a search path, which is a list of schemas to look in. The first matching
table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported,
even if matching table names exist in other schemas in the database.

The first schema named in the search path is called the current schema. Aside from being the first schema searched, it
is also the schema in which new tables will be created if the CREATE TABLE command does not specify a schema name.

To show the current search path, use the following command:

SHOW search_path;

In the default setup this returns:

 search_path
--------------
 $user,public

The first element specifies that a schema with the same name as the current user is to be searched. If no such schema
exists, the entry is ignored. The second element refers to the public schema that we have seen already.

The first schema in the search path that exists is the default location for creating new objects. That is the reason
that by default objects are created in the public schema. When objects are referenced in any other context without
schema qualification (table modification, data modification, or query commands) the search path is traversed until a
matching object is found. Therefore, in the default configuration, any unqualified access again can only refer to the
public schema.

To put our new schema in the path, we use

SET search_path TO myschema,public;

(We omit the $user here because we have no immediate need for it.) And then we can access the table without schema
qualification:

DROP TABLE mytable;

Also, since myschema is the first element in the path, new objects would by default be created in it.

We could also have written

SET search_path TO myschema;

Then we no longer have access to the public schema without explicit qualification. There is nothing special about the
public schema except that it exists by default. It can be dropped, too.

To reproduce, open a connection to PostgreSQL, and select the 'public' schema. 
 
- Open up the SQL editor and do 'create table foo (id integer primary key)'
- Create a schema named 'test'.
- Disconnect, open the connection properties, and change the schema to 'test' 
- Reconnect
- Open up the SQL editor and do 'create table foo (id integer primary key)'
- You get "relation already exists".  Obviously you're still defaulting to the public schema

The explorer actually shows the correct schema when you look at tables, probably because the underlying code is
explicitly specifying the schema.

Not sure what this issue is about. It looks like we just have to make it clear to the users that the schema they choose
in the Advanced panel is default for display purposes only. I would say most users do understand the difference already.

Well, it wasn't clear to me.  Note that the connection itself actually says what schema you are on.  One would assume
that would mean you were in that schema when using the connection for SQL.  That is true for other databases (unless you
change the schema property after your initial connection).

The whole way we manage schemas is unclear.  I am thinking perhaps rather than try to tweak it at this late stage in the
game we should revisit how we do schemas when we redo the explorer code.

I think this is going to be fixed as part of our overall UI work, where we plan to support displaying multiple schemas

Reassigned to new owner.

IMHO was fixed already.