Re: model-validation: Excessive (and weird) queries during validation

[Mark L.]

On Nov 2, 10:27 pm, Karen Tracey  wrote:
> On Mon, Nov 2, 2009 at 1:41 PM, Mark L.  wrote:
>
> > As the subject states, I am using the model-validation branch at the
> > moment, which allows me to do, well, validation in the models.
>
> > I've however, noticed a very strange behaviour of the validation
> > logic, - any time  the clean() method is fired on the
> > "materialized" (saved or retrieved from the database) model, that
> > contains unique fields, the following query is fired (let me just give
> > you an example):
>
> > from django.db import models
>
> > class A(models.Model):
> >    pass
>
> > a = A.objects.create()
> > a.clean()
>
> > a.clean() results, among everything, in this: 'SELECT (1) AS "a" FROM
> > "val_a" WHERE ("val_a"."id" = 1 AND NOT ("val_a"."id" = 1 ))'
>
> > I might be missing something vital, but what is that "where" clause
> > there for? What is it supposed to check?
>
> It's trying to verify the uniqueness of the supposed-to-be-unique field id.
> The query makes sense for fields other than the primary key, where the WHERE
> clause turns out more like:
>
> WHERE model.unique_field = proposed_value AND NOT model.id =
> current_instance.pk
>
> That is, it's asking "Is there any other instance in the DB, besides this
> one I'm looking at, that has the proposed_value for this
> supposed-to-be-unique field?"  If the answer is no, then all is fine, the
> proposed value doesn't violate the unique constraint.  If the answer is yes,
> then the validation check needs to fail because saving this model instance
> to the DB would violate the unique constraint.
>
> Of course, it doesn't make sense for the primary key field.  For that field,
> when you exclude the current instance from the search the WHERE clause
> becomes impossible to satisfy.  The model validation code, I'd guess, has
> been moved from where it was initially implemented (ModelForms).  In its
> original place the primary key field would not ordinarily have been included
> in the list of unique fields to check because it wouldn't ordinarily be on
> the form, and only values on the form are checked for uniqueness.
>
> The code in the new place likely needs to realize the primary key field
> shouldn't be checked for uniqueness.  You could check to see if this has
> been reported in trac and if not open a ticket for it.  I don't recall it
> having been brought up by anyone else but I could have missed it.
>
> Karen

Thanks for the quick reply, Karen

I've investigated a bit and it seems, that the problem is just what
you've said - the logic, that decides whether the field needs to be
checked for uniqueness or not, just doesn't take into account the
fact, that the field might be a primary key. Should be an easy fix,
especially since it doesn't seem to affect the ModelForm validation.

I've created a ticket on the issue tracker with a demonstration of the
problem and the fix.

Either way, it should be noted, that adding unique fields to the
models (besides the obvious pk field), can cause extreme performance
drops. Imagine cleaning a collection of 200 or objects causing 200 db
queries to be run (I'll benchmark it later). Perhaps the uniqueness
check for the Model could be moved to save() instead of clean() just
for this one reason?

Mark
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
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
-~--~~~~--~~--~--~---

Re: model-validation: Excessive (and weird) queries during validation

[Karen Tracey]

On Mon, Nov 2, 2009 at 1:41 PM, Mark L.  wrote:

>
> As the subject states, I am using the model-validation branch at the
> moment, which allows me to do, well, validation in the models.
>
> I've however, noticed a very strange behaviour of the validation
> logic, - any time  the clean() method is fired on the
> "materialized" (saved or retrieved from the database) model, that
> contains unique fields, the following query is fired (let me just give
> you an example):
>
> from django.db import models
>
> class A(models.Model):
>pass
>
> a = A.objects.create()
> a.clean()
>
> a.clean() results, among everything, in this: 'SELECT (1) AS "a" FROM
> "val_a" WHERE ("val_a"."id" = 1 AND NOT ("val_a"."id" = 1 ))'
>
> I might be missing something vital, but what is that "where" clause
> there for? What is it supposed to check?


It's trying to verify the uniqueness of the supposed-to-be-unique field id.
The query makes sense for fields other than the primary key, where the WHERE
clause turns out more like:

WHERE model.unique_field = proposed_value AND NOT model.id =
current_instance.pk

That is, it's asking "Is there any other instance in the DB, besides this
one I'm looking at, that has the proposed_value for this
supposed-to-be-unique field?"  If the answer is no, then all is fine, the
proposed value doesn't violate the unique constraint.  If the answer is yes,
then the validation check needs to fail because saving this model instance
to the DB would violate the unique constraint.

Of course, it doesn't make sense for the primary key field.  For that field,
when you exclude the current instance from the search the WHERE clause
becomes impossible to satisfy.  The model validation code, I'd guess, has
been moved from where it was initially implemented (ModelForms).  In its
original place the primary key field would not ordinarily have been included
in the list of unique fields to check because it wouldn't ordinarily be on
the form, and only values on the form are checked for uniqueness.

The code in the new place likely needs to realize the primary key field
shouldn't be checked for uniqueness.  You could check to see if this has
been reported in trac and if not open a ticket for it.  I don't recall it
having been brought up by anyone else but I could have missed it.

Karen

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
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
-~--~~~~--~~--~--~---

model-validation: Excessive (and weird) queries during validation

[Mark L.]

As the subject states, I am using the model-validation branch at the
moment, which allows me to do, well, validation in the models.

I've however, noticed a very strange behaviour of the validation
logic, - any time  the clean() method is fired on the
"materialized" (saved or retrieved from the database) model, that
contains unique fields, the following query is fired (let me just give
you an example):

from django.db import models

class A(models.Model):
pass

a = A.objects.create()
a.clean()

a.clean() results, among everything, in this: 'SELECT (1) AS "a" FROM
"val_a" WHERE ("val_a"."id" = 1 AND NOT ("val_a"."id" = 1 ))'

I might be missing something vital, but what is that "where" clause
there for? What is it supposed to check? The stacktrace for the query
is the following:
787  clean django/db/models/base.py
619   validate django/db/models/base.py
624   validate_unique django/db/models/base.py
683   _perform_unique_checks django/db/models/base.py
112   __nonzero__ django/db/models/query.py
106   _result_iter django/db/models/query.py
692   _fill_cache django/db/models/query.py
756   iterator django/db/models/query.py
287   results_iter django/db/models/sql/query.py
2369 execute_sql django/db/models/sql/query.py

Apparently, it is happening inside the uniqueness check. Anyway, the
most strange thing, is that calling clean() results in a query. I've
noticed it when I was displaying a set of a few hundred objects (each
of them has to be cleaned before sending the data to template), which
resulted in a few hundred of queries (for a single request), which
brought my dev-machine to the knees. It becomes worse if the cleaned
model is a part of a table-inheritance hierarchy, - clean() is called
sequentially for every model "up" the hierarchy, which results in a
tremendous number of queries executed per single request.

I must say, however, that I, at this moment, have no idea how to check
for uniqueness *without* issuing a query and, from what I see, django
doesn't like to assume things, so the db hits *will* be made.

Obviously, I can swindle Django into not checking uniqueness on clean
(by rolling my own clean method in models.Model subclasses), however,
that doesn't sound like a very good idea (if alternatives exist).

Sorry if this is an inappropriate list/group for this question. Maybe
I should instead address it to the django-developers or the issue
tracker.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
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
-~--~~~~--~~--~--~---