Thread: Missing schema name

Missing schema name

From
"Little, Douglas"
Date:
<div class="WordSection1"><p class="MsoNormal"> <p class="MsoNormal">We’re setting up a new machine.    <p
class="MsoNormal">Frompgadmin when I look at the old machine  tables,  the schema name is displayed in the screen<p
class="MsoNormal"><imgalt="cid:image001.png@01CC580A.C04D7240" height="189" id="_x0000_i1027"
src="cid:image001.png@01CC580A.FFB1A050"width="534" /><p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal">Whenlooking at the new machine, the schema name is not displayed.<p class="MsoNormal"><img
alt="cid:image002.png@01CC580A.C04D7240"height="241" id="Picture_x0020_2" src="cid:image002.png@01CC580A.FFB1A050"
width="336"/><p class="MsoNormal"> <p class="MsoNormal">Visually, it’s not a problem.  But I do lots of cut/paste and
needthe schema name to show up. <p class="MsoNormal">Any thoughts why it’s not.<p class="MsoNormal"> <p
class="MsoNormal">Thanks<pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Doug
Little</span></b><pclass="MsoNormal"><b><span style="font-size:12.0pt"> </span></b><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Sr.Data Warehouse Architect | Business Intelligence
Architecture| Orbitz Worldwide </span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">500W. Madison, Suite 1000  Chicago IL 60661| Office
312.260.2588 |Fax 312.894.5164 | Cell 847-997-5741</span><span style="font-size:12.0pt;font-family:"Times New
Roman","serif""></span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif""><a
href="mailto:Douglas.Little@orbitz.com"title="mailto:dlittle@orbitz.com"><b><span
style="color:blue">Douglas.Little@orbitz.com</span></b></a></span><b><u><spanstyle="font-size:13.5pt;font-family:"Times
NewRoman","serif";color:blue"></span></u></b><p class="MsoNormal"> <img alt="cid:image001.jpg@01CABEC8.D4980670"
border="0"height="61" id="Picture_x0020_1" src="cid:image003.jpg@01CC580A.FFB1A050" width="83" />  <a
href="http://www.orbitz.com/"title="http://www.orbitz.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">orbitz.com</span></a><span
style="color:blue">|</span><a href="http://www.ebookers.com/" title="http://www.ebookers.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">ebookers.com</span></a><span
style="color:blue">|</span><a href="http://www.hotelclub.com/" title="http://www.hotelclub.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">hotelclub.com</span></a><span
style="color:blue">|</span><a href="http://www.cheaptickets.com/" title="http://www.cheaptickets.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">cheaptickets.com</span></a><span
style="color:blue">|</span><a href="http://www.ratestogo.com/" title="http://www.ratestogo.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">ratestogo.com</span></a><spanstyle="color:blue">
|</span><a href="http://www.asiahotels.com/" title="http://www.asiahotels.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">asiahotels.com</span></a><p
class="MsoNormal"> </div>

Re: Missing schema name

From
Guillaume Lelarge
Date:
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



Re: Missing schema name

From
Michael Shapiro
Date:
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<br/>When I look at any table in any of the schemas, the table name has the schema prepended to it.<br
/><br/>If it isn't PgAdmin doing it, then it must be in the Pg server itself.<br />How does PgAdmin get the DDL for a
tablefrom the server?<br /><br /><div class="gmail_quote">On Thu, Aug 11, 2011 at 2:14 PM, Guillaume Lelarge <span
dir="ltr"><<ahref="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">On Thu, 2011-08-11 at 09:42
-0500,Little, Douglas wrote:<br /> > We're setting up a new machine.<br /> > From pgadmin when I look at the old
machine tables,  the schema name is displayed in the screen<br /> > [cid:image001.png@01CC580A.FFB1A050]<br />
><br/> ><br /> > When looking at the new machine, the schema name is not displayed.<br /> >
[cid:image002.png@01CC580A.FFB1A050]<br/> ><br /> > Visually, it's not a problem.  But I do lots of cut/paste and
needthe schema name to show up.<br /> > Any thoughts why it's not.<br /> ><br /><br /> Guess you have different
search_pathon these servers.<br /><br /><br /> --<br /> Guillaume<br />  <a href="http://blog.guillaume.lelarge.info"
target="_blank">http://blog.guillaume.lelarge.info</a><br/>  <a href="http://www.dalibo.com"
target="_blank">http://www.dalibo.com</a><br/><font color="#888888"><br /><br /> --<br /> Sent via pgadmin-support
mailinglist (<a href="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.org</a>)<br /> To make changes
toyour subscription:<br /><a href="http://www.postgresql.org/mailpref/pgadmin-support"
target="_blank">http://www.postgresql.org/mailpref/pgadmin-support</a><br/></font></blockquote></div><br /> 

Re: Missing schema name

From
Guillaume Lelarge
Date:
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



Re: Missing schema name

From
Michael Shapiro
Date:
If you right click on the database, and select properties, under the variables tab, there is a search_path variable in
theVariable Name pulldown.<br /><br /><div class="gmail_quote">On Thu, Aug 11, 2011 at 4:01 PM, Guillaume Lelarge <span
dir="ltr"><<ahref="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">On Thu, 2011-08-11 at 15:26
-0500,Little, Douglas wrote:<br /> > That worked, but I'm confused why it worked.<br /> > On the new machine, I'm
usinggpadmin which does have different search_path.<br /> > When I switch to my regular id, the schema name shows
up.<br/> ><br /> > In pgadmin, is it even possible to change the search_path?<br /> ><br /><br /> Nope, I
don'tthink you can.<br /><br /></blockquote></div> 

Re: Missing schema name

From
Guillaume Lelarge
Date:
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



Re: Missing schema name

From
Guillaume Lelarge
Date:
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