nsmcp::AccessRecordStoreSQLite method pruneAge (protected)

 <instance of nsmcp::AccessRecordStoreSQLite> pruneAge batchRows

Defined in /usr/local/ns/tcl/nsmcp/log-traffic-record-store.tcl

Parameters:

batchRows (required)

Testcases:

No testcase defined.

Source code:

if {![string is integer -strict $batchRows] || $batchRows<1 || $batchRows>10000} {
    error "retention batch rows must be 1..10000"
}
set now [clock seconds]
set cutoff [expr {wide(floor(($now-[dict get ${:settings} retention])/60.0))*60}]
set cutoff [expr {max($cutoff,[:stateGet retentionFloor 0])}]
set peerCutoff [expr {$now-[dict get ${:settings} peerRetention]}]
set counts {requests 0 diagnostics 0 minutes 0 peers 0}
# Select once for both eviction accounting and deletion. Explicit
# indexes keep empty expiry probes from becoming full-table scans.
${:db} execute {
    DROP TABLE IF EXISTS temp.nsmcp_retention_batch;
    CREATE TEMP TABLE nsmcp_retention_batch(id INTEGER PRIMARY KEY)
}
try {
    ${:db} transaction {
        :statePut retentionFloor $cutoff
        ${:db} execute {
            INSERT INTO temp.nsmcp_retention_batch
            SELECT rowid FROM nsmcp_records.rows INDEXED BY by_time
            WHERE kind=0 AND t<:cutoff ORDER BY t LIMIT :batchRows
        }
        set deleted [lindex [${:db} execute {
            SELECT count(*) AS n,coalesce(min(t),0) AS first,coalesce(max(t),0) AS last
            FROM nsmcp_records.rows WHERE rowid IN(SELECT id FROM temp.nsmcp_retention_batch)
        }] 0]
        ${:db} execute {
            DELETE FROM nsmcp_records.rows WHERE rowid IN(SELECT id FROM temp.nsmcp_retention_batch)
        }
        dict set counts requests [dict get $deleted n]
        ${:db} execute {
            DELETE FROM nsmcp_records.rows WHERE rowid IN(
                                                          SELECT rowid FROM nsmcp_records.rows INDEXED BY diagnostic_expiry
                                                          WHERE kind<>0 AND completed<:cutoff ORDER BY completed LIMIT :batchRows)
        }
        dict set counts diagnostics [${:db} changes]
        set expiring [${:db} scalar {
            SELECT coalesce(sum(malformed+oversized+skipped), 0) FROM nsmcp_records.scan_counts WHERE rowid
            IN(SELECT rowid FROM nsmcp_records.scan_counts INDEXED BY scan_counts_expiry WHERE completed<:cutoff
               ORDER BY completed LIMIT :batchRows)
        }]
        ${:db} execute {
            DELETE FROM nsmcp_records.scan_counts WHERE rowid IN(SELECT rowid FROM nsmcp_records.scan_counts
                                                                 INDEXED BY scan_counts_expiry WHERE completed<:cutoff ORDER BY completed LIMIT :batchRows)
        }
        dict incr counts diagnostics $expiring
        ${:db} execute {
            DELETE FROM nsmcp_records.minutes WHERE rowid IN(
                                                             SELECT rowid FROM nsmcp_records.minutes INDEXED BY minute_time
                                                             WHERE slot<:cutoff ORDER BY slot LIMIT :batchRows)
        }
        dict set counts minutes [${:db} changes]
        ${:db} execute {
            DELETE FROM nsmcp_records.peer_addresses WHERE rowid IN(
                                                                    SELECT rowid FROM nsmcp_records.peer_addresses INDEXED BY peer_expiry
                                                                    WHERE seen<:peerCutoff ORDER BY seen LIMIT :batchRows)
        }
        dict set counts peers [${:db} changes]
        if {[dict get $deleted n]>0} {
            set first [dict get $deleted first]
            set last [dict get $deleted last]
            set count [dict get $deleted n]
            ${:db} execute {
                INSERT INTO nsmcp_records.evictions(dropped_at, first_time, last_time, rows, reason)
                VALUES(:now, :first, :last, :count, 'age');
                DELETE FROM nsmcp_records.evictions WHERE id NOT IN(SELECT id FROM nsmcp_records.evictions ORDER
                                                                    BY id DESC LIMIT 64)
            }
        }
        :statePut droppedThrough [expr {$cutoff-1}]
        :statePut lastEvictionAt $now
    }
} finally {
    ${:db} execute {DROP TABLE IF EXISTS temp.nsmcp_retention_batch}
}
# Keep committed ranges as ingestion watermarks after expiry, preventing
# the continuous backfill from resurrecting deliberately expired rows.
return $counts

XQL Not present:

Generic, PostgreSQL, Oracle