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