Skip to main content

prax_postgres/
connection.rs

1//! PostgreSQL connection wrapper.
2
3use std::sync::Arc;
4
5use deadpool_postgres::Object;
6use tokio_postgres::Row;
7use tracing::{debug, trace};
8
9use prax_query::sql::is_valid_sql_identifier;
10
11use crate::error::{PgError, PgResult};
12use crate::notify::{validate_channel, validate_payload};
13use crate::statement::PreparedStatementCache;
14
15/// A wrapper around a PostgreSQL connection with statement caching.
16pub struct PgConnection {
17    client: Object,
18    statement_cache: Arc<PreparedStatementCache>,
19}
20
21/// Whether a driver error is PostgreSQL's `0A000 "cached plan must not change
22/// result type"`.
23///
24/// This is raised when a server-side prepared statement is executed after DDL
25/// altered the result columns of a table it references (e.g. a pooled
26/// connection that prepared the statement before an `ALTER TABLE … ADD
27/// COLUMN`). It is transient: re-preparing against the current schema resolves
28/// it. `0A000` is the shared `FEATURE_NOT_SUPPORTED` class, so the specific
29/// message is required — other `0A000` conditions (genuinely unsupported
30/// features) are terminal and must not trigger recovery.
31fn is_stale_cached_plan(err: &tokio_postgres::Error) -> bool {
32    // The human-readable message lives in the DbError, not in `Display`, which
33    // renders a DB error as just "db error". Reading `to_string()` here would
34    // never match the cached-plan text.
35    match err.as_db_error() {
36        Some(db) => {
37            db.code() == &tokio_postgres::error::SqlState::FEATURE_NOT_SUPPORTED
38                && is_stale_cached_plan_message(db.message())
39        }
40        None => false,
41    }
42}
43
44/// The message half of [`is_stale_cached_plan`], split out so the gate can be
45/// unit-tested without constructing a `tokio_postgres::Error` (which cannot be
46/// built with a chosen SQLSTATE via the public API).
47fn is_stale_cached_plan_message(msg: &str) -> bool {
48    msg.contains("cached plan must not change result type")
49}
50
51impl PgConnection {
52    /// Create a new connection wrapper.
53    pub(crate) fn new(client: Object, statement_cache: Arc<PreparedStatementCache>) -> Self {
54        Self {
55            client,
56            statement_cache,
57        }
58    }
59
60    /// Execute a query and return all rows.
61    pub async fn query(
62        &self,
63        sql: &str,
64        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
65    ) -> PgResult<Vec<Row>> {
66        trace!(sql = %sql, "Executing query");
67
68        // Try to get a cached prepared statement
69        let stmt = self
70            .statement_cache
71            .get_or_prepare(&self.client, sql)
72            .await?;
73
74        match self.client.query(&stmt, params).await {
75            Ok(rows) => Ok(rows),
76            Err(e) if is_stale_cached_plan(&e) => {
77                let stmt = self.reprepare_after_stale_plan(sql).await?;
78                let rows = self.client.query(&stmt, params).await?;
79                Ok(rows)
80            }
81            Err(e) => Err(e.into()),
82        }
83    }
84
85    /// Execute a query and return exactly one row.
86    pub async fn query_one(
87        &self,
88        sql: &str,
89        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
90    ) -> PgResult<Row> {
91        trace!(sql = %sql, "Executing query_one");
92
93        let stmt = self
94            .statement_cache
95            .get_or_prepare(&self.client, sql)
96            .await?;
97
98        match self.client.query_one(&stmt, params).await {
99            Ok(row) => Ok(row),
100            Err(e) if is_stale_cached_plan(&e) => {
101                let stmt = self.reprepare_after_stale_plan(sql).await?;
102                let row = self.client.query_one(&stmt, params).await?;
103                Ok(row)
104            }
105            Err(e) => Err(e.into()),
106        }
107    }
108
109    /// Execute a query and return zero or one row.
110    pub async fn query_opt(
111        &self,
112        sql: &str,
113        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
114    ) -> PgResult<Option<Row>> {
115        trace!(sql = %sql, "Executing query_opt");
116
117        let stmt = self
118            .statement_cache
119            .get_or_prepare(&self.client, sql)
120            .await?;
121
122        match self.client.query_opt(&stmt, params).await {
123            Ok(row) => Ok(row),
124            Err(e) if is_stale_cached_plan(&e) => {
125                let stmt = self.reprepare_after_stale_plan(sql).await?;
126                let row = self.client.query_opt(&stmt, params).await?;
127                Ok(row)
128            }
129            Err(e) => Err(e.into()),
130        }
131    }
132
133    /// Execute a statement and return the number of affected rows.
134    pub async fn execute(
135        &self,
136        sql: &str,
137        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
138    ) -> PgResult<u64> {
139        trace!(sql = %sql, "Executing statement");
140
141        let stmt = self
142            .statement_cache
143            .get_or_prepare(&self.client, sql)
144            .await?;
145
146        match self.client.execute(&stmt, params).await {
147            Ok(count) => Ok(count),
148            Err(e) if is_stale_cached_plan(&e) => {
149                let stmt = self.reprepare_after_stale_plan(sql).await?;
150                let count = self.client.execute(&stmt, params).await?;
151                Ok(count)
152            }
153            Err(e) => Err(e.into()),
154        }
155    }
156
157    /// Recover from a stale cached plan (`0A000`): drop the SQL from the
158    /// statement cache and prepare it afresh, bypassing deadpool's per-
159    /// connection cache so the new plan is built against the current schema.
160    ///
161    /// `prepare_cached` would hand back the same invalidated statement, so the
162    /// retry must use the uncached `prepare`. The freshly prepared statement is
163    /// what the caller re-executes; the cache is left empty for this SQL so the
164    /// next ordinary call re-primes it via `get_or_prepare`.
165    async fn reprepare_after_stale_plan(&self, sql: &str) -> PgResult<tokio_postgres::Statement> {
166        debug!(
167            sql = %sql,
168            "Recovering from stale cached plan (0A000): re-preparing statement"
169        );
170        self.statement_cache.evict(sql);
171        let stmt = self.client.prepare(sql).await?;
172        Ok(stmt)
173    }
174
175    /// Execute a batch of statements in a single round-trip.
176    pub async fn batch_execute(&self, sql: &str) -> PgResult<()> {
177        trace!(sql = %sql, "Executing batch");
178        self.client.batch_execute(sql).await?;
179        Ok(())
180    }
181
182    /// Send a notification on `channel` (`SELECT pg_notify($1, $2)`).
183    ///
184    /// The channel name is matched verbatim (case preserved) by a
185    /// [`PgListener`](crate::listener::PgListener) subscribed to the same
186    /// string. The name must be non-empty, at most 63 bytes and free of NUL,
187    /// and the payload shorter than 8000 bytes; both are checked before
188    /// anything is sent.
189    pub async fn notify(&self, channel: &str, payload: &str) -> PgResult<()> {
190        validate_channel(channel)?;
191        validate_payload(payload)?;
192        self.execute("SELECT pg_notify($1, $2)", &[&channel, &payload])
193            .await?;
194        Ok(())
195    }
196
197    /// Begin a transaction.
198    pub async fn transaction(&mut self) -> PgResult<PgTransaction<'_>> {
199        debug!("Beginning transaction");
200        let txn = self.client.transaction().await?;
201        Ok(PgTransaction {
202            txn,
203            statement_cache: self.statement_cache.clone(),
204        })
205    }
206
207    /// Get the underlying tokio-postgres client.
208    ///
209    /// This is useful for advanced operations not covered by this wrapper.
210    pub fn inner(&self) -> &Object {
211        &self.client
212    }
213
214    /// Execute a query using the prepared statement cache.
215    ///
216    /// This is an alias for `query` that makes it explicit that statement caching
217    /// is being used. All query methods already use prepared statement caching,
218    /// but this method name makes it more explicit for benchmark comparisons.
219    #[inline]
220    pub async fn query_cached(
221        &self,
222        sql: &str,
223        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
224    ) -> PgResult<Vec<Row>> {
225        self.query(sql, params).await
226    }
227
228    /// Execute a raw query without using the prepared statement cache.
229    ///
230    /// This is useful for one-off queries where the overhead of preparing
231    /// a statement isn't worth it.
232    pub async fn query_raw(
233        &self,
234        sql: &str,
235        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
236    ) -> PgResult<Vec<Row>> {
237        trace!(sql = %sql, "Executing raw query (no statement cache)");
238        let rows = self.client.query(sql, params).await?;
239        Ok(rows)
240    }
241
242    /// Execute a raw query and return zero or one row without using statement cache.
243    pub async fn query_opt_raw(
244        &self,
245        sql: &str,
246        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
247    ) -> PgResult<Option<Row>> {
248        trace!(sql = %sql, "Executing raw query_opt (no statement cache)");
249        let row = self.client.query_opt(sql, params).await?;
250        Ok(row)
251    }
252}
253
254/// Maximum allowed savepoint name length (matches PostgreSQL's `NAMEDATALEN - 1`).
255const MAX_SAVEPOINT_NAME_LEN: usize = 63;
256
257/// Validate a savepoint name before it is interpolated into SQL.
258///
259/// Savepoint identifiers cannot be parameterized, so they must match the
260/// whitelist pattern `^[A-Za-z_][A-Za-z0-9_]*$` to prevent SQL injection.
261fn validate_savepoint_name(name: &str) -> PgResult<()> {
262    let valid = name.len() <= MAX_SAVEPOINT_NAME_LEN && is_valid_sql_identifier(name);
263    if !valid {
264        return Err(PgError::query(format!("invalid savepoint name: {name:?}")));
265    }
266    Ok(())
267}
268
269/// A PostgreSQL transaction.
270pub struct PgTransaction<'a> {
271    txn: deadpool_postgres::Transaction<'a>,
272    statement_cache: Arc<PreparedStatementCache>,
273}
274
275impl<'a> PgTransaction<'a> {
276    // A stale cached plan (`0A000`) is NOT transparently retried inside a
277    // transaction: the error aborts the transaction, so any subsequent
278    // statement on it fails with `25P02 in_failed_sql_transaction`. Re-running
279    // the one statement cannot succeed here. The error is still classified
280    // retryable (via `classify_sqlstate`), so a caller that retries the whole
281    // transaction recovers on a fresh statement. Only the non-transactional
282    // `PgConnection` methods above self-heal in place.
283
284    /// Execute a query and return all rows.
285    pub async fn query(
286        &self,
287        sql: &str,
288        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
289    ) -> PgResult<Vec<Row>> {
290        trace!(sql = %sql, "Executing query in transaction");
291
292        let stmt = self
293            .statement_cache
294            .get_or_prepare_in_txn(&self.txn, sql)
295            .await?;
296
297        let rows = self.txn.query(&stmt, params).await?;
298        Ok(rows)
299    }
300
301    /// Execute a query and return exactly one row.
302    pub async fn query_one(
303        &self,
304        sql: &str,
305        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
306    ) -> PgResult<Row> {
307        let stmt = self
308            .statement_cache
309            .get_or_prepare_in_txn(&self.txn, sql)
310            .await?;
311
312        let row = self.txn.query_one(&stmt, params).await?;
313        Ok(row)
314    }
315
316    /// Execute a query and return zero or one row.
317    pub async fn query_opt(
318        &self,
319        sql: &str,
320        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
321    ) -> PgResult<Option<Row>> {
322        let stmt = self
323            .statement_cache
324            .get_or_prepare_in_txn(&self.txn, sql)
325            .await?;
326
327        let row = self.txn.query_opt(&stmt, params).await?;
328        Ok(row)
329    }
330
331    /// Execute a statement and return the number of affected rows.
332    pub async fn execute(
333        &self,
334        sql: &str,
335        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
336    ) -> PgResult<u64> {
337        let stmt = self
338            .statement_cache
339            .get_or_prepare_in_txn(&self.txn, sql)
340            .await?;
341
342        let count = self.txn.execute(&stmt, params).await?;
343        Ok(count)
344    }
345
346    /// Send a notification on `channel` (`SELECT pg_notify($1, $2)`) as part
347    /// of this transaction: it is delivered when the transaction commits and
348    /// discarded if it rolls back.
349    ///
350    /// Validation is the same as [`PgConnection::notify`].
351    pub async fn notify(&self, channel: &str, payload: &str) -> PgResult<()> {
352        validate_channel(channel)?;
353        validate_payload(payload)?;
354        self.execute("SELECT pg_notify($1, $2)", &[&channel, &payload])
355            .await?;
356        Ok(())
357    }
358
359    /// Create a savepoint.
360    pub async fn savepoint(&mut self, name: &str) -> PgResult<()> {
361        validate_savepoint_name(name)?;
362        debug!(name = %name, "Creating savepoint");
363        self.txn
364            .batch_execute(&format!("SAVEPOINT {}", name))
365            .await?;
366        Ok(())
367    }
368
369    /// Rollback to a savepoint.
370    pub async fn rollback_to(&mut self, name: &str) -> PgResult<()> {
371        validate_savepoint_name(name)?;
372        debug!(name = %name, "Rolling back to savepoint");
373        self.txn
374            .batch_execute(&format!("ROLLBACK TO SAVEPOINT {}", name))
375            .await?;
376        Ok(())
377    }
378
379    /// Release a savepoint.
380    pub async fn release_savepoint(&mut self, name: &str) -> PgResult<()> {
381        validate_savepoint_name(name)?;
382        debug!(name = %name, "Releasing savepoint");
383        self.txn
384            .batch_execute(&format!("RELEASE SAVEPOINT {}", name))
385            .await?;
386        Ok(())
387    }
388
389    /// Commit the transaction.
390    pub async fn commit(self) -> PgResult<()> {
391        debug!("Committing transaction");
392        self.txn.commit().await?;
393        Ok(())
394    }
395
396    /// Rollback the transaction.
397    pub async fn rollback(self) -> PgResult<()> {
398        debug!("Rolling back transaction");
399        self.txn.rollback().await?;
400        Ok(())
401    }
402}
403
404#[cfg(test)]
405mod tests {
406    use super::*;
407
408    // Integration tests would require a real PostgreSQL connection
409    // Unit tests for connection wrapper are limited without mocking
410
411    #[test]
412    fn test_stale_cached_plan_message_gate() {
413        // The exact PostgreSQL wording is recognized.
414        assert!(is_stale_cached_plan_message(
415            "db error: ERROR: cached plan must not change result type"
416        ));
417        // Other 0A000 (FEATURE_NOT_SUPPORTED) messages are not the stale-plan
418        // case and must not trigger recovery.
419        assert!(!is_stale_cached_plan_message(
420            "ERROR: cannot insert into view \"v\""
421        ));
422        assert!(!is_stale_cached_plan_message("some unrelated error"));
423    }
424
425    #[test]
426    fn test_validate_savepoint_name_accepts_valid_names() {
427        assert!(validate_savepoint_name("sp1").is_ok());
428        assert!(validate_savepoint_name("my_savepoint").is_ok());
429        assert!(validate_savepoint_name("_private").is_ok());
430        assert!(validate_savepoint_name("SP_2").is_ok());
431        assert!(validate_savepoint_name("a").is_ok());
432        // 63 chars (the max) is accepted
433        let max_name = "a".repeat(MAX_SAVEPOINT_NAME_LEN);
434        assert!(validate_savepoint_name(&max_name).is_ok());
435    }
436
437    #[test]
438    fn test_validate_savepoint_name_rejects_invalid_names() {
439        assert!(validate_savepoint_name("sp1; DROP TABLE").is_err());
440        assert!(validate_savepoint_name("my savepoint").is_err());
441        assert!(validate_savepoint_name("\"quoted\"").is_err());
442        assert!(validate_savepoint_name("").is_err());
443        assert!(validate_savepoint_name("1leading_digit").is_err());
444        assert!(validate_savepoint_name("has-dash").is_err());
445        assert!(validate_savepoint_name("has.dot").is_err());
446        // 64 chars exceeds the limit
447        let too_long = "a".repeat(MAX_SAVEPOINT_NAME_LEN + 1);
448        assert!(validate_savepoint_name(&too_long).is_err());
449    }
450}