Re: making queryset.delete issue only a single SQL query

[Tobias McNulty]

On Sun, Sep 19, 2010 at 11:41 AM, Alex Gaynor  wrote:

> On Sun, Sep 19, 2010 at 10:28 AM, drakkan  wrote:
> > in my opinion django should emulate "ON DELETE CASCADE" only on
> > database backends that doesn't support it, if you are using a database
> > such as postgres delete() on a queryset should issue a single sql
> > query and should be the database to care about the cascade/set null
> > etc.. behaviour
> >
> > http://code.djangoproject.com/ticket/7539
> >
> > I think this way django could archive the best performance,
> >
> > if one need to delete a lot of row and there are relations to other
> > tables a single raw SQL query is not enough, you need to modify the
> > database schema too to ensure the correct cascade behaviuor or you
> > need to issue an sql query for every related table
>
>
> We also support cascading generic relationships, which no database
> supports.
>

And currently the pre- and post-delete signals are called for each object,
even in a bulk delete, so changing that would be a backwards-incompatible
change.  I don't much like that it does this, but changing it would still
undoubtedly cause trouble for some people.

That said, queryset.update() does NOT iterate through all the objects in a
queryset and call save(), and nor does it call the pre- and post-save
signals. [1]

We have two bulk operations that behave differently; would it be worthwhile,
at least, to make the behavior configurable?

Tobias

[1]
http://docs.djangoproject.com/en/dev/topics/db/queries/#updating-multiple-objects-at-once
-- 
Tobias McNulty, Managing Partner
Caktus Consulting Group, LLC
http://www.caktusgroup.com

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-develop...@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: making queryset.delete issue only a single SQL query

[Alex Gaynor]

On Sun, Sep 19, 2010 at 10:28 AM, drakkan  wrote:
>
>
> On 17 Set, 15:38, Tobias McNulty  wrote:
>> On Fri, Sep 17, 2010 at 8:27 AM, SmileyChris  wrote:
>>
>> >  On Sep 11, 1:12 pm, Tobias McNulty  wrote:
>> > > I may be missing something, but queryset.delete() seems oddly implemented
>> > in
>> > > Django.  It does a select to get all the IDs to be deleted, and then
>> > deletes
>> > > them, in blocks of 100 I believe, by ID.
>>
>> > It's because .delete() is emulating the behavior of the SQL constraint
>> > ON DELETE CASCADE
>>
>> > A list of objects to be deleted is recursively populated, then this
>> > complete list of objects is iteratively deleted (also calling the
>> > pre_delete and post_delete signals in their respective places).
>>
>> Hm, I see that now, and I suppose there's no sense in changing that
>> behavior.
>>
>> To my credit, the docs are a little misleading, specifically the line
>> reading "Keep in mind that this will, whenever possible, be executed purely
>> in SQL, and so the delete() methods of individual object instances will not
>> necessarily be called during the process." [1]  Additionally, is ambiguous
>> whether the part about "ON DELETE CASCADE" applies just to single objection
>> deletion or to queryset deletion as well.  Admittedly what it says is not
>> wrong, but it does /suggest/ that delete() on a queryset doesn't do anything
>> per-object, which is not true at all.
>>
>> Perhaps I'll work on clarifying the docs and adding a warning that Django's
>> delete() on a queryset will chunk the actual deletions--in addition to
>> calling signals and deleting related objects one by one--so raw SQL should
>> be used instead if one needs to delete a lot of rows and efficiency is a
>> concern?  Does that seem reasonable?
>
> in my opinion django should emulate "ON DELETE CASCADE" only on
> database backends that doesn't support it, if you are using a database
> such as postgres delete() on a queryset should issue a single sql
> query and should be the database to care about the cascade/set null
> etc.. behaviour
>
> http://code.djangoproject.com/ticket/7539
>
> I think this way django could archive the best performance,
>
> if one need to delete a lot of row and there are relations to other
> tables a single raw SQL query is not enough, you need to modify the
> database schema too to ensure the correct cascade behaviuor or you
> need to issue an sql query for every related table
>
> Nicola
>
>>
>> Tobias
>>
>> [1]http://docs.djangoproject.com/en/dev/topics/db/queries/#deleting-objects
>> --
>> Tobias McNulty, Managing Partner
>> Caktus Consulting Group, LLChttp://www.caktusgroup.com
>
> --
> You received this message because you are subscribed to the Google Groups 
> "Django developers" group.
> To post to this group, send email to django-develop...@googlegroups.com.
> To unsubscribe from this group, send email to 
> django-developers+unsubscr...@googlegroups.com.
> For more options, visit this group at 
> http://groups.google.com/group/django-developers?hl=en.
>
>

We also support cascading generic relationships, which no database supports.

Alex

-- 
"I disapprove of what you say, but I will defend to the death your
right to say it." -- Voltaire
"The people's good is the highest law." -- Cicero
"Code can always be simpler than you think, but never as simple as you
want" -- Me

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-develop...@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: making queryset.delete issue only a single SQL query

