存储过程功能分析专家

Data operation types:

• Query: Select candidate rows by time range and order by id.
• Delete: Remove selected rows from the hot table and return the deleted rows.
• Insert: Insert deleted rows into the history table.
• Insert: Record per-batch archive metrics.
• Control/utility: Looping, sleep for throttling, diagnostics for row counts, exception on invalid input.

Business logic description:

• Validates that the time window is correct (p_from < p_to); otherwise raises an exception.
• Repeatedly performs batch moves:
  • Selects up to p_batch rows from ops.audit_logs whose created_at is within [p_from, p_to), ordered by id for deterministic batching.
  • Deletes those rows from ops.audit_logs and simultaneously captures the deleted rows (DELETE … RETURNING) inside the same statement.
  • Inserts the captured rows into ops.audit_logs_hist, ensuring each batch move is atomic.
  • Records the number of rows moved in ops.archive_audit_metrics with the time window and execution timestamp.
  • Stops when the last batch moves fewer than p_batch rows; otherwise sleeps briefly (50 ms) and continues.

Input/output parameters:

• p_from timestamptz (input): Inclusive start of the archive window (created_at >= p_from).
• p_to timestamptz (input): Exclusive end of the archive window (created_at < p_to).
• p_batch integer (input, default 5000): Target batch size per iteration.
• Output: None (procedure has side effects on tables and metrics).

Execution flow:

1. Check p_from < p_to; if not, raise “Invalid time range”.
2. Loop:
   • Single SQL statement with data-modifying CTE:
     • moved: select candidate rows by time window, ordered by id, limited by p_batch.
     • del: delete from ops.audit_logs using moved; return deleted rows.
     • Insert into ops.audit_logs_hist selecting from del (atomic move per batch).
   • Retrieve number of inserted rows via GET DIAGNOSTICS v_moved = ROW_COUNT.
   • Insert a metrics row into ops.archive_audit_metrics (run time, moved_rows, range_from, range_to).
   • If v_moved < p_batch, exit; otherwise sleep 50 ms and iterate.
3. End.

Key technical points:

• Atomic batch move: Using a data-modifying CTE (DELETE … RETURNING) feeding the INSERT within one statement ensures each batch either fully moves or does nothing on error.
• Half-open time window [p_from, p_to) prevents overlap across adjacent runs and supports safe reruns with the same p_to.
• Deterministic batching via ORDER BY id with LIMIT avoids reprocessing/skipping in concurrent environments.
• v_moved uses ROW_COUNT of the INSERT statement to reflect exactly how many rows were archived in that batch.
• Throttling with pg_sleep(0.05) reduces sustained lock pressure and resource spikes between batches.
• Metrics per batch provide operational observability (progress and throughput).

Performance considerations:

• Filtering and ordering efficiency depend on indexing (e.g., created_at for the predicate, id for ordering/PK lookups).
• Each run executes in the caller’s transaction context; long-running transactions retain dead tuples until commit, increasing bloat/WAL and delaying vacuum.
• Large payload moves generate disk I/O and WAL; batch size affects throughput vs. impact on concurrency.
• Per-batch metrics insert adds write overhead and grows the metrics table with large archival windows.
• The selection is evaluated each iteration; rows newly meeting the time predicate during the run will be included in subsequent batches.

Applicable business scenarios:

• Periodic archiving of audit trails from an active table to a history table to maintain performant OLTP workloads while preserving full audit history.
• Operational maintenance tasks where online, low-lock impact movement of older records is required.

Invocation timing:

• Scheduled jobs (e.g., nightly) to move logs older than a threshold into history.
• On-demand archival before heavy maintenance or index operations on the hot table.

Expected outcome:

• All rows in ops.audit_logs with created_at in [p_from, p_to) are removed from the hot table and inserted into ops.audit_logs_hist.
• ops.archive_audit_metrics contains one row per batch with the number of rows moved and the time window.
• The hot table size and write amplification for ongoing workloads are reduced, while history remains available.

Usage constraints:

• p_from must be earlier than p_to; otherwise the procedure fails with “Invalid time range”.
• The batch move is atomic per iteration; if the INSERT into history fails, the associated DELETE is rolled back for that batch.
• No explicit conflict handling is present for inserts into ops.audit_logs_hist; if a row with the same primary key already exists, the batch will fail.
• The time window is half-open: created_at >= p_from and created_at < p_to.
• The procedure does not manage transactions internally; it executes within the caller’s transaction scope.

Maintenance points:

• Monitor growth of ops.archive_audit_metrics when archiving large ranges or running frequently.
• Overall runtime and resource usage are sensitive to batch size, data volume, payload size, and available indexes.
• Consider operational windows and concurrency expectations; deletes acquire row-level locks on the hot table rows being moved.

データ操作タイプ:

