Opened 13 years ago

Closed 10 years ago

#16426 closed Bug (fixed)

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

Reported by: Karen Tracey Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: will@…, Akos Ladanyi 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 by Aymeric Augustin, 13 years ago

Triage Stage: UnreviewedAccepted

comment:2 by version2beta, 13 years ago

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 by Martin Bächtold, 12 years ago

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 by adam.hotz@…, 12 years ago

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 by anonymous, 12 years ago

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 by will@…, 12 years ago

Cc: will@… added

comment:7 by Ramiro Morales, 12 years ago

See also #17788.

comment:8 by anonymous, 12 years ago

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 by Aymeric Augustin, 12 years ago

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 by anonymous, 12 years ago

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 by Aymeric Augustin, 11 years ago

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

comment:13 by Aymeric Augustin <aymeric.augustin@…>, 11 years ago

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 by Aymeric Augustin <aymeric.augustin@…>, 11 years ago

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 by Anssi Kääriäinen, 11 years ago

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

comment:14 by Anssi Kääriäinen, 11 years ago

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 by Akos Ladanyi, 11 years ago

Cc: Akos Ladanyi added

comment:16 by Andrei Picus, 10 years ago

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

comment:17 by Aymeric Augustin, 10 years ago

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

comment:18 by Anssi Kääriäinen <akaariai@…>, 10 years ago

Resolution: fixed
Status: newclosed

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