PostgreSQL – Cannot See Two Tables with the Same Name

postgresql

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?

Best Answer

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.
 */

Possible solution

If you want late binding you could use a function instead of a view and default to the current search path (one could SET the search path for the scope of a function to avoid that effect precisely.)

So instead of:

CREATE VIEW v_mechanics_schedule AS
SELECT * FROM schedule s  -- use aliases for short qualified column names in display
LEFT     JOIN mechanics m USING (mechanic_id);

Without schema-qualification, table names are resolved according to the current search_path at creation time.

You could:

CREATE FUNCTION f_mechanics_schedule()
  RETURNS TABLE (...) AS  -- you have to spell out columns
$func$
SELECT * FROM schedule s  -- this is typically error prone
LEFT     JOIN mechanics m USING(mechanic_id);
$func$  LANGUAGE SQL

Now, table names are resolved according to the search_path at execution time. Note that the same does not apply to the return type ( RETURNS TABLE (...)), which is determined at creation time. So this trick only works for compatible tables - the query has to return the same list of columns, only data types and the number of columns matter, column names are ignored (only the names in the definition of the return type are visible outside the function).

That's also why SELECT * to return values in the function body is typically unreliable. If you change the definition of underlying tables, the function breaks: The defined return type remains the same, but SELECT * resolves to a different column list ...

OTOH, if you declare a function as RETURNS SETOF some_table (different syntax variant), a functional dependency is registered and the return type of the function is bound to the table definition, so SELECT * FROM some_table would make more sense.

_{This is also the reason why it is unsafe to have SECURITY DEFINER functions without fixing the search_path: Any user with the TEMP privilege can create a temp table that hides other tables because pg_temp comes first in the search path by default ...

Related:}

Permission for sequence in another schema

You really need to understand the underlying mechanisms to play with this.

Postgresql – Postgres: Preventing objects with same name

That's like asking Microsoft or GNU.org to remove PATH from the command line. Ain't gonna happen.

Use fully qualified names or always run ALTER ROLE <your_login_role> SET search_path TO a,b,c; at the beginning of your session. I'm not sure if you can run this with psqlrc but you could try.

You can view your search_path with show search_path

You could write a cron or pgagent job to run often and check information_schema for views and tables with the same name

Repro

Best Answer

Related Solutions

Postgresql – Turn off auto qualification of table names when creating a view

Possible solution

Postgresql – Postgres: Preventing objects with same name

Related Question