Login

Circular reference with Django ORM and Postgres without breaking NOT NULL FK constraints

Author:
pstiasny
Posted:
March 26, 2015
Language:
Python
Version:
Not specified
Tags:
django orm postgres
Score:
0 (after 0 ratings)

The recipe uses deferred constraint validation to create circular references across database tables. The example requires Postgres (MySQL doesn't support deferred constraints).

To achieve this, the following is required:

  • Insertions must be performed in a transaction. Foreign key constraints will be validated at the end of the transactions, allowing for insertion of rows with FKs pointing to rows that don't exist yet.

  • Primary keys need to be generated before insertion. That's what prefetch_id does by pulling the next value from the *_id_seq sequence.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
#
# models.py
#

from django.db import connection, models
 
 
class PrefetchIDMixin(object):
    def prefetch_id(self):
        # <https://djangosnippets.org/snippets/2731/>
        cursor = connection.cursor()
        cursor.execute(
            "SELECT nextval('{0}_{1}_{2}_seq'::regclass)".format(
                self._meta.app_label.lower(),
                self._meta.object_name.lower(),
                self._meta.pk.name,
            )
        )
        row = cursor.fetchone()
        cursor.close()
        self.pk = row[0]
 
 
class Master(PrefetchIDMixin, models.Model):
    name = models.CharField(max_length=20)
    main_thing = models.OneToOneField('Detail', related_name='main_thing_of')
 
 
class Detail(models.Model):
    name = models.CharField(max_length=20)
    master = models.ForeignKey('Master')
    def __unicode__(self): return self.name


#
# tests.py
#

from django.test import TestCase
from django.db import transaction
from .models import Master, Detail
 
 
class CircularReference(TestCase):
    def test_adding_with_circular_reference(self):
        # Postgres will defer validation of foreign key constraints 
        # until the end of the transaction
        with transaction.atomic():
            m = Master(name='Zardoz')
 
            # NOT NULL constraints can't be defered in postgres, so
            # foreign key fields need to be populated beforehand
            # <http://postgresql.nabble.com/DEFERRABLE-NOT-NULL-constraint-tp5743655p5743779.html>
            m.prefetch_id()
            Detail.objects.create(master=m, name='gun')
            m.main_thing = Detail.objects.create(master=m, name='Zed')
 
            m.save()
        
        m = Master.objects.get()
        self.assertQuerysetEqual(
            m.detail_set.order_by('name').all(),
            ['<Detail: gun>', '<Detail: Zed>'])
        self.assertEqual(m.main_thing, Detail.objects.get(name='Zed'))

More like this

Comments

Please login first before commenting.