[drakkan]

On 17 Set, 15:38, Tobias McNulty  wrote:
> On Fri, Sep 17, 2010 at 8:27 AM, SmileyChris  wrote:
>
> >  On Sep 11, 1:12 pm, Tobias McNulty  wrote:
> > > I may be missing something, but queryset.delete() seems oddly implemented
> > in
> > > Django.  It does a select to get all the IDs to be deleted, and then
> > deletes
> > > them, in blocks of 100 I believe, by ID.
>
> > It's because .delete() is emulating the behavior of the SQL constraint
> > ON DELETE CASCADE
>
> > A list of objects to be deleted is recursively populated, then this
> > complete list of objects is iteratively deleted (also calling the
> > pre_delete and post_delete signals in their respective places).
>
> Hm, I see that now, and I suppose there's no sense in changing that
> behavior.
>
> To my credit, the docs are a little misleading, specifically the line
> reading "Keep in mind that this will, whenever possible, be executed purely
> in SQL, and so the delete() methods of individual object instances will not
> necessarily be called during the process." [1]  Additionally, is ambiguous
> whether the part about "ON DELETE CASCADE" applies just to single objection
> deletion or to queryset deletion as well.  Admittedly what it says is not
> wrong, but it does /suggest/ that delete() on a queryset doesn't do anything
> per-object, which is not true at all.
>
> Perhaps I'll work on clarifying the docs and adding a warning that Django's
> delete() on a queryset will chunk the actual deletions--in addition to
> calling signals and deleting related objects one by one--so raw SQL should
> be used instead if one needs to delete a lot of rows and efficiency is a
> concern?  Does that seem reasonable?

in my opinion django should emulate "ON DELETE CASCADE" only on
database backends that doesn't support it, if you are using a database
such as postgres delete() on a queryset should issue a single sql
query and should be the database to care about the cascade/set null
etc.. behaviour

http://code.djangoproject.com/ticket/7539

I think this way django could archive the best performance,

if one need to delete a lot of row and there are relations to other
tables a single raw SQL query is not enough, you need to modify the
database schema too to ensure the correct cascade behaviuor or you
need to issue an sql query for every related table

Nicola

>
> Tobias
>
> [1]http://docs.djangoproject.com/en/dev/topics/db/queries/#deleting-objects
> --
> Tobias McNulty, Managing Partner
> Caktus Consulting Group, LLChttp://www.caktusgroup.com

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-develop...@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: making queryset.delete issue only a single SQL query

[Tobias McNulty]

On Fri, Sep 17, 2010 at 8:27 AM, SmileyChris  wrote:
>
>  On Sep 11, 1:12 pm, Tobias McNulty  wrote:
> > I may be missing something, but queryset.delete() seems oddly implemented
> in
> > Django.  It does a select to get all the IDs to be deleted, and then
> deletes
> > them, in blocks of 100 I believe, by ID.
>
> It's because .delete() is emulating the behavior of the SQL constraint
> ON DELETE CASCADE
>
> A list of objects to be deleted is recursively populated, then this
> complete list of objects is iteratively deleted (also calling the
> pre_delete and post_delete signals in their respective places).


Hm, I see that now, and I suppose there's no sense in changing that
behavior.

To my credit, the docs are a little misleading, specifically the line
reading "Keep in mind that this will, whenever possible, be executed purely
in SQL, and so the delete() methods of individual object instances will not
necessarily be called during the process." [1]  Additionally, is ambiguous
whether the part about "ON DELETE CASCADE" applies just to single objection
deletion or to queryset deletion as well.  Admittedly what it says is not
wrong, but it does /suggest/ that delete() on a queryset doesn't do anything
per-object, which is not true at all.

Perhaps I'll work on clarifying the docs and adding a warning that Django's
delete() on a queryset will chunk the actual deletions--in addition to
calling signals and deleting related objects one by one--so raw SQL should
be used instead if one needs to delete a lot of rows and efficiency is a
concern?  Does that seem reasonable?

Tobias

[1] http://docs.djangoproject.com/en/dev/topics/db/queries/#deleting-objects
-- 
Tobias McNulty, Managing Partner
Caktus Consulting Group, LLC
http://www.caktusgroup.com

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-develop...@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.

Re: making queryset.delete issue only a single SQL query

[SmileyChris]

On Sep 11, 1:12 pm, Tobias McNulty  wrote:
> Hi All,
>
> I may be missing something, but queryset.delete() seems oddly implemented in
> Django.  It does a select to get all the IDs to be deleted, and then deletes
> them, in blocks of 100 I believe, by ID.

It's because .delete() is emulating the behavior of the SQL constraint
ON DELETE CASCADE

A list of objects to be deleted is recursively populated, then this
complete list of objects is iteratively deleted (also calling the
pre_delete and post_delete signals in their respective places).

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers" group.
To post to this group, send email to django-develop...@googlegroups.com.
To unsubscribe from this group, send email to 
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-developers?hl=en.