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

Tested with psql & postgres 13.3