Opened 4 years ago

Closed 7 months ago

#16426 closed Bug (fixed)

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

Reported by: kmtracey Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: will@…, AkosLadanyi Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (19)

comment:1 Changed 4 years ago by aaugustin

  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 3 years ago by 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.

comment:3 Changed 3 years ago by mbaechtold

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."

comment:4 Changed 3 years ago by 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.

comment:5 Changed 3 years ago by 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.

comment:6 Changed 3 years ago by will@…

  • Cc will@… added

comment:7 Changed 3 years ago by ramiro

See also #17788.

comment:8 Changed 3 years ago by anonymous

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

comment:9 Changed 3 years ago by aaugustin

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

comment:10 Changed 3 years ago by 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';

comment:11 Changed 2 years ago by aaugustin

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

comment:13 Changed 2 years ago by 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.

comment:12 Changed 2 years ago by 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.

comment:13 Changed 2 years ago by akaariai

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

comment:14 Changed 2 years ago by akaariai

  • Has patch set

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.

comment:15 Changed 2 years ago by AkosLadanyi

  • Cc AkosLadanyi added

comment:16 Changed 18 months ago by NiGhTTraX

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

comment:17 Changed 17 months ago by aaugustin

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

comment:18 Changed 7 months ago by Anssi Kääriäinen <akaariai@…>

  • Resolution set to fixed
  • Status changed from new to closed

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.

Note: See TracTickets for help on using tickets.
Back to Top