describe working as intended?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

describe working as intended?

Melanie Plageman
So, I noticed that if I make a table in one schema and then a table with the
same name in another schema that describe only shows me one of them.
Demonstrating with temp table and regular table just for simplicity:
If I make a temp table t1 and a normal table t1 (it doesn't
matter which one I create first), describe only shows the temp table.

test=# create table t1();
CREATE TABLE
test=# \d
         List of relations
 Schema | Name | Type  |   Owner
--------+------+-------+-----------
 public | t1   | table | mplageman
(1 row)

test=# create temp table t1();
CREATE TABLE
test=# \d
          List of relations
  Schema   | Name | Type  |   Owner
-----------+------+-------+-----------
 pg_temp_4 | t1   | table | mplageman
(1 row)

I'm not sure if this is the intended behavior or if it is a bug.

I looked briefly at the describe code and ran the query in describeTableDetails
which it constructs at the beginning and this, of course, returns the results I
would expect.

test=# select c.oid, n.nspname, c.relname from pg_catalog.pg_class c left join
pg_catalog.pg_namespace n on n.oid = c.relnamespace where c.relname = 't1';
  oid  |  nspname  | relname
-------+-----------+---------
 23609 | public    | t1
 23612 | pg_temp_4 | t1
(2 rows)

So, without much more digging, is the current behavior of describe intended?
I couldn't find an email thread discussing this with the search terms I tried.

(I noticed it on master and checked 11 as well and got the same behavior.)

--
Melanie Plageman
Reply | Threaded
Open this post in threaded view
|

Re: describe working as intended?

Tom Lane-2
Melanie Plageman <[hidden email]> writes:
> So, I noticed that if I make a table in one schema and then a table with the
> same name in another schema that describe only shows me one of them.

Yes, that's intended, psql's \d will only show you tables that are
visible in the search path, unless you give it a qualified pattern.
You can do something like "\d *.t1" if you want to see all the
instances of t1.

This is documented I believe ... ah yes, here:

    Whenever the pattern parameter is omitted completely, the \d commands
    display all objects that are visible in the current schema search path
    — this is equivalent to using * as the pattern. (An object is said to
    be visible if its containing schema is in the search path and no
    object of the same kind and name appears earlier in the search
    path. This is equivalent to the statement that the object can be
    referenced by name without explicit schema qualification.) To see all
    objects in the database regardless of visibility, use *.* as the
    pattern.
    ...
    A pattern that contains a dot (.) is interpreted as a schema name
    pattern followed by an object name pattern. For example, \dt
    foo*.*bar* displays all tables whose table name includes bar that are
    in schemas whose schema name starts with foo. When no dot appears,
    then the pattern matches only objects that are visible in the current
    schema search path. Again, a dot within double quotes loses its
    special meaning and is matched literally.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: describe working as intended?

Sergei Kornilov
In reply to this post by Melanie Plageman
Hello

No, this is not bug. This is expected beharior of search_path setting: https://www.postgresql.org/docs/current/runtime-config-client.html

> Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp. If it is not listed in the path then it is searched first

psql \d command checks current search_path (by pg_table_is_visible call). You can use \d *.t1 syntax to display tables with such name in all schemas.

regards, Sergei


Reply | Threaded
Open this post in threaded view
|

Re: describe working as intended?

Melanie Plageman


On Sat, May 18, 2019 at 1:17 AM Sergei Kornilov <[hidden email]> wrote:
Hello

No, this is not bug. This is expected beharior of search_path setting: https://www.postgresql.org/docs/current/runtime-config-client.html

> Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp. If it is not listed in the path then it is searched first

psql \d command checks current search_path (by pg_table_is_visible call). You can use \d *.t1 syntax to display tables with such name in all schemas.

regards, Sergei


Thanks! I suppose it would behoove me to check the documentation
before resorting to looking at the source code :)

--
Melanie Plageman