#29722: Add introspection of special table and view types.
-------------------------------------+-------------------------------------
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 |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Nick Pope:
Old description:
> 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).
New description:
**Originally this ticket was aimed at PostgreSQL:**
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).
**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 <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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.405a7229d252cb18573498908e1aea82%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
