Opened 2 years ago

Last modified 21 months ago

#29722 assigned New feature

Add introspection of special table and view types.

Reported by: Nick Pope Owned by: Nick Pope
Component: Database layer (models, ORM) Version: master
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 (11)

comment:1 Changed 2 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 2 years ago by Nick Pope

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

comment:3 Changed 2 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 23 months ago by Tim Graham <timograham@…>

In bf8b625a:

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

comment:5 Changed 23 months ago by Tim Graham

Patch needs improvement: set

comment:6 Changed 21 months ago by Nick Pope

Patch needs improvement: unset

PR for partitions support for PostgreSQL updated.

comment:7 Changed 21 months ago by Tim Graham <timograham@…>

In ebd27062:

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

comment:8 Changed 21 months ago by Tim Graham

Has patch: unset

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

comment:9 Changed 21 months ago by felixxm

Has patch: set

PR with introspection of materialized views for Oracle.

comment:10 Changed 21 months ago by GitHub <noreply@…>

In f091ea3:

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

Thanks Tim Graham for the review.

comment:11 Changed 21 months ago by felixxm

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