• 読み取り（SELECT … FOR UPDATE による行ロック取得）
• 集計（SUM）
• 更新（UPDATE）
• 追加（INSERT）
• トランザクション制御（START TRANSACTION/COMMIT）

ビジネスロジック記述:

1. users テーブルの対象ユーザー行を行ロックし、現在の残高を取得。
2. ledger テーブルから、指定期間内の「入金（type='CREDIT'）」合計と「出金（type='DEBIT'）」合計をそれぞれ算出。
   • 期間条件は tx_date >= p_from AND tx_date < DATE_ADD(p_to, INTERVAL 1 DAY)（p_to 当日を含む半開区間）。
   • 対象期間に該当行がない場合は 0 とみなす。
3. 新残高 = 旧残高 + 入金合計 - 出金合計 を計算。
4. users 残高と残高更新時刻（UTC）を更新。
5. balance_audit に期間、入出金合計、最終残高、作成時刻（UTC）を記録。
6. トランザクションをコミットし、新残高を出力パラメータに設定。

入出力パラメータ:

• IN p_user_id BIGINT: 対象ユーザーID
• IN p_from DATE: 集計開始日（この日を含む）
• IN p_to DATE: 集計終了日（この日を含む）
• OUT p_new_balance DECIMAL(18,2): 更新後のユーザー残高

実行フロー:

1. トランザクション開始。
2. users.id = p_user_id を FOR UPDATE で読み取り、v_bal に現在残高を取得（行ロック）。
3. ledger から CREDIT の期間合計を v_credit に取得。
4. ledger から DEBIT の期間合計を v_debit に取得。
5. v_bal = v_bal + v_credit - v_debit を計算。
6. users を新残高と UTC の更新時刻で更新。
7. balance_audit に監査レコードを挿入。
8. コミットし、p_new_balance に v_bal を返却。

关键技术点:

• 行ロック（SELECT … FOR UPDATE）で対象ユーザー行の同時更新競合を抑止。
• 期間条件は「>= p_from かつ < p_to+1日」を採用し、p_to 当日を含めた日付範囲を厳密に表現。
• IFNULL(SUM(...), 0) により対象なしでも 0 として安全に集計。
• 更新・監査挿入までを単一トランザクションで原子化。
• タイムスタンプは UTC_TIMESTAMP() を一貫使用（監査・更新時刻の統一性）。

性能考虑:

• ledger に対する期間集計（CREDIT/DEBIT で2回）は、データ量・期間長に比例して I/O 負荷が増加。
• 効率的な検索には、ledger(user_id, tx_date[, type]) や少なくとも user_id と tx_date を先頭とするインデックスが有効。
• users.id は主キー/一意インデックスが前提。
• トランザクション中は users の対象行がロックされるため、ロック保持時間が長いと待機が発生。
• 監査テーブル balance_audit は実行毎に1行増えるため、肥大化による検索性能への影響に留意。

适用业务场景:

• 指定期間の入出金に基づくユーザー残高の再計算・調整。
• 日次締め処理や特定期間の照合（リコンシリエーション）。
• 障害・遅延発生後の残高補正や再同期に伴う更新と監査記録。

调用时机:

• 期間の入出金計上が確定した後のバッチ処理や運用オペレーション。
• 特定ユーザーの残高不整合検知時の是正処置としてオンデマンド実行。
• 監査やレポート作成前の整合性担保。

预期效果:

• users の残高が指定期間の入出金反映後の正味値に更新される。
• balance_audit に対象期間・入金合計・出金合計・最終残高が記録され、追跡可能性が向上。
• 新残高が出力パラメータで取得可能。

使用约束:

• users に対象行が存在しない場合、SELECT … INTO は行無しでエラーとなり得る（エラーハンドラは未実装）。
• 同一の期間で本プロシージャを複数回実行すると、同じ期間の入出金が都度加減算される挙動となるため、運用上の再実行可否・回数管理が重要。
• ledger.type は 'CREDIT' と 'DEBIT' のみを集計対象としている。他の種別は無視される。
• 期間条件は p_from 以上かつ p_to 当日を含む。日時型の tx_date を扱う場合、時刻・タイムゾーンの設計と境界条件を確認すること。
• トランザクション分離レベルにより、実行中に新規コミットされた ledger 行の可視性が変わる（MySQL 既定の REPEATABLE READ ではスナップショットに基づく一貫読み取り）。

维护要点:

• ledger の期間集計に適したインデックス設計の維持（user_id、tx_date、必要に応じて type）。
• balance_audit の増加に伴う保守（パーティショニング、アーカイブ運用、統計・索引の管理）。
• 残高更新の再実行ポリシーや重複監査記録の運用ルール整備。
• UTC 時刻での記録方針と業務上のタイムゾーン要件の整合確認。
• トランザクション競合・ロック待ちの監視（実行タイミング・期間幅・対象ユーザー数に応じた運用計画）。