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

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

According to the docs,
http://www.postgresql.org/docs/8.4/interactive/app-psql.html says in
part:

Whenever the pattern parameter is omitted completely, the \d commands
display all objects that are visible in the current schema search path
— this is equivalent to using the pattern *. To see all objects in the
database, use the pattern *.*.

So yeah, according to the documented expected behaviour it would be a bug.

2009/12/21 Filip Rembiałkowski <plk.zuber@gmail.com>:
>
>
> 2009/12/19 Ralph Graulich <ralph.graulich@t-online.de>
>
>>
>> -- Only one of the two relations is shown
>>
>
>
> I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8
>
>
>
>
>
> --
> Filip Rembiałkowski
> JID,mailto:filip.rembialkowski@gmail.com
> http://filip.rembialkowski.net/
>



--
When fascism comes to America, it will be intolerance sold as diversity.