Обсуждение: Missing schema name

On Thu, 2011-08-11 at 09:42 -0500, Little, Douglas wrote:
> We're setting up a new machine.
> From pgadmin when I look at the old machine  tables,  the schema name is displayed in the screen
> [cid:image001.png@01CC580A.FFB1A050]
> 
> 
> When looking at the new machine, the schema name is not displayed.
> [cid:image002.png@01CC580A.FFB1A050]
> 
> Visually, it's not a problem.  But I do lots of cut/paste and need the schema name to show up.
> Any thoughts why it's not.
> 

Guess you have different search_path on these servers.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

I have multiple schemas in my database. I do not have a search_path set for the database (so it wouold use the default: $user,public
When I look at any table in any of the schemas, the table name has the schema prepended to it.

If it isn't PgAdmin doing it, then it must be in the Pg server itself.
How does PgAdmin get the DDL for a table from the server?

On Thu, 2011-08-11 at 15:26 -0500, Little, Douglas wrote:
> That worked, but I'm confused why it worked.
> On the new machine, I'm using gpadmin which does have different search_path.  
> When I switch to my regular id, the schema name shows up. 
> 
> In pgadmin, is it even possible to change the search_path?
> 

Nope, I don't think you can.

> Seems like it should be a server/db option.
> 

Could be a a good way to fix this.

> Thanks. 
> --- old
> current_user=dlittle
> show search_path 
> "$user",public,pg_catalog,dba_work
> 
> -- new
> current_user=gpadmin
> dba_work, pg_catalog, public
> 
> 
> current_user=gpadmin
> "$user",public,pg_catalog,dba_work


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

If you right click on the database, and select properties, under the variables tab, there is a search_path variable in the Variable Name pulldown.

On Thu, 2011-08-11 at 14:38 -0500, Michael Shapiro wrote:
> I have multiple schemas in my database. I do not have a search_path set for
> the database (so it wouold use the default: $user,public
> When I look at any table in any of the schemas, the table name has the
> schema prepended to it.
> 
> If it isn't PgAdmin doing it, then it must be in the Pg server itself.
> How does PgAdmin get the DDL for a table from the server?
> 

pgAdmin doesn't get the DDL, it builds it from all the properties it
grabed in the system catalog. What's more interesting is how it gets the
default schema. It's the first valid one in the search_path parameter
(with one caveat, if you have $user in your search_path, pgadmin will
replace it with your username, and check if it's a valid schema name).
So, IOW, with search_path=a,$user,b,c,d,e, and connection as guillaume,
with no a and guillaume schemas, but an existing c schema, the default
schema will be c. Every object with this schema won't have their schema
name prepended. Every other object will have.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

On Thu, 2011-08-11 at 16:08 -0500, Michael Shapiro wrote:
> If you right click on the database, and select properties, under the
> variables tab, there is a search_path variable in the Variable Name
> pulldown.
> 

Yes, but this changes every connection to this DB, not only the pgadmin
ones (it does an ALTER DATABASE for that).


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com