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)

Search Discussions

  • Tom Lane at Sep 28, 2010 at 5:19 pm

    Chris Ross 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
  • Chris Ross at Sep 29, 2010 at 7:25 pm

    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
  • Chris Ross at Sep 29, 2010 at 6:20 pm

    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
  • Robert Haas at Sep 30, 2010 at 10:12 pm

    On Wed, Sep 29, 2010 at 2:19 PM, Chris Ross 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
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedSep 28, '10 at 4:58p
activeSep 30, '10 at 10:12p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase