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

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(dot)stehule(at)gmail(dot)com> napisał(a):

>
>
> po 2. 8. 2021 v 11:12 odesílatel Wojciech Strzalka <wstrzalka(at)gmail(dot)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
>>
>>
>>
>>