Skip to content

Commit 787c7c4

Browse files
author
Elrond
committed
fix(db): lock workflow runs before deleting cross-workflow approvals
An approval naming another workflow can reference one of the deleted workflow's runs; its insert key-share locks only that run, so the workflow row lock did not exclude it. Lock the runs FOR UPDATE in id order before the child deletes, and pin it with a concurrent regression test. Also tighten the FK-retirement guard to the wrapper's exact database name shape and fix two stale rows_affected comments. Signed-off-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
1 parent 00cba2f commit 787c7c4

4 files changed

Lines changed: 88 additions & 5 deletions

File tree

‎crates/buzz-db/src/store/relay_members.rs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -299,7 +299,7 @@ pub async fn remove_relay_member(
299299
return Ok(RemoveResult::Removed);
300300
}
301301

302-
// rows_affected == 0: either not found or is owner. One cheap read to
302+
// Nothing was locked: either not found or is owner. One cheap read to
303303
// distinguish the two cases so callers can return the right error message.
304304
let exists = sqlx::query("SELECT 1 FROM relay_members WHERE community_id = $1 AND pubkey = $2")
305305
.bind(community.as_uuid())
@@ -340,7 +340,7 @@ pub async fn remove_relay_member_if_role(
340340
return Ok(RemoveResult::Removed);
341341
}
342342

343-
// rows_affected == 0: either not found or role changed. One cheap read to
343+
// Nothing was locked: either not found or role changed. One cheap read to
344344
// distinguish the cases so callers can return the right error message.
345345
let row = sqlx::query("SELECT role FROM relay_members WHERE community_id = $1 AND pubkey = $2")
346346
.bind(community.as_uuid())

‎crates/buzz-db/src/store/workflow/deletion.rs‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,18 @@ async fn delete_workflow_in_transaction(
159159
/// runs), scheduled fires, then runs. Returns the workflow's `channel_id`.
160160
async fn delete_workflow_with_children(tx: &mut AdmittedTx, id: Uuid) -> Result<Option<Uuid>> {
161161
let community = *tx.community().as_uuid();
162+
// The workflow lock stops new runs, but an approval naming another workflow
163+
// can still reference one of these runs; it key-share locks only that run.
164+
// Lock the runs (in a fixed order) so such an insert either commits before
165+
// the approval delete below or waits until this transaction ends.
166+
sqlx::query(
167+
"SELECT id FROM workflow_runs WHERE community_id = $1 AND workflow_id = $2 \
168+
ORDER BY id FOR UPDATE",
169+
)
170+
.bind(community)
171+
.bind(id)
172+
.execute(tx.conn())
173+
.await?;
162174
sqlx::query(
163175
"DELETE FROM workflow_approvals WHERE community_id = $1 AND (workflow_id = $2 \
164176
OR run_id IN (SELECT id FROM workflow_runs \

‎crates/buzz-db/src/store/workflow/deletion_tests.rs‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -493,3 +493,67 @@ async fn workflow_deletion_waits_for_and_removes_concurrent_run() {
493493
0
494494
);
495495
}
496+
497+
/// An approval naming another workflow but one of this workflow's runs only
498+
/// key-share locks that run, so the workflow lock does not exclude it. The
499+
/// delete must lock the runs, wait for the insert, and then remove it; with no
500+
/// cascade, committing it between the approval and run deletes would fail the
501+
/// run delete's foreign-key check.
502+
#[tokio::test]
503+
#[ignore = "requires Postgres"]
504+
async fn workflow_deletion_waits_for_and_removes_concurrent_cross_workflow_approval() {
505+
let (db, community) = setup().await;
506+
crate::test_support::retire_foreign_key_delete_actions(&db.pool).await;
507+
let keys = Keys::generate();
508+
let owner = keys.public_key().to_bytes();
509+
let id = Uuid::new_v4();
510+
let d_tag = id.to_string();
511+
let now = Timestamp::now().as_secs();
512+
seed(&db, community, &keys, id, &d_tag, now).await;
513+
let other = Uuid::new_v4();
514+
seed(&db, community, &keys, other, &other.to_string(), now).await;
515+
let run_id = create_workflow_run(&db.pool, community, id, None, None)
516+
.await
517+
.expect("run");
518+
519+
let mut inserter = db.pool.begin().await.expect("begin approval insert");
520+
sqlx::query(
521+
"INSERT INTO workflow_approvals (community_id, token, workflow_id, run_id, step_id, \
522+
step_index, approver_spec, status, expires_at) \
523+
VALUES ($1, $2, $3, $4, 'gate', 0, '@anyone', 'pending', NOW() + INTERVAL '1 hour')",
524+
)
525+
.bind(community.as_uuid())
526+
.bind(Uuid::new_v4().as_bytes().to_vec())
527+
.bind(other)
528+
.bind(run_id)
529+
.execute(&mut *inserter)
530+
.await
531+
.expect("insert concurrent cross-workflow approval");
532+
533+
let name = format!("workflow-delete-{}", Uuid::new_v4().simple());
534+
let deleter = Db::from_pool(crate::test_support::named_pool(&name).await);
535+
let deletion = tokio::spawn(async move {
536+
deleter
537+
.delete_workflow_by_coordinate(community, &owner, &d_tag, now as i64)
538+
.await
539+
});
540+
crate::test_support::wait_for_lock_wait(&db.pool, &name).await;
541+
inserter.commit().await.expect("commit concurrent approval");
542+
543+
let outcome = deletion
544+
.await
545+
.expect("join deletion")
546+
.expect("delete after concurrent approval");
547+
assert!(outcome.changed);
548+
for (table, column, key) in [
549+
("workflow_approvals", "run_id", run_id),
550+
("workflow_runs", "workflow_id", id),
551+
] {
552+
assert_eq!(
553+
count_where(&db, table, column, community, key).await,
554+
0,
555+
"{table}"
556+
);
557+
}
558+
assert!(get_workflow(&db.pool, community, other).await.is_ok());
559+
}

‎crates/buzz-db/src/test_support.rs‎

Lines changed: 10 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -90,15 +90,22 @@ pub(crate) fn desired_state_schema_dir() -> std::path::PathBuf {
9090
/// children itself, in an order the remaining constraints accept.
9191
///
9292
/// Each PostgreSQL test process owns its database (`crates/buzz-db/TESTING.md`),
93-
/// so the rewrite does not reach other tests. It refuses any database the
94-
/// per-test wrapper did not create, so it cannot rewrite a shared one.
93+
/// so the rewrite does not reach other tests. It refuses any database whose
94+
/// name does not match the per-test wrapper's (`buzz_nt_` plus 24 hex digits),
95+
/// which excludes the run's `_desired` template. The name is the only check:
96+
/// a manually created database with that shape would pass.
9597
pub(crate) async fn retire_foreign_key_delete_actions(pool: &sqlx::PgPool) {
9698
let database: String = sqlx::query_scalar("SELECT current_database()")
9799
.fetch_one(pool)
98100
.await
99101
.expect("read current database");
100102
assert!(
101-
database.starts_with("buzz_nt_"),
103+
database
104+
.strip_prefix("buzz_nt_")
105+
.is_some_and(|hash| hash.len() == 24
106+
&& hash
107+
.bytes()
108+
.all(|b| b.is_ascii_digit() || (b'a'..=b'f').contains(&b))),
102109
"refusing to rewrite foreign keys in shared database {database}; \
103110
run this test through scripts/postgres-test-run.sh"
104111
);

0 commit comments

Comments
 (0)