Skip to main content

fraiseql_db/postgres/adapter/
mod.rs

1//! PostgreSQL database adapter implementation.
2
3mod admin_sql;
4mod composed;
5mod database;
6mod numeric;
7mod query_stats;
8mod relay;
9mod streaming;
10#[cfg(feature = "test-postgres")]
11pub mod test_hooks;
12pub mod transaction_retry;
13
14#[cfg(test)]
15mod composed_tests;
16#[cfg(test)]
17mod tests;
18
19use std::{
20    fmt::Write,
21    sync::{
22        Arc, Weak,
23        atomic::{AtomicBool, AtomicU64, AtomicUsize, Ordering},
24    },
25    time::Duration,
26};
27
28use deadpool_postgres::{Config, ManagerConfig, Pool, RecyclingMethod, Runtime};
29use fraiseql_error::{FraiseQLError, Result};
30use tokio::sync::Semaphore;
31use tokio_postgres::{NoTls, Row};
32use transaction_retry::TransactionRetry;
33
34use super::{
35    server_version::require_supported_server,
36    tls::{PostgresConnector, PostgresSslMode, PostgresTlsConfig},
37    where_generator::PostgresWhereGenerator,
38};
39use crate::{
40    dialect::PostgresDialect,
41    identifier::quote_postgres_identifier,
42    order_by::{Tiebreak, append_order_by, render_order_by_columns},
43    postgres::pg_detail,
44    traits::{
45        DatabaseAdapter, DecimalShape, RawRows, WriteColumn, quote_cascade_decimals,
46        quote_composed_decimals, quote_decimals,
47    },
48    types::{
49        DatabaseType, JsonbValue, QueryParam, ReadRouting,
50        sql_hints::{OrderByClause, SqlProjectionHint},
51    },
52    where_clause::WhereClause,
53};
54
55/// How many concurrent streaming reads this pool allows (#958).
56///
57/// The derived default is a quarter of the pool, never zero and never the whole
58/// pool: a streaming read holds its connection until the client finishes reading,
59/// so a bound at `max_size` is no bound at all — exports would evict every
60/// ordinary request from the pool. An explicit `Some(n)` is the operator's
61/// decision and is honoured as given, except that `Some(0)` would disable
62/// streaming reads *silently* (they would wait forever on a permit that can
63/// never be issued), so it is raised to 1.
64const fn streaming_read_slots(configured: Option<usize>, max_size: usize) -> usize {
65    let requested = match configured {
66        Some(n) => n,
67        None => max_size / 4,
68    };
69    if requested == 0 { 1 } else { requested }
70}
71
72/// Extract the JSONB `data` cell from a result row, failing loud rather than
73/// panicking.
74///
75/// `Row::get` panics on SQL NULL or a non-JSONB column type, so a backing view
76/// that projects NULL `data` (e.g. via a LEFT JOIN) or a mistyped `data` column
77/// turned a query into a request-path panic — the PostgreSQL adapter was the
78/// only backend that aborted here instead of returning an error (audit H34).
79/// Both the NULL and the type-mismatch cases now map to
80/// [`FraiseQLError::Database`], including a bounded, char-safe slice of the
81/// query so an operator can identify the offending view.
82/// The most statements one pooled connection keeps prepared for reads (#1557).
83///
84/// A read's SQL text follows the client's document: the fields it selects, the shape of its
85/// `where`, the length of an `in` list. The set of distinct texts is therefore the client's to
86/// grow, and each one held is memory in this process and in the database backend. Past the cap
87/// the connection's cache is emptied, which closes its statements on the server, and refills
88/// with what is in use: a client cycling shapes costs what an uncached read costs, never memory.
89pub const READ_STATEMENT_CACHE_CAP: usize = 256;
90
91/// Run a read on `client`'s connection as a statement prepared once per connection and reused,
92/// so a repeated read costs one round trip instead of two (Parse/Describe, then Bind/Execute)
93/// (#1557).
94async fn query_read(
95    client: &deadpool_postgres::Client,
96    sql: &str,
97    params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
98) -> std::result::Result<Vec<Row>, tokio_postgres::Error> {
99    hold_under_cap(&client.statement_cache);
100    let statement = client.prepare_cached(sql).await?;
101    client.query(&statement, params).await
102}
103
104/// [`query_read`], after `session_vars` are applied in a transaction on the same connection,
105/// committed when the read succeeds.
106///
107/// The outer error is the transaction's or a variable's; the inner one is the read's, kept
108/// apart so the caller can tell a [stale statement](is_stale_cached_statement) from the rest.
109async fn query_read_in_session(
110    client: &mut deadpool_postgres::Client,
111    sql: &str,
112    params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
113    session_vars: &[(&str, &str)],
114) -> Result<std::result::Result<Vec<Row>, tokio_postgres::Error>> {
115    let txn = client.build_transaction().start().await.map_err(|e| {
116        crate::postgres::database_error(
117            format!("Failed to start session-var transaction: {}", pg_detail(&e)),
118            &e,
119        )
120    })?;
121
122    database::apply_session_vars(&txn, session_vars).await?;
123
124    hold_under_cap(&txn.statement_cache);
125    let rows = match txn.prepare_cached(sql).await {
126        Ok(statement) => txn.query(&statement, params).await,
127        Err(e) => Err(e),
128    };
129    let rows = match rows {
130        Ok(rows) => rows,
131        // Dropping the transaction rolls it back.
132        Err(e) => return Ok(Err(e)),
133    };
134
135    txn.commit().await.map_err(|e| {
136        crate::postgres::database_error(
137            format!("Failed to commit session-var transaction: {}", pg_detail(&e)),
138            &e,
139        )
140    })?;
141    Ok(Ok(rows))
142}
143
144/// Whether a read failed because the statement its connection cached returns another row type
145/// since a DDL change (`cached plan must not change result type`, SQLSTATE 0A000). The caller
146/// drops that statement and runs the read once more, prepared afresh. Keyed on the SQLSTATE,
147/// not the message, which the server may translate.
148fn is_stale_cached_statement(e: &tokio_postgres::Error) -> bool {
149    e.code() == Some(&tokio_postgres::error::SqlState::FEATURE_NOT_SUPPORTED)
150}
151
152/// Empty a connection's statement cache once it holds [`READ_STATEMENT_CACHE_CAP`] statements,
153/// before it would grow past it.
154fn hold_under_cap(cache: &deadpool_postgres::StatementCache) {
155    if cache.size() >= READ_STATEMENT_CACHE_CAP {
156        cache.clear();
157    }
158}
159
160/// The error a failed read is reported as.
161fn read_failed(e: &tokio_postgres::Error) -> FraiseQLError {
162    crate::postgres::database_error(format!("Query execution failed: {}", pg_detail(e)), e)
163}
164
165fn jsonb_cell<I>(row: &Row, column: I, sql: &str) -> Result<JsonbValue>
166where
167    I: tokio_postgres::row::RowIndex + std::fmt::Display,
168{
169    match row.try_get::<_, Option<serde_json::Value>>(column) {
170        Ok(Some(value)) => Ok(JsonbValue::new(value)),
171        Ok(None) => Err(null_data_column(sql)),
172        Err(e) => Err(unreadable_data_column(&e, sql)),
173    }
174}
175
176/// Where a read's documents hold a `Decimal`, and how its rows carry them.
177#[derive(Clone, Copy)]
178pub(super) enum Decimals<'a> {
179    /// Each row is one document of the shape's root.
180    Document(&'a DecimalShape),
181    /// Each row is a composed row: its document and its embedded levels.
182    Composed(&'a DecimalShape),
183    /// A mutation's cascade: its entries' entities, each of the type its entry names.
184    Cascade(&'a DecimalShape),
185}
186
187impl<'a> Decimals<'a> {
188    /// Rows that are documents of `shape`, or `None` for no shape.
189    pub(super) fn documents(shape: Option<&'a DecimalShape>) -> Option<Self> {
190        shape.map(Self::Document)
191    }
192
193    /// How a mutation's response column is read.
194    pub(super) const fn column(column: &'a WriteColumn) -> Self {
195        match column {
196            WriteColumn::Document(shape) => Self::Document(shape),
197            WriteColumn::Cascade(shape) => Self::Cascade(shape),
198        }
199    }
200
201    /// `text`, a JSON document, parsed with each number at one of these `Decimal`
202    /// positions kept as a string of its digits.
203    pub(super) fn parse(self, text: &[u8]) -> serde_json::Result<serde_json::Value> {
204        let mut quoted = Vec::new();
205        match self {
206            Self::Document(shape) => quote_decimals(text, shape, &mut quoted),
207            Self::Composed(shape) => quote_composed_decimals(text, shape, &mut quoted),
208            Self::Cascade(shape) => quote_cascade_decimals(text, shape, &mut quoted),
209        }
210        serde_json::from_slice(&quoted)
211    }
212}
213
214/// [`jsonb_cell`], each number at a `Decimal` position of `decimals` kept as a string of
215/// the digits the server sent, which a decoded number would round. `None` is [`jsonb_cell`].
216fn document_cell<I>(
217    row: &Row,
218    column: I,
219    sql: &str,
220    decimals: Option<Decimals<'_>>,
221) -> Result<JsonbValue>
222where
223    I: tokio_postgres::row::RowIndex + std::fmt::Display,
224{
225    let Some(decimals) = decimals else {
226        return jsonb_cell(row, column, sql);
227    };
228    match row.try_get::<_, Option<JsonText<'_>>>(column) {
229        Ok(Some(JsonText(text))) => {
230            decimals.parse(text).map(JsonbValue::new).map_err(|e| FraiseQLError::Database {
231                message:    format!(
232                    "Failed to parse the `data` column as JSON ({e}). Query: {}",
233                    query_preview(sql)
234                ),
235                sql_state:  None,
236                constraint: None,
237            })
238        },
239        Ok(None) => Err(null_data_column(sql)),
240        Err(e) => Err(unreadable_data_column(&e, sql)),
241    }
242}
243
244/// [`jsonb_cell`]'s cell, appended to `rows` as the text the server sent, never parsed.
245fn raw_json_cell(row: &Row, sql: &str, rows: &mut RawRows) -> Result<()> {
246    match row.try_get::<_, Option<JsonText<'_>>>(0) {
247        Ok(Some(JsonText(text))) => {
248            rows.push(text);
249            Ok(())
250        },
251        Ok(None) => Err(null_data_column(sql)),
252        Err(e) => Err(unreadable_data_column(&e, sql)),
253    }
254}
255
256/// The first 200 characters of a statement, for an error that quotes it.
257fn query_preview(sql: &str) -> String {
258    // `.chars().take(..)` is inherently char-boundary-safe (no byte slicing).
259    sql.chars().take(200).collect()
260}
261
262fn null_data_column(sql: &str) -> FraiseQLError {
263    FraiseQLError::Database {
264        message:    format!(
265            "Query returned a NULL `data` column; the backing view must project a non-NULL \
266             JSONB `data` value (a view yielding NULL `data`, e.g. via a LEFT JOIN, is \
267             unsupported). Query: {}",
268            query_preview(sql)
269        ),
270        sql_state:  None,
271        constraint: None,
272    }
273}
274
275fn unreadable_data_column(e: &tokio_postgres::Error, sql: &str) -> FraiseQLError {
276    FraiseQLError::Database {
277        message:    format!(
278            "Failed to read the `data` column as JSON ({e}); the backing view must project a \
279             JSONB `data` column (a composed read returns `json`). Query: {}",
280            query_preview(sql)
281        ),
282        sql_state:  None,
283        constraint: None,
284    }
285}
286
287/// A `json` or `jsonb` cell's text, borrowed from the row.
288///
289/// Results arrive in binary format: a `json` value's is its text, a `jsonb` value's is a
290/// version byte (`1`) and then its text.
291pub(super) struct JsonText<'a>(pub(super) &'a [u8]);
292
293impl<'a> tokio_postgres::types::FromSql<'a> for JsonText<'a> {
294    fn from_sql(
295        ty: &tokio_postgres::types::Type,
296        raw: &'a [u8],
297    ) -> std::result::Result<Self, Box<dyn std::error::Error + Sync + Send>> {
298        if *ty != tokio_postgres::types::Type::JSONB {
299            return Ok(Self(raw));
300        }
301        match raw.split_first() {
302            Some((1, text)) => Ok(Self(text)),
303            _ => Err("unsupported jsonb binary version".into()),
304        }
305    }
306
307    fn accepts(ty: &tokio_postgres::types::Type) -> bool {
308        matches!(*ty, tokio_postgres::types::Type::JSON | tokio_postgres::types::Type::JSONB)
309    }
310}
311
312/// Default maximum pool size for PostgreSQL connections.
313/// Increased from 10 to 25 to prevent pool exhaustion under concurrent
314/// nested query load (fixes Issue #41).
315const DEFAULT_POOL_SIZE: usize = 25;
316
317/// Maximum retries for connection acquisition with exponential backoff.
318const MAX_CONNECTION_RETRIES: u32 = 3;
319
320/// Base delay in milliseconds for connection retry backoff.
321const CONNECTION_RETRY_DELAY_MS: u64 = 50;
322
323/// Configuration for connection pool construction and pre-warming.
324///
325/// Controls the minimum guaranteed connections (pre-warmed at startup),
326/// the maximum pool ceiling, and the wait/create timeout for connection
327/// acquisition.
328///
329/// # Example
330///
331/// ```rust
332/// use fraiseql_db::postgres::{PoolPrewarmConfig, PostgresTlsConfig, VectorScanConfig};
333///
334/// let cfg = PoolPrewarmConfig {
335///     min_size:      5,
336///     max_size:      20,
337///     timeout_secs:  Some(30),
338///     search_path:   None,
339///     // Mandatory: every pool site must state its transport security (#801/#824).
340///     tls:           PostgresTlsConfig::default(),
341///     // Mandatory: every pool site must state its replica topology (#407).
342///     read_replicas: None,
343///     // `None` derives a quarter of `max_size` (#958).
344///     max_streaming_reads: None,
345///     // Continue a filtered ANN scan until `k` rows survive (#1116).
346///     vector_scan: VectorScanConfig::default(),
347/// };
348/// ```
349#[derive(Debug, Clone)]
350pub struct PoolPrewarmConfig {
351    /// Number of connections to establish at pool creation time.
352    ///
353    /// After the pool is created, `min_size` connections are opened eagerly
354    /// so they are ready when the first request arrives. Set to `0` to disable
355    /// pre-warming (lazy init — one connection from the startup health check).
356    pub min_size: usize,
357
358    /// Maximum number of connections the pool may hold.
359    pub max_size: usize,
360
361    /// Optional timeout (in seconds) for connection acquisition and creation.
362    ///
363    /// Applied to both the `wait` (blocked waiting for an idle connection) and
364    /// `create` (time to open a new TCP connection to PostgreSQL) deadpool slots.
365    /// When `None`, acquisition can block indefinitely on pool exhaustion.
366    pub timeout_secs: Option<u64>,
367
368    /// Schema search path every connection in this pool must resolve unqualified
369    /// relations against. `None` leaves the server default in force.
370    ///
371    /// This is a **pool** property, not a session one: it is lowered into the
372    /// PostgreSQL startup `options` parameter, so the server applies it while
373    /// establishing each connection — including connections deadpool opens later
374    /// to grow the pool, and replacements it creates after a backend dies. See
375    /// [`SearchPath`] for why the session-`SET` alternative is unsound.
376    pub search_path: Option<SearchPath>,
377
378    /// Transport security for every connection this pool opens.
379    ///
380    /// Mandatory, and deliberately not `Option`: the pool used to be built with
381    /// `NoTls` unconditionally while three separate configuration surfaces claimed
382    /// to control database TLS, so each caller must now state what it wants and a
383    /// new pool site cannot compile without deciding (#801, #824). Callers with no
384    /// opinion pass [`PostgresTlsConfig::default`] (libpq's `prefer`).
385    pub tls: PostgresTlsConfig,
386
387    /// How many of this pool's connections may be held by streaming reads at once
388    /// (#958). `None` derives a quarter of [`max_size`](Self::max_size), at least 1.
389    ///
390    /// A streamed read holds its connection for the whole life of the stream — for
391    /// an export, that is however long the client takes to consume a table, not
392    /// however long a query takes to run. Without a bound below `max_size`,
393    /// concurrent exports can occupy every connection in the pool and ordinary
394    /// requests then fail on the acquisition timeout: a feature for large reads
395    /// would take the server down by being used.
396    ///
397    /// The bound is *not* a queue-length limit. A streaming read past the bound
398    /// waits for a slot, exactly as any read waits for a connection.
399    pub max_streaming_reads: Option<usize>,
400
401    /// pgvector scan behaviour for every connection this pool opens (#1116).
402    ///
403    /// Defaults, via [`VectorScanConfig`], to continuing an index scan until `k`
404    /// rows survive a filter. pgvector's own default stops at one bounded
405    /// candidate list, which makes a filtered similarity search return fewer
406    /// rows than asked for **and report success** — the reason this is not left
407    /// to the server's setting.
408    ///
409    /// Like [`search_path`](Self::search_path) it is lowered into the startup
410    /// packet, so it reaches every connection the pool opens later, and it is
411    /// inert on a database without pgvector.
412    pub vector_scan: VectorScanConfig,
413
414    /// Read replicas for this pool set, or `None` for a single-primary adapter.
415    ///
416    /// Like [`tls`](Self::tls), this is a mandatory decision at every pool site:
417    /// replica pools are built **from this same config** — the same
418    /// [`search_path`](Self::search_path), the same [`tls`](Self::tls), the same
419    /// sizing — so tenant isolation and transport security cannot silently differ
420    /// between the primary and a replica (#809 generalised: isolation is a
421    /// property of how *every* pool's connections are made). See
422    /// [`ReadReplicaConfig`] for the routing and consistency rules (#407).
423    pub read_replicas: Option<ReadReplicaConfig>,
424}
425
426/// Read-replica configuration for a PostgreSQL adapter (#407).
427///
428/// # Routing model
429///
430/// FraiseQL's read/write partition is **static**: compiled GraphQL queries execute
431/// through the adapter's structurally read-only methods (`execute_where_query*`,
432/// `execute_with_projection*`, `execute_parameterized_aggregate*`, `explain_*`,
433/// relay pagination), and those route to a replica selected round-robin. The
434/// mutation pipeline (`execute_write`) and every mixed-use or
435/// administrative surface (`execute_raw_query`, `execute_row_query`, query stats,
436/// health checks, schema DDL) always run on the primary — a surface that *can*
437/// write is never routed to a replica.
438///
439/// # Read-your-writes
440///
441/// Every mutation-pipeline write arms a shared watermark; for
442/// [`pin_after_write`](Self::pin_after_write) afterwards, reads route to the
443/// primary so replication lag cannot serve a client its own stale write. The
444/// window is an operator assertion about worst-case replica lag: writes that
445/// bypass the mutation pipeline (raw SQL, out-of-band jobs) do not arm it, and a
446/// replica lagging beyond the window can serve stale — but never torn — rows.
447///
448/// # Failure behaviour
449///
450/// A replica that is unreachable at boot **refuses to boot** (a configured-but-
451/// unusable pool must not downgrade silently). A replica that fails at runtime is
452/// skipped for that acquisition — the next replica is tried, then the primary —
453/// so replica loss degrades read capacity, never read availability.
454/// # Bounded staleness
455///
456/// [`max_lag`](Self::max_lag) turns the pin window's *assertion* into a
457/// *measurement*: a replica is only eligible while its last probed replay lag,
458/// aged by how long ago that probe ran, is still inside the budget.
459///
460/// That sum is a true upper bound rather than an estimate — replay lag grows at
461/// most one millisecond per millisecond of wall clock, so however stale the probe
462/// is, the lag cannot have grown by more than its age — and it makes the gate
463/// self-closing: an unrefreshed probe ages past any budget on its own. A replica
464/// whose lag cannot be *measured* at all is never eligible; unknown staleness is
465/// not zero staleness.
466#[derive(Debug, Clone)]
467pub struct ReadReplicaConfig {
468    /// Connection URLs of the read replicas, tried round-robin. Must be non-empty.
469    pub urls: Vec<String>,
470
471    /// How long after a mutation-pipeline write reads keep routing to the primary.
472    ///
473    /// Must be at least the worst replication lag the operator is prepared to
474    /// tolerate; within it, a client is guaranteed to read its own writes.
475    pub pin_after_write: Duration,
476
477    /// Bounded staleness: the most replication lag a replica-served read may
478    /// carry. `None` disables lag-based routing entirely — replicas are then
479    /// selected round-robin regardless of how far behind they are, which is the
480    /// behaviour before this option existed.
481    ///
482    /// A replica whose lag cannot be measured — never probed, last probe failed,
483    /// no transaction replayed yet, or promoted out of recovery by a failover —
484    /// is **not** eligible while this is set. Unknown staleness is not "zero
485    /// staleness", and the primary is always a correct answer.
486    ///
487    /// Must be strictly greater than [`health_probe_interval`](Self::health_probe_interval):
488    /// eligibility ages the last probe, so a budget no larger than the probe
489    /// period would make even a perfectly caught-up replica ineligible for part
490    /// of every cycle.
491    pub max_lag: Option<Duration>,
492
493    /// How often each replica is probed for recovery state and replay lag.
494    ///
495    /// Probing is unconditional when replicas are configured: the boot health
496    /// check runs once, and a replica promoted by a failover after boot is a
497    /// writable server that a `pg_is_in_recovery()` reading taken at startup can
498    /// never notice.
499    pub health_probe_interval: Duration,
500}
501
502/// [`ReadReplicaConfig`] without the URLs: how replica routing *behaves*,
503/// separated from *where* the replicas are (#957).
504///
505/// The split exists because the two halves have different owners once tenant
506/// pools carry replicas. A tenant registration names its own replica URLs, the
507/// same way it names its own primary connection string — but the pin window, the
508/// staleness budget and the probe cadence are the operator's policy, stamped
509/// onto every tenant pool by `make_executor_factory`, exactly as `[database_tls]`
510/// is (#801). A tenant that could send its own `max_lag` in a registration body
511/// would be choosing how stale *its* reads may be, against a server whose
512/// operator already decided.
513///
514/// [`Default`] carries the shipped defaults — a 5 s pin, no staleness budget, a
515/// 1 s probe cadence — so they live in one place rather than once per binary.
516#[derive(Debug, Clone)]
517pub struct ReadReplicaPolicy {
518    /// See [`ReadReplicaConfig::pin_after_write`].
519    pub pin_after_write:       Duration,
520    /// See [`ReadReplicaConfig::max_lag`].
521    pub max_lag:               Option<Duration>,
522    /// See [`ReadReplicaConfig::health_probe_interval`].
523    pub health_probe_interval: Duration,
524}
525
526impl Default for ReadReplicaPolicy {
527    fn default() -> Self {
528        Self {
529            pin_after_write:       Duration::from_secs(5),
530            max_lag:               None,
531            health_probe_interval: Duration::from_secs(1),
532        }
533    }
534}
535
536impl ReadReplicaPolicy {
537    /// Apply this policy to `urls`, or `None` when there are no replicas.
538    ///
539    /// Returning `None` for an empty list is what keeps a pool site that has no
540    /// replicas from constructing the one shape
541    /// [`ReadReplicaConfig`](ReadReplicaConfig) refuses to build.
542    #[must_use]
543    pub fn with_urls(&self, urls: Vec<String>) -> Option<ReadReplicaConfig> {
544        if urls.is_empty() {
545            return None;
546        }
547        Some(ReadReplicaConfig {
548            urls,
549            pin_after_write: self.pin_after_write,
550            max_lag: self.max_lag,
551            health_probe_interval: self.health_probe_interval,
552        })
553    }
554}
555
556/// A validated PostgreSQL `search_path`, applied at connection establishment.
557///
558/// # Why this is a type and not a `SET` statement
559///
560/// `SET search_path` is *session*-scoped, and a connection pool is N independent
561/// sessions. Issuing the `SET` through a pooled query configures whichever single
562/// connection was checked out and leaves every other connection — including ones
563/// the pool opens later, and every replacement created after a backend
564/// disconnects — on the server default. Schema-per-tenant isolation built that way
565/// resolves most queries against `public`: silently wrong rows where `public`
566/// shadows the relation, an intermittent `relation ... does not exist` where it does
567/// not (#809).
568///
569/// Lowering the path into the startup `options` parameter instead makes it a
570/// property of *how connections are made*, so it cannot be missed by a connection
571/// and cannot be lost to recycling — `RESET`/`DISCARD ALL` restore the startup
572/// value, not the server default.
573///
574/// # Example
575///
576/// ```rust
577/// use fraiseql_db::postgres::SearchPath;
578///
579/// let path = SearchPath::new(["tenant_acme", "public"])?;
580/// assert_eq!(path.as_str(), "tenant_acme,public");
581///
582/// // Anything that is not a bare identifier is refused rather than escaped.
583/// assert!(SearchPath::new(["public; DROP SCHEMA x"]).is_err());
584/// # Ok::<(), fraiseql_error::FraiseQLError>(())
585/// ```
586#[derive(Debug, Clone, PartialEq, Eq)]
587pub struct SearchPath(String);
588
589impl SearchPath {
590    /// Build a search path from schema names, in resolution order.
591    ///
592    /// # Errors
593    ///
594    /// Returns [`FraiseQLError::Validation`] if the list is empty or any entry is
595    /// not a bare, unquoted PostgreSQL identifier (`[A-Za-z_][A-Za-z0-9_]*`, at most
596    /// 63 characters). Rejecting rather than quoting keeps the emitted startup
597    /// option free of characters that could terminate it.
598    pub fn new<I, S>(schemas: I) -> Result<Self>
599    where
600        I: IntoIterator<Item = S>,
601        S: AsRef<str>,
602    {
603        let mut parts: Vec<String> = Vec::new();
604        for schema in schemas {
605            let name = schema.as_ref();
606            let mut chars = name.chars();
607            let valid = match chars.next() {
608                Some(c) if c.is_ascii_alphabetic() || c == '_' => {
609                    chars.all(|c| c.is_ascii_alphanumeric() || c == '_')
610                },
611                _ => false,
612            };
613            if !valid || name.len() > MAX_PG_IDENTIFIER_LEN {
614                return Err(FraiseQLError::validation(format!(
615                    "Schema name '{name}' is not a bare PostgreSQL identifier and cannot be \
616                     used in a search path"
617                )));
618            }
619            parts.push(name.to_string());
620        }
621        if parts.is_empty() {
622            return Err(FraiseQLError::validation("A search path needs at least one schema"));
623        }
624        Ok(Self(parts.join(",")))
625    }
626
627    /// The comma-separated path, as it is written into `-c search_path=…`.
628    #[must_use]
629    pub fn as_str(&self) -> &str {
630        &self.0
631    }
632}
633
634impl std::fmt::Display for SearchPath {
635    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
636        f.write_str(&self.0)
637    }
638}
639
640/// Maximum length of a PostgreSQL identifier.
641const MAX_PG_IDENTIFIER_LEN: usize = 63;
642
643/// How pgvector's HNSW index behaves when a filter eliminates the candidates it
644/// handed up (#1116).
645///
646/// With pgvector's own default — `off` — an HNSW scan produces one bounded
647/// candidate list and the filter is applied to it. Once the filter is selective
648/// the list is exhausted before `k` survivors exist, and the query **succeeds**
649/// with fewer rows: measured at 100 000 documents, `k = 10` and a 1%-selective
650/// filter, three rows and a recall of 0.20, with nothing to distinguish that
651/// from "only three matched". That is why FraiseQL names a value here instead
652/// of inheriting the server's.
653///
654/// ⚠ An invalid value is **not** an error. pgvector's GUC is defined when its
655/// library loads, after the startup packet has been accepted, so a bad value
656/// produces `WARNING: invalid value for parameter "hnsw.iterative_scan"` and
657/// silently falls back to `off` — the failure this setting exists to prevent.
658/// Being an enum is what keeps an unrepresentable value from reaching the wire.
659#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, serde::Serialize, serde::Deserialize)]
660#[serde(rename_all = "snake_case")]
661pub enum HnswIterativeScan {
662    /// pgvector's own default: one bounded candidate list, no continuation.
663    /// Filtered searches may silently return fewer than `k` rows.
664    Off,
665    /// Continue scanning until `k` rows survive the filter, without buffering
666    /// for exact ordering. Results may be slightly out of distance order.
667    RelaxedOrder,
668    /// Continue scanning until `k` rows survive, preserving exact distance
669    /// order.
670    ///
671    /// The default, and the reason is the contract rather than the benchmark:
672    /// `nearest` is documented to return rows most-similar-first, and a plan
673    /// whose ordering comes from the index scan can only honour that if the
674    /// scan is ordered. `relaxed_order` would trade #1116's silent
675    /// under-return for a quieter version of the same thing — a result that
676    /// looks right and is subtly not.
677    ///
678    /// The benchmark in `benches/vector_filtered_ann.sql` found the two
679    /// indistinguishable in both recall and latency, so the guarantee is the
680    /// only thing separating them there. An operator whose corpus disagrees can
681    /// say so.
682    #[default]
683    StrictOrder,
684}
685
686/// The same setting for pgvector's `IVFFlat` index (#1116).
687///
688/// A separate type from [`HnswIterativeScan`] because pgvector accepts a
689/// **different set of values** here — measured against pgvector 0.8.6:
690///
691/// ```text
692/// WARNING:  invalid value for parameter "ivfflat.iterative_scan": "strict_order"
693/// HINT:  Available values: off, relaxed_order.
694/// ```
695///
696/// and the setting then falls back to `off`. One shared enum with a clamp would
697/// turn the operator's choice into a value the server discards, which is the
698/// shape of the defect being fixed. Here it is simply not expressible.
699#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, serde::Serialize, serde::Deserialize)]
700#[serde(rename_all = "snake_case")]
701pub enum IvfflatIterativeScan {
702    /// pgvector's own default; see [`HnswIterativeScan::Off`].
703    Off,
704    /// Continue scanning until `k` rows survive the filter. The only
705    /// continuation mode `IVFFlat` defines.
706    #[default]
707    RelaxedOrder,
708}
709
710/// pgvector scan settings applied to every connection a pool opens (#1116).
711///
712/// Lowered into the PostgreSQL startup `options` packet for the same reason
713/// [`SearchPath`] is: a pooled `SET` configures one session out of N, and misses
714/// every connection the pool opens later. Riding the startup packet costs no
715/// round trip and survives `DISCARD ALL`.
716///
717/// ⚠ Safe on a database **without** pgvector. A two-part GUC name is accepted as
718/// a placeholder when no extension has claimed it, so the connection succeeds
719/// and the value simply never takes effect — verified against a database with
720/// the extension absent. The same mechanism covers a pgvector older than 0.8,
721/// which defines neither GUC.
722#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, serde::Serialize, serde::Deserialize)]
723pub struct VectorScanConfig {
724    /// `hnsw.iterative_scan`.
725    pub hnsw:      HnswIterativeScan,
726    /// `ivfflat.iterative_scan`.
727    pub ivfflat:   IvfflatIterativeScan,
728    /// `hnsw.ef_search`, or `None` to leave pgvector's default of 40.
729    ///
730    /// ⚠ **`iterative_scan` alone does not guarantee `k` rows, and on some
731    /// corpora it is not even the binding constraint.** Measured on this
732    /// repository's rig — 20 000 rows × 64 dimensions of uniform-random vectors,
733    /// a filter matching 1%, `k = 10`, three independently generated datasets:
734    ///
735    /// | configuration | rows returned |
736    /// |---|---|
737    /// | `iterative_scan=off` (pgvector's default) | 2, 3, 4 |
738    /// | `iterative_scan=strict_order`, other GUCs default | 10, 5, 4 |
739    /// | …plus `max_scan_tuples` raised 100× | 10, 5, 4 — **no change** |
740    /// | …plus `ef_search=1000` instead | **10, 10, 10** |
741    /// | `iterative_scan=off` **and** `ef_search=1000` | **10, 10, 10** |
742    ///
743    /// So `ef_search` — the size of the candidate list the graph search keeps —
744    /// is what decides whether ten members of a 1% subset are found at all, and
745    /// `max_scan_tuples` (which #1314 was filed against) changed nothing.
746    /// Uniform-random high-dimensional vectors are the adversarial case, and the
747    /// benchmark in `benches/vector_filtered_ann.sql` reports recall 1.00 from
748    /// `iterative_scan` alone on its own corpus — which is the point: **which
749    /// knob binds depends on the data**, so both have to be reachable.
750    ///
751    /// Left unset by default deliberately. `iterative_scan` costs nothing on an
752    /// unfiltered search (measured: recall already 1.000, timings tie), so
753    /// naming it is free. `ef_search` is a latency/recall trade paid by **every**
754    /// search on the deployment, filtered or not, and its right value is a
755    /// property of the corpus. FraiseQL will not choose it for an operator; it
756    /// makes it reachable and says, above, how to tell that it is the one to
757    /// reach for.
758    pub ef_search: Option<u32>,
759}
760
761impl HnswIterativeScan {
762    /// The value as pgvector spells it.
763    #[must_use]
764    pub const fn as_guc_value(self) -> &'static str {
765        match self {
766            Self::Off => "off",
767            Self::RelaxedOrder => "relaxed_order",
768            Self::StrictOrder => "strict_order",
769        }
770    }
771}
772
773impl IvfflatIterativeScan {
774    /// The value as pgvector spells it.
775    #[must_use]
776    pub const fn as_guc_value(self) -> &'static str {
777        match self {
778            Self::Off => "off",
779            Self::RelaxedOrder => "relaxed_order",
780        }
781    }
782}
783
784impl VectorScanConfig {
785    /// The `-c` fragments these settings contribute to the startup packet.
786    ///
787    /// Empty when both are [`Off`](HnswIterativeScan::Off) — an operator who
788    /// turned the feature off gets pgvector's own default rather than an
789    /// explicit `off`, so the server's or the database's own `ALTER DATABASE`
790    /// setting still applies. Writing `-c hnsw.iterative_scan=off` would
791    /// *override* such a setting, which is not what "leave it alone" means.
792    #[must_use]
793    pub fn startup_fragments(self) -> Vec<String> {
794        let mut out = Vec::new();
795        if self.hnsw != HnswIterativeScan::Off {
796            out.push(format!("-c hnsw.iterative_scan={}", self.hnsw.as_guc_value()));
797        }
798        if self.ivfflat != IvfflatIterativeScan::Off {
799            out.push(format!("-c ivfflat.iterative_scan={}", self.ivfflat.as_guc_value()));
800        }
801        if let Some(ef) = self.ef_search {
802            out.push(format!("-c hnsw.ef_search={ef}"));
803        }
804        out
805    }
806}
807
808/// Compose the PostgreSQL startup `options` string for a pool.
809///
810/// Appends this pool's `-c` settings to whatever `options` the operator already put
811/// in the connection string, rather than replacing them: deadpool's struct-level
812/// `options` field overrides the URL-parsed value outright, so building this by
813/// assignment alone would silently discard an operator's own `-c` settings.
814///
815/// Returns `None` when the pool contributes nothing, so the caller can leave
816/// `cfg.options` unset and let deadpool use the URL's own value — assigning the
817/// URL's options back to the struct field would be a no-op today and a trap the
818/// first time the two are read differently.
819fn compose_startup_options(
820    connection_string: &str,
821    search_path: Option<&SearchPath>,
822    vector_scan: VectorScanConfig,
823) -> Option<String> {
824    let mut fragments: Vec<String> = Vec::new();
825    if let Some(path) = search_path {
826        fragments.push(format!("-c search_path={path}"));
827    }
828    // Both settings ride the same packet as the search path. Vector scan
829    // behaviour is a property of connection establishment for exactly the reason
830    // schema isolation is (#1116, #809): a pooled `SET` reaches one session of N.
831    fragments.extend(vector_scan.startup_fragments());
832    if fragments.is_empty() {
833        return None;
834    }
835
836    let existing = connection_string
837        .parse::<tokio_postgres::Config>()
838        .ok()
839        .and_then(|c| c.get_options().map(str::to_owned))
840        .unwrap_or_default();
841    let existing = existing.trim();
842    if existing.is_empty() {
843        Some(fragments.join(" "))
844    } else {
845        Some(format!("{existing} {}", fragments.join(" ")))
846    }
847}
848
849/// Build a `deadpool-postgres` pool with an optional wait/create timeout.
850///
851/// # Errors
852///
853/// Returns `FraiseQLError::ConnectionPool` if pool creation fails (e.g., unparseable URL).
854fn build_pool(
855    connection_string: &str,
856    max_size: usize,
857    timeout_secs: Option<u64>,
858    search_path: Option<&SearchPath>,
859    vector_scan: VectorScanConfig,
860    tls: &PostgresTlsConfig,
861) -> Result<Pool> {
862    let mut cfg = Config::new();
863    cfg.url = Some(connection_string.to_string());
864    cfg.manager = Some(ManagerConfig {
865        recycling_method: RecyclingMethod::Fast,
866    });
867
868    // Schema isolation is a property of connection *establishment*, so it rides the
869    // startup packet and applies to every connection this pool ever opens — see
870    // `SearchPath` for why a pooled `SET` is unsound (#809). `VectorScanConfig`
871    // rides the same packet for the same reason (#1116).
872    cfg.options = compose_startup_options(connection_string, search_path, vector_scan);
873
874    let mut pool_cfg = deadpool_postgres::PoolConfig::new(max_size);
875    if let Some(secs) = timeout_secs {
876        let t = Duration::from_secs(secs);
877        pool_cfg.timeouts.wait = Some(t);
878        pool_cfg.timeouts.create = Some(t);
879        // `recycle` intentionally stays None — fast recycle, not user-configurable.
880    }
881    cfg.pool = Some(pool_cfg);
882
883    // Transport security is a property of the connection, so it is decided here, at
884    // the one place connections are made, rather than by a log line elsewhere (#801).
885    //
886    // `ssl_mode` is applied by deadpool *after* it parses `cfg.url`, so an explicit
887    // setting overrides a `?sslmode=` in the URL while leaving the URL authoritative
888    // when the caller expressed no preference. Both routes now reach a connector that
889    // can actually negotiate TLS; previously the hard-coded `NoTls` made the driver's
890    // own sslmode handling unreachable no matter which surface set it.
891    // Only when the operator actually chose one: deadpool applies `ssl_mode` after
892    // parsing the URL, so setting it unconditionally would override an explicit
893    // `?sslmode=require` with this struct's default and silently downgrade it. The
894    // URL form is the one surface that already refused a plaintext server, so it
895    // must keep outranking a default nobody wrote.
896    cfg.ssl_mode = tls.mode.map(|mode| match mode {
897        PostgresSslMode::Disable => deadpool_postgres::SslMode::Disable,
898        PostgresSslMode::Prefer => deadpool_postgres::SslMode::Prefer,
899        PostgresSslMode::Require | PostgresSslMode::VerifyFull => {
900            deadpool_postgres::SslMode::Require
901        },
902    });
903
904    let pool = match tls.connector()? {
905        PostgresConnector::Plaintext => cfg.create_pool(Some(Runtime::Tokio1), NoTls),
906        PostgresConnector::Tls(connector) => cfg.create_pool(Some(Runtime::Tokio1), *connector),
907    };
908
909    pool.map_err(|e| FraiseQLError::ConnectionPool {
910        message: format!("Failed to create connection pool: {e}"),
911    })
912}
913
914/// Build and boot-verify the replica pool set (#407).
915///
916/// Each replica pool inherits the primary's `search_path`, TLS, sizing and
917/// timeouts from `cfg`. Every replica is health-checked at boot; an unreachable
918/// replica refuses to boot rather than silently shrinking the read fleet, and a
919/// reachable server that is not in recovery (not actually a standby) is allowed
920/// but loudly logged — dev rigs legitimately stand in a plain database for a
921/// replica, but in production that warning means reads may diverge from the
922/// primary in both directions.
923///
924/// # Errors
925///
926/// Returns [`FraiseQLError::Validation`] when `rc.urls` is empty, and
927/// [`FraiseQLError::ConnectionPool`] / [`FraiseQLError::Database`] when a replica
928/// pool cannot be created or its boot health check fails.
929async fn build_read_replica_set(
930    rc: &ReadReplicaConfig,
931    cfg: &PoolPrewarmConfig,
932) -> Result<ReadReplicaSet> {
933    if rc.urls.is_empty() {
934        return Err(FraiseQLError::validation(
935            "read_replicas was configured with an empty URL list; remove the configuration \
936             or list at least one replica",
937        ));
938    }
939    if rc.health_probe_interval.is_zero() {
940        return Err(FraiseQLError::validation(
941            "read replica health_probe_interval is zero; probing every replica in a tight \
942             loop is never intended. Set an interval, or remove the replica configuration.",
943        ));
944    }
945    if rc.max_lag.is_some_and(|max_lag| max_lag <= rc.health_probe_interval) {
946        return Err(FraiseQLError::validation(format!(
947            "read replica max_lag ({} ms) must be greater than health_probe_interval ({} ms): \
948             a replica's eligibility ages its last probe, so a staleness budget no larger than \
949             the probe period would drop even a fully caught-up replica out of rotation for \
950             part of every cycle. Lower the probe interval or raise the budget.",
951            rc.max_lag.unwrap_or_default().as_millis(),
952            rc.health_probe_interval.as_millis(),
953        )));
954    }
955
956    let mut pools = Vec::with_capacity(rc.urls.len());
957    let mut health = Vec::with_capacity(rc.urls.len());
958    for (index, url) in rc.urls.iter().enumerate() {
959        // The replica pools are built from the SAME config as the primary, so a
960        // schema-isolated tenant's search path, the server's TLS settings and its
961        // vector-scan settings all reach the replicas too (#809 generalised).
962        let pool = build_pool(
963            url,
964            cfg.max_size,
965            cfg.timeout_secs,
966            cfg.search_path.as_ref(),
967            cfg.vector_scan,
968            &cfg.tls,
969        )?;
970
971        // Boot health check: a configured replica that cannot serve refuses to
972        // boot — the alternative is a read fleet that silently collapsed onto
973        // the primary before the first request.
974        let client = pool.get().await.map_err(|e| FraiseQLError::ConnectionPool {
975            message: format!(
976                "Read replica {index} is unreachable at boot ({e}); refusing to start"
977            ),
978        })?;
979        let row = client.query_one("SELECT pg_is_in_recovery()", &[]).await.map_err(|e| {
980            crate::postgres::database_error(
981                format!("Read replica {index} failed its boot health check: {}", pg_detail(&e)),
982                &e,
983            )
984        })?;
985        let in_recovery: bool = row.get(0);
986        // A standby replays the primary's WAL, so it is the same major version in a
987        // physical setup, but a logical replica or a misconfigured URL need not be.
988        require_supported_server(&client).await.map_err(|e| match e {
989            FraiseQLError::Unsupported { message } => FraiseQLError::Unsupported {
990                message: format!("Read replica {index}: {message}"),
991            },
992            other => other,
993        })?;
994        if !in_recovery {
995            tracing::warn!(
996                replica = index,
997                "Configured read replica is not in recovery (pg_is_in_recovery() = false); it \
998                 is a writable server, not a standby. Reads routed to it may diverge from the \
999                 primary."
1000            );
1001        }
1002        drop(client);
1003
1004        pools.push(pool);
1005        health.push(ReplicaHealth::booted(in_recovery));
1006    }
1007
1008    // Reason (fallible u64::try_from + saturation): a pin window beyond u64::MAX
1009    // milliseconds (~585 million years) saturates to "pin forever", which is the
1010    // conservative, primary-only side.
1011    let pin_after_write_ms = u64::try_from(rc.pin_after_write.as_millis()).unwrap_or(u64::MAX);
1012    // Reason (saturation, opposite direction): a budget that overflows is
1013    // effectively unbounded, and an unbounded budget is what `None` already
1014    // means, so saturating to u64::MAX changes nothing an operator could observe.
1015    let max_lag_ms = rc.max_lag.map(|d| u64::try_from(d.as_millis()).unwrap_or(u64::MAX));
1016
1017    Ok(ReadReplicaSet {
1018        pools,
1019        health,
1020        next: AtomicUsize::new(0),
1021        pin_after_write_ms,
1022        max_lag_ms,
1023        probe_interval: rc.health_probe_interval,
1024        last_write_unix_ms: AtomicU64::new(0),
1025    })
1026}
1027
1028/// PostgreSQL database adapter with connection pooling.
1029///
1030/// Uses `deadpool-postgres` for connection pooling and `tokio-postgres` for async queries.
1031///
1032/// # Example
1033///
1034/// ```rust,no_run
1035/// use fraiseql_db::postgres::PostgresAdapter;
1036/// use fraiseql_db::{DatabaseAdapter, WhereClause, WhereOperator};
1037/// use serde_json::json;
1038///
1039/// # async fn example() -> Result<(), Box<dyn std::error::Error>> {
1040/// // Create adapter with connection string
1041/// let adapter = PostgresAdapter::new("postgresql://localhost/mydb").await?;
1042///
1043/// // Execute query
1044/// let where_clause = WhereClause::Field {
1045///     path: vec!["email".to_string()],
1046///     operator: WhereOperator::Icontains,
1047///     value: json!("example.com"),
1048/// };
1049///
1050/// let results = adapter
1051///     .execute_where_query("v_user", Some(&where_clause), Some(10), None, None)
1052///     .await?;
1053///
1054/// println!("Found {} users", results.len());
1055/// # Ok(())
1056/// # }
1057/// ```
1058#[derive(Clone)]
1059pub struct PostgresAdapter {
1060    pub(super) pool:         Pool,
1061    /// Read-replica pool set, or `None` for a single-primary adapter (#407).
1062    read_replicas:           Option<Arc<ReadReplicaSet>>,
1063    /// Whether mutation timing injection is enabled.
1064    mutation_timing_enabled: bool,
1065    /// The PostgreSQL session variable name for timing.
1066    timing_variable_name:    String,
1067    /// Slots for reads that hold a connection for the life of a stream (#958).
1068    ///
1069    /// Shared across clones, because they share the pool the slots protect.
1070    /// See [`PoolPrewarmConfig::max_streaming_reads`].
1071    streaming_permits:       Arc<Semaphore>,
1072    /// How often a mutation's transaction runs when PostgreSQL aborts it (#1879).
1073    transaction_retry:       TransactionRetry,
1074}
1075
1076/// The built replica pools plus the shared read-your-writes watermark.
1077///
1078/// Held behind one `Arc` so adapter clones share a single round-robin cursor and
1079/// a single write watermark — a clone that kept its own watermark would let a
1080/// write through one clone go unseen by reads through another.
1081struct ReadReplicaSet {
1082    pools:              Vec<Pool>,
1083    /// Per-replica probe results, parallel to [`pools`](Self::pools).
1084    health:             Vec<ReplicaHealth>,
1085    /// Round-robin cursor over `pools`.
1086    next:               AtomicUsize,
1087    /// [`ReadReplicaConfig::pin_after_write`], in milliseconds.
1088    pin_after_write_ms: u64,
1089    /// [`ReadReplicaConfig::max_lag`], in milliseconds; `None` = no lag gating.
1090    max_lag_ms:         Option<u64>,
1091    /// [`ReadReplicaConfig::health_probe_interval`].
1092    probe_interval:     Duration,
1093    /// Unix-epoch milliseconds of the most recent mutation-pipeline write;
1094    /// `0` = never written.
1095    last_write_unix_ms: AtomicU64,
1096}
1097
1098/// What the last health probe observed about one replica (#957).
1099///
1100/// # Why a probe and not a measurement on the read path
1101///
1102/// Measuring lag inline would add a round trip to every replica-served read —
1103/// the cost of the routing would exceed the cost of the read. The probe runs on
1104/// its own cadence and the read path consults its last result, which means the
1105/// result is *old*, and eligibility has to account for that.
1106///
1107/// # The staleness bound
1108///
1109/// A replica is eligible while `lag_at_probe + probe_age <= max_lag`.
1110///
1111/// That sum is a true upper bound on the replica's staleness right now, not an
1112/// approximation: replay lag measured as "how long ago was the newest applied
1113/// transaction committed" grows at most one millisecond per millisecond of wall
1114/// clock (it grows exactly 1:1 while replay is stalled and shrinks whenever
1115/// replay advances), so however far the probe has aged, the lag cannot have
1116/// grown by more than that age.
1117///
1118/// It also makes the gate self-closing. Nothing has to notice that probing
1119/// stopped — a probe that stops being refreshed ages past any budget on its own,
1120/// and the replica falls out of rotation.
1121struct ReplicaHealth {
1122    /// Replay lag in milliseconds at [`probed_at_unix_ms`](Self::probed_at_unix_ms),
1123    /// or [`LAG_UNMEASURABLE`] when the last probe could not produce one.
1124    lag_ms:             AtomicU64,
1125    /// Unix-epoch milliseconds of the last *successful* probe; `0` = never
1126    /// probed, which reads as infinitely aged and therefore ineligible.
1127    probed_at_unix_ms:  AtomicU64,
1128    /// `pg_is_in_recovery()` as of the last successful probe. Seeded from the
1129    /// boot health check so the first probe can report a *transition* — a
1130    /// replica that left recovery between boot and now was promoted by a
1131    /// failover, and is a writable server whose contents may diverge from the
1132    /// primary in both directions.
1133    in_recovery:        AtomicBool,
1134    /// `pg_is_in_recovery()` at the boot health check, which is what makes the
1135    /// transition legible.
1136    ///
1137    /// Booting outside recovery is *accepted* with a warning, because a dev rig
1138    /// legitimately stands a plain database in for a replica; a server that
1139    /// booted as a standby and later is not one has been promoted, and no dev
1140    /// rig does that by accident. Only the second case stops taking reads —
1141    /// which is why the boot reading has to be kept, not just the latest one.
1142    booted_in_recovery: bool,
1143}
1144
1145/// Sentinel [`ReplicaHealth::lag_ms`]: the last probe produced no lag reading.
1146///
1147/// A promoted replica (`pg_last_wal_replay_lsn()` is NULL outside recovery), a
1148/// standby that has replayed no transaction yet, or a probe that failed
1149/// outright. Saturating arithmetic keeps it above every budget, so "unknown"
1150/// routes to the primary rather than being read as "zero".
1151const LAG_UNMEASURABLE: u64 = u64::MAX;
1152
1153/// One round trip returning a replica's recovery state and replay lag.
1154///
1155/// `pg_last_xact_replay_timestamp()` alone over-reports on an idle primary — it
1156/// answers "how old is the newest transaction I applied", which keeps growing
1157/// when there is nothing left to apply. The first branch is the standard
1158/// correction: a standby whose received WAL position equals its replayed one has
1159/// applied everything it has been sent, and is by definition not behind.
1160const REPLICA_PROBE_SQL: &str = "SELECT pg_is_in_recovery(), \
1161     CASE \
1162       WHEN NOT pg_is_in_recovery() THEN NULL::bigint \
1163       WHEN pg_last_wal_replay_lsn() IS NOT NULL \
1164            AND pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0::bigint \
1165       WHEN pg_last_xact_replay_timestamp() IS NULL THEN NULL::bigint \
1166       ELSE GREATEST(0, EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000)::bigint \
1167     END";
1168
1169impl ReplicaHealth {
1170    /// Seed from the boot health check, before any probe has run.
1171    const fn booted(in_recovery: bool) -> Self {
1172        Self {
1173            lag_ms:             AtomicU64::new(LAG_UNMEASURABLE),
1174            probed_at_unix_ms:  AtomicU64::new(0),
1175            in_recovery:        AtomicBool::new(in_recovery),
1176            booted_in_recovery: in_recovery,
1177        }
1178    }
1179
1180    /// Whether this replica is still the kind of server it booted as.
1181    ///
1182    /// `false` only for the failover case: it was a standby at boot and the last
1183    /// probe found it outside recovery, so something promoted it. That server is
1184    /// now accepting writes of its own, and reads served from it can diverge
1185    /// from the primary in *both* directions — unlike ordinary lag, which only
1186    /// ever shows the past. It stops taking reads whether or not a staleness
1187    /// budget is configured, because there is no budget under which "a different
1188    /// database" is acceptable.
1189    ///
1190    /// A replica that booted outside recovery keeps the acceptance the boot check
1191    /// gave it (dev rigs stand a plain database in for a standby, and the boot
1192    /// warning already says so).
1193    fn is_still_a_standby(&self) -> bool {
1194        !self.booted_in_recovery || self.in_recovery.load(Ordering::Relaxed)
1195    }
1196
1197    /// Whether this replica may serve a read with at most `max_lag_ms` of
1198    /// staleness, as of `now_ms`. See the type docs for why the sum is a bound.
1199    fn within_lag_budget(&self, now_ms: u64, max_lag_ms: u64) -> bool {
1200        let probed_at = self.probed_at_unix_ms.load(Ordering::Relaxed);
1201        if probed_at == 0 {
1202            return false;
1203        }
1204        let lag = self.lag_ms.load(Ordering::Relaxed);
1205        if lag == LAG_UNMEASURABLE {
1206            return false;
1207        }
1208        lag.saturating_add(now_ms.saturating_sub(probed_at)) <= max_lag_ms
1209    }
1210
1211    /// Record a probe result, logging a promotion the moment it is first seen.
1212    fn record(&self, index: usize, in_recovery: bool, lag_ms: Option<u64>) {
1213        if self.in_recovery.swap(in_recovery, Ordering::Relaxed) && !in_recovery {
1214            tracing::warn!(
1215                replica = index,
1216                "Read replica left recovery (pg_is_in_recovery() = false); a failover has \
1217                 promoted it to a writable server. Reads routed to it may diverge from the \
1218                 primary, and its replication lag is no longer measurable."
1219            );
1220        }
1221        self.lag_ms.store(lag_ms.unwrap_or(LAG_UNMEASURABLE), Ordering::Relaxed);
1222        // Only a probe that produced a reading counts as a probe: leaving the
1223        // timestamp behind is what ages an unreadable replica out of rotation.
1224        if lag_ms.is_some() {
1225            self.probed_at_unix_ms.store(now_unix_ms(), Ordering::Relaxed);
1226        }
1227    }
1228
1229    /// Mark the last probe as failed, without disturbing the recovery state a
1230    /// successful probe established.
1231    fn record_failure(&self) {
1232        self.lag_ms.store(LAG_UNMEASURABLE, Ordering::Relaxed);
1233    }
1234}
1235
1236/// Probe every replica in `set` forever, on its configured cadence.
1237///
1238/// Holds a [`Weak`] so the loop is not itself a reason for the replica set to
1239/// stay alive: when the last adapter clone drops, the upgrade fails and the task
1240/// ends. Probe failures are logged and retried on the next tick — a replica that
1241/// cannot be probed ages out of the lag budget on its own, and one configured
1242/// without a budget keeps serving, exactly as it did before probes existed.
1243async fn probe_read_replicas(set: Weak<ReadReplicaSet>) {
1244    loop {
1245        let Some(set) = set.upgrade() else { return };
1246        let interval = set.probe_interval;
1247
1248        for (index, pool) in set.pools.iter().enumerate() {
1249            let health = &set.health[index];
1250            let client = match pool.get().await {
1251                Ok(client) => client,
1252                Err(e) => {
1253                    tracing::debug!(replica = index, error = %e, "Read replica health probe \
1254                         could not acquire a connection");
1255                    health.record_failure();
1256                    continue;
1257                },
1258            };
1259            match client.query_one(REPLICA_PROBE_SQL, &[]).await {
1260                Ok(row) => {
1261                    let in_recovery: bool = row.get(0);
1262                    let lag_ms: Option<i64> = row.get(1);
1263                    // Reason (try_from): a negative lag would mean the standby
1264                    // replayed a transaction the future has not committed yet;
1265                    // GREATEST(0, …) already excludes it, and treating any such
1266                    // reading as unmeasurable keeps the fail-closed side.
1267                    health.record(index, in_recovery, lag_ms.and_then(|ms| u64::try_from(ms).ok()));
1268                },
1269                Err(e) => {
1270                    tracing::debug!(
1271                        replica = index,
1272                        error = %pg_detail(&e),
1273                        "Read replica health probe failed"
1274                    );
1275                    health.record_failure();
1276                },
1277            }
1278        }
1279
1280        // Drop the strong reference before sleeping: holding it across the sleep
1281        // would keep every replica pool open for one interval after the adapter
1282        // is gone.
1283        drop(set);
1284        tokio::time::sleep(interval).await;
1285    }
1286}
1287
1288/// Whether a read at `now_ms` falls inside the post-write primary pin.
1289///
1290/// Pure so the boundary cases are unit-testable without a clock: a never-armed
1291/// watermark (`last_write_ms == 0`) is only "recent" while the process's clock
1292/// is within `pin_ms` of the epoch, i.e. never in practice.
1293const fn pin_active(now_ms: u64, last_write_ms: u64, pin_ms: u64) -> bool {
1294    now_ms.saturating_sub(last_write_ms) < pin_ms
1295}
1296
1297/// Current Unix time in milliseconds.
1298fn now_unix_ms() -> u64 {
1299    use std::time::{SystemTime, UNIX_EPOCH};
1300    // Reason (map_or 0): a pre-epoch system clock degrades to "always pinned",
1301    // which is the safe (primary-only) side.
1302    SystemTime::now()
1303        .duration_since(UNIX_EPOCH)
1304        .map_or(0, |d| u64::try_from(d.as_millis()).unwrap_or(u64::MAX))
1305}
1306
1307impl std::fmt::Debug for PostgresAdapter {
1308    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
1309        f.debug_struct("PostgresAdapter")
1310            .field("mutation_timing_enabled", &self.mutation_timing_enabled)
1311            .field("timing_variable_name", &self.timing_variable_name)
1312            .field("pool", &"<Pool>")
1313            .field(
1314                "read_replicas",
1315                &self.read_replicas.as_ref().map(|r| r.pools.len()).unwrap_or_default(),
1316            )
1317            .field("streaming_permits", &self.streaming_permits.available_permits())
1318            .field("transaction_retry", &self.transaction_retry)
1319            .finish()
1320    }
1321}
1322
1323impl PostgresAdapter {
1324    /// Create new PostgreSQL adapter with default pool configuration.
1325    ///
1326    /// # Arguments
1327    ///
1328    /// * `connection_string` - PostgreSQL connection string (e.g., "postgresql://localhost/mydb")
1329    ///
1330    /// # Errors
1331    ///
1332    /// Returns `FraiseQLError::ConnectionPool` if pool creation fails.
1333    ///
1334    /// # Example
1335    ///
1336    /// ```rust,no_run
1337    /// # use fraiseql_db::postgres::PostgresAdapter;
1338    /// # async fn example() -> Result<(), Box<dyn std::error::Error>> {
1339    /// let adapter = PostgresAdapter::new("postgresql://localhost/mydb").await?;
1340    /// # Ok(())
1341    /// # }
1342    /// ```
1343    pub async fn new(connection_string: &str) -> Result<Self> {
1344        Self::with_pool_config(
1345            connection_string,
1346            PoolPrewarmConfig {
1347                min_size:            0,
1348                max_size:            DEFAULT_POOL_SIZE,
1349                timeout_secs:        None,
1350                search_path:         None,
1351                max_streaming_reads: None,
1352                // The recall-preserving default; see `VectorScanConfig` (#1116).
1353                vector_scan:         VectorScanConfig::default(),
1354                // libpq's default: negotiate TLS when the server offers it. Callers
1355                // that need a guarantee go through `with_pool_config`.
1356                tls:                 PostgresTlsConfig::default(),
1357                read_replicas:       None,
1358            },
1359        )
1360        .await
1361    }
1362
1363    /// Create new PostgreSQL adapter with pre-warming and timeout configuration.
1364    ///
1365    /// Constructs the pool, runs a startup health check, then eagerly opens
1366    /// `cfg.min_size` connections so they are ready when the first request arrives.
1367    ///
1368    /// # Arguments
1369    ///
1370    /// * `connection_string` - PostgreSQL connection string
1371    /// * `cfg` - Pool pre-warming and timeout configuration
1372    ///
1373    /// # Errors
1374    ///
1375    /// Returns `FraiseQLError::ConnectionPool` if pool creation or the startup
1376    /// health check fails.
1377    pub async fn with_pool_config(connection_string: &str, cfg: PoolPrewarmConfig) -> Result<Self> {
1378        let pool = build_pool(
1379            connection_string,
1380            cfg.max_size,
1381            cfg.timeout_secs,
1382            cfg.search_path.as_ref(),
1383            cfg.vector_scan,
1384            &cfg.tls,
1385        )?;
1386
1387        // Startup health check — establishes the first connection, and refuses a
1388        // server below the supported floor before anything else runs against it.
1389        let client = pool.get().await.map_err(|e| FraiseQLError::ConnectionPool {
1390            message: format!("Failed to acquire connection: {e}"),
1391        })?;
1392        require_supported_server(&client).await?;
1393
1394        // Drop client back to the pool before pre-warming so that the health-check
1395        // connection counts as idle slot #1.
1396        drop(client);
1397
1398        // Replica pools are built from the SAME config as the primary — search
1399        // path, TLS, sizing — so per-tenant isolation applies to every pool this
1400        // adapter ever reads through, not just the one it writes through (#407).
1401        let read_replicas = match &cfg.read_replicas {
1402            None => None,
1403            Some(rc) => {
1404                let set = Arc::new(build_read_replica_set(rc, &cfg).await?);
1405                // Weak, so the probe loop ends when the last adapter clone drops
1406                // rather than keeping the pools alive for the process's lifetime.
1407                tokio::spawn(probe_read_replicas(Arc::downgrade(&set)));
1408                Some(set)
1409            },
1410        };
1411
1412        let adapter = Self {
1413            pool,
1414            read_replicas,
1415            mutation_timing_enabled: false,
1416            timing_variable_name: "fraiseql.started_at".to_string(),
1417            streaming_permits: Arc::new(Semaphore::new(streaming_read_slots(
1418                cfg.max_streaming_reads,
1419                cfg.max_size,
1420            ))),
1421            transaction_retry: TransactionRetry::off(),
1422        };
1423
1424        // Pre-warm to `min_size` — NOT `min_size - 1`. The health-check connection
1425        // above is returned to the pool, so the first warm acquisition recycles it
1426        // rather than opening a new one; subtracting for it left the pool one
1427        // connection short of `min_size` (#937). Acquiring `min_size` concurrently
1428        // is self-correcting: whatever is already idle gets reused, the rest are
1429        // opened, and the pool's own `max_size` caps the total.
1430        let warm_target = cfg.min_size.min(cfg.max_size);
1431        if warm_target > 0 {
1432            adapter.prewarm(warm_target).await;
1433        }
1434
1435        Ok(adapter)
1436    }
1437
1438    /// Create new PostgreSQL adapter with custom pool size.
1439    ///
1440    /// # Arguments
1441    ///
1442    /// * `connection_string` - PostgreSQL connection string
1443    /// * `max_size` - Maximum number of connections in pool
1444    ///
1445    /// # Errors
1446    ///
1447    /// Returns `FraiseQLError::ConnectionPool` if pool creation fails.
1448    pub async fn with_pool_size(connection_string: &str, max_size: usize) -> Result<Self> {
1449        Self::with_pool_config(
1450            connection_string,
1451            PoolPrewarmConfig {
1452                min_size: 0,
1453                max_size,
1454                timeout_secs: None,
1455                search_path: None,
1456                tls: PostgresTlsConfig::default(),
1457                read_replicas: None,
1458                max_streaming_reads: None,
1459                vector_scan: VectorScanConfig::default(),
1460            },
1461        )
1462        .await
1463    }
1464
1465    /// Pre-warm the pool by opening `count` additional connections.
1466    ///
1467    /// Pre-warming is best-effort: failures from individual connections are logged
1468    /// but do not prevent startup. A 10-second outer timeout ensures the server
1469    /// never blocks indefinitely on a slow or unreachable PostgreSQL instance.
1470    async fn prewarm(&self, count: usize) {
1471        use futures::future::join_all;
1472        use tokio::time::timeout;
1473
1474        // Acquire every guard CONCURRENTLY and hold them all until the last one
1475        // is in hand. Acquiring one at a time — or spawning tasks that release
1476        // as they finish — lets a later acquisition recycle a connection an
1477        // earlier one just returned, so the pool settles below `count` and
1478        // `pool_min_size` silently under-delivers (#937).
1479        let result =
1480            timeout(Duration::from_secs(10), join_all((0..count).map(|_| self.pool.get()))).await;
1481
1482        let (succeeded, failed) = match result {
1483            Ok(guards) => {
1484                let s = guards.iter().filter(|r| r.is_ok()).count();
1485                // Released together, so `s` distinct connections stay in the pool.
1486                drop(guards);
1487                (s, count - s)
1488            },
1489            Err(_elapsed) => {
1490                tracing::warn!(
1491                    target_connections = count,
1492                    "Pool pre-warm timed out after 10s; server will continue with partial pre-warm"
1493                );
1494                (0, count)
1495            },
1496        };
1497
1498        if failed > 0 {
1499            tracing::warn!(
1500                succeeded,
1501                failed,
1502                "Pool pre-warm: some connections could not be established"
1503            );
1504        } else {
1505            // Report what the pool actually holds, not what was attempted.
1506            tracing::info!(
1507                idle_connections = self.pool.status().available,
1508                "PostgreSQL pool pre-warmed successfully"
1509            );
1510        }
1511    }
1512
1513    /// Get a reference to the internal connection pool.
1514    ///
1515    /// This allows sharing the pool with other components like `PostgresIntrospector`.
1516    #[must_use]
1517    pub const fn pool(&self) -> &Pool {
1518        &self.pool
1519    }
1520
1521    /// Enable mutation timing injection.
1522    ///
1523    /// When enabled, `execute_write` sets a session variable to `clock_timestamp()::text` in
1524    /// each write's transaction before the call, allowing SQL functions to compute their own
1525    /// duration.
1526    ///
1527    /// # Arguments
1528    ///
1529    /// * `variable_name` - The PostgreSQL session variable name (e.g., `"fraiseql.started_at"`)
1530    #[must_use]
1531    pub fn with_mutation_timing(mut self, variable_name: &str) -> Self {
1532        self.mutation_timing_enabled = true;
1533        self.timing_variable_name = variable_name.to_string();
1534        self
1535    }
1536
1537    /// Re-run a mutation's transaction that PostgreSQL aborts with `40P01` or `40001`, as
1538    /// `policy` says. Without it, one attempt.
1539    #[must_use]
1540    pub fn with_transaction_retry(mut self, policy: TransactionRetry) -> Self {
1541        self.transaction_retry = policy;
1542        self
1543    }
1544
1545    /// How often a mutation's transaction runs when PostgreSQL aborts it.
1546    #[must_use]
1547    pub const fn transaction_retry(&self) -> &TransactionRetry {
1548        &self.transaction_retry
1549    }
1550
1551    /// Returns whether mutation timing injection is enabled.
1552    #[must_use]
1553    pub const fn mutation_timing_enabled(&self) -> bool {
1554        self.mutation_timing_enabled
1555    }
1556
1557    /// Execute raw SQL query and return JSONB rows.
1558    ///
1559    /// # Errors
1560    ///
1561    /// Returns `FraiseQLError::Database` on query execution failure.
1562    pub(super) async fn execute_raw(
1563        &self,
1564        sql: &str,
1565        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
1566        routing: ReadRouting,
1567    ) -> Result<Vec<JsonbValue>> {
1568        self.execute_raw_with_session(sql, params, &[], routing).await
1569    }
1570
1571    /// Like [`execute_raw`](Self::execute_raw) but applies transaction-local
1572    /// session variables on the same connection / transaction that runs the
1573    /// query.
1574    ///
1575    /// `set_config(..., true)` and the `SELECT` share one transaction, so
1576    /// PostgreSQL RLS policies backed by `current_setting()` see the configured
1577    /// values (fixes #329).
1578    ///
1579    /// # Errors
1580    ///
1581    /// Returns `FraiseQLError::Database` on transaction, `set_config`, query, or
1582    /// commit failure.
1583    pub(super) async fn execute_raw_with_session(
1584        &self,
1585        sql: &str,
1586        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
1587        session_vars: &[(&str, &str)],
1588        routing: ReadRouting,
1589    ) -> Result<Vec<JsonbValue>> {
1590        self.execute_documents_with_session(sql, params, session_vars, routing, None)
1591            .await
1592    }
1593
1594    /// [`execute_raw_with_session`](Self::execute_raw_with_session), each document's
1595    /// `Decimal` numbers kept as the digits stored (see [`document_cell`]).
1596    ///
1597    /// # Errors
1598    ///
1599    /// As [`execute_raw_with_session`](Self::execute_raw_with_session).
1600    pub(super) async fn execute_documents_with_session(
1601        &self,
1602        sql: &str,
1603        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
1604        session_vars: &[(&str, &str)],
1605        routing: ReadRouting,
1606        decimals: Option<Decimals<'_>>,
1607    ) -> Result<Vec<JsonbValue>> {
1608        let rows = self.read_rows(sql, params, session_vars, routing).await?;
1609        rows.into_iter().map(|row| document_cell(&row, 0, sql, decimals)).collect()
1610    }
1611
1612    /// [`execute_raw_with_session`](Self::execute_raw_with_session)'s read, each row kept as
1613    /// the text the server sent.
1614    ///
1615    /// # Errors
1616    ///
1617    /// As [`execute_raw_with_session`](Self::execute_raw_with_session).
1618    pub(super) async fn execute_raw_text_with_session(
1619        &self,
1620        sql: &str,
1621        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
1622        session_vars: &[(&str, &str)],
1623        routing: ReadRouting,
1624    ) -> Result<RawRows> {
1625        let rows = self.read_rows(sql, params, session_vars, routing).await?;
1626        let mut raw = RawRows::new();
1627        for row in &rows {
1628            raw_json_cell(row, sql, &mut raw)?;
1629        }
1630        Ok(raw)
1631    }
1632
1633    /// Run a read-only statement and return its rows, `session_vars` applied in a
1634    /// transaction on the same connection when there are any.
1635    ///
1636    /// Read-only by construction (`SELECT data FROM <view>`), so replica-eligible:
1637    /// `set_config` + SELECT run fine inside a hot-standby read-only transaction.
1638    async fn read_rows(
1639        &self,
1640        sql: &str,
1641        params: &[&(dyn tokio_postgres::types::ToSql + Sync)],
1642        session_vars: &[(&str, &str)],
1643        routing: ReadRouting,
1644    ) -> Result<Vec<Row>> {
1645        let mut client = self.acquire_read_connection_with_retry(routing).await?;
1646        if session_vars.is_empty() {
1647            return match query_read(&client, sql, params).await {
1648                Err(e) if is_stale_cached_statement(&e) => {
1649                    client.statement_cache.remove(sql, &[]);
1650                    query_read(&client, sql, params).await
1651                },
1652                rows => rows,
1653            }
1654            .map_err(|e| read_failed(&e));
1655        }
1656        match query_read_in_session(&mut client, sql, params, session_vars).await? {
1657            Err(e) if is_stale_cached_statement(&e) => {
1658                client.statement_cache.remove(sql, &[]);
1659                query_read_in_session(&mut client, sql, params, session_vars).await?
1660            },
1661            rows => rows,
1662        }
1663        .map_err(|e| read_failed(&e))
1664    }
1665
1666    /// Record a mutation-pipeline write for read-your-writes pinning (#407).
1667    ///
1668    /// Called at both entry and successful exit of every mutation-pipeline
1669    /// method: entry so a read racing a slow write already pins, exit because
1670    /// replication lag is measured from commit. A no-op without replicas.
1671    pub(super) fn mark_write(&self) {
1672        if let Some(replicas) = &self.read_replicas {
1673            replicas.last_write_unix_ms.store(now_unix_ms(), Ordering::Relaxed);
1674        }
1675    }
1676
1677    /// Acquire a connection for a **structurally read-only** statement (#407).
1678    ///
1679    /// Routing, in order:
1680    /// 1. no replicas configured → primary;
1681    /// 2. inside the post-write pin window → primary (read-your-writes);
1682    /// 3. otherwise round-robin over the replicas, skipping any outside the bounded-staleness
1683    ///    budget and any whose acquisition fails, falling back to the primary when none is left.
1684    ///
1685    /// Only methods that can never write may call this; anything mixed-use
1686    /// (`execute_raw_query`, stats, health, DDL) stays on
1687    /// [`acquire_connection_with_retry`](Self::acquire_connection_with_retry).
1688    ///
1689    /// # Errors
1690    ///
1691    /// Returns `FraiseQLError::ConnectionPool` when every replica *and* the
1692    /// primary fallback fail to yield a connection.
1693    pub(super) async fn acquire_read_connection_with_retry(
1694        &self,
1695        routing: ReadRouting,
1696    ) -> Result<deadpool_postgres::Client> {
1697        let Some(replicas) = &self.read_replicas else {
1698            return self.acquire_connection_with_retry().await;
1699        };
1700
1701        // A query annotated `primary` is asking not to be served stale, which no
1702        // replica can promise (#957).
1703        if !routing.allows_replica() {
1704            return self.acquire_connection_with_retry().await;
1705        }
1706
1707        let now_ms = now_unix_ms();
1708        // `replica` opts out of the pin: the pin exists so a client reads its own
1709        // writes, and this query has said that is not what it is for.
1710        if routing.honours_write_pin()
1711            && pin_active(
1712                now_ms,
1713                replicas.last_write_unix_ms.load(Ordering::Relaxed),
1714                replicas.pin_after_write_ms,
1715            )
1716        {
1717            return self.acquire_connection_with_retry().await;
1718        }
1719
1720        let max_lag_ms = routing.effective_max_lag_ms(replicas.max_lag_ms);
1721
1722        let count = replicas.pools.len();
1723        let start = replicas.next.fetch_add(1, Ordering::Relaxed);
1724        let mut ineligible = 0_usize;
1725        for offset in 0..count {
1726            let index = (start.wrapping_add(offset)) % count;
1727            // The lag gate comes before acquisition, not after: a replica outside
1728            // the budget must not be *reached*, and checking afterwards would
1729            // burn a connection to learn what the last probe already knew.
1730            let health = &replicas.health[index];
1731            let eligible = health.is_still_a_standby()
1732                && max_lag_ms.is_none_or(|budget| health.within_lag_budget(now_ms, budget));
1733            if !eligible {
1734                ineligible += 1;
1735                continue;
1736            }
1737            match replicas.pools[index].get().await {
1738                Ok(client) => return Ok(client),
1739                Err(e) => {
1740                    tracing::warn!(
1741                        replica = index,
1742                        error = %e,
1743                        "Read replica connection acquisition failed; trying the next candidate"
1744                    );
1745                },
1746            }
1747        }
1748
1749        if ineligible > 0 {
1750            tracing::debug!(
1751                ineligible,
1752                replicas = count,
1753                "Read replicas outside the bounded-staleness budget, or promoted out of \
1754                 recovery; serving the read from the primary"
1755            );
1756        } else {
1757            tracing::warn!("All read replicas unavailable; serving the read from the primary");
1758        }
1759        self.acquire_connection_with_retry().await
1760    }
1761
1762    /// Acquire a connection from the pool with retry logic.
1763    ///
1764    /// - `PoolError::Timeout`: the pool was exhausted for the full configured wait period. This is
1765    ///   not transient — retrying would only multiply the wait. Fails immediately.
1766    /// - `PoolError::Backend` / create errors: potentially transient. Retries with exponential
1767    ///   backoff (up to `MAX_CONNECTION_RETRIES` attempts).
1768    ///
1769    /// # Errors
1770    ///
1771    /// Returns `FraiseQLError::ConnectionPool` on timeout or when all retries are exhausted.
1772    pub(super) async fn acquire_connection_with_retry(&self) -> Result<deadpool_postgres::Client> {
1773        use deadpool_postgres::PoolError;
1774
1775        let mut last_error = None;
1776
1777        for attempt in 0..MAX_CONNECTION_RETRIES {
1778            match self.pool.get().await {
1779                Ok(client) => {
1780                    if attempt > 0 {
1781                        tracing::info!(attempt, "Successfully acquired connection after retries");
1782                    }
1783                    return Ok(client);
1784                },
1785                // Pool exhausted for the full wait period — not transient, fail immediately.
1786                Err(PoolError::Timeout(_)) => {
1787                    let metrics = self.pool_metrics();
1788                    tracing::error!(
1789                        available = metrics.idle_connections,
1790                        active = metrics.active_connections,
1791                        max = metrics.total_connections,
1792                        "Connection pool timeout: all connections busy"
1793                    );
1794                    return Err(FraiseQLError::ConnectionPool {
1795                        message: format!(
1796                            "Connection pool timeout: {}/{} connections busy. \
1797                             Increase pool_max_size or reduce concurrent load.",
1798                            metrics.active_connections, metrics.total_connections,
1799                        ),
1800                    });
1801                },
1802                // Backend/create errors are potentially transient — retry with backoff.
1803                Err(e) => {
1804                    last_error = Some(e);
1805                    if attempt < MAX_CONNECTION_RETRIES - 1 {
1806                        let delay = CONNECTION_RETRY_DELAY_MS * (u64::from(attempt) + 1);
1807                        tracing::warn!(
1808                            attempt = attempt + 1,
1809                            total = MAX_CONNECTION_RETRIES,
1810                            delay_ms = delay,
1811                            "Transient connection error, retrying"
1812                        );
1813                        tokio::time::sleep(Duration::from_millis(delay)).await;
1814                    }
1815                },
1816            }
1817        }
1818
1819        // All retries for transient errors exhausted.
1820        let pool_metrics = self.pool_metrics();
1821        tracing::error!(
1822            retries = MAX_CONNECTION_RETRIES,
1823            available = pool_metrics.idle_connections,
1824            active = pool_metrics.active_connections,
1825            max = pool_metrics.total_connections,
1826            "Failed to acquire connection after all retries"
1827        );
1828
1829        Err(FraiseQLError::ConnectionPool {
1830            message: format!(
1831                "Failed to acquire connection after {} retries: {}. \
1832                 Pool state: idle={}, active={}, max={}",
1833                MAX_CONNECTION_RETRIES,
1834                last_error.expect("last_error is set on every retry iteration"),
1835                pool_metrics.idle_connections,
1836                pool_metrics.active_connections,
1837                pool_metrics.total_connections,
1838            ),
1839        })
1840    }
1841
1842    /// Execute query with SQL field projection optimization.
1843    ///
1844    /// Uses the provided `SqlProjectionHint` to generate optimized SQL that projects
1845    /// only the requested fields from the JSONB column, reducing network payload and
1846    /// JSON deserialization overhead.
1847    ///
1848    /// # Arguments
1849    ///
1850    /// * `view` - View/table name to query
1851    /// * `projection` - Optional SQL projection hint with field list
1852    /// * `where_clause` - Optional WHERE clause for filtering
1853    /// * `limit` - Optional row limit
1854    ///
1855    /// # Returns
1856    ///
1857    /// Vector of projected JSONB rows with only the requested fields
1858    ///
1859    /// # Errors
1860    ///
1861    /// Returns `FraiseQLError::Database` on query execution failure.
1862    ///
1863    /// # Panics
1864    ///
1865    /// Cannot panic in practice: the inner `expect` is guarded by an `is_none()` check
1866    /// immediately above it.
1867    ///
1868    /// # Example
1869    ///
1870    /// ```no_run
1871    /// // Requires: running PostgreSQL database.
1872    /// use fraiseql_db::postgres::PostgresAdapter;
1873    /// use fraiseql_db::types::SqlProjectionHint;
1874    /// use fraiseql_db::DatabaseType;
1875    ///
1876    /// # async fn example(adapter: &PostgresAdapter) -> Result<(), Box<dyn std::error::Error>> {
1877    /// let projection = SqlProjectionHint::new(
1878    ///     DatabaseType::PostgreSQL,
1879    ///     "jsonb_build_object('id', data->>'id')".to_string(),
1880    ///     75,
1881    /// );
1882    ///
1883    /// let results = adapter
1884    ///     .execute_with_projection("v_user", Some(&projection), None, Some(10), None)
1885    ///     .await?;
1886    /// # Ok(())
1887    /// # }
1888    /// ```
1889    /// Implementation of `execute_with_projection` with ORDER BY support.
1890    ///
1891    /// Called by both the inherent convenience method and the `DatabaseAdapter`
1892    /// trait implementation.
1893    pub(super) async fn execute_with_projection_impl(
1894        &self,
1895        view: &str,
1896        projection: Option<&SqlProjectionHint>,
1897        where_clause: Option<&WhereClause>,
1898        limit: Option<u32>,
1899        offset: Option<u32>,
1900        order_by: Option<&[OrderByClause]>,
1901    ) -> Result<Vec<JsonbValue>> {
1902        // If no projection, fall back to standard query
1903        if projection.is_none() {
1904            return self.execute_where_query(view, where_clause, limit, offset, order_by).await;
1905        }
1906
1907        let projection = projection.expect("projection is Some; None was returned above");
1908
1909        let (sql, typed_params) = build_projection_select_sql(
1910            projection,
1911            ReadFrom::relation(view),
1912            where_clause,
1913            limit,
1914            offset,
1915            order_by,
1916        )?;
1917
1918        tracing::debug!("SQL with projection = {}", sql);
1919        tracing::debug!("typed_params = {:?}", typed_params);
1920
1921        let param_refs = crate::types::as_sql_param_refs(&typed_params);
1922
1923        self.execute_raw(&sql, &param_refs, ReadRouting::Any).await
1924    }
1925
1926    /// Execute query with SQL field projection optimization.
1927    ///
1928    /// Convenience wrapper for callers that don't need ORDER BY.
1929    /// See `execute_with_projection_impl` for details.
1930    ///
1931    /// # Errors
1932    ///
1933    /// Returns `FraiseQLError::Database` on query execution failure.
1934    pub async fn execute_with_projection(
1935        &self,
1936        view: &str,
1937        projection: Option<&SqlProjectionHint>,
1938        where_clause: Option<&WhereClause>,
1939        limit: Option<u32>,
1940        offset: Option<u32>,
1941    ) -> Result<Vec<JsonbValue>> {
1942        self.execute_with_projection_impl(view, projection, where_clause, limit, offset, None)
1943            .await
1944    }
1945}
1946
1947/// Build a parameterized `SELECT data FROM {view}` SQL string.
1948///
1949/// Shared by [`PostgresAdapter::execute_where_query`] and
1950/// [`PostgresAdapter::explain_where_query`] so that SQL construction
1951/// logic is never duplicated.
1952///
1953/// # Returns
1954///
1955/// `(sql, typed_params)` — the SQL string and the bound parameter values.
1956///
1957/// # Errors
1958///
1959/// Returns `FraiseQLError` if WHERE clause generation fails.
1960pub(super) fn build_where_select_sql(
1961    view: &str,
1962    where_clause: Option<&WhereClause>,
1963    limit: Option<u32>,
1964    offset: Option<u32>,
1965) -> Result<(String, Vec<QueryParam>)> {
1966    build_where_select_sql_ordered(ReadFrom::relation(view), where_clause, limit, offset, None)
1967}
1968
1969/// Build a parameterized `SELECT data FROM {view}` SQL string with optional ORDER BY.
1970///
1971/// ORDER BY is inserted between the WHERE clause and LIMIT/OFFSET as required by SQL.
1972///
1973/// # Returns
1974///
1975/// `(sql, typed_params)` — the SQL string and the bound parameter values.
1976///
1977/// # Errors
1978///
1979/// Returns `FraiseQLError` if WHERE clause generation or field name validation fails.
1980/// `SELECT COUNT(*) FROM {view} [WHERE …]` and its parameters (#938).
1981///
1982/// Deliberately shares [`PostgresWhereGenerator`] with the row query rather than
1983/// formatting its own predicate: the count and the page it describes have to
1984/// agree, and two independent renderings of the same `WhereClause` is how they
1985/// would stop agreeing. No `ORDER BY` (it cannot change a count, and sorting the
1986/// rows first is pure cost) and no `LIMIT`/`OFFSET` (the total is the whole
1987/// point).
1988pub(super) fn build_count_sql(
1989    view: &str,
1990    where_clause: Option<&WhereClause>,
1991) -> Result<(String, Vec<QueryParam>)> {
1992    let mut sql = format!("SELECT COUNT(*) FROM {}", quote_postgres_identifier(view));
1993    let typed_params: Vec<QueryParam> = if let Some(clause) = where_clause {
1994        let generator = PostgresWhereGenerator::new(PostgresDialect);
1995        let (where_sql, where_params) = generator.generate(clause)?;
1996        sql.push_str(" WHERE ");
1997        sql.push_str(&where_sql);
1998        where_params.into_iter().map(QueryParam::from).collect()
1999    } else {
2000        Vec::new()
2001    };
2002    Ok((sql, typed_params))
2003}
2004
2005/// What a read selects `FROM`: a relation, or a set-returning function whose
2006/// `arity` parameters are bound first, as `$1 … $arity`, every later placeholder of the
2007/// statement numbered after them.
2008#[derive(Debug, Clone, Copy)]
2009pub(super) struct ReadFrom<'a> {
2010    name:  &'a str,
2011    arity: Option<usize>,
2012}
2013
2014impl<'a> ReadFrom<'a> {
2015    /// The relation `name`.
2016    pub(super) const fn relation(name: &'a str) -> Self {
2017        Self { name, arity: None }
2018    }
2019
2020    /// The set-returning function `name`, called with `arity` bound parameters.
2021    pub(super) const fn function(name: &'a str, arity: usize) -> Self {
2022        Self {
2023            name,
2024            arity: Some(arity),
2025        }
2026    }
2027
2028    /// What `request` reads.
2029    pub(super) const fn of(request: &crate::traits::ProjectionRequest<'a>) -> Self {
2030        match request.source_args {
2031            Some(args) => Self::function(request.view, args.len()),
2032            None => Self::relation(request.view),
2033        }
2034    }
2035
2036    /// The `FROM` target as SQL: the quoted relation, or the quoted function called with
2037    /// its placeholders.
2038    fn sql(self) -> String {
2039        let name = quote_postgres_identifier(self.name);
2040        match self.arity {
2041            Some(arity) => {
2042                let placeholders: Vec<String> = (1..=arity).map(|i| format!("${i}")).collect();
2043                format!("{name}({})", placeholders.join(", "))
2044            },
2045            None => name,
2046        }
2047    }
2048
2049    /// How many placeholders the target binds before the rest of the statement.
2050    const fn bound(self) -> usize {
2051        match self.arity {
2052            Some(arity) => arity,
2053            None => 0,
2054        }
2055    }
2056}
2057
2058/// `page` (a page's `SELECT`, its parameters `params`) widened to count, in the same
2059/// statement and so the same snapshot, how many rows of `view` match `where_clause` (every
2060/// row, without one), up to `bound` (#1314). Each document carries the count under
2061/// [`MATCHED_KEY`](crate::traits::MATCHED_KEY); an empty page comes back as one document
2062/// marked [`EMPTY_PAGE_KEY`](crate::traits::EMPTY_PAGE_KEY), so the count survives it.
2063///
2064/// The count reuses the page's own `WHERE` text and placeholders (both builders number them
2065/// from `$1`), and the page keeps its order through an ordinal.
2066///
2067/// # Errors
2068///
2069/// As the `WHERE` generator.
2070pub(super) fn with_matched_count(
2071    page: &str,
2072    mut params: Vec<QueryParam>,
2073    from: ReadFrom<'_>,
2074    where_clause: Option<&WhereClause>,
2075    bound: u32,
2076) -> Result<(String, Vec<QueryParam>)> {
2077    let where_sql = match where_clause {
2078        Some(clause) => {
2079            let (sql, _) = PostgresWhereGenerator::new(PostgresDialect)
2080                .generate_with_param_offset(clause, from.bound())?;
2081            format!(" WHERE {sql}")
2082        },
2083        None => String::new(),
2084    };
2085    params.push(QueryParam::BigInt(i64::from(bound)));
2086    let bound_index = from.bound() + params.len();
2087    let matched = crate::traits::MATCHED_KEY;
2088    let empty = crate::traits::EMPTY_PAGE_KEY;
2089    // The count is appended as the document's last key without leaving `json`: a projection
2090    // builds its document as `json` so its keys stay in selection order at every depth
2091    // (#1563), and a jsonb `||` would sort them. `json_each … WITH ORDINALITY` reads the
2092    // document's entries in order, whether the page returned `json` or `jsonb`.
2093    let sql = format!(
2094        "SELECT CASE WHEN _p._o IS NULL \
2095           THEN json_build_object('{empty}', true, '{matched}', _m.matched) \
2096           ELSE (SELECT json_object_agg(_e.key, _e.value ORDER BY _e.n) FROM \
2097                 (SELECT key, value, n FROM json_each(_p.data::json) WITH ORDINALITY \
2098                    AS _d(key, value, n) \
2099                  UNION ALL SELECT '{matched}', to_json(_m.matched), 9223372036854775807) _e) \
2100         END \
2101         FROM (SELECT count(*) AS matched FROM (SELECT 1 FROM {view}{where_sql} \
2102         LIMIT ${bound_index}) _s) _m \
2103         LEFT JOIN LATERAL (SELECT _q.data, row_number() OVER () AS _o FROM ({page}) AS _q(data)) \
2104         _p ON true ORDER BY _p._o",
2105        view = from.sql(),
2106    );
2107    Ok((sql, params))
2108}
2109
2110pub(super) fn build_where_select_sql_ordered(
2111    from: ReadFrom<'_>,
2112    where_clause: Option<&WhereClause>,
2113    limit: Option<u32>,
2114    offset: Option<u32>,
2115    order_by: Option<&[OrderByClause]>,
2116) -> Result<(String, Vec<QueryParam>)> {
2117    // Build base query
2118    let mut sql = format!("SELECT data FROM {}", from.sql());
2119
2120    // Collect WHERE clause params (if any)
2121    let mut typed_params: Vec<QueryParam> = if let Some(clause) = where_clause {
2122        let generator = PostgresWhereGenerator::new(PostgresDialect);
2123        let (where_sql, where_params) =
2124            generator.generate_with_param_offset(clause, from.bound())?;
2125        sql.push_str(" WHERE ");
2126        sql.push_str(&where_sql);
2127
2128        // Convert WHERE clause JSON values to QueryParam
2129        where_params.into_iter().map(QueryParam::from).collect()
2130    } else {
2131        Vec::new()
2132    };
2133    let mut param_count = from.bound() + typed_params.len();
2134
2135    // ORDER BY must come before LIMIT/OFFSET in SQL — and so must its
2136    // parameters. A relevance ordering (#1284) binds the search text, so the
2137    // placeholders run WHERE → ORDER BY → LIMIT/OFFSET, which is the order the
2138    // fragments are appended in.
2139    // `Tiebreak::Identity`: this read is paged by LIMIT/OFFSET, so its ordering
2140    // has to be total or the pages are a slice of no sequence (#1287).
2141    for bound in append_order_by(
2142        &mut sql,
2143        order_by,
2144        DatabaseType::PostgreSQL,
2145        param_count + 1,
2146        Tiebreak::Identity,
2147    )? {
2148        param_count += 1;
2149        typed_params.push(QueryParam::Text(bound));
2150    }
2151
2152    // Add LIMIT as BigInt (PostgreSQL requires integer type for LIMIT).
2153    // Reason (expect below): fmt::Write for String is infallible.
2154    if let Some(lim) = limit {
2155        param_count += 1;
2156        write!(sql, " LIMIT ${param_count}").expect("write to String");
2157        typed_params.push(QueryParam::BigInt(i64::from(lim)));
2158    }
2159
2160    // Add OFFSET as BigInt (PostgreSQL requires integer type for OFFSET)
2161    if let Some(off) = offset {
2162        param_count += 1;
2163        write!(sql, " OFFSET ${param_count}").expect("write to String");
2164        typed_params.push(QueryParam::BigInt(i64::from(off)));
2165    }
2166
2167    Ok((sql, typed_params))
2168}
2169
2170/// [`build_projection_select_sql`] for a template that reads only the document: the page is
2171/// cut first, each returned row's document is detoasted once, and only then projected (#1554).
2172///
2173/// ```text
2174/// SELECT <projection over data>
2175/// FROM (SELECT CASE WHEN pg_column_compression(data) IS NOT NULL
2176///                     OR pg_column_toast_chunk_id(data) IS NOT NULL
2177///                   THEN data || '{}'::jsonb ELSE data END AS data, "_o"
2178///       FROM (SELECT data, row_number() OVER (ORDER BY <keys>) AS "_o"
2179///             FROM <view> [WHERE …] ORDER BY <keys> LIMIT $n OFFSET $m) AS _fq_page
2180///       OFFSET 0) AS _fq_doc
2181/// ORDER BY "_o"
2182/// ```
2183///
2184/// - The innermost `SELECT` is the single-`SELECT` read's `WHERE`, `ORDER BY`, `LIMIT` and
2185///   `OFFSET`, unchanged, so the rows a page holds cannot differ; a skipped row is never projected.
2186/// - The ordinal carries the page's order out of the subquery, so the outer `ORDER BY` never
2187///   re-evaluates a key expression (a collation, a locale chain, a bound relevance operand) in
2188///   another scope. The keys are rendered once and written twice, so a bound operand keeps one
2189///   placeholder.
2190/// - A document is copied (`|| '{}'`, which yields it detoasted) only when it is compressed or
2191///   stored out of line, so every extraction in the projection reads memory instead of the toast
2192///   relation; an inline uncompressed one is used as it is. The template only extracts keys from
2193///   the document, which the copy leaves unchanged. `OFFSET 0` keeps the planner from folding the
2194///   copy back into each reference to `data`.
2195fn build_page_first_select_sql(
2196    projection: &SqlProjectionHint,
2197    from: ReadFrom<'_>,
2198    where_clause: Option<&WhereClause>,
2199    limit: Option<u32>,
2200    offset: Option<u32>,
2201    order_by: Option<&[OrderByClause]>,
2202) -> Result<(String, Vec<QueryParam>)> {
2203    let mut typed_params: Vec<QueryParam> = Vec::new();
2204    let where_sql = if let Some(clause) = where_clause {
2205        let generator = PostgresWhereGenerator::new(PostgresDialect);
2206        let (where_sql, where_params) =
2207            generator.generate_with_param_offset(clause, from.bound())?;
2208        typed_params.extend(where_params.into_iter().map(QueryParam::from));
2209        format!(" WHERE {where_sql}")
2210    } else {
2211        String::new()
2212    };
2213    let mut param_count = from.bound() + typed_params.len();
2214
2215    // `Tiebreak::Identity`, as the single-`SELECT` read: a paged order must be total (#1287).
2216    let rendered = render_order_by_columns(
2217        order_by,
2218        DatabaseType::PostgreSQL,
2219        param_count + 1,
2220        Tiebreak::Identity,
2221    )?;
2222    let (ordinal, inner_order, outer_order) = match &rendered {
2223        Some(rendered) => {
2224            for bound in &rendered.params {
2225                param_count += 1;
2226                typed_params.push(QueryParam::Text(bound.clone()));
2227            }
2228            (
2229                format!(", row_number() OVER (ORDER BY {}) AS \"_o\"", rendered.columns),
2230                format!(" ORDER BY {}", rendered.columns),
2231                " ORDER BY \"_o\"",
2232            )
2233        },
2234        None => (String::new(), String::new(), ""),
2235    };
2236
2237    let mut page = format!("SELECT data{ordinal} FROM {}{where_sql}{inner_order}", from.sql());
2238    // Reason (expect below): fmt::Write for String is infallible.
2239    if let Some(lim) = limit {
2240        param_count += 1;
2241        write!(page, " LIMIT ${param_count}").expect("write to String");
2242        typed_params.push(QueryParam::BigInt(i64::from(lim)));
2243    }
2244    if let Some(off) = offset {
2245        param_count += 1;
2246        write!(page, " OFFSET ${param_count}").expect("write to String");
2247        typed_params.push(QueryParam::BigInt(i64::from(off)));
2248    }
2249
2250    let carried = if rendered.is_some() { ", \"_o\"" } else { "" };
2251    let sql = format!(
2252        "SELECT {} FROM (SELECT CASE WHEN pg_column_compression(data) IS NOT NULL \
2253         OR pg_column_toast_chunk_id(data) IS NOT NULL THEN data || '{{}}'::jsonb ELSE data END \
2254         AS data{carried} FROM ({page}) AS _fq_page OFFSET 0) AS _fq_doc{outer_order}",
2255        projection.projection_template
2256    );
2257    Ok((sql, typed_params))
2258}
2259
2260/// Build a parameterized projection `SELECT` SQL string.
2261///
2262/// Mirrors the SQL produced inline by [`PostgresAdapter::execute_with_projection_impl`],
2263/// extracted so the connection-affine `*_with_session` path can reuse it
2264/// without acquiring its own connection.
2265///
2266/// # Returns
2267///
2268/// `(sql, typed_params)` — the SQL string and the bound parameter values.
2269///
2270/// # Errors
2271///
2272/// Returns `FraiseQLError` if WHERE clause generation fails.
2273pub(super) fn build_projection_select_sql(
2274    projection: &SqlProjectionHint,
2275    from: ReadFrom<'_>,
2276    where_clause: Option<&WhereClause>,
2277    limit: Option<u32>,
2278    offset: Option<u32>,
2279    order_by: Option<&[OrderByClause]>,
2280) -> Result<(String, Vec<QueryParam>)> {
2281    if projection.reads_only_document {
2282        return build_page_first_select_sql(
2283            projection,
2284            from,
2285            where_clause,
2286            limit,
2287            offset,
2288            order_by,
2289        );
2290    }
2291    // The projection_template is the SELECT clause with projection SQL,
2292    // e.g. "jsonb_build_object('id', data->>'id', 'email', data->>'email')".
2293    let mut sql = format!("SELECT {} FROM {}", projection.projection_template, from.sql());
2294
2295    let mut typed_params: Vec<QueryParam> = if let Some(clause) = where_clause {
2296        let generator = PostgresWhereGenerator::new(PostgresDialect);
2297        let (where_sql, where_params) =
2298            generator.generate_with_param_offset(clause, from.bound())?;
2299        sql.push_str(" WHERE ");
2300        sql.push_str(&where_sql);
2301        where_params.into_iter().map(QueryParam::from).collect()
2302    } else {
2303        Vec::new()
2304    };
2305    let mut param_count = from.bound() + typed_params.len();
2306
2307    // ORDER BY must come before LIMIT/OFFSET in SQL — and so must its
2308    // parameters (#1284); see `build_where_select_sql_ordered`.
2309    // `Tiebreak::Identity`: this read is paged by LIMIT/OFFSET, so its ordering
2310    // has to be total or the pages are a slice of no sequence (#1287).
2311    for bound in append_order_by(
2312        &mut sql,
2313        order_by,
2314        DatabaseType::PostgreSQL,
2315        param_count + 1,
2316        Tiebreak::Identity,
2317    )? {
2318        param_count += 1;
2319        typed_params.push(QueryParam::Text(bound));
2320    }
2321
2322    // Append LIMIT/OFFSET as BigInt (PostgreSQL requires integer type).
2323    // Reason (expect below): fmt::Write for String is infallible.
2324    if let Some(lim) = limit {
2325        param_count += 1;
2326        write!(sql, " LIMIT ${param_count}").expect("write to String");
2327        typed_params.push(QueryParam::BigInt(i64::from(lim)));
2328    }
2329
2330    if let Some(off) = offset {
2331        param_count += 1;
2332        write!(sql, " OFFSET ${param_count}").expect("write to String");
2333        typed_params.push(QueryParam::BigInt(i64::from(off)));
2334    }
2335
2336    Ok((sql, typed_params))
2337}