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, ¶m_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}