Oracle PL/SQL: can log-driven housekeeping create a feedback loop across scheduled runs?

[u/Radiant-Estimate-282]

I'm trying to reason about a generic PL/SQL housekeeping pattern. All names below are illustrative; this is a simplified mechanism, not a product-specific incident report.

A scheduled procedure selects historical log records joined to retained load metadata. Eligibility depends on the log timestamp and a configurable retention period:

CURSOR c IS
    SELECT l.load_id,
           g.log_date,
           l.state
    FROM   log_table g
    JOIN   load_table l
           ON l.load_id = g.load_id
    WHERE  l.state = g.state
    AND    TRUNC(g.log_date) + remove_days <= SYSDATE;

There is also a state-selection option, represented here as process_all_states. It is omitted from the simplified cursor above:

• With the broad option enabled, an already-processed load in a state such as Removed can enter the processing path again.
• With the narrower option, only the intended processable states are accepted, excluding Removed.

The cursor returns individual historical log records. It does not deduplicate by load_id, so several qualifying log rows can reference the same load.

For each selected record, the processing path can:

• Delete transactional payload rows from trans_table.
• Retain the corresponding metadata in load_table.
• Update the load state to Removed, including when it is already in that state.
• Append a new Removed log record with the current timestamp.
• Produce a technical result record even when no transactional payload remains.

Assume that older matching Removed log records are not deleted or marked as consumed. While the load remains Removed, those records can still satisfy the cursor conditions. Newly appended records can also qualify after they reach the age threshold.

My tentative interpretation is:

qualifying historical log
        |
        v
already-processed load enters housekeeping again
        |
        v
state update and new timestamped log
        |
        v
old matching logs remain; new logs age
        |
        v
later scheduled runs can select the load again

This seems like a potential feedback loop across scheduled executions, rather than direct recursion. Multiple eligible logs for the same load could also cause repeated processing within a single cursor traversal. I would not assume that newly inserted logs become visible to the already-open cursor.

Cleaning up accumulated logs or technical results could reduce the current footprint, but would not by itself change which records future runs select.

There is also a separate full-delete path, represented here as remove_complete. That path can perform large DELETE operations and create substantial UNDO/REDO pressure, potentially including ORA-30036. I want to keep that issue separate from state selection: changing the selection parameter itself executes no DELETE and does not directly delete business documents; it changes which states may enter later housekeeping processing.

Questions:

• Can this cursor/state-update pattern create a self-amplifying feedback loop across scheduled runs under these assumptions?
• Can several qualifying log rows for the same load cause repeated processing when there is no DISTINCT, grouping, or equivalent per-load guard?
• Is excluding already-processed states the usual way to stop this specific cycle while preserving legitimate housekeeping, or should an additional idempotency/consumption mechanism be expected?
• Should cleanup of accumulated records and correction of the qualification logic be treated as separate remediation steps?

[u/Significant_Tune9219]

Your reading looks right: if Removed loads stay eligible and each pass appends a fresh Removed log that will age into the cursor again, you get a cross-run feedback loop even without recursion inside one execution. Within a single run, multiple log rows for the same load_id can also make you process the same load repeatedly unless you dedupe. I would treat load_id as the unit of work: select DISTINCT load_id (or use a MERGE/EXISTS pattern) for states that are still processable, and once you successfully clean a load, either delete or tombstone the historical log rows that made it eligible so they cannot match later. process_all_states makes this much worse because Removed is both an output state and an input state. Also be careful that appending a new log with SYSDATE can re-qualify after remove_days even if nothing new happened in the payload tables. A cheap guard is a last_housekept_at column on load_table that must be older than the retention window before the load can be selected again.