Skip site navigation (1) Skip section navigation (2)

Re: Unexpected omission of tables with duplicate names across schemas

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Chris Ross <cross(at)markmonitor(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org, Chris Ross <chris(dot)ross(at)markmonitor(dot)com>
Subject: Re: Unexpected omission of tables with duplicate names across schemas
Date: 2010-09-30 22:11:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
On Wed, Sep 29, 2010 at 2:19 PM, Chris Ross <cross(at)markmonitor(dot)com> wrote:
> On 09/29/2010 02:08 PM, Chris Ross wrote:
>> On 09/28/2010 01:17 PM, Tom Lane wrote:
>>> That's the intended behavior, because only the first one is actually
>>> accessible without schema-qualifying its name. You can use a pattern
>>> of "*.*" if you want to see objects that are hidden according to the
>>> search path. The default behavior is equivalent to a pattern of "*",
>>> which only shows objects reachable with unqualified names.
>> Is there a way to ask the database "What are all of the tables/views/etc
>> in my current search path?" without having it infer "that I can reach
>> without schema-qualifing them" ?
>> That's what I've always used \d for, and while it's certainly a habit
>> rather than anything documented explicitly to do what I think it should
>> do, there needs to be *a* way to do this I think...
>  After thinking about this a little more, I think the problem here is more
> subtle/complex.  The problem, in some ways, is that I am (in some ways)
> misusing search_path to hide parts of the database from my view.
>  We have a database with dozens of schemas, some of which have many dozens
> of tables et al.  So, I typically use search_path to modify which section of
> the database I'm looking at, and I typically schema-qualify everything when
> I code (and often when I'm just typing in psql).  So it's not really that I
> want to know what I can reach without schema qualifications, it's that I
> want a list of all things that exist, but only within the schemas that are
> in my search_path.
>  Of course, that's not what search_path is *for*, so it's a confusing issue.
>  I'm using it for something slightly along-side what it is really designed
> for.  But, except for this minor issue, it works well for that.
>  Does this help make more clear what problem I'm trying to solve, and the
> problem as I see it?

I think what you're trying to do is totally reasonable, but psql
doesn't really support it.  You might be able to rig something up
using macros (see \set).

There's a pretty significant difference between the way that graphical
admin tools like pgAdmin view schemas and the way they look from psql.
 In pgAdmin, you get a tree (which is how you seem to be thinking
about it), whereas in psql it tends to feel more like a flat namespace
that's constructed by smashing several namespaces together, a la UNIX
$PATH.  The underlying reality is that it's some of both.

Robert Haas
The Enterprise Postgres Company

In response to

pgsql-bugs by date

Next:From: stagirusDate: 2010-09-30 22:18:53
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
Previous:From: Mark KirkwoodDate: 2010-09-30 22:09:33
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group