sqlite: Cannot delete more than 999 things if there is a relation pointing to them

[Karen Tracey]

Given these models:

class Thing(models.Model):
    name = models.CharField(max_length=32)

    def __unicode__(self):
        return self.name


class RelatedThing(models.Model):
    thing = models.ForeignKey(Thing)

    def __unicode__(self):
        return u'object related to %s.' % self.thing

using sqlite as the DB, attempting to delete more than 999 Things in one go fails:

--> python manage.py shell
/home/kmtracey/django/hg-django/django/conf/__init__.py:75: DeprecationWarning: The ADMIN_MEDIA_PREFIX setting has been removed; use STATIC_URL instead.
  "use STATIC_URL instead.", DeprecationWarning)
Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41) 
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from ttt.models import Thing
>>> Thing.objects.count()
0
>>> for i in range(1000):
...     Thing.objects.create(name='Thing %d' % i)
... 
<Thing: Thing 0>
<Thing: Thing 1>
<Thing: Thing 2>
[...output snipped...]
<Thing: Thing 996>
<Thing: Thing 997>
<Thing: Thing 998>
<Thing: Thing 999>
>>> Thing.objects.all().delete()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/kmtracey/django/hg-django/django/db/models/query.py", line 444, in delete
    collector.collect(del_query)
  File "/home/kmtracey/django/hg-django/django/db/models/deletion.py", line 167, in collect
    if not sub_objs:
  File "/home/kmtracey/django/hg-django/django/db/models/query.py", line 113, in __nonzero__
    iter(self).next()
  File "/home/kmtracey/django/hg-django/django/db/models/query.py", line 107, in _result_iter
    self._fill_cache()
  File "/home/kmtracey/django/hg-django/django/db/models/query.py", line 784, in _fill_cache
    self._result_cache.append(self._iter.next())
  File "/home/kmtracey/django/hg-django/django/db/models/query.py", line 273, in iterator
    for row in compiler.results_iter():
  File "/home/kmtracey/django/hg-django/django/db/models/sql/compiler.py", line 699, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/kmtracey/django/hg-django/django/db/models/sql/compiler.py", line 754, in execute_sql
    cursor.execute(sql, params)
  File "/home/kmtracey/django/hg-django/django/db/backends/util.py", line 34, in execute
    return self.cursor.execute(sql, params)
  File "/home/kmtracey/django/hg-django/django/db/backends/sqlite3/base.py", line 226, in execute
    return Database.Cursor.execute(self, query, params)
DatabaseError: too many SQL variables
>>> Thing.objects.count()
1000
>>> Thing.objects.filter(pk__gte=1000).delete()
>>> Thing.objects.count()
999
>>> Thing.objects.all().delete()
>>> Thing.objects.count()
0

The problem is occurring when trying to collect the RelatedThings that might need to be deleted along with the Things being deleted. The SQL it is trying to execute is of the form:

SELECT [list_of_columns] FROM [related_table] WHERE [related_table]."thing_id" IN ([list of 1000 pks of Things being deleted])

I'm guessing this is due to item #9 in ​http://www.sqlite.org/limits.html

I tried the same thing on 1.2.X branch level and did not see the failure, it is new with 1.3 (I did also try 1.3 from around 1.3 beta in addition to the above which is with current trunk...it failed with the pre-release 1.3 as well).

[version2beta]

Looks to me like the problem is with the reference to sub_objs in django/db/models/deletion.py line 166. At that point, one cannot reference the object without generating a DatabaseError: too many SQL variables error.

[Martin Bächtold]

Maybe this is SQLite limition.

See "9. Maximum Number Of Host Parameters In A Single SQL Statement" on ​http://www.sqlite.org/limits.html:

"To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999."

[adam.hotz@…]

I have also found this problem using an SQLite database.
I am working around it using code similar to the following:

if not connection.features.supports_1000_query_parameters:

while len(Thing.objects.all()):

pks = Thing.objects.all()[0:999]
Thing.objects.filter(pkin = pks).delete()

However this is far from optimal.

[anonymous]

Never mind, it was easier just to recompile python and then copy the sqlite3.dll and _sqlite.pyd files into my install. I changed the limit to MAX_INT. A better solution would be for the sqlite3 python module to expose the int sqlite3_limit(sqlite3*, int id, int newVal) runtime method.

[Ramiro Morales]

See also #17788.

[anonymous]

couldn't some kind of checking be done in sqlite backend's delete() method so it deletes the requested objects in groups < 999?

[Aymeric Augustin]

It isn't that easy -- in particular, it could break assumptions regarding transactions and integrity (in case of a self-referencing foreign key).

[anonymous]

Perhaps a silly question but why does it need to list the PK of all the things being deleted? This seems inefficient and vulnerable to limits like the one in SQLite. Why not use SQL DELETE statements with WHERE clause?

Thing.objects.all().delete()
=> DELETE FROM Thing;

Thing.objects.filter(field='Whatsit').delete()
=> DELETE FROM Thing WHERE field='Whatsit';

[Aymeric Augustin]

Unfortunately, this bug makes the test suite fail under SQLite since I added a test that deletes all content types.

[Aymeric Augustin <aymeric.augustin@…>]

In a892cd3191cd2e0d98756764ed7be3ad59b95850:

[1.5.x] Tweak a test to avoid hitting a limit with SQLite.

Django cannot delete more than 999 objects at a time with SQLite.

Refs #16426, #16039.

Backport of 2875b5d from master.

[Aymeric Augustin <aymeric.augustin@…>]

In 2875b5dcab23c027d019656b08da8b911bc60711:

Tweak a test to avoid hitting a limit with SQLite.

Django cannot delete more than 999 objects at a time with SQLite.

Refs #16426, #16039.

[Anssi Kääriäinen]

Here is a quick attempt for fixing the limit issue: ​https://github.com/akaariai/django/compare/ticket_16426

[Anssi Kääriäinen]

I have force-updated the ​https://github.com/akaariai/django/compare/ticket_16426 branch, and now the patch is looking pretty good to me. The query-amount counting in the added test case is ugly... But it is just ugliness in a test case.

I would not be surprised if there are delete queries which still hit the sqlite limit - generic foreign keys, multi-inheritance etc. But, getting this moved forward is enough IMO.

Master-only in my opinion, we can consider backpatching to 1.5.x, but lets first see how this behaves in master.

[Andrei Picus]

Any updates regarding this issue? The patch looks good to me. Should be at least merged into master.

[Aymeric Augustin]

#21205 was a duplicate with an alternative patch: ​https://github.com/django/django/pull/1699

[Anssi Kääriäinen <akaariai@…>]

In dfadbdac6a63dce3304dff1977b5b0a15dc2d7b5:

Fixed #16426 -- deletion of 1000+ objects with relations on SQLite

SQLite doesn't work with more than 1000 parameters in a single query.
The deletion code could generate queries that try to get related
objects for more than 1000 objects thus breaking the limit. Django now
splits the related object fetching into batches with at most 1000
parameters.

The tests and patch include some work done by Trac alias NiGhTTraX in
ticket #21205.