Postgres doesn't respect Django's "on_delete = models.CASCADE" in models.py. Why?

Question

I just migrated from SQLite3 to Postgres 12 (using pgloader).

I can't delete certain model objects, because there are other model objects referencing to it.

However, I'm very sure my model object has "on_delete = models.CASCADE" set in the referencing model object (in my Django code in models.py).

So I get this error generated by Postgres:

...
django.db.utils.IntegrityError: update or delete on table "app_reply" violates foreign key constraint "app_replyimage_reply_id_fkey" on table "app_replyimage"
DETAIL:  Key (id)=(SRB6Nf98) is still referenced from table "app_replyimage".

Is there a way (hopefully without manually editing the table schema in Postgres) to resolve this?

Edit: Adding some code below...

models.py

class ReplyImage(models.Model):
    id = models.CharField(default = make_id(), unique = True, primary_key = True, max_length = 8)
    
    file = models.ImageField(upload_to = customer_images_directory_path)
    
    reply = models.ForeignKey(Reply, on_delete = models.CASCADE, related_name = 'reply_images')
    
    #Meta
    created = models.DateTimeField(auto_now_add = True)
    updated = models.DateTimeField(auto_now = True)
    created_by = models.ForeignKey(User, on_delete = models.CASCADE, related_name = '+', null = True)
    updated_by = models.ForeignKey(User, on_delete = models.CASCADE, related_name = '+', null = True)
    
    def __str__(self):
        return str(self.id)
    
    def delete(self, *args, **kwargs):
        self.file.delete()
        super(ReplyImage, self).delete(*args, **kwargs)

@receiver(post_delete, sender = ReplyImage)
def reply_image_delete(sender, instance, **kwargs):
    instance.file.delete()

This is the referenced model, Reply:

class Reply(models.Model):
    id = models.CharField(default = make_id(), unique = True, primary_key = True, max_length = 8)
    
    #Content
    content = models.CharField(max_length = 560, blank = True, null = True)
    replies = GenericRelation('self')
    link = models.ForeignKey(Link, on_delete = models.SET_NULL, related_name = 'reply', blank = True, null = True)
    
    #Drip
    drip_interval_quantity = models.IntegerField(default = 0, blank = True, null = True) #Custom quantity
    drip_interval_calendar_unit = models.CharField(default = 'minute', max_length = 6, choices = DRIP_INTERVAL_CALENDAR_UNIT_CHOICES, blank = True, null = True) #Custom calendar unit
    
    post_datetime = models.DateTimeField(blank = True, null = True) #Post datetime
    
    #Twitter API
    self_status_id_str = models.CharField(max_length = 19, null = True, blank = True) #status_id_str of self
    in_reply_to_status_id_str = models.CharField(max_length = 19, null = True, blank = True) #status_id_str of tweet replied to
    
    #Meta
    created = models.DateTimeField(auto_now_add = True)
    updated = models.DateTimeField(auto_now = True)
    created_by = models.ForeignKey(User, on_delete = models.CASCADE, related_name = '+', null = True)
    updated_by = models.ForeignKey(User, on_delete = models.CASCADE, related_name = '+', null = True)
    
    #Mandatory fields for generic relation
    content_type = models.ForeignKey(ContentType, on_delete = models.CASCADE)
    object_id = models.CharField(default = make_id(), unique = True, max_length = 8)
    content_object = GenericForeignKey()
    
    customer = models.ForeignKey(Customer, on_delete = models.CASCADE, related_name = 'postreplies')
    
    def delete(self, *args, **kwargs):
        if self.reply_images.all():
            for i in self.reply_images.all():
                i.delete()
        
        if self.link:
            self.link.delete()
        
        super(Reply, self).delete(*args, **kwargs)
    
    def __str__(self):
        return self.content

@receiver(post_save, sender = Reply)
def reply_model_save(sender, instance, **kwargs):
    if kwargs['raw']:
        return
    
    recursive_reply_save_mock_post(instance)

@receiver(post_delete, sender = Reply)
def reply_model_delete(sender, instance, **kwargs):
    if instance.reply_images.all():
        for i in instance.reply_images.all():
            i.delete()

views.py

...
post.replies.all().delete()
...

