@@ -52,8 +52,8 @@ pub use error::{DbError, Result};
5252pub use event:: { EventQuery , ReactionEventInsertOutcome } ;
5353
5454use chrono:: { DateTime , Utc } ;
55- use sqlx:: postgres:: PgPoolOptions ;
56- use sqlx:: { PgPool , QueryBuilder , Row } ;
55+ use sqlx:: postgres:: { PgConnection , PgPoolOptions } ;
56+ use sqlx:: { Connection , PgPool , QueryBuilder , Row } ;
5757use std:: time:: Duration ;
5858use uuid:: Uuid ;
5959
@@ -181,6 +181,27 @@ pub struct DbPoolStats {
181181 pub max : u32 ,
182182}
183183
184+ /// Owns the detached Postgres session holding the relay usage-metrics advisory lock.
185+ ///
186+ /// The connection deliberately does not return to the main pool: session advisory
187+ /// locks must remain bound to this exact physical connection, and the poller
188+ /// pings it before each leader-only collection tick.
189+ pub struct UsageMetricsLeader {
190+ connection : PgConnection ,
191+ }
192+
193+ impl UsageMetricsLeader {
194+ /// Returns whether the lock-owning session is still reachable.
195+ ///
196+ /// Bounded to 5 seconds — a blackholed connection (no RST) would otherwise
197+ /// stall the entire poller tick until the OS TCP timeout.
198+ pub async fn is_live ( & mut self ) -> bool {
199+ tokio:: time:: timeout ( std:: time:: Duration :: from_secs ( 5 ) , self . connection . ping ( ) )
200+ . await
201+ . is_ok_and ( |r| r. is_ok ( ) )
202+ }
203+ }
204+
184205/// Configuration for the Postgres connection pool.
185206#[ derive( Debug , Clone ) ]
186207pub struct DbConfig {
@@ -343,6 +364,30 @@ impl Db {
343364 }
344365 }
345366
367+ /// Try to acquire the detached session advisory lock for relay usage metrics.
368+ ///
369+ /// The returned guard owns the exact connection that acquired the lock. It is
370+ /// detached from the shared pool so a stable leader neither returns a locked
371+ /// session to other callers nor permanently consumes a pool slot. Dropping the
372+ /// guard closes the connection and releases the session-scoped lock.
373+ pub async fn try_lock_usage_metrics (
374+ & self ,
375+ lock_key : i64 ,
376+ ) -> Result < Option < UsageMetricsLeader > > {
377+ let mut connection = self . pool . acquire ( ) . await ?;
378+ let acquired = sqlx:: query_scalar :: < _ , bool > ( "SELECT pg_try_advisory_lock($1)" )
379+ . bind ( lock_key)
380+ . fetch_one ( & mut * connection)
381+ . await ?;
382+ if acquired {
383+ Ok ( Some ( UsageMetricsLeader {
384+ connection : connection. detach ( ) ,
385+ } ) )
386+ } else {
387+ Ok ( None )
388+ }
389+ }
390+
346391 /// Return total number of communities on this relay.
347392 pub async fn usage_community_count ( & self ) -> Result < i64 > {
348393 usage:: community_count ( & self . pool ) . await
@@ -3510,6 +3555,7 @@ mod tests {
35103555 //! channels, that fail-closed chain would go blind.
35113556 use super :: * ;
35123557 use buzz_core:: CommunityId ;
3558+ use sqlx:: postgres:: PgPoolOptions ;
35133559 use sqlx:: { Acquire , PgPool } ;
35143560 use uuid:: Uuid ;
35153561
@@ -4163,6 +4209,44 @@ mod tests {
41634209 assert_eq ! ( watermark. 1 , c. id. as_bytes( ) . as_slice( ) ) ;
41644210 }
41654211
4212+ #[ tokio:: test]
4213+ #[ ignore = "requires Postgres" ]
4214+ async fn test_usage_metrics_lock_has_single_owner_and_releases_on_drop ( ) {
4215+ let pool = PgPoolOptions :: new ( )
4216+ . max_connections ( 2 )
4217+ . connect ( TEST_DB_URL )
4218+ . await
4219+ . expect ( "connect to test DB" ) ;
4220+ let first = Db :: from_pool ( pool. clone ( ) ) ;
4221+ let second = Db :: from_pool ( pool) ;
4222+ let key = 0x4255_5A5A_4D45_5452 ;
4223+
4224+ let mut leader = first
4225+ . try_lock_usage_metrics ( key)
4226+ . await
4227+ . expect ( "first lock attempt" )
4228+ . expect ( "first database handle becomes leader" ) ;
4229+ assert ! ( leader. is_live( ) . await , "lock owner remains reachable" ) ;
4230+ assert ! (
4231+ second
4232+ . try_lock_usage_metrics( key)
4233+ . await
4234+ . expect( "second lock attempt" )
4235+ . is_none( ) ,
4236+ "another session cannot become leader while the guard exists"
4237+ ) ;
4238+
4239+ drop ( leader) ;
4240+ assert ! (
4241+ second
4242+ . try_lock_usage_metrics( key)
4243+ . await
4244+ . expect( "lock attempt after leader drop" )
4245+ . is_some( ) ,
4246+ "dropping the detached session releases its advisory lock"
4247+ ) ;
4248+ }
4249+
41664250 #[ tokio:: test]
41674251 #[ ignore = "requires Postgres" ]
41684252 async fn lookup_community_by_host_matches_case_insensitive_host_index ( ) {
0 commit comments