\dt doesn't show all relations in user's schemas (8.4.2)

Поиск
Список
Период
Сортировка
От Ralph Graulich
Тема \dt doesn't show all relations in user's schemas (8.4.2)
Дата
Msg-id B1DF65BF-26A1-460A-A0B7-ED4D59E07F24@t-online.de
обсуждение исходный текст
Ответы Re: \dt doesn't show all relations in user's schemas (8.4.2)  (Filip Rembiałkowski <plk.zuber@gmail.com>)
Список pgsql-general
Hello,

I am running PostgreSQL 8.4.2. For the testcase I have a database, a
user, and two schemas within the database. Two tables in those two
different schemas have the same name, but only on of those tables
shows up using the "\dt" command.

How-To-Repeat:

-- psql template1

CREATE ROLE testrole
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOINHERIT
LOGIN
ENCRYPTED PASSWORD 'testrole'
;

CREATE DATABASE test
OWNER testrole
TEMPLATE template0
LC_COLLATE = 'de_DE.UTF-8'
LC_CTYPE = 'de_DE.UTF-8'
ENCODING 'UTF-8'
;

ALTER ROLE testrole SET search_path=schema1, schema2;

-- psql test testrole

show search_path;
--    search_path
-- ------------------
--  schema1, schema2
-- (1 row)


CREATE SCHEMA schema1;
CREATE SCHEMA schema2;

CREATE TABLE schema1.table1
(
field1 VARCHAR(10)
);

CREATE TABLE schema2.table1
(
field1 VARCHAR(10)
);

\dt

--           List of relations
--  Schema  |  Name  | Type  |  Owner
-- ---------+--------+-------+----------
--  schema1 | table1 | table | testrole
-- (1 row)

-- Only one of the two relations is shown

CREATE TABLE schema2.table2
(
field1 VARCHAR(10)
);

\dt

--           List of relations
--  Schema  |  Name  | Type  |  Owner
-- ---------+--------+-------+----------
--  schema1 | table1 | table | testrole
--  schema2 | table2 | table | testrole
-- (2 rows)


I think both table1 in schema1 and schema2 should show up in the \dt
listing, because the user has access to both tables, is the owner of
both tables and has the search_path set accordingly.
If I delete the "AND pg_catalog.pg_table_is_visible(c.oid)" from the
where clause of the SQL statement that is issued when using "\dt"
command, all the tables show up as expected (but I am unaware of the
possible side effects...):

  Schema  |  Name  | Type  |  Owner
---------+--------+-------+----------
  schema1 | table1 | table | testrole
  schema2 | table1 | table | testrole
  schema2 | table2 | table | testrole
(3 rows)

Does it work as expected? Where is that behaviour explained? Is there
a command to show all the relations (/objects) a user has access to?

Best regards,
Ralph


В списке pgsql-general по дате отправления:

Предыдущее
От: Jaime Casanova
Дата:
Сообщение: Re: alter table performance
Следующее
От: Raimon Fernandez
Дата:
Сообщение: Re: Extended Query, flush or sync ?