Opened 12 years ago

Closed 9 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 Changed 12 years ago by Aymeric Augustin

Triage Stage: UnreviewedAccepted

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

comment:4 Changed 12 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 12 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 12 years ago by will@…

Cc: will@… added

comment:7 Changed 12 years ago by Ramiro Morales

See also #17788.

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

comment:10 Changed 11 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 11 years ago by Aymeric Augustin

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

comment:13 Changed 11 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 11 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 11 years ago by Anssi Kääriäinen

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

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

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

Cc: Akos Ladanyi added

comment:16 Changed 10 years ago by Andrei Picus

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

comment:17 Changed 10 years ago by Aymeric Augustin

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

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

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