psql doesn't show tables duplicated in multiple schemas

po 2. 8. 2021 v 11:12 odesílatel Wojciech Strzalka <wstrzalka@gmail.com>
napsal:

Setup
----------------------
CREATE SCHEMA x;
CREATE SCHEMA y;
CREATE TABLE x.a(f int4);
CREATE TABLE x.b(f int4);
CREATE TABLE y.b(f int4);
CREATE TABLE y.c(f int4);
-----------------------

Test
-----------------------
docker=# set search_path=x;
SET
docker=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
x | a | table | docker
x | b | table | docker
(2 rows)

docker=# set search_path=y;
SET
docker=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
y | b | table | docker
y | c | table | docker
(2 rows)

docker=# set search_path=x,y;
SET
docker=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
x | a | table | docker
x | b | table | docker
y | c | table | docker
(3 rows)
-----------------------

I would expect last table listing to include 'y.b' table. Although
shadowed when referencing by 'b' it's still there.

When the schema is not specified, then psql uses query

SELECT ...
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

and filtering is done by pg_catalog.pg_table_is_visible(c.oid). This is the
reason why you don't see y.b.

It is hard to say what the correct solution is.

1. The current solution is good because it shows so you don't see y.b
without an explicitly qualified identifier.

2. but the current solution can be messy, because you don't see the table,
that exists, and that is available.

Although I understand different opinions in this case well, the current
implementation makes sense.

Regards

Pavel

On Mon, Aug 02, 2021 at 09:05:19AM +0200, Wojciech Strzalka wrote:

I would expect last table listing to include 'y.b' table. Although shadowed
when referencing by 'b' it's still there.

Not really a bug. It shows you "visible" tables, that is - tables that
you can reach without specifying schema.

If you want to find all tables "b" across all schema, do:

#v+
\dt *.b
#v-

Best regards,

depesz

I understand why but still - that was very surprising after ~20 years of
using Postgres :)

pon., 2 sie 2021 o 11:32 Pavel Stehule <pavel.stehule@gmail.com> napisał(a):

At Mon, 2 Aug 2021 12:57:17 +0200, Wojciech Strzalka <wstrzalka@gmail.com> wrote in

I understand why but still - that was very surprising after ~20 years of
using Postgres :)

I agree that it is astonishing but I think it's just a matter of
choice and I found that it is surely written in the documentaion.

https://www.postgresql.org/docs/14/app-psql.html

Note
If \d is used without a pattern argument, it is equivalent to \dtvmsE
which will show a list of all visible tables, views, materialized

~~~~~~~

views, sequences and foreign tables. This is purely a convenience
measure.

However, I don't think the "visible" works as expected for ordinary
users. (It could be confused with "accessible", aside from the
unnoticeability of the word itself...)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

On Mon, Aug 2, 2021 at 9:35 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com>
wrote:

Note
If \d is used without a pattern argument, it is equivalent to \dtvmsE
which will show a list of all visible tables, views, materialized

~~~~~~~

views, sequences and foreign tables. This is purely a convenience
measure.

However, I don't think the "visible" works as expected for ordinary
users.

I cannot see rewording this to avoid the use of "visible", but if this is a
concern worth addressing I suggest weaving in a link to section 5.9.3

https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

But I will note that if someone follows such a link the section is not
written all that well to address this usage of "visibility". I didn't look
to see if there is a better place to link to.

David J.

At Mon, 2 Aug 2021 22:28:33 -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote in

On Mon, Aug 2, 2021 at 9:35 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com>
wrote:

Note
If \d is used without a pattern argument, it is equivalent to \dtvmsE
which will show a list of all visible tables, views, materialized

~~~~~~~

views, sequences and foreign tables. This is purely a convenience
measure.

However, I don't think the "visible" works as expected for ordinary
users.

I cannot see rewording this to avoid the use of "visible", but if this is a

Year, I think "visible" is the best word there. And I'm not sure
people are actually confused about the word.

concern worth addressing I suggest weaving in a link to section 5.9.3

https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH

But I will note that if someone follows such a link the section is not
written all that well to address this usage of "visibility". I didn't look
to see if there is a better place to link to.

Maybe somethink ike thid would work?

| If \d is used without a pattern argument, it is equivalent to \dtvmsE
| which will show a list of all visible tables, views, materialized
| views, sequences and foreign tables. This is purely a convenience
| measure.
+ Objects with the same name of the same kind in different schema may
+ hide each other according to the search-path setting. See
+ <DDL-SCHEMAS-PATH> for details.

It could be shorter and smarter, though.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Kyotaro Horiguchi <horikyota.ntt@gmail.com> writes:

At Mon, 2 Aug 2021 22:28:33 -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote in

I cannot see rewording this to avoid the use of "visible", but if this is a

Year, I think "visible" is the best word there. And I'm not sure
people are actually confused about the word.

The term is actually defined in the psql ref page, in the second para
of the "Patterns" section:

<para>
Whenever the <replaceable class="parameter">pattern</replaceable> parameter
is omitted completely, the <literal>\d</literal> commands display all objects
that are visible in the current schema search path &mdash; this is
equivalent to using <literal>*</literal> as the pattern.
(An object is said to be <firstterm>visible</firstterm> 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 <literal>*.*</literal> as the pattern.
</para>

Maybe this could be rearranged to make the concept more prominent,
but I'm not convinced that we need any really new text. I definitely
don't want to make duplicative additions to each \d command's text.

regards, tom lane

At Tue, 03 Aug 2021 09:47:41 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote in

Kyotaro Horiguchi <horikyota.ntt@gmail.com> writes:

At Mon, 2 Aug 2021 22:28:33 -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote in

I cannot see rewording this to avoid the use of "visible", but if this is a

Year, I think "visible" is the best word there. And I'm not sure
people are actually confused about the word.

The term is actually defined in the psql ref page, in the second para
of the "Patterns" section:

<para>
Whenever the <replaceable class="parameter">pattern</replaceable> parameter
is omitted completely, the <literal>\d</literal> commands display all objects
that are visible in the current schema search path &mdash; this is
equivalent to using <literal>*</literal> as the pattern.
(An object is said to be <firstterm>visible</firstterm> 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 <literal>*.*</literal> as the pattern.
</para>

Maybe this could be rearranged to make the concept more prominent,
but I'm not convinced that we need any really new text. I definitely
don't want to make duplicative additions to each \d command's text.

Oh, thank you for the pointer. It seems to be enough. (I didn't find
it by myself, though..)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center