[Django] #29719: command inspectdb run against postgres' foreign data wrapper (fdw) fails to list the foreign tables
Django
list the foreign tables
-------------------------------------+-------------------------------------
Reporter: Luke | Owner: Luke
Type: Bug | Status: assigned
Component: Database | Version: 2.1
layer (models, ORM) | Keywords:
Severity: Normal | postgres,fdw,foreign data wrapper
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
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
--
Ticket URL: <https://code.djangoproject.com/ticket/29719>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
list the foreign tables
-------------------------------------+-------------------------------------
Reporter: Luke | Owner: Luke
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
postgres,fdw,foreign data wrapper | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> 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
New description:
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
https://github.com/django/django/pull/10351
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29719#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Luke | Owner: Luke
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
postgres,fdw,foreign data wrapper |
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):
* stage: Unreviewed => Accepted
* type: Bug => New feature
* easy: 1 => 0
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/29719#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Luke | Owner: Luke
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
postgres,fdw,foreign data wrapper |
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Luke:
Old description:
> 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
>
> https://github.com/django/django/pull/10351
New description:
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
[https://github.com/django/django/pull/10351 PR]
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29719#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Luke | Owner: Nick Pope
Type: New feature | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
introspection, inspectdb, fdw, |
foreign data wrapper |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* owner: Luke => Nick Pope
* keywords: postgres,fdw,foreign data wrapper => postgresql,
introspection, inspectdb, fdw, foreign data wrapper
* version: 2.1 => master
* needs_tests: 1 => 0
Comment:
Updated [https://github.com/django/django/pull/10385 PR].
--
Ticket URL: <https://code.djangoproject.com/ticket/29719#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Luke | Owner: Nick Pope
Component: Database layer | Version: master
(models, ORM) |
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, fdw, |
foreign data wrapper |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"45ef3df7d07489ee0b76479cc799faa92e443a69" 45ef3df7]:
{{{
#!CommitTicketReference repository=""
revision="45ef3df7d07489ee0b76479cc799faa92e443a69"
Fixed #29719 -- Added introspection of foreign tables for PostgreSQL.
Thanks infinite-l00p for the initial patch.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29719#comment:5>