Skip to content

Commit 347ce88

Browse files
AresonIan Oberstclaude
authored
fix(db): keep pgschema re-apply from dropping runtime partitions (#8487)
## Summary pgschema doesn't compare partition bounds. Re-applying `schema/schema.sql` to a database a relay has used therefore plans `DROP TABLE IF EXISTS ... CASCADE` for every `events` / `delivery_log` monthly the partition manager created at runtime, and those tables' rows are deleted with them. Since #8464 the manager advances the catch-all by default, so any local dev or test database a relay has started against now hits this. One example is the persistent `buzz` database that `scripts/start-relay-for-tests.sh` re-applies. This adds a root `.pgschemaignore` that hides dated monthlies (`events_p2*`, `delivery_log_p2*`) from pgschema. The monthlies declared in `schema/partitions/` are exempted with `!` patterns, so pgschema still manages them. pgschema reads the file from its working directory, and every caller already runs it from the repo root: the test-relay launchers, `postgres-test-setup.sh`, the desktop release smoke and `_ci-relay.yml`. Both partition files now carry a note pointing at it, and AGENTS.md gotcha #7 adds "run pgschema from the repo root" next to the reconcile rule. New-install layout is unchanged. **Why the drops haven't deleted data yet:** on a reconciled database a re-apply also plans to drop and re-add the `events` primary key on the parent and on each declared child. The apply then fails with `cannot drop inherited constraint "events_p2026_01_pkey"` (42P16). That happens with or without this PR. The planned drops run in the same single transaction, so they roll back, and the runtime monthlies have survived so far. Once that primary-key drift is fixed, re-applies will succeed and this ignore file is what keeps them from dropping the runtime monthlies. The primary-key fix is a separate follow-up. ### Related issue Follow-up to #8464. Salvaged from an earlier branch; no existing issue found. ### Testing - `pgschema_reapply_keeps_runtime_partitions` (Postgres) follows the real bootstrap path in a dedicated database: - `bin/pgschema apply` from the repo root, then `scripts/reconcile-schema-after-pgschema.sql`; - the relay's default maintenance pass (create plus advance); - asserts that both catch-alls were attached before the pass and that their bounds moved after it; - asserts that each parent's `p2026_07` monthly exists. The catch-all advanced past the declared bounds but kept its name. - asserts that `pgschema plan` contains no `DROP TABLE` and no line mentioning any runtime monthly. - Falsified both ways: - With `.pgschemaignore` removed, the test fails and prints the planned drops. - Without the reconcile step, it fails with `reconcile did not attach both catch-alls`. - `pgschemaignore_exempts_exactly_the_declared_monthlies` checks that the `!` exemptions exactly match the dated monthlies in `schema/partitions/`. - The admin parity test now shares the `pgschema` helper, so it also runs from the repo root like the real callers. - All `runtime::migration` and `store::partition` tests pass against local Postgres after merging current main, including the ignored ones: 104 passed, run with `--test-threads=1` because the full nextest Postgres lane needs local `dropdb`. `clippy -D warnings` and the pre-push lanes pass. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Signed-off-by: Ian Oberst <[email protected]> Co-authored-by: Ian Oberst <[email protected]> Co-authored-by: Claude Opus 5.5 <[email protected]>
1 parent 2f2f6e2 commit 347ce88

5 files changed

Lines changed: 250 additions & 37 deletions

File tree

‎.pgschemaignore‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
# pgschema reads this file from its working directory; every caller runs it
2+
# from the repository root.
3+
#
4+
# The relay's partition manager creates dated `events` / `delivery_log`
5+
# monthlies at runtime (see crates/buzz-db/src/store/partition/). They are not
6+
# declared in schema/partitions/, so without this file re-applying pgschema to a
7+
# database a relay has used drops them, and their rows, with
8+
# `DROP TABLE IF EXISTS ... CASCADE`. Only the monthlies schema/partitions/
9+
# declares stay managed by pgschema; keep the `!` exemptions below in sync with
10+
# them.
11+
[tables]
12+
patterns = [
13+
"events_p2*",
14+
"!events_p2026_01",
15+
"!events_p2026_02",
16+
"!events_p2026_03",
17+
"!events_p2026_04",
18+
"!events_p2026_05",
19+
"!events_p2026_06",
20+
"delivery_log_p2*",
21+
"!delivery_log_p2026_03",
22+
"!delivery_log_p2026_04",
23+
"!delivery_log_p2026_05",
24+
"!delivery_log_p2026_06",
25+
]

‎AGENTS.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -613,7 +613,7 @@ description. See [PR #803](https://github.com/block/buzz/pull/803).
613613
4. **Worktrees: `cd` in the same command** — shell CWD doesn't persist between tool calls. Use `cd /path && cargo build` as one command.
614614
5. **Desktop crate excluded from root workspace** — `cargo test` at repo root does NOT run desktop tests. Use `cargo test --manifest-path desktop/src-tauri/Cargo.toml` explicitly.
615615
6. **React render perf: `React.memo` is all-or-nothing** — it only skips a re-render when *every* prop is reference-stable; one unstable prop (inline arrow/JSX, or a hook returning a fresh `{}`/`[]`/`Map` each render) defeats it. Two repeat offenders: (a) React Query results (`useMutation`/`useQuery`) are a **new object each render** — depend on the stable method (`mutation.mutateAsync`), not the object; (b) derived `Map`/array state that recomputes on a version bump — wrap in a content-equality ref cache (`shared/hooks/useStableReference.ts`). When chasing interaction lag, **measure with DevTools closed and no perf probes** (an open Web Inspector + per-keystroke `console.log` inflate the numbers), and isolate by removing one suspect at a time rather than guessing.
616-
7. **`pgschema` omits seed DML and some storage parameters** — `schema/schema.sql` is an ordered `\i` manifest: `schema/tables/public/<table>.sql` holds only that table's `CREATE TABLE` and `CREATE INDEX` statements (one table per file, the shape out-of-band schema tools read), and types, functions, partitions, triggers, and seed rows live in the sibling `schema/` directories. Fresh desired-state bootstraps use `./bin/pgschema apply`, which does not execute `INSERT` statements or preserve every table storage parameter from `schema/schema.sql`. Put each unsupported invariant in `scripts/reconcile-schema-after-pgschema.sql` as an idempotent convergence statement plus a live catalog or data assertion. Every `pgschema apply` caller must run that script. A string assertion against `schema.sql` alone does not prove the pgschema-created database has the intended state.
616+
7. **`pgschema` omits seed DML and some storage parameters** — `schema/schema.sql` is an ordered `\i` manifest: `schema/tables/public/<table>.sql` holds only that table's `CREATE TABLE` and `CREATE INDEX` statements (one table per file, the shape out-of-band schema tools read), and types, functions, partitions, triggers, and seed rows live in the sibling `schema/` directories. Fresh desired-state bootstraps use `./bin/pgschema apply`, which does not execute `INSERT` statements or preserve every table storage parameter from `schema/schema.sql`. Put each unsupported invariant in `scripts/reconcile-schema-after-pgschema.sql` as an idempotent convergence statement plus a live catalog or data assertion. Every `pgschema apply` caller must run that script, and must run pgschema from the repository root so it reads `.pgschemaignore`; from any other directory a re-apply plans to drop the partitions the relay created at runtime. A string assertion against `schema.sql` alone does not prove the pgschema-created database has the intended state.
617617

618618
---
619619

‎crates/buzz-db/src/runtime/migration.rs‎

Lines changed: 216 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -236,6 +236,66 @@ mod postgres_tests {
236236
.unwrap_or_default()
237237
}
238238

239+
/// Runs `bin/pgschema <command>` against `db`, which doubles as the plan
240+
/// database (`--plan-*`): that avoids the embedded-Postgres download and
241+
/// matches start-relay-for-tests.sh. Runs from the repository root, like
242+
/// every pgschema caller, so `.pgschemaignore` applies.
243+
fn pgschema(conn: &PgConn, db: &str, command: &[&str]) -> std::process::Output {
244+
let repo_root = Path::new(env!("CARGO_MANIFEST_DIR")).join("../..");
245+
let port = conn.port.to_string();
246+
std::process::Command::new(repo_root.join("bin/pgschema"))
247+
.current_dir(&repo_root)
248+
.args(command)
249+
.args([
250+
"--file",
251+
"schema/schema.sql",
252+
"--host",
253+
&conn.host,
254+
"--port",
255+
&port,
256+
"--user",
257+
&conn.user,
258+
"--password",
259+
&conn.password,
260+
"--db",
261+
db,
262+
"--plan-host",
263+
&conn.host,
264+
"--plan-port",
265+
&port,
266+
"--plan-user",
267+
&conn.user,
268+
"--plan-password",
269+
&conn.password,
270+
"--plan-db",
271+
db,
272+
])
273+
.output()
274+
.expect("run bin/pgschema (hermit env required)")
275+
}
276+
277+
/// Dated monthlies `schema/partitions/` declares, e.g. `events_p2026_01`.
278+
fn declared_monthlies() -> BTreeSet<String> {
279+
let dir = crate::test_support::desired_state_schema_dir().join("partitions");
280+
let mut names = BTreeSet::new();
281+
for entry in fs::read_dir(&dir).expect("read schema/partitions") {
282+
let sql = fs::read_to_string(entry.expect("partition file").path())
283+
.expect("read partition file");
284+
names.extend(
285+
sql.lines()
286+
.filter_map(|line| line.strip_prefix("CREATE TABLE "))
287+
.filter_map(|rest| rest.split_whitespace().next())
288+
.filter(|name| {
289+
name.rsplit_once("_p").is_some_and(|(_, month)| {
290+
month.starts_with(|c: char| c.is_ascii_digit())
291+
})
292+
})
293+
.map(str::to_owned),
294+
);
295+
}
296+
names
297+
}
298+
239299
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
240300
enum ConstraintKind {
241301
ForeignKey,
@@ -1517,6 +1577,32 @@ mod postgres_tests {
15171577
);
15181578
}
15191579

1580+
/// `.pgschemaignore` hides dated monthlies from pgschema so a re-apply
1581+
/// cannot drop the ones the relay created at runtime. Each monthly
1582+
/// `schema/partitions/` declares must be exempt, or pgschema would stop
1583+
/// creating it on a fresh database.
1584+
#[test]
1585+
fn pgschemaignore_exempts_exactly_the_declared_monthlies() {
1586+
let ignore =
1587+
fs::read_to_string(Path::new(env!("CARGO_MANIFEST_DIR")).join("../../.pgschemaignore"))
1588+
.expect("read .pgschemaignore");
1589+
let exempt: BTreeSet<String> = ignore
1590+
.lines()
1591+
.filter_map(|line| {
1592+
line.trim()
1593+
.trim_end_matches(',')
1594+
.strip_prefix("\"!")?
1595+
.strip_suffix('"')
1596+
})
1597+
.map(str::to_owned)
1598+
.collect();
1599+
assert_eq!(
1600+
exempt,
1601+
declared_monthlies(),
1602+
".pgschemaignore `!` exemptions must match the monthlies schema/partitions/ declares"
1603+
);
1604+
}
1605+
15201606
#[test]
15211607
fn workflow_run_error_codes_are_additive_and_backfilled_without_parsing_diagnostics() {
15221608
let mut migrations: Vec<_> = MIGRATOR.iter().collect();
@@ -2703,42 +2789,8 @@ mod postgres_tests {
27032789
.expect("create migrated probe database");
27042790

27052791
// Bootstrap the desired-state probe through the real pgschema binary, the
2706-
// same invocation the test-relay launchers use. The freshly-created probe
2707-
// db doubles as pgschema's plan database (--plan-*), which avoids the
2708-
// embedded-Postgres download and matches start-relay-for-tests.sh.
2709-
let pgschema = std::path::Path::new(env!("CARGO_MANIFEST_DIR")).join("../../bin/pgschema");
2710-
let schema_file =
2711-
std::path::Path::new(env!("CARGO_MANIFEST_DIR")).join("../../schema/schema.sql");
2712-
let port = conn.port.to_string();
2713-
let apply = std::process::Command::new(&pgschema)
2714-
.args([
2715-
"apply",
2716-
"--auto-approve",
2717-
"--file",
2718-
schema_file.to_str().expect("schema path utf-8"),
2719-
"--host",
2720-
&conn.host,
2721-
"--port",
2722-
&port,
2723-
"--user",
2724-
&conn.user,
2725-
"--password",
2726-
&conn.password,
2727-
"--db",
2728-
&desired_db,
2729-
"--plan-host",
2730-
&conn.host,
2731-
"--plan-port",
2732-
&port,
2733-
"--plan-user",
2734-
&conn.user,
2735-
"--plan-password",
2736-
&conn.password,
2737-
"--plan-db",
2738-
&desired_db,
2739-
])
2740-
.output()
2741-
.expect("run bin/pgschema apply (hermit env required)");
2792+
// same invocation the test-relay launchers use.
2793+
let apply = pgschema(&conn, &desired_db, &["apply", "--auto-approve"]);
27422794
assert!(
27432795
apply.status.success(),
27442796
"pgschema apply failed: {}\n{}",
@@ -2790,6 +2842,134 @@ mod postgres_tests {
27902842
}
27912843
}
27922844

2845+
/// Re-applying `schema/schema.sql` to a database a relay has used plans
2846+
/// `DROP TABLE IF EXISTS ... CASCADE` for every monthly the partition
2847+
/// manager created at runtime, and their rows go with them.
2848+
/// `.pgschemaignore` keeps those monthlies out of pgschema's view, and
2849+
/// pgschema does not compare partition bounds, so the advanced catch-all
2850+
/// is left alone too.
2851+
#[tokio::test]
2852+
#[ignore = "requires Postgres"]
2853+
async fn pgschema_reapply_keeps_runtime_partitions() {
2854+
use crate::store::partition::{
2855+
maintain_partitions, PartitionMaintenancePolicy, PARTITION_MANAGER_MONTHS_AHEAD,
2856+
};
2857+
use sqlx::AssertSqlSafe;
2858+
2859+
let base_url = crate::test_support::database_url();
2860+
let conn = parse_pg_url(&base_url);
2861+
let admin = PgPool::connect(&base_url)
2862+
.await
2863+
.expect("connect admin database");
2864+
let (base_prefix, _) = base_url.rsplit_once('/').expect("database url has a path");
2865+
let database = format!("buzz_pgschema_reapply_{}", uuid::Uuid::new_v4().simple());
2866+
sqlx::query(AssertSqlSafe(format!("CREATE DATABASE {database}")))
2867+
.execute(&admin)
2868+
.await
2869+
.expect("create probe database");
2870+
2871+
let apply = pgschema(&conn, &database, &["apply", "--auto-approve"]);
2872+
assert!(
2873+
apply.status.success(),
2874+
"pgschema apply failed: {}\n{}",
2875+
String::from_utf8_lossy(&apply.stdout),
2876+
String::from_utf8_lossy(&apply.stderr),
2877+
);
2878+
2879+
let pool = PgPool::connect(&format!("{base_prefix}/{database}"))
2880+
.await
2881+
.expect("connect probe database");
2882+
// Every pgschema caller attaches the declared children this way.
2883+
sqlx::raw_sql(include_str!(
2884+
"../../../../scripts/reconcile-schema-after-pgschema.sql"
2885+
))
2886+
.execute(&pool)
2887+
.await
2888+
.expect("reconcile schema after pgschema");
2889+
async fn catch_all_bounds(pool: &PgPool) -> Vec<(String, Option<String>)> {
2890+
sqlx::query_as(
2891+
"SELECT relname::text, pg_get_expr(relpartbound, oid) FROM pg_class \
2892+
WHERE relname IN ('events_p_future', 'delivery_log_p_future') \
2893+
ORDER BY relname",
2894+
)
2895+
.fetch_all(pool)
2896+
.await
2897+
.expect("read catch-all bounds")
2898+
}
2899+
let declared_bounds = catch_all_bounds(&pool).await;
2900+
assert!(
2901+
declared_bounds.len() == 2 && declared_bounds.iter().all(|(_, bound)| bound.is_some()),
2902+
"reconcile did not attach both catch-alls: {declared_bounds:?}"
2903+
);
2904+
2905+
// The relay's default startup pass advances each catch-all into dated
2906+
// monthlies schema/partitions/ does not declare.
2907+
maintain_partitions(
2908+
&pool,
2909+
PARTITION_MANAGER_MONTHS_AHEAD,
2910+
PartitionMaintenancePolicy {
2911+
create_enabled: true,
2912+
advance_enabled: true,
2913+
},
2914+
)
2915+
.await
2916+
.expect("maintain partitions");
2917+
let advanced_bounds = catch_all_bounds(&pool).await;
2918+
for (declared, advanced) in declared_bounds.iter().zip(&advanced_bounds) {
2919+
assert_ne!(declared, advanced, "catch-all did not advance");
2920+
}
2921+
let children: Vec<(String, String)> = sqlx::query_as(
2922+
"SELECT p.relname::text, c.relname::text FROM pg_inherits i \
2923+
JOIN pg_class c ON c.oid = i.inhrelid \
2924+
JOIN pg_class p ON p.oid = i.inhparent \
2925+
WHERE p.relname IN ('events', 'delivery_log')",
2926+
)
2927+
.fetch_all(&pool)
2928+
.await
2929+
.expect("list partitions");
2930+
pool.close().await;
2931+
let declared = declared_monthlies();
2932+
let runtime: Vec<_> = children
2933+
.iter()
2934+
.filter(|(parent, child)| {
2935+
child.starts_with(&format!("{parent}_p2")) && !declared.contains(child)
2936+
})
2937+
.collect();
2938+
for parent in ["events", "delivery_log"] {
2939+
// The first month the declared catch-all served.
2940+
let first = format!("{parent}_p2026_07");
2941+
assert!(
2942+
runtime.iter().any(|(_, child)| *child == first),
2943+
"maintenance did not advance into {first}: {children:?}"
2944+
);
2945+
}
2946+
2947+
let plan = pgschema(&conn, &database, &["plan", "--output-sql", "stdout"]);
2948+
let plan_sql = String::from_utf8_lossy(&plan.stdout);
2949+
assert!(
2950+
plan.status.success(),
2951+
"pgschema plan failed: {plan_sql}\n{}",
2952+
String::from_utf8_lossy(&plan.stderr),
2953+
);
2954+
assert!(
2955+
!plan_sql.contains("DROP TABLE"),
2956+
"re-applying schema.sql would drop tables:\n{plan_sql}"
2957+
);
2958+
for (_, child) in &runtime {
2959+
assert!(
2960+
!plan_sql.contains(child.as_str()),
2961+
"re-applying schema.sql would change runtime monthly {child}:\n{plan_sql}"
2962+
);
2963+
}
2964+
2965+
sqlx::query(AssertSqlSafe(format!(
2966+
"DROP DATABASE {database} WITH (FORCE)"
2967+
)))
2968+
.execute(&admin)
2969+
.await
2970+
.expect("drop probe database");
2971+
}
2972+
27932973
#[tokio::test]
27942974
#[ignore = "requires Postgres"]
27952975
async fn pre_0007_ambiguous_nip_rs_data_blocks_without_mutation_and_allows_retry() {

‎schema/partitions/delivery_log.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,7 @@
1+
-- The static layout migrations/0001 creates. The relay's partition manager
2+
-- (crates/buzz-db/src/store/partition/) adds later monthlies at runtime;
3+
-- .pgschemaignore hides those from pgschema and must exempt every dated
4+
-- monthly declared here.
15
CREATE TABLE delivery_log_p_past PARTITION OF delivery_log
26
FOR VALUES FROM (MINVALUE) TO ('2026-03-01');
37
CREATE TABLE delivery_log_p2026_03 PARTITION OF delivery_log

‎schema/partitions/events.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,7 @@
1+
-- The static layout migrations/0001 creates. The relay's partition manager
2+
-- (crates/buzz-db/src/store/partition/) adds later monthlies at runtime;
3+
-- .pgschemaignore hides those from pgschema and must exempt every dated
4+
-- monthly declared here.
15
CREATE TABLE events_p_past PARTITION OF events
26
FOR VALUES FROM (MINVALUE) TO ('2026-01-01');
37
CREATE TABLE events_p2026_01 PARTITION OF events

0 commit comments

Comments
 (0)