@@ -480,6 +480,15 @@ pub async fn execute_ban_with_marker(
480480 return Ok ( false ) ;
481481 }
482482
483+ // Acquire the action-row lock before evaluating the wall-clock expiry check.
484+ // PostgreSQL can evaluate clock_timestamp() in the marker UPDATE predicate
485+ // before waiting on the row; a lock acquired here ensures expiry is checked
486+ // under the lock, not before it.
487+ sqlx:: query ( "SELECT id FROM relay_admin_actions WHERE id = $1 FOR UPDATE" )
488+ . bind ( action_id)
489+ . fetch_optional ( & mut * tx)
490+ . await ?;
491+
483492 sqlx:: query (
484493 r#"
485494 INSERT INTO community_bans (community_id, pubkey, banned, actor_pubkey, ban_reason)
@@ -559,6 +568,12 @@ pub async fn execute_timeout_with_marker(
559568 return Ok ( false ) ;
560569 }
561570
571+ // Acquire the action-row lock before evaluating the wall-clock expiry check.
572+ sqlx:: query ( "SELECT id FROM relay_admin_actions WHERE id = $1 FOR UPDATE" )
573+ . bind ( action_id)
574+ . fetch_optional ( & mut * tx)
575+ . await ?;
576+
562577 sqlx:: query (
563578 r#"
564579 INSERT INTO community_bans (community_id, pubkey, banned, muted_until, actor_pubkey, mute_reason)
@@ -655,6 +670,12 @@ pub async fn execute_kick_with_marker(
655670 return Ok ( KickWithMarkerResult :: AlreadyMarked ) ;
656671 }
657672
673+ // Acquire the action-row lock before evaluating the wall-clock expiry check.
674+ sqlx:: query ( "SELECT id FROM relay_admin_actions WHERE id = $1 FOR UPDATE" )
675+ . bind ( action_id)
676+ . fetch_optional ( & mut * tx)
677+ . await ?;
678+
658679 let kick = sqlx:: query (
659680 r#"
660681 UPDATE channel_members
@@ -737,6 +758,12 @@ pub async fn execute_delete_with_marker(
737758 return Ok ( false ) ;
738759 }
739760
761+ // Acquire the action-row lock before evaluating the wall-clock expiry check.
762+ sqlx:: query ( "SELECT id FROM relay_admin_actions WHERE id = $1 FOR UPDATE" )
763+ . bind ( action_id)
764+ . fetch_optional ( & mut * tx)
765+ . await ?;
766+
740767 // Canonical delete + thread_metadata counters, fenced by this transaction.
741768 // Counters move only when the row transitions to deleted, so a second
742769 // action against an already-deleted target leaves them unchanged.
@@ -1011,6 +1038,36 @@ pub async fn record_failure(
10111038 lease_token : Uuid ,
10121039 error : & str ,
10131040) -> Result < bool > {
1041+ let mut tx = pool. begin ( ) . await ?;
1042+
1043+ // Acquire the action-row lock first so the wall-clock expiry check below
1044+ // is evaluated under the lock, not before it. Without this, Postgres can
1045+ // test clock_timestamp() before waiting on a locked row, allowing an
1046+ // expired writer to record failure after its lease ended.
1047+ let row = sqlx:: query_scalar :: < _ , bool > (
1048+ r#"
1049+ SELECT action_lease_expires_at > clock_timestamp()
1050+ FROM relay_admin_actions
1051+ WHERE id = $1
1052+ AND action_lease_token = $2
1053+ AND state = 'enforcing'
1054+ AND step_marker IS NULL
1055+ FOR UPDATE
1056+ "# ,
1057+ )
1058+ . bind ( action_id)
1059+ . bind ( lease_token)
1060+ . fetch_optional ( & mut * tx)
1061+ . await ?;
1062+
1063+ match row {
1064+ Some ( true ) => { }
1065+ _ => {
1066+ tx. rollback ( ) . await ?;
1067+ return Ok ( false ) ;
1068+ }
1069+ }
1070+
10141071 let result = sqlx:: query (
10151072 r#"
10161073 UPDATE relay_admin_actions
@@ -1023,9 +1080,16 @@ pub async fn record_failure(
10231080 . bind ( action_id)
10241081 . bind ( error)
10251082 . bind ( lease_token)
1026- . execute ( pool )
1083+ . execute ( & mut * tx )
10271084 . await ?;
1028- Ok ( result. rows_affected ( ) > 0 )
1085+
1086+ if result. rows_affected ( ) == 0 {
1087+ tx. rollback ( ) . await ?;
1088+ return Ok ( false ) ;
1089+ }
1090+
1091+ tx. commit ( ) . await ?;
1092+ Ok ( true )
10291093}
10301094
10311095/// Cancel a failed action (pre-mutation only) and return its report to 'open'.
@@ -3075,12 +3139,12 @@ mod postgres_tests {
30753139 assert_eq ! ( counts( & pool, cid, & root) . await , ( 1 , 2 ) ) ;
30763140 }
30773141
3078- /// Regression for the in-flight lease expiry gap: the lease is live at transaction
3079- /// entry but expires while the event write blocks (simulated by a per-row trigger
3080- /// that sleeps 1 s, shorter than the 500 ms lease ). Before the `clock_timestamp()`
3081- /// fix the final marker fence used `now()` (transaction start time) and committed
3082- /// despite the expired lease. The trigger is installed only in this isolated test
3083- /// database and is dropped with the database on teardown.
3142+ /// Regression: lease live at transaction entry, expires while the event write blocks
3143+ /// (simulated by a per-row trigger that sleeps 1 s; the lease expires after 500 ms,
3144+ /// so 1 s is *longer* than 500 ms). Before the `clock_timestamp()` + row-lock fix
3145+ /// the final marker fence committed despite the expired lease. The trigger is
3146+ /// installed only in this isolated test database and is dropped with the database
3147+ /// on teardown.
30843148 #[ tokio:: test]
30853149 #[ ignore = "requires Postgres" ]
30863150 async fn admin_delete_with_lease_expiring_during_write_changes_nothing ( ) {
@@ -3090,7 +3154,7 @@ mod postgres_tests {
30903154 let [ root, reply, nested] = make_thread ( & pool, cid) . await ;
30913155
30923156 // Install a trigger that delays every UPDATE on events by 1 second.
3093- // The lease expires after 500 ms, so the final fence is reached after expiry.
3157+ // The lease expires after 500 ms; 1 s > 500 ms , so the trigger runs past expiry.
30943158 sqlx:: raw_sql (
30953159 "CREATE FUNCTION _test_delay_event_update() RETURNS trigger LANGUAGE plpgsql AS \
30963160 $$ BEGIN PERFORM pg_sleep(1); RETURN NEW; END $$; \
@@ -3104,6 +3168,20 @@ mod postgres_tests {
31043168 let lease_until = Utc :: now ( ) + chrono:: Duration :: milliseconds ( 500 ) ;
31053169 let ( action_id, token) = enforcing_action ( & pool, community_id, lease_until) . await ;
31063170
3171+ // Precondition: setup must complete before the lease expires. A slow environment
3172+ // should fail here rather than pass the wrong code path (early rejection).
3173+ let live_at_entry: bool = sqlx:: query_scalar (
3174+ "SELECT action_lease_expires_at > clock_timestamp() FROM relay_admin_actions WHERE id = $1" ,
3175+ )
3176+ . bind ( action_id)
3177+ . fetch_one ( & pool)
3178+ . await
3179+ . expect ( "live-at-entry check" ) ;
3180+ assert ! (
3181+ live_at_entry,
3182+ "setup consumed the lease before entry — precondition failed, not a test failure"
3183+ ) ;
3184+
31073185 let committed = execute_delete_with_marker (
31083186 & pool,
31093187 action_id,
@@ -3130,6 +3208,12 @@ mod postgres_tests {
31303208 !deleted,
31313209 "event must not be deleted when lease expires mid-write"
31323210 ) ;
3211+ let stored_marker = get_action ( & pool, action_id)
3212+ . await
3213+ . expect ( "action lookup" )
3214+ . expect ( "action exists" )
3215+ . step_marker ;
3216+ assert_eq ! ( stored_marker, None , "step_marker must not be persisted" ) ;
31333217 assert_eq ! (
31343218 counts( & pool, cid, & reply) . await ,
31353219 ( 1 , 0 ) ,
@@ -3142,6 +3226,157 @@ mod postgres_tests {
31423226 ) ;
31433227 }
31443228
3229+ /// Regression for the row-lock wait gap: Postgres can evaluate
3230+ /// `clock_timestamp()` before waiting to lock the action row. If another
3231+ /// transaction holds the row and releases it unchanged after the lease expires,
3232+ /// the final marker UPDATE would commit without the lock-first shape.
3233+ ///
3234+ /// This test locks the action row externally, confirms the worker is blocked
3235+ /// at the marker UPDATE while the lease is still live, waits for DB-clock
3236+ /// expiry, releases the lock unchanged, and asserts the worker rejects.
3237+ #[ tokio:: test]
3238+ #[ ignore = "requires Postgres" ]
3239+ async fn admin_delete_with_lease_expiring_during_row_lock_wait_changes_nothing ( ) {
3240+ let pool = setup_pool ( ) . await ;
3241+ let community_id = make_community ( & pool) . await ;
3242+ let cid = CommunityId :: from_uuid ( community_id) ;
3243+ let [ root, reply, nested] = make_thread ( & pool, cid) . await ;
3244+
3245+ // 3-second lease gives enough headroom for setup; will be expired before unlock.
3246+ let ( action_id, token) = enforcing_action (
3247+ & pool,
3248+ community_id,
3249+ Utc :: now ( ) + chrono:: Duration :: seconds ( 3 ) ,
3250+ )
3251+ . await ;
3252+
3253+ // Lock the action row without changing it; the worker will block at the
3254+ // explicit FOR UPDATE in execute_delete_with_marker.
3255+ let mut locker = pool. begin ( ) . await . expect ( "locker tx" ) ;
3256+ let locker_pid: i32 = sqlx:: query_scalar ( "SELECT pg_backend_pid()" )
3257+ . fetch_one ( & mut * locker)
3258+ . await
3259+ . expect ( "locker pid" ) ;
3260+ sqlx:: query ( "SELECT id FROM relay_admin_actions WHERE id = $1 FOR UPDATE" )
3261+ . bind ( action_id)
3262+ . fetch_one ( & mut * locker)
3263+ . await
3264+ . expect ( "lock action row" ) ;
3265+
3266+ let worker_pool = pool. clone ( ) ;
3267+ let nr = nested. clone ( ) ;
3268+ let pr = reply. clone ( ) ;
3269+ let rr = root. clone ( ) ;
3270+ let worker = tokio:: spawn ( async move {
3271+ execute_delete_with_marker (
3272+ & worker_pool,
3273+ action_id,
3274+ token,
3275+ cid,
3276+ & nr,
3277+ Some ( & pr) ,
3278+ Some ( & rr) ,
3279+ )
3280+ . await
3281+ } ) ;
3282+
3283+ // Wait until the worker is blocked at the action-row lock.
3284+ tokio:: time:: timeout ( std:: time:: Duration :: from_secs ( 10 ) , async {
3285+ loop {
3286+ let blocked: bool = sqlx:: query_scalar (
3287+ "SELECT EXISTS (\
3288+ SELECT 1 FROM pg_stat_activity \
3289+ WHERE datname = current_database() \
3290+ AND wait_event_type = 'Lock' \
3291+ AND query LIKE '%relay_admin_actions%FOR UPDATE%' \
3292+ AND $1 = ANY(pg_blocking_pids(pid))\
3293+ )",
3294+ )
3295+ . bind ( locker_pid)
3296+ . fetch_one ( & pool)
3297+ . await
3298+ . expect ( "observe worker blocked" ) ;
3299+ if blocked {
3300+ break ;
3301+ }
3302+ tokio:: time:: sleep ( std:: time:: Duration :: from_millis ( 10 ) ) . await ;
3303+ }
3304+ } )
3305+ . await
3306+ . expect ( "worker must block on the action-row FOR UPDATE within 10 s" ) ;
3307+
3308+ // Precondition: lease must still be live when the worker is blocked.
3309+ let live_when_blocked: bool = sqlx:: query_scalar (
3310+ "SELECT action_lease_expires_at > clock_timestamp() FROM relay_admin_actions WHERE id = $1" ,
3311+ )
3312+ . bind ( action_id)
3313+ . fetch_one ( & pool)
3314+ . await
3315+ . expect ( "live_when_blocked" ) ;
3316+ assert ! (
3317+ live_when_blocked,
3318+ "lease must be live when worker reaches the row lock"
3319+ ) ;
3320+
3321+ // Wait until the lease is definitely expired according to the DB clock.
3322+ sqlx:: query (
3323+ "SELECT pg_sleep(GREATEST(EXTRACT(EPOCH FROM action_lease_expires_at - clock_timestamp()), 0)::double precision + 0.05) \
3324+ FROM relay_admin_actions WHERE id = $1",
3325+ )
3326+ . bind ( action_id)
3327+ . execute ( & pool)
3328+ . await
3329+ . expect ( "wait through expiry" ) ;
3330+
3331+ let expired: bool = sqlx:: query_scalar (
3332+ "SELECT action_lease_expires_at <= clock_timestamp() FROM relay_admin_actions WHERE id = $1" ,
3333+ )
3334+ . bind ( action_id)
3335+ . fetch_one ( & pool)
3336+ . await
3337+ . expect ( "expired_before_unlock" ) ;
3338+ assert ! (
3339+ expired,
3340+ "lease must be expired before releasing the row lock"
3341+ ) ;
3342+
3343+ // Release the row lock without writing a new tuple version.
3344+ locker. rollback ( ) . await . expect ( "release row lock" ) ;
3345+
3346+ let committed = worker. await . expect ( "worker join" ) . expect ( "worker result" ) ;
3347+
3348+ let deleted: bool = sqlx:: query_scalar (
3349+ "SELECT deleted_at IS NOT NULL FROM events WHERE community_id = $1 AND id = $2" ,
3350+ )
3351+ . bind ( community_id)
3352+ . bind ( & nested)
3353+ . fetch_one ( & pool)
3354+ . await
3355+ . expect ( "event row" ) ;
3356+ let stored_marker = get_action ( & pool, action_id)
3357+ . await
3358+ . expect ( "action lookup" )
3359+ . expect ( "action exists" )
3360+ . step_marker ;
3361+
3362+ assert ! (
3363+ !committed,
3364+ "lease expired during row-lock wait must not commit"
3365+ ) ;
3366+ assert ! ( !deleted, "event must not be deleted" ) ;
3367+ assert_eq ! ( stored_marker, None , "step_marker must not be persisted" ) ;
3368+ assert_eq ! (
3369+ counts( & pool, cid, & reply) . await ,
3370+ ( 1 , 0 ) ,
3371+ "parent counts unchanged"
3372+ ) ;
3373+ assert_eq ! (
3374+ counts( & pool, cid, & root) . await ,
3375+ ( 1 , 2 ) ,
3376+ "root counts unchanged"
3377+ ) ;
3378+ }
3379+
31453380 #[ tokio:: test]
31463381 #[ ignore = "requires Postgres" ]
31473382 async fn admin_delete_rolled_back_by_marker_fence_changes_nothing ( ) {
0 commit comments