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)
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;