Re: psql doesn't show tables duplicated in multiple schemas
| От | Pavel Stehule |
|---|---|
| Тема | Re: psql doesn't show tables duplicated in multiple schemas |
| Дата | |
| Msg-id | CAFj8pRBurjH0_Nc+5YTsEuDFZ7sb6Bkvi7RzL5GeWoELBLz_7w@mail.gmail.com обсуждение исходный текст |
| Ответ на | psql doesn't show tables duplicated in multiple schemas (Wojciech Strzalka <wstrzalka@gmail.com>) |
| Ответы |
Re: psql doesn't show tables duplicated in multiple schemas
|
| Список | pgsql-bugs |
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;
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
В списке pgsql-bugs по дате отправления: