Cannot see two tables with the same name

I have two non-public schemas in one database. A table named "foo" exists in both schemas and I can execute all necessary DDL & DML commands against both. However, when I execute \d, I cannot see both of them. What gives?

Repro

create schema a;
create schema b;

create table a.foo (i int);
create table b.foo (i int);

set search_path = "$user", public, a, b;


In psql

postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 a      | foo       | table | pvandivier
(1 row)

I \set ECHO_HIDDEN on to extract the underlying query run by \d and moved the relevant lines from the WHERE clause into the SELECT to examine the differences between the two table objects.

SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_table_is_visible(c.oid),
n.nspname,
c.relkind
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname IN ('a','b')
ORDER BY 1,2;


Running this query reveals...

Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | t                   | a       | r
 b      | foo  | table | pvandivier | f                   | b       | r

The relevant difference appears to be the output of pg_table_is_visible(). But why should it return "False" when asking if b.foo is visible?


Why is pg_table_is_visible() returning False for a table I have access to?

The concept is_visible here appears to mean "the first one you see in the search_path"; and not "can you see it". You can demonstrate this for yourself by changing the ordering of schemas in the search_path.

postgres=# set search_path = "$user", public, b, a;
SET
postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 b      | foo       | table | pvandivier
(1 row)

postgres=#

Note that b.foo is returned when b is listed first in the search_path. This is supported as well by the output of the previous query re-run in the same search_path.

Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | f                   | a       | r
 b      | foo  | table | pvandivier | t                   | b       | r
(2 rows)

We can now clearly see that pg_table_is_visible() is True for b and False for a. Therefore "is_visible" is an indicator of current accessibility and not an indicator of whether a relation exists.

Note that attempting to determine visibility of a non-existent object has a different set of behaviours.

postgres=# -- attempting to get the oid of a non-existent object
postgres=# select 'c.foo'::regclass;
ERROR:  schema "c" does not exist
LINE 1: select 'c.foo'::regclass;
               ^
postgres=# select 'bar'::regclass::oid;
ERROR:  relation "bar" does not exist
LINE 1: select 'bar'::regclass::oid
               ^
postgres=# -- passing an oid for a non-existent object returns NULL
postgres=# select pg_table_is_visible((random() * 1e7)::int::oid);
 pg_table_is_visible
---------------------

(1 row)

postgres=#

You can also read this somewhat in source at src/backend/catalog/namespace.c. I'm not fluent in C, but the following citations appear to support this reasoning.

• pg_table_is_visible() references RelationIsVisible

Datum
pg_table_is_visible(PG_FUNCTION_ARGS)
{
Oid oid = PG_GETARG_OID(0);

if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(oid)))
PG_RETURN_NULL();

PG_RETURN_BOOL(RelationIsVisible(oid));
}


• RelationIsVisible reveals the following code comments just above the return logic

/*
* If it is in the path, it might still not be visible; it could be
* hidden by another relation of the same name earlier in the path. So
* we must do a slow check for conflicting relations.
*/


StackExchange Database Administrators Q#256894, answer score: 3