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

[Ralph Graulich <ralph.graulich@xxxxxxxxxxx>]

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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general