Opened 6 years ago

Closed 5 years ago

#29719 closed New feature (fixed)

Allow inspectdb to introspect foreign data wrapper tables

Reported by: Luke Owned by: Nick Pope
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: postgresql, introspection, inspectdb, fdw, foreign data wrapper
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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

Change History (5)

comment:1 by Luke, 6 years ago

Description: modified (diff)

comment:2 by Tim Graham, 6 years ago

Easy pickings: unset
Needs tests: set
Summary: command inspectdb run against postgres' foreign data wrapper (fdw) fails to list the foreign tablesAllow inspectdb to introspect foreign data wrapper tables
Triage Stage: UnreviewedAccepted
Type: BugNew feature

comment:3 by Luke, 6 years ago

Description: modified (diff)

comment:4 by Nick Pope, 6 years ago

Keywords: postgresql introspection inspectdb added; postgres removed
Needs tests: unset
Owner: changed from Luke to Nick Pope
Version: 2.1master

Updated PR.

comment:5 by Tim Graham <timograham@…>, 5 years ago

Resolution: fixed
Status: assignedclosed

In 45ef3df7:

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

Thanks infinite-l00p for the initial patch.

Note: See TracTickets for help on using tickets.
Back to Top