#29722: Add introspection of special table and view types.
-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
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
-------------------------------------+-------------------------------------
Changes (by Nick Pope):
* 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>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.9dfe5083aacd9df7df8689e1f18c1a7e%40djangoproject.com.
