Opened 3 years ago

Last modified 7 months ago

#24632 new New feature

PostgreSQL table inheritance

Reported by: Yennick Schepers Owned by:
Component: contrib.postgres Version: master
Severity: Normal Keywords: orm postgresql table-inheritance inheritance object-relational
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (5)

comment:1 Changed 3 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

Seems worth exploring.

comment:2 Changed 3 years ago by Simon Charette

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.

class Parent(models.Model):
    pass

class Child(Parent):
    pass

class Referent(models.Model):
    parent = models.ForeignKey(Parent)
CREATE TABLE parent (id serial PRIMARY KEY);
CREATE TABLE child () INHERITS (parent);
CREATE TABLE referent (
    parent_id int REFERENCES parent 
);
>>> child = Child.objects.create()
>>> ref = ParentReferent(parent=child)
IntegrityError ...
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".

comment:3 Changed 3 years ago by Marc Tamlyn

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.

comment:4 Changed 2 years ago by Asif Saifuddin Auvi

what about postgresql foreign table inheritence?

comment:5 Changed 7 months ago 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.

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