[Django] #29722: Improve introspection for special table and view types in PostgreSQL
Django
-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: master
layer (models, ORM) | Keywords: postgresql,
Severity: Normal | introspection, inspectdb, views,
Triage Stage: | partitions
Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Looking at the documentation for
[https://www.postgresql.org/docs/10/static/catalog-pg-class.html pg_class]
there are the following types that we might be interested in introspecting
for generation of models:
- `r` — ordinary table — obviously this is already supported.
- `v` — views — support was implemented in #29004.
- `f` — foreign table — being addressed by #29719.
- `m` — materialized views — could be handled much like #29004, mapping
`m` to `v`.
- `p` — partitioned table — could be handled mapping `p` to `t`, but gets
more complicated, read on below...
For partitioned tables, we are unlikely to want to generate models for all
of the individual partitions, although this could be supported with an
`--include-partitions` flag.
From [https://paquier.xyz/postgresql-2/partition-information/ this link]
we can see an example of output for partitioned tables:
{{{
relid | relname | relsize | relispartition | relkind
-------+--------------------+---------+----------------+---------
16410 | population | 0 | f | p
16417 | population_s | 8192 | t | r
16424 | population_t | 0 | t | p
16431 | population_t_10_20 | 8192 | t | r
16445 | population_t_20_30 | 8192 | t | r
}}}
On PostgreSQL 10+ we need to filter on `NOT relispartition` to ignore all
of the partitions and only include the parent (which is the table that is
usually interacted with).
--
Ticket URL: <https://code.djangoproject.com/ticket/29722>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Nick Pope
* status: new => assigned
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/10385 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
**Originally this ticket was aimed at PostgreSQL:**
**Regarding support for all backends:**
Feature support by backend:
||= =||= PostgreSQL =||= MySQL =||= Oracle =||= SQLite =||
|| Partitions || ✔ || ✔ || ✔ || ✘ ||
|| Foreign Tables || ✔ || ✘ || ✘ || ✘ ||
|| Materialized Views || ✔ || ✘ || ✔ || ✘ ||
It looks partitions can be introspected using
`INFORMATION_SCHEMA.PARTITIONS` for
[https://dev.mysql.com/doc/refman/8.0/en/partitions-table.html MySQL] and
there are other options for
[https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin005.htm
Oracle].
For introspection of materialized views, I think we could use
`USER_MVIEWS` for
[https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5332.htm#REFRN26139
Oracle].
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:3>
Django
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"bf8b625a3bb6c2cb5f1be3713f3bafe2c1050366" bf8b625a]:
{{{
#!CommitTicketReference repository=""
revision="bf8b625a3bb6c2cb5f1be3713f3bafe2c1050366"
Refs #29722 -- Added introspection of materialized views for PostgreSQL.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:4>
Django
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:5>
Django
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
Comment:
[https://github.com/django/django/pull/10385 PR] for partitions support
for PostgreSQL updated.
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:6>
Django
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"ebd270627c3350101959fac59650259f2d33efcf" ebd27062]:
{{{
#!CommitTicketReference repository=""
revision="ebd270627c3350101959fac59650259f2d33efcf"
Refs #29722 -- Added introspection of partitions for PostgreSQL.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:7>
Django
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 1 => 0
Comment:
The ticket remains open for adding support for databases besides
PostgreSQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:8>
Django
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/10683 PR] with introspection of
materialized views for Oracle.
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:9>
Django
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by GitHub <noreply@…>):
In [changeset:"f091ea35150d95fc6732bbf0c27b971dd445509a" f091ea3]:
{{{
#!CommitTicketReference repository=""
revision="f091ea35150d95fc6732bbf0c27b971dd445509a"
Refs #29722 -- Added introspection of materialized views for Oracle.
Thanks Tim Graham for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:10>
Django
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:11>
Django
Reporter: Nick Pope | Owner: Nick Pope
Component: Database layer | Version: master
(models, ORM) |
Keywords: postgresql, | Triage Stage: Accepted
introspection, inspectdb, views, |
partitions |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
The main remaining item to be supported here was partitions for
MySQL/MariaDB. In practice this isn't required, however.
This is because they behave differently to partitions in PostgreSQL:
- In PostgreSQL, partitions are "normal" tables and needed to be excluded
from introspection by default.
- In MySQL/MariaDB, partitions are only listed in
`INFORMATION_SCHEMA.PARTITIONS` so are already excluded by default.
- In PostgreSQL it is possible to query a partition directly with no
special syntax (e.g. `SELECT * FROM child`) so it may still be helpful to
introspect partitions.
- In MySQL/MariaDB you must use special syntax to directly access a
partition (e.g. `SELECT * FROM parent PARTITION (child)`) so it wouldn't
be useful to introspect them.
Here is a dump of some SQL written while checking whether this could be
supported (for reference):
{{{#!sql
CREATE TABLE inspectdb_partition_parent (date date NOT NULL)
PARTITION BY LIST (quarter(date)) (
PARTITION inspectdb_partition_child VALUES IN (1, 2, 3, 4)
);
CREATE VIEW inspectdb_view AS (SELECT * FROM inspectdb_partition_parent);
INSERT INTO inspectdb_partition_parent values (now());
SELECT date FROM inspectdb_partition_child; -- Table
'db_282816758.inspectdb_partition_child' doesn't exist
SELECT date FROM inspectdb_partition_parent; -- 2021-01-28
SELECT date FROM inspectdb_partition_parent PARTITION
(inspectdb_partition_child); -- 2021-01-28
-- Implementation of DatabaseIntrospection.get_table_list() to support
partitions:
SELECT table_name, CASE WHEN table_type = 'VIEW' THEN 'v' ELSE 't' END
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql',
'performance_schema')
UNION
SELECT partition_name, 'p'
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema', 'mysql',
'performance_schema') AND partition_name IS NOT NULL;
-- inspectdb_partition_parent t
-- inspectdb_view v
-- inspectdb_partition_child p
}}}
Also remaining was support for partitions with Oracle. This looks very
complex compared to PostgreSQL/MySQL.
As mentioned above, the main issue was that introspection for PostgreSQL
resulted in tables being generated for partitions as partitions are
treated as normal tables - this was undesirable.
--
Ticket URL: <https://code.djangoproject.com/ticket/29722#comment:12>