Обсуждение: Unexpected omission of tables with duplicate names across schemas

   When there is a table (or view, or sequence) of the same name in one
schema as another, and both of these schemas are in the set search_path,
only the first schema in the search path will show that name in the
output of \d[S+].  (Also \dt, \dv, etc)

PostgreSQL versions:  8.2.x, 8.3.x, 8.4.4, 9.0.0
Operating System: Linux (Ubuntu 10 and RedHat EL5 tested)

   Running the attached file to create tables in schemas, then running
the below commands show output as noted.  At the bottom, I have also
simulated what I expect the output to be.

   I *think* this is a bug.  I did not expect filtration to occur with
table/view names in the output of a simple "\d".  This also seems to
prevent display of any tables where a table of the same name exists in
the pg_catalog schema.

   Thank you.  Please let me know if this is a known issue, or if
there's anything I can do to help describe the problem.

                                                            - Chris


% \i pgsql9.bugshow-database.sql
% \d
            List of relations
  Schema |     Name     | Type  | Owner
--------+--------------+-------+-------
  test2  | test_table   | table | cross
  test2  | test_table_2 | table | cross
(2 rows)

% set search_path to test1,test2;
% \d

Output:

            List of relations
  Schema |     Name     | Type  | Owner
--------+--------------+-------+-------
  test1  | test_table   | table | cross
  test1  | test_table_1 | table | cross
  test2  | test_table_2 | table | cross
(3 rows)

Expected Output:

            List of relations
  Schema |     Name     | Type  | Owner
--------+--------------+-------+-------
  test1  | test_table   | table | cross
  test1  | test_table_1 | table | cross
  test2  | test_table   | table | cross
  test2  | test_table_2 | table | cross
(4 rows)

Chris Ross <cross@markmonitor.com> writes:
>    When there is a table (or view, or sequence) of the same name in one
> schema as another, and both of these schemas are in the set search_path,
> only the first schema in the search path will show that name in the
> output of \d[S+].  (Also \dt, \dv, etc)

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.

            regards, tom lane

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?

   Thanks again.

                        - Chris

On 09/28/2010 01:17 PM, Tom Lane wrote:
> Chris Ross<cross@markmonitor.com>  writes:
>>     When there is a table (or view, or sequence) of the same name in one
>> schema as another, and both of these schemas are in the set search_path,
>> only the first schema in the search path will show that name in the
>> output of \d[S+].  (Also \dt, \dv, etc)
>
> 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.

   Okay.  However, that doesn't quite do what I want.  In the case of
\d, it takes a name, not a pattern, and if a name/pattern is specified
as * or *.*, it shows detail about the item, not just a list.
   For \dt, \dv, etc, I can supply a pattern, but *.* does not give me
what I want either.  It gives me *all* schemas, not limited to the
schemas that are in my search path.

   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...

                           - Chris

On Wed, Sep 29, 2010 at 2:19 PM, Chris Ross <cross@markmonitor.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...
>
> =A0After thinking about this a little more, I think the problem here is m=
ore
> subtle/complex. =A0The problem, in some ways, is that I am (in some ways)
> misusing search_path to hide parts of the database from my view.
>
> =A0We have a database with dozens of schemas, some of which have many doz=
ens
> of tables et al. =A0So, I typically use search_path to modify which secti=
on of
> the database I'm looking at, and I typically schema-qualify everything wh=
en
> I code (and often when I'm just typing in psql). =A0So it's not really th=
at 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.
>
> =A0Of course, that's not what search_path is *for*, so it's a confusing i=
ssue.
> =A0I'm using it for something slightly along-side what it is really desig=
ned
> for. =A0But, except for this minor issue, it works well for that.
>
> =A0Does this help make more clear what problem I'm trying to solve, and t=
he
> 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.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company