Large deletes: an optional size limit and chunked deletes

未關閉
#1,695 0 則留言 0 個 reaction 已指派 0 人 在 GitHub 檢視

維護者通常 6 天內回覆

還沒有人認領這個 Issue。

評估

難度
5/5
預估耗時
一週以上
新手友好度
18/100
Issue 類型
功能
描述清晰度
基本清楚
活躍度
活躍
技術堆疊
mysql, postgresql, python
領域
databases

研究方向

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.

由索引模型根據 Issue 內容生成。

描述

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)).

主要語言
Python
星號
198
分支
103
平均合併
6 天 1 小時
30 天內合併 PR
10

環境準備

  • 提供 Dockerfile 或 Docker Compose 檔案
  • 沒有 Pull Request 範本
  • 閱讀貢獻指南

從這裡開始

  1. 先讀完整個 Issue,再讀專案的貢獻指南。
  2. 在 Issue 下留言說明你要接手 —— 這能避免兩個人做同樣的事。
  3. Fork 儲存庫,在一個分支上完成修改。
  4. 送出 Pull Request,並在描述裡引用這個 Issue 編號。

datajoint/datajoint-python 的其他 Issue

查看 datajoint/datajoint-python 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 寄到你的電子郵件信箱

精選適合新手參與的 GitHub issue 摘要。