Thread: BUG #15353: \det (list foreign tables) ignoring search_path

The following bug has been logged on the website:

Bug reference:      15353
Logged by:          Maksym Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.6.8
Operating system:   Linux
Description:

Hi,

Looks like that the   \det[+] [PATTERN]      list foreign tables
ignoring current searh_path settings:

postgres=# \det+
               List of foreign tables
 Schema | Table | Server | FDW Options | Description
--------+-------+--------+-------------+-------------
(0 rows)

postgres=# show search_path ;
   search_path
-----------------
 "$user", public
(1 row)

postgres=# \det+ public.*
                                               List of foreign tables
 Schema |        Table        | Server |                         FDW Options
                         | Description
--------+---------------------+--------+--------------------------------------------------------------+-------------
 public | pg_stat_user_tables | test   | (schema_name 'pg_catalog',
table_name 'pg_stat_user_tables') |
(1 row)

Look like that there are problem with  pg_catalog.pg_table_is_visible(c.oid)
for foreign tables because query
postgres=# SELECT n.nspname AS "Schema",
  c.relname AS "Table",
  s.srvname AS "Server"
FROM pg_catalog.pg_foreign_table ft
  INNER JOIN pg_catalog.pg_class c ON c.oid = ft.ftrelid
  INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  INNER JOIN pg_catalog.pg_foreign_server s ON s.oid = ft.ftserver
--WHERE pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
 Schema |        Table        | Server
--------+---------------------+--------
 public | pg_stat_user_tables | test


work without issues.

PS: initialization of test case:
\c postgres
create extension IF NOT EXISTS postgres_fdw;
CREATE SERVER test FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    dbname 'postgres',
    use_remote_estimate 'true'
);
CREATE USER MAPPING FOR public SERVER test OPTIONS (
    "user" 'postgres'
);
IMPORT FOREIGN SCHEMA pg_catalog
LIMIT TO (pg_stat_user_tables)
FROM SERVER test
INTO public;

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> Looks like that the   \det[+] [PATTERN]      list foreign tables
 PG> ignoring current searh_path settings:

Nope, what you're missing is that pg_catalog is implicitly on the front
of the search path (if not otherwise specified), so
pg_catalog.pg_stat_user_tables is considered to "hide"
public.pg_stat_user_tables. Object X in schema Y is only considered
"visible" if using the unqualified name "X" actually resolves to the Y.X
object, and in this case, if one just refers to "pg_stat_user_tables"
one gets pg_catalog.pg_stat_user_tables, not public.pg_stat_user_tables.

-- 
Andrew (irc:RhodiumToad)