Thanks for your reply Anssi.
My previous post probably got lost somewhere (or maybe just awaiting
moderation?), so I am going to repeat the relevant part. It is related with
your point on qs.filter(id__in=large_list), so I will try to say it again.
Why do you use SQL variables when you construct query for the IN operator?
It does not make sense to me for both -- bulk inserts (which you are
currently solving), as well as the IN operator -- or at least not for
integer arguments. I think there is no limit on the IN clause argument size
in SQLite and for databases where there is, it could be solved by breaking
it into OR clauses, as is already done). A simple solution for the IN
operator problems would be to change the Django code in
db/models/sql/where.py in a way that it puts the parameters into the SQL
query straight away instead of as parameters, e.g.:
201: return ('%s IN (%s)' % (field_sql,
202: ', '.join(repeat('%s',
len(params))) % tuple(params)) , ())
This worked for my particular case. The only limitation that can be reached
is a 1MB size limit on query in SQLite. Is this going to break anything?
Best,
Lukas
On Monday, April 23, 2012 10:58:19 AM UTC+2, akaariai wrote:
>
> On Monday, April 23, 2012 1:56:19 AM UTC+3, Russell Keith-Magee wrote:
>>
>> It's also probable that it's something that is new to 1.4 (or, at least,
>> that it manifests in slightly different ways in 1.4). One of the features
>> added in 1.4 is bulk insertion of data. This means that you can use a
>> single SQL statement to insert all your m2m relation; however, the downside
>> is that on SQLite, there is a limit of 500 insertions that can be done at
>> any one time (2 values per insertion).
>>
>> This is logged as ticket #17788. There have been some discussions about a
>> fix for this problem that will break the bulk insertion into batches. I
>> suspect that when this problem is fixed, your problem will go away.
>>
> There are at least three different instances of the 1000 variables
> problem. One is the bulk_create() which is likely to get fixed. Then there
> is delete(). This might get fixed. The last one is
> qs.filter(id__in=large_list) which will not get fixed.
>
> The reason for fixing bulk_create() is that it is somewhat easy to do so.
> Just split the batch of objects into smaller batches and iterate. delete()
> isn't as easy, but the same split + iterate approach could work. If it gets
> too complicated then it will not be fixed, if it is easy then it might get
> fixed. The last one, qs.filter(id__in=large_list) is pretty much impossible
> to fix. The reason is you can't split that query into multiple parts. Or
> not for any non-trivial query anyways. For example ORDER BY is enough to
> break any attempt to split the query into parts.
>
> While Django's ORM should hide limitations in the underlying database it
> can't do it perfectly. My viewpoint is that Django should hide limitations
> if it is easy to do so. It is all about cost-benefit ratio.
>
> For your particular problem you have at least these choices:
> 1. Use different database. I guess you have a reason to use SQLite so
> this might not be an option.
> 2. Compile SQLite yourself with a higher parameter limit. If you need to
> deploy your software on multiple machines this might be hard to do.
> 3. Work around the issues in your code. Maybe a ModelMultipleChoiceField
> subclass which does the .clean() in a safe way would work. Or better yet do
> as Russell said: try to remove the need to have 1000 options in one field
> altogether.
>
> - Anssi
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-users/-/95QFVjj5QosJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
