Large deletes: an optional size limit and chunked deletes

Đang mở
#1,695 0 bình luận 0 reaction 0 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 6 ngày

Chưa có ai nhận issue này.

Đánh giá

Độ khó
5/5
Thời gian dự kiến
Hơn một tuần
Mức phù hợp với người mới
18/100
Loại issue
Tính năng
Độ rõ ràng
Khá rõ ràng
Mức độ hoạt động
Sôi nổi
Công nghệ
mysql, postgresql, python
Lĩnh vực
databases

Hướng nghiên cứu

This is a design proposal, not a scoped change: start from the delete() implementation and the cascade preview dj.Diagram.cascade(...).counts() to see how the cascade is enumerated and where the transaction is opened, then read the discussion in #1496 (traversal for delete in 2.4) and #1682 (the confirmation prompt). The open questions — batching by seed-table keys vs per-table rows, and what a partial delete guarantees together with part_integrity — have to be settled with the maintainers before any code is written. Done means an agreed design note covering limit semantics, batching strategy, and resume behaviour, not a merged patch.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Mô tả

enhancement

Problem

delete() runs the whole cascade in one transaction: it deletes from every affected table, leaves first, and commits at the end. For a large cascade (a subject with many sessions, each with many computed results), that transaction can delete millions of rows. On a server shared with other users it then:

  • holds row locks, and on MySQL locks on the index ranges around them, for as long as the deletes take;
  • builds a large undo log, so a rollback or a failure late in the cascade is slow;
  • can delay replication and backups.

The preview (dj.Diagram.cascade(...).counts()) shows the size beforehand, but nothing stops an unexpectedly large delete from running, and nothing splits a large delete into smaller transactions.

Proposal (to be designed)

  1. An optional size limit. For example delete(max_rows=...), or a setting, that refuses the delete before anything is deleted when the cascade would remove more rows than the limit, and reports the per-table counts. A caller who has reviewed the preview raises the limit or passes None.
  2. Chunked deletes. An option that deletes a large cascade in bounded batches, leaves first, each batch in its own transaction, so locks are held briefly and each transaction stays small.

Questions to settle

  • Chunked deletes give up atomicity: a failure partway leaves some rows deleted. What does a partial delete guarantee, and how is it resumed? Deleting leaves first keeps foreign keys valid at every step; part-integrity checks (part_integrity) then need to hold per batch or be checked up front.
  • Batches by what: rows of the seed table (each batch is the cascade of a slice of seed keys), or rows per table?
  • Interaction with the new traversal for delete in 2.4 (#1496), which decides which rows the cascade reaches.
  • Defaults: off by default, with the limit and chunking chosen explicitly by the caller.

Related: #1682 (the confirmation prompt holds the delete transaction open; production code previews and then calls delete(prompt=False)).

Ngôn ngữ chính
Python
Star
198
Fork
103
Merge trung bình
6 ngày 1 giờ
Pull request đã merge (30 ngày)
10

Chuẩn bị môi trường

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của datajoint/datajoint-python

Tất cả issue của datajoint/datajoint-python

Issue tương tự

Thêm issue về Python

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.