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