[Django] #24632: PostgreSQL table inheritance
Django
-------------------------+-------------------------------------------------
Reporter: | Owner:
yennicks |
Type: New | Status: new
feature |
Component: | Version: master
contrib.postgres | Keywords: orm postgresql table-inheritance
Severity: Normal | inheritance object-relational
Triage Stage: | Has patch: 0
Unreviewed |
Easy pickings: 0 | UI/UX: 0
-------------------------+-------------------------------------------------
The Django ORM multi-table inheritance approach works by creating a
different table for every subclass. For working with these subclasses the
ORM uses SQL table joins to query the database. This makes sense for
databases such as MySQL and SQLite but not for PostgreSQL.
I suggest adding a PostgreSQL specific model that optimizes the ORM
behaviour when developing for a PostgreSQL database. This allows for using
the PostgreSQL object-relational table approach by the ORM. When
implemented, a table used by a subclass inherits from the table used by
the superclass, avoiding for example the usage of joins.
Another approach is optimizing the default behaviour of the Django ORM but
this might impact existing Django implementations that expect traditional
multi-table inheritance.
PostgreSQL 9.4 manual about table inheritance:
http://www.postgresql.org/docs/9.4/static/ddl-inherit.html
--
Ticket URL: <https://code.djangoproject.com/ticket/24632>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: yennicks | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: orm postgresql | Triage Stage: Accepted
table-inheritance inheritance |
object-relational |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* needs_docs: => 0
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
Seems worth exploring.
--
Ticket URL: <https://code.djangoproject.com/ticket/24632#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: yennicks | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: orm postgresql | Triage Stage: Accepted
table-inheritance inheritance |
object-relational |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by charettes):
I think it might be worth exploring a way to specify how model inheritance
should be handled at the database level.
This could allow us to support MTI (the actual), STI (Single Table
Inheritance) and PostgreSQL table inheritance.
IMHO it would be more worthwhile to abstract this whole concept with
documented caveats and limitations because it's going to be hard to mimic
the actual MTI implementation (Fk's as primary keys) with PostgreSQL table
inheritance.
For example, constraints are not inherited by children tables so the
following scenario would fail if we simply replaced the existing MTI model
on PostgreSQL by table inheritance.
{{{#!python
class Parent(models.Model):
pass
class Child(Parent):
pass
class Referent(models.Model):
parent = models.ForeignKey(Parent)
}}}
{{{#!sql
CREATE TABLE parent (id serial PRIMARY KEY);
CREATE TABLE child () INHERITS (parent);
CREATE TABLE referent (
parent_id int REFERENCES parent
);
}}}
{{{#!python
>>> child = Child.objects.create()
>>> ref = ParentReferent(parent=child)
IntegrityError ...
}}}
{{{#!sql
ERROR: insert or update on table "referent" violates foreign key
constraint "referent_parent_id_fkey"
DETAIL: Key (parent_id)=(1) is not present in table "parent".
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24632#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: yennicks | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: orm postgresql | Triage Stage: Accepted
table-inheritance inheritance |
object-relational |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by mjtamlyn):
For the record, I did actually consider this when working out which
features to include in contrib.postgres and ultimately rejected it as I'm
really not sure how we can handle it. I also spoke to some postgres people
who suggested that in most cases the way Django does inheritance is better
anyway. This is of course all up for discussion, but I don't intend to try
to implement this myself.
--
Ticket URL: <https://code.djangoproject.com/ticket/24632#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: yennicks | Owner:
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: orm postgresql | Triage Stage: Accepted
table-inheritance inheritance |
object-relational |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by auvipy):
what about postgresql foreign table inheritence?
--
Ticket URL: <https://code.djangoproject.com/ticket/24632#comment:4>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: orm postgresql | Triage Stage: Accepted
table-inheritance inheritance |
object-relational |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by Thomas Güttler):
Just for the records. There is a third party app which gives you single-
table-inheritance: https://github.com/craigds/django-typed-models
This works for all database engines, not just PostgreSQL.
Maybe this is a solution if the other two types of inheritance (abstract
or multi-table) don't fit your needs.
--
Ticket URL: <https://code.djangoproject.com/ticket/24632#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Yennick Schepers | Owner: (none)
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: orm postgresql | Triage Stage: Accepted
table-inheritance inheritance |
object-relational |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by bverhoeve):
I came across this ticket while exploring some more advanced PostgreSQL
features in combination with Django.
I was wondering if the ticket is still open? Is it still worth exploring
or were other solutions deemed more viable? Or is this a feature that
isn't used much in practice?
--
Ticket URL: <https://code.djangoproject.com/ticket/24632#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Yennick Schepers | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution:
Keywords: orm postgresql | Triage Stage: Accepted
table-inheritance inheritance |
object-relational |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by Simon Charette):
Based on the above discussions I think it might be better to invest time
in adding an entry point in how Django model inheritance is represented at
the database level rather then focusing on getting PostgreSQL multi-table
inheritance working by default which would be highly backward
incompatible.
It would be large undertaking but an API along the following lines would
be interesting.
{{{#!python
from django.db.models.inheritance import single_table_inheritance #
default is MTI
from django.contrib.postgres.inheritance import builtin_inheritance,
partition_inheritance
class Event(models.Model):
datetime = models.DateTimeField()
class SportEvent(Event, inheritance=builtin_inheritance):
pass
class Event2020(
Event, inheritance=partition_inheritance(
field='datetime',
range=(datetime(2020, 0, 0), datetime(2021, 0, 0)),
):
pass
class STIEvent(models.Model, inheritance=single_table_inheritance):
pass
}}}
That would allow different inheritance models to be used and implemented
in third-party applications while maintaining support for the current
abstract, proxy, and MTI based inheritance scheme.
--
Ticket URL: <https://code.djangoproject.com/ticket/24632#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Yennick Schepers | Owner: (none)
Component: contrib.postgres | Version: dev
Severity: Normal | Resolution: wontfix
table-inheritance inheritance |
object-relational |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by Simon Charette):
* resolution: => wontfix
* status: new => closed
Comment:
This was brought up [https://forum.djangoproject.com/t/postgresql-table-
inheritance-and-orm/39047/2 on the forum recently] which made me dig a bit
more in the latest stage of things.
Now that Postgres partitioning is done natively, which is what inheritance
was mainly recommended for prior to that,
[https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_table_inheritance
they recommend against using table inheritance] which I believe is a
strong enough motive to won't-fix this stagnant decade old new feature
request given how invasive such a change would be.
--
Ticket URL: <https://code.djangoproject.com/ticket/24632#comment:8>