pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql doesn't show tables duplicated in multiple schemas
9+ messages / 6 participants
[nested] [flat]
* psql doesn't show tables duplicated in multiple schemas
@ 2021-08-02 07:05 Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-02 09:32 ` Re: psql doesn't show tables duplicated in multiple schemas Pavel Stehule <pavel.stehule@gmail.com>
2021-08-02 10:42 ` Re: psql doesn't show tables duplicated in multiple schemas hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 9+ messages in thread
From: Wojciech Strzalka @ 2021-08-02 07:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
Tested with psql & postgres 13.3
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: psql doesn't show tables duplicated in multiple schemas
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
@ 2021-08-02 09:32 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-08-02 10:57 ` Re: psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2021-08-02 09:32 UTC (permalink / raw)
To: Wojciech Strzalka <wstrzalka@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: psql doesn't show tables duplicated in multiple schemas
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-02 09:32 ` Re: psql doesn't show tables duplicated in multiple schemas Pavel Stehule <pavel.stehule@gmail.com>
@ 2021-08-02 10:57 ` Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-03 04:35 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Wojciech Strzalka @ 2021-08-02 10:57 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: psql doesn't show tables duplicated in multiple schemas
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-02 09:32 ` Re: psql doesn't show tables duplicated in multiple schemas Pavel Stehule <pavel.stehule@gmail.com>
2021-08-02 10:57 ` Re: psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
@ 2021-08-03 04:35 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-03 05:28 ` Re: psql doesn't show tables duplicated in multiple schemas David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Kyotaro Horiguchi @ 2021-08-03 04:35 UTC (permalink / raw)
To: wstrzalka@gmail.com; +Cc: pavel.stehule@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: psql doesn't show tables duplicated in multiple schemas
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-02 09:32 ` Re: psql doesn't show tables duplicated in multiple schemas Pavel Stehule <pavel.stehule@gmail.com>
2021-08-02 10:57 ` Re: psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-03 04:35 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
@ 2021-08-03 05:28 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-03 06:16 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2021-08-03 05:28 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: wstrzalka@gmail.com, Pavel Stehule <pavel.stehule@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: psql doesn't show tables duplicated in multiple schemas
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-02 09:32 ` Re: psql doesn't show tables duplicated in multiple schemas Pavel Stehule <pavel.stehule@gmail.com>
2021-08-02 10:57 ` Re: psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-03 04:35 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-03 05:28 ` Re: psql doesn't show tables duplicated in multiple schemas David G. Johnston <david.g.johnston@gmail.com>
@ 2021-08-03 06:16 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-03 13:47 ` Re: psql doesn't show tables duplicated in multiple schemas Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Kyotaro Horiguchi @ 2021-08-03 06:16 UTC (permalink / raw)
To: david.g.johnston@gmail.com; +Cc: wstrzalka@gmail.com; pavel.stehule@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: psql doesn't show tables duplicated in multiple schemas
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-02 09:32 ` Re: psql doesn't show tables duplicated in multiple schemas Pavel Stehule <pavel.stehule@gmail.com>
2021-08-02 10:57 ` Re: psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-03 04:35 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-03 05:28 ` Re: psql doesn't show tables duplicated in multiple schemas David G. Johnston <david.g.johnston@gmail.com>
2021-08-03 06:16 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
@ 2021-08-03 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-08-04 02:05 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2021-08-03 13:47 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; +Cc: david.g.johnston@gmail.com; wstrzalka@gmail.com; pavel.stehule@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: psql doesn't show tables duplicated in multiple schemas
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-02 09:32 ` Re: psql doesn't show tables duplicated in multiple schemas Pavel Stehule <pavel.stehule@gmail.com>
2021-08-02 10:57 ` Re: psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-03 04:35 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-03 05:28 ` Re: psql doesn't show tables duplicated in multiple schemas David G. Johnston <david.g.johnston@gmail.com>
2021-08-03 06:16 ` Re: psql doesn't show tables duplicated in multiple schemas Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-03 13:47 ` Re: psql doesn't show tables duplicated in multiple schemas Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-08-04 02:05 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Kyotaro Horiguchi @ 2021-08-04 02:05 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: david.g.johnston@gmail.com; wstrzalka@gmail.com; pavel.stehule@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: psql doesn't show tables duplicated in multiple schemas
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
@ 2021-08-02 10:42 ` hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 9+ messages in thread
From: hubert depesz lubaczewski @ 2021-08-02 10:42 UTC (permalink / raw)
To: Wojciech Strzalka <wstrzalka@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2021-08-04 02:05 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-02 07:05 psql doesn't show tables duplicated in multiple schemas Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-02 09:32 ` Pavel Stehule <pavel.stehule@gmail.com>
2021-08-02 10:57 ` Wojciech Strzalka <wstrzalka@gmail.com>
2021-08-03 04:35 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-03 05:28 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-03 06:16 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-03 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-08-04 02:05 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2021-08-02 10:42 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox