systemprompt_database/lifecycle/installation/
backfill.rs

1//! Finds the statements in a migration that populate data rather than
2//! declare structure: a backfill.
3//!
4//! Under the data paradigm (`crate::projection`) an upgrade migration is DDL
5//! plus one keys-only `INSERT INTO projection_backlog … SELECT`. Everything a
6//! fact needs is in the base schema — the projector, the statement trigger
7//! that queues keys, the drainer job — so a fresh install (which stamps
8//! migrations and never runs them) and an upgraded one reach the same state
9//! by the same code. A migration that instead `UPDATE`s a fact column,
10//! `INSERT … SELECT`s a rollup or calls `refresh_*()` inline populates the
11//! upgraded database by a path the fresh one never takes, on the boot path,
12//! at a cost that grows with history.
13//!
14//! The detector reports `UPDATE`, `DELETE`, select-driven `INSERT` and any
15//! `SELECT` that calls a `refresh_*` routine; the single sanctioned keys-only
16//! `INSERT INTO projection_backlog SELECT <projection>, <key> FROM <one
17//! relation>` is not reported. An unparseable body yields nothing, since
18//! `check_migration_references` already refuses it with the parse error.
19//!
20//! Both repositories' static gates (`no_backfill_after_floor`) use this one
21//! detector so "backfill" means the same thing on each side.
22//!
23//! Copyright (c) systemprompt.io — Business Source License 1.1.
24//! See <https://systemprompt.io> for licensing details.
25
26use pg_query::NodeEnum;
27use pg_query::protobuf::{self, Node};
28
29pub const PROJECTION_BACKLOG: &str = "projection_backlog";
30
31/// One data-populating statement. `position` is 1-based, matching how the
32/// runner numbers statements when one fails. `target` is the table written or
33/// the function called.
34#[derive(Debug, Clone, PartialEq, Eq)]
35pub struct BackfillStatement {
36    pub position: usize,
37    pub target: String,
38    pub form: &'static str,
39}
40
41#[must_use]
42pub fn backfill_statements(sql: &str) -> Vec<BackfillStatement> {
43    let Ok(parsed) = pg_query::parse(sql) else {
44        return Vec::new();
45    };
46    parsed
47        .protobuf
48        .stmts
49        .iter()
50        .filter_map(|raw| raw.stmt.as_ref().and_then(|s| s.node.as_ref()))
51        .enumerate()
52        .filter_map(|(index, node)| {
53            classify(node).map(|(target, form)| BackfillStatement {
54                position: index + 1,
55                target,
56                form,
57            })
58        })
59        .collect()
60}
61
62fn classify(node: &NodeEnum) -> Option<(String, &'static str)> {
63    match node {
64        NodeEnum::UpdateStmt(stmt) => Some((stmt.relation.as_ref()?.relname.clone(), "UPDATE")),
65        NodeEnum::DeleteStmt(stmt) => Some((stmt.relation.as_ref()?.relname.clone(), "DELETE")),
66        NodeEnum::InsertStmt(stmt) => {
67            let select = select_of(stmt.select_stmt.as_deref())?;
68            let table = stmt.relation.as_ref()?.relname.clone();
69            if table == PROJECTION_BACKLOG && is_keys_only(select) {
70                return None;
71            }
72            Some((table, "INSERT … SELECT"))
73        },
74        NodeEnum::SelectStmt(select) => {
75            refresh_call(select).map(|name| (name, "SELECT refresh_*()"))
76        },
77        _ => None,
78    }
79}
80
81// Why: pg_query models `INSERT … VALUES` as a SelectStmt carrying
82// `values_lists`; only a select without them reads other rows.
83fn select_of(node: Option<&Node>) -> Option<&protobuf::SelectStmt> {
84    match node?.node.as_ref()? {
85        NodeEnum::SelectStmt(select) if select.values_lists.is_empty() => Some(select),
86        _ => None,
87    }
88}
89
90// Why: a keys-only queue insert projects two columns from one relation with
91// no join, aggregate or grouping — it costs one index scan, which is the
92// whole point of queuing instead of rebuilding. DISTINCT is allowed: it
93// collapses the keys before the insert instead of leaving one conflict check
94// per ledger row to `ON CONFLICT DO NOTHING`.
95fn is_keys_only(select: &protobuf::SelectStmt) -> bool {
96    let single_relation = select.from_clause.len() == 1
97        && matches!(
98            select.from_clause[0].node.as_ref(),
99            Some(NodeEnum::RangeVar(_))
100        );
101    single_relation
102        && select.target_list.len() == 2
103        && select.group_clause.is_empty()
104        && select.having_clause.is_none()
105        && select.with_clause.is_none()
106}
107
108fn refresh_call(select: &protobuf::SelectStmt) -> Option<String> {
109    select.target_list.iter().find_map(|target| {
110        let NodeEnum::ResTarget(res) = target.node.as_ref()? else {
111            return None;
112        };
113        let NodeEnum::FuncCall(call) = res.val.as_ref()?.node.as_ref()? else {
114            return None;
115        };
116        let name = call.funcname.last().and_then(|n| match n.node.as_ref()? {
117            NodeEnum::String(s) => Some(s.sval.clone()),
118            _ => None,
119        })?;
120        name.starts_with("refresh_").then_some(name)
121    })
122}
systemprompt_database/lifecycle/installation/backfill.rs

systemprompt_database/lifecycle/installation/
backfill.rs
