Обсуждение: psql doesn't show tables duplicated in multiple schemas

Поиск

Список

Период

Сортировка

psql doesn't show tables duplicated in multiple schemas

От

Wojciech Strzalka

Дата:

02 августа 2021 г., 10:05:19

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

-----------------------

-----------------------

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

Tested with psql & postgres 13.3

Re: psql doesn't show tables duplicated in multiple schemas

От

Pavel Stehule

Дата:

02 августа 2021 г., 12:32:16

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

Tested with psql & postgres 13.3

Re: psql doesn't show tables duplicated in multiple schemas

От

hubert depesz lubaczewski

Дата:

02 августа 2021 г., 13:42:08

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

Re: psql doesn't show tables duplicated in multiple schemas

От

Wojciech Strzalka

Дата:

02 августа 2021 г., 13:57:17

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):

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

Tested with psql & postgres 13.3

Re: psql doesn't show tables duplicated in multiple schemas

От

Kyotaro Horiguchi

Дата:

03 августа 2021 г., 07:35:02

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

Re: psql doesn't show tables duplicated in multiple schemas

От

"David G. Johnston"

Дата:

03 августа 2021 г., 08:28:33

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.

Re: psql doesn't show tables duplicated in multiple schemas

От

Kyotaro Horiguchi

Дата:

03 августа 2021 г., 09:16:52

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

Re: psql doesn't show tables duplicated in multiple schemas

От

Tom Lane

Дата:

03 августа 2021 г., 16:47:41

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

Re: psql doesn't show tables duplicated in multiple schemas

От

Kyotaro Horiguchi

Дата:

04 августа 2021 г., 05:05:00

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: psql doesn't show tables duplicated in multiple schemas