django database delete specific number of entries

Question

How to delete specific number of entries from the database? I did something like this

EntriesToDelete=Statusmessages.objects.filter(time__lt=date)[:30000]
EntriesToDelete.delete()

But I get an error which says: AssertionError. Cannot use 'limit' or 'offset' with delete.

How can I specify the number of entries to be deleted.

Dissatisfactory

You can accelerate this process by using: _raw_delete. Here is related answer: https://mcmap.net/q/348296/-how-to-make-django-queryset-bulk-delete-more-efficient . Please read limitations and drawbacks of this method carefully. – Principium

Answer 1

You could do it like this:

pks = (Statusmessages.objects
       .filter(time__lt=date)
       .values_list('pk')[:30000])
Statusmessages.objects.filter(pk__in=pks).delete()

Zucker

I used something similar and looped over it to maintain prevent the memory usage of the DB from going to wild when deleting millions of rows – Lukelukens

Nice. You probably also need 'flat=True' in the values_list call. – Briton

MariaDB/Mysql both don't like this - you get django.db.utils.NotSupportedError: (1235, "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"). – Marry

The thing to watch out for using the approach @Lukelukens described is a race condition or contention if the targeted records are being accessed by another user or process. – Briones

Answer 2

use this:

Statusmessages.objects.filter(pk__in=Statusmessages.objects.filter(time__lt=date).values_list('pk', flat=True)[:30000]).delete()

use flat=True, if not then is a tuple and raise exception:

{NotSupportedError}(1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")

Schopenhauer

Answer 3

If you get the following error:

django.db.utils.NotSupportedError: (1235, "This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")

Force evaluate the list of ids with list(). Here's an example:

sessions_to_delete = LoginSession.objects.filter(user=self.userProfile)[3:]
pks_of_sessions_to_delete = list(sessions_to_delete.values_list("pk", flat=True))
LoginSession.objects.filter(pk__in=pks_of_sessions_to_delete).delete()

Insecticide