(I redefined the delete() methods and also added post_delete() signals, but I don't think they should affect anything, as it worked perfectly with SQLite.)

What went wrong?

Edit 2:

Postgres table schema

                       Table "public.app_replyimage"
    Column     |           Type           | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
 file          | text                     |           |          |
 created       | timestamp with time zone |           |          |
 updated       | timestamp with time zone |           |          |
 created_by_id | bigint                   |           |          |
 reply_id      | text                     |           |          |
 updated_by_id | bigint                   |           |          |
 id            | text                     |           | not null |
Indexes:
    "idx_85696_sqlite_autoindex_app_replyimage_1" PRIMARY KEY, btree (id)
    "idx_85696_app_replyimage_created_by_id_a5974d1f" btree (created_by_id)
    "idx_85696_app_replyimage_reply_id_7a8aff2c" btree (reply_id)
    "idx_85696_app_replyimage_updated_by_id_d73f7446" btree (updated_by_id)
Foreign-key constraints:
    "app_replyimage_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES auth_user(id)
    "app_replyimage_reply_id_fkey" FOREIGN KEY (reply_id) REFERENCES app_reply(id)
    "app_replyimage_updated_by_id_fkey" FOREIGN KEY (updated_by_id) REFERENCES auth_user(id)

Postgres does not handle the DELETE triggers. Django itself does the cascade handling. — Willem Van Onsem, Jul 18 '20 at 22:09
Add your model definition to the question. Also the complete table definition from Postgres. Also code for how you are deleting objects. — Adrian Klaver, Jul 18 '20 at 22:13

score 5 · Accepted Answer · answered Jul 18 '20 at 22:14

5

In short: Postgres does not handle the ON DELETE triggers. Django itself does the cascade handling.

Indeed, models.CASCADE is a callable that is called and collects other elements that needs to be deleted. We can for example look at the source code [GitHub]:

def CASCADE(collector, field, sub_objs, using):
    collector.collect(
        sub_objs, source=field.remote_field.model, source_attr=field.name,
        nullable=field.null, fail_on_restricted=False,
    )
    if field.null and not connections[using].features.can_defer_constraint_checks:
        collector.add_field_update(field, None, sub_objs)

Here the collector is thus a collection that collects objects that needs to be deleted.

So Django will when you delete an object, determine what other objects need to be deleted, updated, etc. and then make the queries in the correct order. It does not use any triggers in the database.

In fact you can implement your own deletion policy by implementing a callable that has the same parameters, and runs different logic, although I would advise to keep it simple.

If you want to let Django do the same, you should alter the field such that it looks like:

ALTER TABLE app_replyimage ALTER COLUMN reply_id
    integer REFERENCES orders ON DELETE CASCADE;

answered Jul 18 '20 at 22:14

Willem Van Onsem

443,496
30
428
555

So if Django handles this behind the scenes (i.e. collecting and rearranging the model instances so they're deleted in the correct order so as not to trigger a Postgres foreign key constraint error), why would I still need to alter my Postgres table? I'm asking because I have a whole big bunch of those tables, and I'm trying not to alter them manually. – Hmong Bong Jul 18 '20 at 22:41
@HmongBong: you don't, what I mean is that if you delete it manually, for example in the shell, it will raise such error. The modeling however looks "strange". For example `default=make_id()` is a problem since that means it will make one id at startup, and then assign that id to all records, it should be `default=make_id` (without parenthesis). Furthermore I don't see why you use `.delete()` on the elements of `self.reply_images.all()` manually, since that is the work of Django, and probably will only make it worse. – Willem Van Onsem Jul 18 '20 at 22:43
@HmongBong: deleting media files is better done with a tool like [`django-unused-media`](https://pypi.org/project/django-unused-media/), especially since multiple `FileField`s can refer to the same file/image/... – Willem Van Onsem Jul 18 '20 at 22:44
@willem-van-onsen I'm aware of the awkwardness of `default = make_id()`, but it works fine for now, as I manually assign an ID anyway whenever I create a new model instance. No issue there. As for `self.reply_images.all()`, I've commented it out, and still get the same error from Postgres. I highly suspect my post_delete signals in Reply and ReplyImage are problematic, but I'm not sure. Thoughts? – Hmong Bong Jul 18 '20 at 22:55
@HmongBong: hmmm... you delete these in "bulk". Then the `delete()` method is not called on the individual elements. Is something else referencing to the `ReplyImage` model? – Willem Van Onsem Jul 18 '20 at 23:03
@willem-van-onsen No other references to `ReplyImage`. That said, I removed the `post_delete` receiver signal from the model ReplyImage, and everything worked fine. No more foreign key constraint errors. Strange. Thanks for your help anyhow, I really appreciate it! – Hmong Bong Jul 18 '20 at 23:21
What I do with ForeignKey() is have the action to ```on_delete``` be ```DO_NOTHING```. This keeps Django out of it and lets the database do the work assuming you have correctly set up the FK in the database. – Adrian Klaver Jul 19 '20 at 00:14
@AdrianKlaver: well for `CASCADE` that is indeed perfectly valid. For `SET(callable)` that would of course be a different story, since the callable can not run at the database side. This is sometimes used to run a query that will decide how to set the new value. Another thing that is a bit sad, is that right now one can not use a `RawTrigger(..)` or something, and thus that means that if one later migrates to another database, one has to update the triggers again. – Willem Van Onsem Jul 19 '20 at 00:47

Postgres doesn't respect Django's "on_delete = models.CASCADE" in models.py. Why?

1 Answers1