Re: [Django] #30533: Delete cascade can break postgresql database: too large 'DELETE FROM a WHERE a.b_id IN (...)'. (was: Delete cascade can break postgresql database: too large 'DELETE FROM a WHERE a

[Django]

#30533: Delete cascade can break postgresql database: too large 'DELETE FROM a
WHERE a.b_id IN (...)'.
-+-
 Reporter:  Thomas Riccardi  |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  wontfix
 Keywords:  queryset delete  | Triage Stage:
  cascade batch bulk_batch_size  |  Unreviewed
  postgresql crash   |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

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


Comment:

 If we don't have any restrictions in PostgreSQL I wouldn't classify this
 as a bug in Django. In SQLite and Oracle we limit the number of queries
 parameters or even the number of parameters in ` IN (...)` list, because
 we have to. These are databases limitations, which is not the case on
 PostgreSQL. You have at least two ways to handle this:
  - `delete()` in chunks (1-2M) using ORM, or
  - [https://www.postgresql.org/docs/11/sql-truncate.html TRUNCATE ...
 CASCADE] table manually .

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/073.9d0e69f750d7aa2876064a2bb4e6ae56%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30533: Delete cascade can break postgresql database: too large 'DELETE FROM a WHERE a.b_id IN (...)'

[Django]

#30533: Delete cascade can break postgresql database: too large 'DELETE FROM a
WHERE a.b_id IN (...)'
-+-
 Reporter:  Thomas Riccardi  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  queryset delete  | Triage Stage:
  cascade batch bulk_batch_size  |  Unreviewed
  postgresql crash   |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Thomas Riccardi):

 Replying to [comment:1 felixxm]:
 > Thanks for the report. Do you know any source that describes the max
 number of parameters for PostgreSQL? (e.g. 64k is specified in db docs on
 Oracle).

 I think there is no hardcoded limits in postgresql; what I think probably
 happened is a high memory allocation that resulted in an OOMKill on the
 connection process on the db side: with more RAM the query could probably
 work.

 The queries pattern in this scenario is:
 - get all B objects to delete: SELECT
 - delete all A related objects: the unbound DELETE IN that crashes
 - delete all B objects: DELETE IN with batch of 100

 It would make sense to have the same size for the 2 DELETE IN, but 100 is
 really small for DELETEs (the result size is constant, not proportional to
 the number of deleted objects): maybe add another constant for DELETE:
 `GET_ITERATOR_CHUNK_SIZE` should be for get (where the result size is
 proportional to the number of objects), with a much higher value; this
 could be part of the fix for this ticket?

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/073.bfbcaa99b3f16cba454b43f8f4dfdeaa%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30533: Delete cascade can break postgresql database: too large 'DELETE FROM a WHERE a.b_id IN (...)'

[Django]

#30533: Delete cascade can break postgresql database: too large 'DELETE FROM a
WHERE a.b_id IN (...)'
-+-
 Reporter:  Thomas Riccardi  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  queryset delete  | Triage Stage:
  cascade batch bulk_batch_size  |  Unreviewed
  postgresql crash   |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * version:  2.0 => master


Comment:

 Thanks for the report. Do you know any source that describes the max
 number of parameters for PostgreSQL? (e.g. 64k is specified in db docs on
 Oracle).

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/073.bff0a711c53ad38bac5ec33ea85d7255%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.