[Django] #23713: SQlite3: too many SQL variables, Foreign Keys & Delete

[Django]

#23713: SQlite3: too many SQL variables, Foreign Keys & Delete
-------------------------------------+-------------------------------------
Reporter: Tanbouz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) | Keywords: sqlite cascade delete
Severity: Normal | foreign
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Using:
{{{
Python 3.4.2 (default, Oct 23 2014, 12:36:46)
[GCC 4.9.1] on linux
Type "help", "copyright", "credits" or "license" for more information.

import django
django.VERSION

(1, 7, 1, 'final', 0)

import sqlite3
sqlite3.version

'2.6.0'

sqlite3.sqlite_version

'3.8.7'
}}}

Database Structure

{{{
# Attempt to delete a row from the Categories table

Table-1 Categories
Table-2 Groups (FK: Groups -> Categories)
Table-3 Items (FK: Items -> Groups)
Table-R Features <-> Items (Many to Many) Error happens on a query to
delete rows from this table!
Table-4 Features
}}}



Test code:
{{{
from django.db import connection as dbconnection

# Pass a model to have its data deleted
def clean(db_model):
while db_model.objects.count():
row = db_model.objects.all()[:1]
# Can't use delete on a slice
row = db_model.objects.get(pk = row[0].pk)
try:
row.delete()
except django.db.utils.OperationalError:
print(dbconnection.queries)
}}}

dbconnection.queries prints several small SELECT queries but the
'''last''' query is a DELETE query with more than 1000 parameters

{{{
{'time': '0.001', 'sql': 'QUERY =
\'DELETE FROM "myapp_foreignmodel" WHERE
"myapp_foreignmodel"."foreignkey_id"
IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s....
And so on (1180 total in my case)
}}}


Errors:
{{{
django.db.utils.OperationalError: too many SQL variables
sqlite3.OperationalError: too many SQL variables
}}}

[https://code.djangoproject.com/ticket/16426 Related ticket #16426]

The above ticket solves the problem by splitting delete queries into
batches. It doesn't seem to include cascaded delete queries aimed at a
foreign table.

''
[https://docs.djangoproject.com/en/1.7/topics/db/queries/#deleting-objects
When Django deletes an object, by default it emulates the behavior of the
SQL constraint ON DELETE CASCADE – in other words, any objects which had
foreign keys pointing at the object to be deleted will be deleted along
with it.]''

--
Ticket URL: <https://code.djangoproject.com/ticket/23713>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#23713: SQlite3: too many SQL variables, Foreign Keys & Delete
-------------------------------------+-------------------------------------
Reporter: Tanbouz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: sqlite cascade | Unreviewed
delete foreign | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by timgraham):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0


Comment:

Note that the fix for #16426 isn't included in 1.7. Can you submit a test
for Django's test suite that fails on master?

--
Ticket URL: <https://code.djangoproject.com/ticket/23713#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#23713: SQlite3: too many SQL variables, Foreign Keys & Delete
-------------------------------------+-------------------------------------
Reporter: Tanbouz | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.7
(models, ORM) | Resolution: duplicate
Severity: Normal | Triage Stage:
Keywords: sqlite cascade | Unreviewed
delete foreign | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by Tanbouz):

* status: new => closed
* resolution: => duplicate


Comment:

I thought it was included in 1.7, my bad. I did the test again several
times using the same data and it worked fine in master. I will figure a
workaround for now.
Thanks timgraham

--
Ticket URL: <https://code.djangoproject.com/ticket/23713#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.