Allow inspectdb to introspect foreign data wrapper tables

[Luke]

When running the "inspectdb" command against a postgres database with foreign data wrapper (fdw) tables, these foreign tables aren't listed.

The bug arises here
line#41 of db/backends/postgresql/introspection.py

SELECT c.relname, c.relkind
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)""")

pg_class.relkind stores foreign tables as "F" , so the condition "c.relkind in ('r','v')" is never met .

The query must be rewritten as

SELECT c.relname, c.relkind
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v', 't')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid)""")

and on line#50

return [TableInfo(row[0], {'r': 't', 'v': 'v', 'f': 't'}.get(row[1]))
                for row in cursor.fetchall()
                if row[0] not in self.ignored_tables]

in order to map the 'f' to a table

I patched the file and made this pull-request

​PR

[Nick Pope]

Updated ​PR.

[Tim Graham <timograham@…>]

In 45ef3df7:

Fixed #29719 -- Added introspection of foreign tables for PostgreSQL.

Thanks infinite-l00p for the initial patch.