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

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