Skip to main content

yang_db/postgres/
transaction.rs

1use crate::error::DbError;
2use crate::isolation::IsolationLevel;
3use crate::postgres::params::{json_signature, keyed_sql, PgUntypedNull};
4use crate::postgres::query_builder::QueryBuilder;
5use sqlx::Transaction as SqlxTransaction;
6use std::collections::VecDeque;
7use std::sync::atomic::{AtomicU64, Ordering};
8
9/// 流式游标默认每批拉取的行数。
10pub(crate) const DEFAULT_STREAM_BATCH_SIZE: usize = 1000;
11
12/// 事务级排他 advisory lock(阻塞)。
13const XACT_LOCK_SQL: &str = "SELECT pg_advisory_xact_lock($1)";
14/// 事务级共享 advisory lock(阻塞)。
15const XACT_LOCK_SHARED_SQL: &str = "SELECT pg_advisory_xact_lock_shared($1)";
16/// 事务级排他 advisory lock(非阻塞)。
17const XACT_TRY_LOCK_SQL: &str = "SELECT pg_try_advisory_xact_lock($1)";
18/// 事务级共享 advisory lock(非阻塞)。
19const XACT_TRY_LOCK_SHARED_SQL: &str = "SELECT pg_try_advisory_xact_lock_shared($1)";
20
21/// 进程内游标名计数器。游标名只由本 crate 生成,不含外部输入。
22static STREAM_CURSOR_COUNTER: AtomicU64 = AtomicU64::new(1);
23
24/// 生成进程内唯一的游标名。
25pub(crate) fn next_cursor_name() -> String {
26    format!(
27        "yang_cursor_{}",
28        STREAM_CURSOR_COUNTER.fetch_add(1, Ordering::Relaxed)
29    )
30}
31
32/// 规整流式查询 SQL:去掉首尾空白与末尾分号,避免 `DECLARE ... CURSOR FOR` 语法错误。
33pub(crate) fn normalize_stream_sql(sql: &str) -> Result<&str, DbError> {
34    let trimmed = sql.trim().trim_end_matches(';').trim_end();
35    if trimmed.is_empty() {
36        return Err(DbError::InvalidArgument(
37            "流式查询的 SQL 不能为空".to_string(),
38        ));
39    }
40    Ok(trimmed)
41}
42
43/// 生成保存点控制语句;`name` 必须是合法 SQL 标识符。
44fn savepoint_statement(prefix: &str, name: &str) -> Result<String, DbError> {
45    Ok(format!(
46        "{prefix} {}",
47        crate::postgres::identifier::quote_identifier(name)?
48    ))
49}
50
51/// 两阶段提交 GID 的字节上限(PostgreSQL `gid` 字段长度)。
52const MAX_PREPARED_GID_BYTES: usize = 200;
53
54/// 校验两阶段提交 GID。
55///
56/// `PREPARE TRANSACTION` / `COMMIT PREPARED` 的 GID 语法要求字符串常量,
57/// **不能**用绑定参数;因此只允许 `[A-Za-z0-9_.:-]` 白名单字符,杜绝把任意
58/// 文本拼进 SQL。
59pub(crate) fn validate_prepared_gid(gid: &str) -> Result<(), DbError> {
60    if gid.is_empty() || gid.len() > MAX_PREPARED_GID_BYTES {
61        return Err(DbError::InvalidArgument(format!(
62            "两阶段提交 GID 长度必须在 1..={MAX_PREPARED_GID_BYTES} 字节,当前 {} 字节",
63            gid.len()
64        )));
65    }
66    if !gid
67        .chars()
68        .all(|c| c.is_ascii_alphanumeric() || matches!(c, '_' | '.' | '-' | ':'))
69    {
70        return Err(DbError::InvalidArgument(format!(
71            "两阶段提交 GID 只允许 ASCII 字母、数字与 _ . - : 字符: {gid:?}"
72        )));
73    }
74    Ok(())
75}
76
77/// 校验并引用会话 GUC 名(如 `app.tenant_id`)。
78///
79/// 键名不能参数化,必须严格校验:每一段都必须是 `[A-Za-z_][A-Za-z0-9_]*`,
80/// 最多三段。返回整串作为单引号字符串常量(`'app.tenant_id'`),供
81/// `set_config('key', $1, true)` 使用。
82fn quote_guc_name(key: &str) -> Result<String, DbError> {
83    if key.is_empty() || key.len() > 128 {
84        return Err(DbError::InvalidArgument(format!(
85            "会话 GUC 名长度必须在 1..=128 字节: {key:?}"
86        )));
87    }
88    let parts = key
89        .split('.')
90        .map(crate::sql_types::Identifier::parse)
91        .collect::<Result<Vec<_>, _>>()?;
92    if parts.is_empty() || parts.len() > 3 {
93        return Err(DbError::InvalidArgument(format!(
94            "会话 GUC 名最多三段(`prefix.name`): {key:?}"
95        )));
96    }
97    // 每段已通过严格标识符校验,整串只会含 `[A-Za-z0-9_.]`,可直接作为
98    // 单引号字符串常量(无内嵌单引号)。
99    Ok(format!("'{key}'"))
100}
101
102/// 表锁模式(`LOCK TABLE ... IN <mode> MODE`)。
103///
104/// 只暴露 PostgreSQL 文档定义的 8 种表级锁模式,避免把任意字符串拼进 SQL。
105#[derive(Debug, Clone, Copy, PartialEq, Eq)]
106#[non_exhaustive]
107pub enum TableLockMode {
108    /// `ACCESS SHARE`
109    AccessShare,
110    /// `ROW SHARE`
111    RowShare,
112    /// `ROW EXCLUSIVE`
113    RowExclusive,
114    /// `SHARE UPDATE EXCLUSIVE`
115    ShareUpdateExclusive,
116    /// `SHARE`
117    Share,
118    /// `SHARE ROW EXCLUSIVE`
119    ShareRowExclusive,
120    /// `EXCLUSIVE`
121    Exclusive,
122    /// `ACCESS EXCLUSIVE`
123    AccessExclusive,
124}
125
126impl TableLockMode {
127    /// 返回 SQL 关键字串。
128    pub fn as_sql(self) -> &'static str {
129        match self {
130            Self::AccessShare => "ACCESS SHARE",
131            Self::RowShare => "ROW SHARE",
132            Self::RowExclusive => "ROW EXCLUSIVE",
133            Self::ShareUpdateExclusive => "SHARE UPDATE EXCLUSIVE",
134            Self::Share => "SHARE",
135            Self::ShareRowExclusive => "SHARE ROW EXCLUSIVE",
136            Self::Exclusive => "EXCLUSIVE",
137            Self::AccessExclusive => "ACCESS EXCLUSIVE",
138        }
139    }
140}
141
142/// 生成 `LOCK TABLE` 语句;表名按 `table` / `schema.table` 口径校验并加引号。
143fn lock_table_statement(table: &str, mode: TableLockMode) -> Result<String, DbError> {
144    let quoted = crate::postgres::identifier::quote_table(table)?;
145    Ok(format!("LOCK TABLE {quoted} IN {} MODE", mode.as_sql()))
146}
147
148/// PostgreSQL 事务属性(Phase B)。
149///
150/// 与 [`IsolationLevel`] 组合使用:`isolation` 为 `None` 时不发
151/// `SET TRANSACTION ISOLATION LEVEL`,沿用服务端默认(通常是 READ COMMITTED)。
152///
153/// 标注 `#[non_exhaustive]`:未来新增事务属性不构成破坏性变更;跨 crate 请用
154/// [`TransactionOptions::default`] / [`TransactionOptions::new`] 加链式 setter 构造。
155#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
156#[non_exhaustive]
157pub struct TransactionOptions {
158    /// 隔离级别;`None` 表示沿用服务端默认。
159    pub isolation: Option<IsolationLevel>,
160    /// 是否声明为只读事务(`READ ONLY`)。只读事务内写入由服务端拒绝(SQLSTATE 25006)。
161    pub read_only: bool,
162    /// 是否声明 `DEFERRABLE`(只对 SERIALIZABLE READ ONLY 事务有实际意义)。
163    pub deferrable: bool,
164}
165
166impl TransactionOptions {
167    /// 全默认选项(等价于 `Database::transaction`)。
168    pub fn new() -> Self {
169        Self::default()
170    }
171
172    /// 只读事务选项。
173    pub fn read_only() -> Self {
174        Self {
175            read_only: true,
176            ..Self::default()
177        }
178    }
179
180    /// 设置隔离级别(链式)。
181    pub fn with_isolation(mut self, isolation: IsolationLevel) -> Self {
182        self.isolation = Some(isolation);
183        self
184    }
185
186    /// 设置只读属性(链式)。
187    pub fn with_read_only(mut self, read_only: bool) -> Self {
188        self.read_only = read_only;
189        self
190    }
191
192    /// 设置 `DEFERRABLE` 属性(链式)。
193    pub fn with_deferrable(mut self, deferrable: bool) -> Self {
194        self.deferrable = deferrable;
195        self
196    }
197
198    /// 生成 `SET TRANSACTION ...` 语句;全默认时返回 `None`(不发语句)。
199    pub(crate) fn set_transaction_sql(&self) -> Option<String> {
200        let mut modes = Vec::new();
201        if let Some(isolation) = self.isolation {
202            modes.push(format!("ISOLATION LEVEL {}", isolation.as_sql()));
203        }
204        if self.read_only {
205            modes.push("READ ONLY".to_string());
206        }
207        if self.deferrable {
208            modes.push("DEFERRABLE".to_string());
209        }
210        if modes.is_empty() {
211            None
212        } else {
213            Some(format!("SET TRANSACTION {}", modes.join(", ")))
214        }
215    }
216}
217
218/// 数据库事务(PostgreSQL)
219pub struct Transaction {
220    tx: Option<SqlxTransaction<'static, sqlx::Postgres>>,
221    enable_logging: bool,
222}
223
224impl Transaction {
225    /// 创建新的事务实例
226    pub(crate) fn new(tx: SqlxTransaction<'static, sqlx::Postgres>, enable_logging: bool) -> Self {
227        Self {
228            tx: Some(tx),
229            enable_logging,
230        }
231    }
232
233    /// 提交事务
234    pub async fn commit(mut self) -> Result<(), DbError> {
235        if self.enable_logging {
236            log::debug!("提交事务");
237        }
238
239        if let Some(tx) = self.tx.take() {
240            tx.commit().await?;
241        }
242
243        Ok(())
244    }
245
246    /// 回滚事务
247    pub async fn rollback(mut self) -> Result<(), DbError> {
248        if self.enable_logging {
249            log::debug!("回滚事务");
250        }
251
252        if let Some(tx) = self.tx.take() {
253            tx.rollback().await?;
254        }
255
256        Ok(())
257    }
258
259    /// 执行原生 SQL
260    ///
261    /// # 弃用说明
262    ///
263    /// 已弃用(与 MySQL `Transaction::execute` 对齐)。请使用参数化查询方法
264    /// [`execute_with_params`](Self::execute_with_params) 或
265    /// [`query_with_params`](Self::query_with_params) 以防 SQL 注入。
266    #[deprecated(
267        since = "0.1.0",
268        note = "使用 execute_with_params / query_with_params 等参数化方法替代"
269    )]
270    pub async fn execute(&mut self, sql: &str) -> Result<u64, DbError> {
271        if self.enable_logging {
272            log::debug!("事务中执行: {}", sql);
273        }
274
275        if let Some(tx) = &mut self.tx {
276            let result = sqlx::query(sql).execute(&mut **tx).await?;
277            Ok(result.rows_affected())
278        } else {
279            Err(DbError::TransactionError("事务已提交或回滚".to_string()))
280        }
281    }
282
283    /// 执行带参数的原生 SQL(参数化查询,防止 SQL 注入)
284    ///
285    /// # 参数
286    /// - sql: SQL 语句,使用 `$N` 作为参数占位符
287    /// - params: 参数列表,使用 `serde_json::Value` 类型
288    ///
289    /// # 返回
290    /// - Ok(u64): 受影响的行数
291    /// - Err(DbError): 执行失败错误
292    ///
293    /// # 示例
294    ///
295    /// ```no_run
296    /// use yang_db::postgres::Database;
297    /// use serde_json::json;
298    ///
299    /// # async fn example() -> Result<(), yang_db::DbError> {
300    /// let db = Database::connect("postgres://postgres:postgres@localhost:5432/test").await?;
301    /// let mut tx = db.transaction().await?;
302    /// let params = vec![json!("张三"), json!("张三@example.com")];
303    /// tx.execute_with_params("INSERT INTO users (name, email) VALUES ($1, $2)", params).await?;
304    /// tx.commit().await?;
305    /// # Ok(())
306    /// # }
307    /// ```
308    pub async fn execute_with_params(
309        &mut self,
310        sql: &str,
311        params: Vec<serde_json::Value>,
312    ) -> Result<u64, DbError> {
313        if self.enable_logging {
314            log::debug!("事务中执行参数化语句: {}, 参数数量: {}", sql, params.len());
315        }
316
317        if let Some(tx) = &mut self.tx {
318            // 构建查询并逐一绑定参数;缓存键带上参数类型签名。
319            let statement = keyed_sql(sql, json_signature(&params));
320            let mut query = sqlx::query(&statement);
321            for param in &params {
322                query = bind_json_param_tx(query, param);
323            }
324            let result = query.execute(&mut **tx).await?;
325            Ok(result.rows_affected())
326        } else {
327            Err(DbError::TransactionError("事务已提交或回滚".to_string()))
328        }
329    }
330
331    /// 执行带参数的原生 SELECT 查询(参数化查询,防止 SQL 注入)
332    ///
333    /// # 参数
334    /// - sql: SQL 查询语句,使用 `$N` 作为参数占位符
335    /// - params: 参数列表,使用 `serde_json::Value` 类型
336    ///
337    /// # 返回
338    /// - Ok(Vec<T>): 查询结果列表
339    /// - Err(DbError): 查询失败错误
340    ///
341    /// # 示例
342    ///
343    /// ```no_run
344    /// use yang_db::postgres::Database;
345    /// use serde_json::json;
346    ///
347    /// # async fn example() -> Result<(), yang_db::DbError> {
348    /// let db = Database::connect("postgres://postgres:postgres@localhost:5432/test").await?;
349    /// let mut tx = db.transaction().await?;
350    /// let params = vec![json!(1i64)];
351    /// // let users: Vec<User> = tx.query_with_params("SELECT * FROM users WHERE id = $1", params).await?;
352    /// tx.commit().await?;
353    /// # Ok(())
354    /// # }
355    /// ```
356    pub async fn query_with_params<T>(
357        &mut self,
358        sql: &str,
359        params: Vec<serde_json::Value>,
360    ) -> Result<Vec<T>, DbError>
361    where
362        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
363    {
364        if self.enable_logging {
365            log::debug!("事务中执行参数化查询: {}, 参数数量: {}", sql, params.len());
366        }
367
368        if let Some(tx) = &mut self.tx {
369            // 构建查询并逐一绑定参数;缓存键带上参数类型签名。
370            let statement = keyed_sql(sql, json_signature(&params));
371            let mut query = sqlx::query_as::<_, T>(&statement);
372            for param in &params {
373                query = bind_json_param_as_tx(query, param);
374            }
375            let rows = query.fetch_all(&mut **tx).await?;
376            Ok(rows)
377        } else {
378            Err(DbError::TransactionError("事务已提交或回滚".to_string()))
379        }
380    }
381
382    /// 在当前事务中执行普通 QueryBuilder SELECT。
383    pub async fn select<T>(&mut self, query: QueryBuilder<'_>) -> Result<Vec<T>, DbError>
384    where
385        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
386    {
387        self.select_with_optional_lock(query, None).await
388    }
389
390    /// 在当前事务中执行 `FOR UPDATE` SELECT。
391    pub async fn select_for_update<T>(&mut self, query: QueryBuilder<'_>) -> Result<Vec<T>, DbError>
392    where
393        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
394    {
395        self.select_locked(query, crate::RowLock::ForUpdate).await
396    }
397
398    /// 在当前事务中执行 `FOR SHARE` SELECT。
399    pub async fn select_for_share<T>(&mut self, query: QueryBuilder<'_>) -> Result<Vec<T>, DbError>
400    where
401        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
402    {
403        self.select_locked(query, crate::RowLock::ForShare).await
404    }
405
406    /// 在当前事务中按指定模式执行行锁 SELECT。
407    pub async fn select_locked<T>(
408        &mut self,
409        query: QueryBuilder<'_>,
410        lock: crate::RowLock,
411    ) -> Result<Vec<T>, DbError>
412    where
413        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
414    {
415        self.select_with_optional_lock(query, Some(lock)).await
416    }
417
418    /// 在当前事务中执行完整的行锁 SELECT:可指定 `FOR ... OF` 表列表、`NOWAIT`
419    /// 或 `SKIP LOCKED`(与 [`QueryBuilder::try_to_sql_with_lock`] 的 SQL 语义一致)。
420    ///
421    /// `nowait` 与 `skip_locked` 互斥,同时为 `true` 时返回
422    /// [`DbError::InvalidArgument`];`ForUpdateSkipLocked` / `ForShareSkipLocked`
423    /// 隐含 `SKIP LOCKED`。锁冲突在 `NOWAIT` 下返回 SQLSTATE `55P03`,
424    /// 可通过 [`DbError::sqlstate`] 或 [`DbError::sql_error`] 识别。
425    pub async fn select_locked_with<T>(
426        &mut self,
427        query: QueryBuilder<'_>,
428        lock: crate::RowLock,
429        of: &[&crate::FieldRef],
430        nowait: bool,
431        skip_locked: bool,
432    ) -> Result<Vec<T>, DbError>
433    where
434        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
435    {
436        let (sql, params) = query.render_with_lock(lock, of, nowait, skip_locked)?;
437        self.fetch_all_with_sql(&sql, &params).await
438    }
439
440    async fn select_with_optional_lock<T>(
441        &mut self,
442        query: QueryBuilder<'_>,
443        lock: Option<crate::RowLock>,
444    ) -> Result<Vec<T>, DbError>
445    where
446        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
447    {
448        let (sql, params) = query.render_for_transaction(lock)?;
449        self.fetch_all_with_sql(&sql, &params).await
450    }
451
452    /// 在当前事务连接上执行已渲染的参数化查询。
453    async fn fetch_all_with_sql<T>(
454        &mut self,
455        sql: &str,
456        params: &[crate::postgres::SqlValue],
457    ) -> Result<Vec<T>, DbError>
458    where
459        T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
460    {
461        if self.enable_logging {
462            log::debug!("事务中执行 SELECT: {}, 参数: {:?}", sql, params);
463        }
464        let tx = self
465            .tx
466            .as_mut()
467            .ok_or_else(|| DbError::TransactionError("事务已提交或回滚".to_string()))?;
468        let mut sql_query = sqlx::query_as::<_, T>(sql);
469        for param in params {
470            sql_query = crate::postgres::query_builder::bind_param(sql_query, param);
471        }
472        Ok(sql_query.fetch_all(&mut **tx).await?)
473    }
474
475    /// 选择表,返回事务中的查询构建器
476    ///
477    /// # 参数
478    /// - table_name: 表名
479    ///
480    /// # 返回
481    /// - QueryBuilder: 与连接池路径共享的事务查询构建器
482    ///
483    /// # 示例
484    /// ```no_run
485    /// use yang_db::postgres::Database;
486    /// use serde_json::json;
487    ///
488    /// # async fn example() -> Result<(), yang_db::DbError> {
489    /// let db = Database::connect("postgres://postgres:postgres@localhost:5432/test").await?;
490    /// let mut tx = db.transaction().await?;
491    ///
492    /// // 在事务中插入数据
493    /// let user_data = json!({"name": "张三", "email": "zhangsan@example.com"});
494    /// let user_id = tx.table(yang_db::table!("users")).insert(&user_data).await?;
495    ///
496    /// // 在事务中更新数据
497    /// let update_data = json!({"status": 1});
498    /// tx.table(yang_db::table!("users"))
499    ///     .where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, user_id)?
500    ///     .update(&update_data)
501    ///     .await?;
502    ///
503    /// // 提交事务
504    /// tx.commit().await?;
505    /// # Ok(())
506    /// # }
507    /// ```
508    pub fn table(&mut self, table: &crate::TableRef) -> QueryBuilder<'_> {
509        let enable_logging = self.enable_logging;
510        QueryBuilder::new_transaction(self, table.as_str(), enable_logging)
511    }
512
513    /// 借出底层 sqlx 连接(受控逃生舱,供同 workspace 上层在事务内执行自构建的参数化语句)
514    ///
515    /// 语义与 MySQL 侧一致:上层已持有参数化好的 SQL 与参数,需要在**同一事务**
516    /// 里执行以保证原子性。标记 `#[doc(hidden)]`,非稳定公开 API,仅供 workspace
517    /// 内部桥接使用;值侧由 `$N` 绑定,标识符安全由调用方保证。
518    ///
519    /// # 返回
520    /// - `Some(&mut PgConnection)`:事务仍处于活动状态
521    /// - `None`:事务已 `commit`/`rollback`,连接不再可用
522    #[doc(hidden)]
523    pub fn executor(&mut self) -> Option<&mut sqlx::PgConnection> {
524        // `SqlxTransaction` 通过 DerefMut 解引用为底层 PgConnection
525        self.tx.as_deref_mut()
526    }
527
528    /// 在当前事务内创建保存点(`SAVEPOINT`)。
529    ///
530    /// `name` 必须是合法 SQL 标识符(首字符字母/下划线,其余字母/数字/下划线);
531    /// 非法名称返回 [`DbError::InvalidArgument`],不会被拼接进 SQL。
532    pub async fn savepoint(&mut self, name: &str) -> Result<(), DbError> {
533        let sql = savepoint_statement("SAVEPOINT", name)?;
534        self.execute_control(&sql).await
535    }
536
537    /// 回滚到指定保存点(`ROLLBACK TO SAVEPOINT`)。
538    ///
539    /// 只撤销保存点之后的工作;保存点之前的外层事务与更早的保存点不受影响。
540    pub async fn rollback_to(&mut self, name: &str) -> Result<(), DbError> {
541        let sql = savepoint_statement("ROLLBACK TO SAVEPOINT", name)?;
542        self.execute_control(&sql).await
543    }
544
545    /// 释放保存点(`RELEASE SAVEPOINT`)。
546    pub async fn release_savepoint(&mut self, name: &str) -> Result<(), DbError> {
547        let sql = savepoint_statement("RELEASE SAVEPOINT", name)?;
548        self.execute_control(&sql).await
549    }
550
551    /// 设置事务级会话参数(`SET LOCAL` 语义,随事务提交/回滚自动失效)。
552    ///
553    /// `key` 必须是白名单标识符(允许点号,如 `app.tenant_id`),非法名返回
554    /// [`DbError::InvalidArgument`];`value` 通过绑定参数传入。
555    ///
556    /// 实现说明:PostgreSQL 的 `SET` 语法在扩展查询协议下不接受参数占位符
557    /// (`SET LOCAL "k" = $1` 会报语法错误),因此这里用等价的
558    /// `set_config('k', $1, true)`:键名经严格校验后以字符串常量进入,
559    /// 值仍走绑定参数,安全性不变。
560    pub async fn set_local(&mut self, key: &str, value: &str) -> Result<(), DbError> {
561        let quoted = quote_guc_name(key)?;
562        let sql = format!("SELECT set_config({quoted}, $1, true)");
563        if self.enable_logging {
564            log::debug!("设置事务级会话参数: {}", key);
565        }
566        let tx = self.active_tx()?;
567        sqlx::query(&sql).bind(value).execute(&mut **tx).await?;
568        Ok(())
569    }
570
571    /// 在当前事务内获取表级锁(`LOCK TABLE ... IN <mode> MODE`)。
572    ///
573    /// 表名按 `table` / `schema.table` 口径校验并加引号;锁在事务结束时自动释放。
574    /// 模式见 [`TableLockMode`]。
575    pub async fn lock_table(
576        &mut self,
577        table: &crate::TableRef,
578        mode: TableLockMode,
579    ) -> Result<(), DbError> {
580        let sql = lock_table_statement(table.as_str(), mode)?;
581        self.execute_control(&sql).await
582    }
583
584    /// 将当前事务准备为两阶段提交(`PREPARE TRANSACTION '<gid>'`),消费事务句柄。
585    ///
586    /// 成功后事务进入服务端 prepared 状态,需在任意连接上调用
587    /// [`Database::commit_prepared`](crate::postgres::Database::commit_prepared) 或
588    /// [`Database::rollback_prepared`](crate::postgres::Database::rollback_prepared)
589    /// 收尾。服务端 `max_prepared_transactions = 0` 时返回明确的
590    /// [`DbError::TransactionError`],而非仅透传驱动报文。
591    ///
592    /// GID 不能参数化,只接受 `[A-Za-z0-9_.:-]` 白名单字符(最长 200 字节)。
593    pub async fn prepare(mut self, gid: &str) -> Result<(), DbError> {
594        validate_prepared_gid(gid)?;
595        let mut tx = self
596            .tx
597            .take()
598            .ok_or_else(|| DbError::TransactionError("事务已提交或回滚".to_string()))?;
599        let sql = format!("PREPARE TRANSACTION '{gid}'");
600        if self.enable_logging {
601            log::debug!("准备两阶段提交: gid={gid}");
602        }
603        let result = sqlx::query(&sql).execute(&mut *tx).await;
604        match result {
605            Ok(_) => {
606                // PREPARE 已结束服务端事务;主动 rollback 清空 sqlx 的事务状态,
607                // 服务端只会回 25P01 警告,连接可安全归还连接池。
608                let _ = tx.rollback().await;
609                Ok(())
610            }
611            Err(error) => {
612                let error = DbError::from(error);
613                let _ = tx.rollback().await;
614                if error.sqlstate() == Some("55000") {
615                    return Err(DbError::TransactionError(
616                        "两阶段提交不可用:服务端 max_prepared_transactions 为 0;请启用后重试"
617                            .to_string(),
618                    ));
619                }
620                Err(error)
621            }
622        }
623    }
624
625    /// 获取事务级排他 advisory lock(`pg_advisory_xact_lock`),事务结束自动释放。
626    ///
627    /// 该调用会阻塞直到拿到锁;需要非阻塞语义请用
628    /// [`try_advisory_xact_lock`](Self::try_advisory_xact_lock)。
629    pub async fn advisory_xact_lock(&mut self, key: i64) -> Result<(), DbError> {
630        let tx = self.active_tx()?;
631        sqlx::query(XACT_LOCK_SQL)
632            .bind(key)
633            .execute(&mut **tx)
634            .await?;
635        Ok(())
636    }
637
638    /// 获取事务级共享 advisory lock(`pg_advisory_xact_lock_shared`),事务结束自动释放。
639    ///
640    /// 共享锁彼此不互斥,但与同 key 的排他锁互斥。
641    pub async fn advisory_xact_lock_shared(&mut self, key: i64) -> Result<(), DbError> {
642        let tx = self.active_tx()?;
643        sqlx::query(XACT_LOCK_SHARED_SQL)
644            .bind(key)
645            .execute(&mut **tx)
646            .await?;
647        Ok(())
648    }
649
650    /// 尝试获取事务级排他 advisory lock(`pg_try_advisory_xact_lock`),不阻塞。
651    ///
652    /// 返回 `true` 表示已持有锁,`false` 表示锁被其他会话占用。
653    pub async fn try_advisory_xact_lock(&mut self, key: i64) -> Result<bool, DbError> {
654        let tx = self.active_tx()?;
655        let acquired: bool = sqlx::query_scalar(XACT_TRY_LOCK_SQL)
656            .bind(key)
657            .fetch_one(&mut **tx)
658            .await?;
659        Ok(acquired)
660    }
661
662    /// 尝试获取事务级共享 advisory lock(`pg_try_advisory_xact_lock_shared`),不阻塞。
663    pub async fn try_advisory_xact_lock_shared(&mut self, key: i64) -> Result<bool, DbError> {
664        let tx = self.active_tx()?;
665        let acquired: bool = sqlx::query_scalar(XACT_TRY_LOCK_SHARED_SQL)
666            .bind(key)
667            .fetch_one(&mut **tx)
668            .await?;
669        Ok(acquired)
670    }
671
672    /// 在当前事务内执行多语句脚本(不新开事务)。
673    ///
674    /// 脚本经 [`split_sql_script`](crate::postgres::init::split_sql_script) 按
675    /// PostgreSQL 词法切分(识别单/双引号、行/块注释与 dollar-quote),函数体、
676    /// `DO` 块内的分号不会被误切。任一条语句失败时本方法返回错误,事务进入中止
677    /// 态,需由调用方 `rollback()`(或回滚到保存点)。
678    ///
679    /// 返回执行的语句条数(空脚本返回 0)。
680    pub async fn execute_script_in_transaction(&mut self, script: &str) -> Result<usize, DbError> {
681        let statements = crate::postgres::init::split_sql_script(script)?;
682        crate::postgres::init::execute_statements(self, &statements).await?;
683        Ok(statements.len())
684    }
685
686    /// 在当前事务内以服务端游标流式读取结果集(默认每批 1000 行)。
687    ///
688    /// 游标通过 `DECLARE ... CURSOR FOR` + 分批 `FETCH` 实现,大结果集不会一次性
689    /// 进入内存。游标借出事务的可变引用,使用期间不能再操作同一事务;未读尽即丢弃
690    /// 时,服务端游标随事务结束(提交/回滚)一并释放。
691    ///
692    /// # 错误
693    ///
694    /// SQL 为空或 `batch_size` 为 0 时返回 [`DbError::InvalidArgument`]。
695    pub async fn stream_query(
696        &mut self,
697        sql: &str,
698        params: Vec<serde_json::Value>,
699    ) -> Result<TxRowCursor<'_>, DbError> {
700        self.stream_query_with_batch_size(sql, params, DEFAULT_STREAM_BATCH_SIZE)
701            .await
702    }
703
704    /// 同 [`stream_query`](Self::stream_query),可指定每批拉取行数。
705    pub async fn stream_query_with_batch_size(
706        &mut self,
707        sql: &str,
708        params: Vec<serde_json::Value>,
709        batch_size: usize,
710    ) -> Result<TxRowCursor<'_>, DbError> {
711        if batch_size == 0 {
712            return Err(DbError::InvalidArgument(
713                "流式查询 batch_size 必须大于 0".to_string(),
714            ));
715        }
716        let body = normalize_stream_sql(sql)?;
717        let cursor_name = next_cursor_name();
718        let declare = format!(
719            "DECLARE {} CURSOR FOR {}",
720            crate::postgres::identifier::quote_identifier(&cursor_name)?,
721            body
722        );
723        let enable_logging = self.enable_logging;
724        {
725            let tx = self.active_tx()?;
726            if enable_logging {
727                log::debug!("事务内声明游标: {}", declare);
728            }
729            let keyed_declare = keyed_sql(&declare, json_signature(&params));
730            let mut query = sqlx::query(&keyed_declare);
731            for param in &params {
732                query = bind_json_param_tx(query, param);
733            }
734            query.execute(&mut **tx).await?;
735        }
736        let tx = self.active_tx()?;
737        Ok(TxRowCursor::new(
738            tx,
739            cursor_name,
740            batch_size,
741            enable_logging,
742        ))
743    }
744
745    /// 执行事务控制语句(保存点等),不做参数绑定。
746    async fn execute_control(&mut self, sql: &str) -> Result<(), DbError> {
747        if self.enable_logging {
748            log::debug!("事务控制语句: {}", sql);
749        }
750        let tx = self.active_tx()?;
751        sqlx::query(sql).execute(&mut **tx).await?;
752        Ok(())
753    }
754
755    /// 借出仍处于活动状态的事务句柄。
756    fn active_tx(&mut self) -> Result<&mut SqlxTransaction<'static, sqlx::Postgres>, DbError> {
757        self.tx
758            .as_mut()
759            .ok_or_else(|| DbError::TransactionError("事务已提交或回滚".to_string()))
760    }
761}
762
763// NEW-39: 与 MySQL 对齐——未提交/未回滚的事务被丢弃时输出诊断日志
764impl Drop for Transaction {
765    fn drop(&mut self) {
766        if self.tx.is_some() {
767            log::warn!(
768                "PG 事务被丢弃而未提交/回滚——底层将自动回滚。请显式调用 commit() 或 rollback()。"
769            );
770        }
771    }
772}
773
774/// 事务内的服务端游标(`DECLARE ... CURSOR` + 分批 `FETCH`)。
775///
776/// 逐行消费:缓冲区为空时才向后端拉取下一批,内存占用以 `batch_size` 为上限。
777/// 游标未读尽即被丢弃时无需显式清理——服务端游标随所属事务的提交/回滚一并释放。
778pub struct TxRowCursor<'a> {
779    conn: &'a mut sqlx::PgConnection,
780    cursor_name: String,
781    batch_size: usize,
782    buffer: VecDeque<sqlx::postgres::PgRow>,
783    exhausted: bool,
784    closed: bool,
785    enable_logging: bool,
786}
787
788impl<'a> TxRowCursor<'a> {
789    fn new(
790        conn: &'a mut sqlx::PgConnection,
791        cursor_name: String,
792        batch_size: usize,
793        enable_logging: bool,
794    ) -> Self {
795        Self {
796            conn,
797            cursor_name,
798            batch_size,
799            buffer: VecDeque::with_capacity(batch_size.min(1024)),
800            exhausted: false,
801            closed: false,
802            enable_logging,
803        }
804    }
805
806    /// 每批拉取的最大行数。
807    pub fn batch_size(&self) -> usize {
808        self.batch_size
809    }
810
811    /// 取下一条结果;流结束返回 `Ok(None)`。
812    pub async fn next(&mut self) -> Result<Option<sqlx::postgres::PgRow>, DbError> {
813        loop {
814            if let Some(row) = self.buffer.pop_front() {
815                return Ok(Some(row));
816            }
817            if self.exhausted {
818                self.close_inner().await?;
819                return Ok(None);
820            }
821            self.fetch_batch().await?;
822        }
823    }
824
825    /// 提前关闭游标,丢弃尚未消费的缓冲行。
826    pub async fn close(mut self) -> Result<(), DbError> {
827        self.buffer.clear();
828        self.close_inner().await
829    }
830
831    async fn fetch_batch(&mut self) -> Result<(), DbError> {
832        let sql = format!(
833            "FETCH FORWARD {} FROM {}",
834            self.batch_size,
835            crate::postgres::identifier::quote_identifier(&self.cursor_name)?
836        );
837        if self.enable_logging {
838            log::debug!("事务内游标拉取: {}", sql);
839        }
840        let rows = sqlx::query(&sql).fetch_all(&mut *self.conn).await?;
841        if rows.len() < self.batch_size {
842            self.exhausted = true;
843        }
844        self.buffer.extend(rows);
845        Ok(())
846    }
847
848    async fn close_inner(&mut self) -> Result<(), DbError> {
849        if self.closed {
850            return Ok(());
851        }
852        self.closed = true;
853        self.exhausted = true;
854        let sql = format!(
855            "CLOSE {}",
856            crate::postgres::identifier::quote_identifier(&self.cursor_name)?
857        );
858        sqlx::query(&sql).execute(&mut *self.conn).await?;
859        Ok(())
860    }
861}
862
863fn bind_json_param_tx<'q>(
864    query: sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>,
865    param: &serde_json::Value,
866) -> sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments> {
867    match param {
868        serde_json::Value::String(value) => query.bind(value.clone()),
869        serde_json::Value::Number(value) => match crate::json_param::classify_json_number(value) {
870            Some(crate::json_param::JsonNumber::Int(i)) => query.bind(i),
871            Some(crate::json_param::JsonNumber::Uint(u)) => query.bind(u.to_string()),
872            Some(crate::json_param::JsonNumber::Float(f)) => query.bind(f),
873            None => query.bind(PgUntypedNull),
874        },
875        serde_json::Value::Bool(value) => query.bind(*value),
876        serde_json::Value::Null => query.bind(PgUntypedNull),
877        other => query.bind(other.clone()),
878    }
879}
880
881fn bind_json_param_as_tx<'q, T>(
882    query: sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
883    param: &serde_json::Value,
884) -> sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
885where
886    T: for<'row> sqlx::FromRow<'row, sqlx::postgres::PgRow>,
887{
888    match param {
889        serde_json::Value::String(value) => query.bind(value.clone()),
890        serde_json::Value::Number(value) => match crate::json_param::classify_json_number(value) {
891            Some(crate::json_param::JsonNumber::Int(i)) => query.bind(i),
892            Some(crate::json_param::JsonNumber::Uint(u)) => query.bind(u.to_string()),
893            Some(crate::json_param::JsonNumber::Float(f)) => query.bind(f),
894            None => query.bind(PgUntypedNull),
895        },
896        serde_json::Value::Bool(value) => query.bind(*value),
897        serde_json::Value::Null => query.bind(PgUntypedNull),
898        other => query.bind(other.clone()),
899    }
900}
901
902#[cfg(test)]
903#[allow(clippy::unwrap_used, clippy::expect_used)]
904mod tests {
905    use super::*;
906
907    #[test]
908    fn transaction_options_default_emits_no_set_transaction() {
909        assert_eq!(TransactionOptions::default().set_transaction_sql(), None);
910        assert_eq!(TransactionOptions::new().set_transaction_sql(), None);
911    }
912
913    #[test]
914    fn transaction_options_renders_isolation_read_only_and_deferrable() {
915        let isolation_only =
916            TransactionOptions::default().with_isolation(IsolationLevel::Serializable);
917        assert_eq!(
918            isolation_only.set_transaction_sql().as_deref(),
919            Some("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
920        );
921
922        assert_eq!(
923            TransactionOptions::read_only()
924                .set_transaction_sql()
925                .as_deref(),
926            Some("SET TRANSACTION READ ONLY")
927        );
928
929        let full = TransactionOptions::default()
930            .with_isolation(IsolationLevel::RepeatableRead)
931            .with_read_only(true)
932            .with_deferrable(true);
933        assert_eq!(
934            full.set_transaction_sql().as_deref(),
935            Some("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY, DEFERRABLE")
936        );
937    }
938
939    #[test]
940    fn savepoint_statements_quote_names_and_reject_injection() {
941        assert_eq!(
942            savepoint_statement("SAVEPOINT", "sp_1").unwrap(),
943            "SAVEPOINT \"sp_1\""
944        );
945        assert_eq!(
946            savepoint_statement("ROLLBACK TO SAVEPOINT", "sp_1").unwrap(),
947            "ROLLBACK TO SAVEPOINT \"sp_1\""
948        );
949        assert_eq!(
950            savepoint_statement("RELEASE SAVEPOINT", "sp_1").unwrap(),
951            "RELEASE SAVEPOINT \"sp_1\""
952        );
953
954        for invalid in ["", "1sp", "sp; DROP TABLE t"] {
955            assert!(
956                matches!(
957                    savepoint_statement("SAVEPOINT", invalid),
958                    Err(DbError::InvalidArgument(_))
959                ),
960                "保存点名应拒绝 {invalid:?}"
961            );
962        }
963
964        // P1-28 起内嵌双引号是合法标识符(渲染为 ""),不再被拒绝。
965        assert_eq!(
966            savepoint_statement("SAVEPOINT", "sp\"x").unwrap(),
967            "SAVEPOINT \"sp\"\"x\""
968        );
969    }
970
971    /// P1-18:共享/排他、阻塞/非阻塞的事务级 advisory lock 必须用对应函数。
972    #[test]
973    fn advisory_xact_lock_sql_covers_shared_and_exclusive() {
974        assert_eq!(XACT_LOCK_SQL, "SELECT pg_advisory_xact_lock($1)");
975        assert_eq!(
976            XACT_LOCK_SHARED_SQL,
977            "SELECT pg_advisory_xact_lock_shared($1)"
978        );
979        assert_eq!(XACT_TRY_LOCK_SQL, "SELECT pg_try_advisory_xact_lock($1)");
980        assert_eq!(
981            XACT_TRY_LOCK_SHARED_SQL,
982            "SELECT pg_try_advisory_xact_lock_shared($1)"
983        );
984    }
985
986    #[test]
987    fn stream_sql_is_normalized_and_empty_is_rejected() {
988        assert_eq!(normalize_stream_sql("  SELECT 1;  ").unwrap(), "SELECT 1");
989        assert_eq!(normalize_stream_sql("SELECT 1;;").unwrap(), "SELECT 1");
990        assert!(matches!(
991            normalize_stream_sql("   ;  "),
992            Err(DbError::InvalidArgument(_))
993        ));
994    }
995
996    #[test]
997    fn cursor_names_are_unique_and_identifier_safe() {
998        let first = next_cursor_name();
999        let second = next_cursor_name();
1000        assert_ne!(first, second);
1001        assert!(crate::postgres::identifier::is_valid_identifier(&first));
1002        assert!(crate::postgres::identifier::is_valid_identifier(&second));
1003    }
1004
1005    #[test]
1006    fn guc_names_allow_dotted_identifiers_and_reject_injection() {
1007        assert_eq!(quote_guc_name("app.tenant_id").unwrap(), "'app.tenant_id'");
1008        assert_eq!(
1009            quote_guc_name("statement_timeout").unwrap(),
1010            "'statement_timeout'"
1011        );
1012
1013        for invalid in [
1014            "",
1015            ".app",
1016            "app.",
1017            "app..tenant",
1018            "a.b.c.d",
1019            "app.tenant_id = 'x'",
1020            "app.tenant_id; DROP TABLE t",
1021            "app tenant",
1022            "app\"tenant\"",
1023        ] {
1024            assert!(
1025                matches!(quote_guc_name(invalid), Err(DbError::InvalidArgument(_))),
1026                "GUC 名应拒绝 {invalid:?}"
1027            );
1028        }
1029    }
1030
1031    #[test]
1032    fn prepared_gid_validation_is_strict_whitelist() {
1033        assert!(validate_prepared_gid("phase_c_gid-1.2:3").is_ok());
1034        assert!(validate_prepared_gid(&"a".repeat(MAX_PREPARED_GID_BYTES)).is_ok());
1035
1036        for invalid in [
1037            "",
1038            "gid with space",
1039            "gid'; DROP TABLE t; --",
1040            "gid\"x",
1041            "gid\nx",
1042        ] {
1043            assert!(
1044                matches!(
1045                    validate_prepared_gid(invalid),
1046                    Err(DbError::InvalidArgument(_))
1047                ),
1048                "GID 应拒绝 {invalid:?}"
1049            );
1050        }
1051        assert!(matches!(
1052            validate_prepared_gid(&"a".repeat(MAX_PREPARED_GID_BYTES + 1)),
1053            Err(DbError::InvalidArgument(_))
1054        ));
1055    }
1056
1057    #[test]
1058    fn lock_table_statement_quotes_table_and_uses_controlled_mode() {
1059        assert_eq!(
1060            lock_table_statement("phase_c.locks", TableLockMode::AccessExclusive).unwrap(),
1061            "LOCK TABLE \"phase_c\".\"locks\" IN ACCESS EXCLUSIVE MODE"
1062        );
1063        assert_eq!(
1064            lock_table_statement("locks", TableLockMode::ShareRowExclusive).unwrap(),
1065            "LOCK TABLE \"locks\" IN SHARE ROW EXCLUSIVE MODE"
1066        );
1067        assert!(matches!(
1068            lock_table_statement("locks; DROP TABLE t", TableLockMode::Share),
1069            Err(DbError::InvalidArgument(_))
1070        ));
1071
1072        let modes = [
1073            (TableLockMode::AccessShare, "ACCESS SHARE"),
1074            (TableLockMode::RowShare, "ROW SHARE"),
1075            (TableLockMode::RowExclusive, "ROW EXCLUSIVE"),
1076            (
1077                TableLockMode::ShareUpdateExclusive,
1078                "SHARE UPDATE EXCLUSIVE",
1079            ),
1080            (TableLockMode::Share, "SHARE"),
1081            (TableLockMode::ShareRowExclusive, "SHARE ROW EXCLUSIVE"),
1082            (TableLockMode::Exclusive, "EXCLUSIVE"),
1083            (TableLockMode::AccessExclusive, "ACCESS EXCLUSIVE"),
1084        ];
1085        for (mode, expected) in modes {
1086            assert_eq!(mode.as_sql(), expected);
1087        }
1088    }
1089}