Opened 3 years ago

Closed 12 months ago

#29722 closed New feature (fixed)

Add introspection of special table and view types.

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

Description (last modified by Nick Pope)

Originally this ticket was aimed at PostgreSQL:

Looking at the documentation for 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 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).

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 MySQL and there are other options for Oracle.

For introspection of materialized views, I think we could use USER_MVIEWS for Oracle.

Change History (12)

comment:1 Changed 3 years ago by Tim Graham

Summary: Improve introspection for special table and view types in PostgreSQLAdd introspection of special table and view types in PostgreSQL
Triage Stage: UnreviewedAccepted

comment:2 Changed 3 years ago by Nick Pope

Has patch: set
Owner: changed from nobody to Nick Pope
Status: newassigned

comment:3 Changed 3 years ago by Nick Pope

Description: modified (diff)
Summary: Add introspection of special table and view types in PostgreSQLAdd introspection of special table and view types.

comment:4 Changed 3 years ago by Tim Graham <timograham@…>

In bf8b625a:

Refs #29722 -- Added introspection of materialized views for PostgreSQL.

comment:5 Changed 3 years ago by Tim Graham

Patch needs improvement: set

comment:6 Changed 3 years ago by Nick Pope

Patch needs improvement: unset

PR for partitions support for PostgreSQL updated.

comment:7 Changed 3 years ago by Tim Graham <timograham@…>

In ebd27062:

Refs #29722 -- Added introspection of partitions for PostgreSQL.

comment:8 Changed 3 years ago by Tim Graham

Has patch: unset

The ticket remains open for adding support for databases besides PostgreSQL.

comment:9 Changed 3 years ago by Mariusz Felisiak

Has patch: set

PR with introspection of materialized views for Oracle.

comment:10 Changed 3 years ago by GitHub <noreply@…>

In f091ea3:

Refs #29722 -- Added introspection of materialized views for Oracle.

Thanks Tim Graham for the review.

comment:11 Changed 3 years ago by Mariusz Felisiak

Has patch: unset

comment:12 Changed 12 months ago by Nick Pope

Resolution: fixed
Status: assignedclosed

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):

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.

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