Update detached-parts KB for ClickHouse 23.10+ accuracy and safe cleanup recipes

[BorisTyshkevich]

Detached Parts KB Accuracy + Extension Plan (Target: ClickHouse 23.10+)

Summary

Update content/en/altinity-kb-useful-queries/detached-parts.md to be correct for modern ClickHouse (23.10+), remove risky cleanup guidance, add version-scoped behavior notes from changelog/source, and add production-safe recipes informed by relevant GitHub issues.

Findings To Fix First (ordered by severity)

1. P1: Unsafe deletion guidance encourages filesystem deletion directly

   • Current text suggests broad deletion and generates sudo rm -r commands.
   • Risk: bypasses ClickHouse metadata/replication/object-storage safety.
   • Fix: SQL-first cleanup (ALTER ... DROP DETACHED ... SETTINGS allow_drop_detached=1), filesystem deletion only as emergency manual procedure.

2. P1: Auto-cleanup statement needs version scope

   • Behavior changed across versions.
   • 22.6 introduced timeout-based cleanup for selected detached reasons.
   • 23.10 removed automatic broken-part removal option.
   • Fix: explicit version matrix and 23.10+ default behavior.

3. P1: Existing drop script is brittle due to manual part-name parsing

   • Current script parses names via split-by-underscore.
   • Edge cases (*_tryN, invalid detached names) can break logic.
   • Fix: rely on system.detached_parts columns (reason, partition_id, min_block_number, max_block_number) and explicit invalid-name detector query.

4. P2: Reason taxonomy is duplicated and misses NULL/empty semantics

   • reason in system.detached_parts is nullable.
   • Empty string and NULL have different operational meaning.
   • Fix: single canonical reason/action table with explicit handling.

5. P2: DROP DETACHED section needs concrete actionable examples

   • Add PART, PARTITION ID, and ALL examples with allow_drop_detached=1.

6. P3: Source references pinned to old commit hash

   • Fix: use maintained source/changelog links and add issue/PR references for rare behaviors.

Implementation Plan

1. Restructure article sections while preserving page slug/title.
2. Add version scope section (23.10+ primary, 22.6-23.9 compatibility note).
3. Consolidate detached reasons into one reason/action matrix.
4. Replace risky guidance with SQL-first cleanup workflow.
5. Add recipe set:
   • inventory by reason/size/age,
   • invalid-name detector,
   • replica health gate,
   • safe-drop generator for covered ignored|clone,
   • user-detached attach generator,
   • stale temporary prefix triage,
   • downgrade edge-case helper for broken-on-start_ rename.
6. Add references section with docs/changelog/source/issues.

Public Interface Changes

• File changed: content/en/altinity-kb-useful-queries/detached-parts.md
• No product/runtime code changes.
• Documentation behavior: clearer safety boundaries and version-accurate guidance.

Acceptance Criteria

1. Article states 23.10+ manual cleanup behavior explicitly.
2. Legacy 22.6-23.9 auto-cleanup behavior is documented as compatibility note.
3. No default recommendation uses rm -r for routine cleanup.
4. reason nullable semantics ('' vs NULL) are documented.
5. Recipes are copy-paste SQL and operationally safe for replicated/object-storage deployments.
6. References include upstream docs/changelog and relevant issues/PRs.

Source Evidence To Cite

• Changelog 2022: detached timeout cleanup introduced (PR #37975).
• Changelog 2023 (23.10): automatic broken detached parts removal option removed (PR #55184).
• Docs: ALTER PARTITION / DROP DETACHED syntax.
• Docs: system.detached_parts semantics.
• Source: detached reasons list in MergeTreePartInfo.h.
• Rare/edge references: #55309, #55645, #53877, #54506, #40031, #37466, #58509, #68408, #85351.