Skip to main content

yang_db/postgres/
transaction.rs

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