Skip to main content

fraiseql_db/postgres/adapter/
relay.rs

1//! `RelayDatabaseAdapter` implementation for `PostgresAdapter`.
2
3use fraiseql_error::{FraiseQLError, Result};
4
5use super::PostgresAdapter;
6use crate::{
7    dialect::PostgresDialect,
8    identifier::quote_postgres_identifier,
9    postgres::{pg_detail, where_generator::PostgresWhereGenerator},
10    traits::{RelayCursor, RelayDatabaseAdapter, RelayPageResult},
11    types::{QueryParam, ReadRouting, sql_hints::OrderByClause},
12    where_clause::WhereClause,
13};
14
15impl RelayDatabaseAdapter for PostgresAdapter {
16    /// Execute keyset (cursor-based) pagination against a JSONB view.
17    ///
18    /// # `totalCount` semantics
19    ///
20    /// When `include_total_count` is `true`, **two queries** are issued on the same
21    /// connection:
22    ///
23    /// 1. A count query — `SELECT COUNT(*) FROM {view} WHERE {user_filter}` — that reflects the
24    ///    **full connection** size, ignoring cursor position. This is required by the Relay Cursor
25    ///    Connections spec, which defines `totalCount` as the count of all objects in the
26    ///    connection, regardless of `after`/`before`.
27    ///
28    /// 2. A page query — the cursor-filtered, limited result set.
29    ///
30    /// The two-query approach fixes a previous bug where `COUNT(*) OVER()` ran
31    /// inside the cursor-filtered subquery, causing `totalCount` to shrink as the
32    /// cursor advanced.  It also handles the edge case where the current page is
33    /// empty but the total count is non-zero (e.g., cursor past the last row).
34    ///
35    /// When `include_total_count` is `false`, only the page query is issued.
36    ///
37    /// # Performance note
38    ///
39    /// The count query scans all rows matching the user filter without LIMIT. On
40    /// large unfiltered tables this may be slow. Mitigations:
41    /// - Only enable `totalCount` when the client explicitly requests it (enforced by the executor
42    ///   via `include_total_count`).
43    /// - Add a `statement_timeout` on the connection for relay queries on very large datasets.
44    /// - Maintain a denormalised count table or materialised view for hot paths.
45    async fn execute_relay_page(
46        &self,
47        view: &str,
48        cursor_column: &str,
49        after: Option<RelayCursor>,
50        before: Option<RelayCursor>,
51        limit: u32,
52        forward: bool,
53        where_clause: Option<&WhereClause>,
54        order_by: Option<&[OrderByClause]>,
55        include_total_count: bool,
56    ) -> Result<RelayPageResult> {
57        // Relay pagination is a compiled SELECT pair: replica-eligible (#407).
58        let client = self.acquire_read_connection_with_retry(ReadRouting::Any).await?;
59        let active = if forward {
60            after.clone()
61        } else {
62            before.clone()
63        };
64        let page = self
65            .run_relay_page(
66                &**client,
67                view,
68                cursor_column,
69                after,
70                before,
71                limit,
72                forward,
73                where_clause,
74                order_by,
75                include_total_count,
76            )
77            .await;
78        drop(client);
79        self.refuse_a_cursor_the_page_could_not_read(page, active.as_ref(), order_by)
80            .await
81    }
82
83    #[allow(clippy::too_many_arguments)] // Reason: relay pagination requires all cursor/filter/sort/count arguments plus session vars; no natural grouping
84    async fn execute_relay_page_with_session(
85        &self,
86        view: &str,
87        cursor_column: &str,
88        after: Option<RelayCursor>,
89        before: Option<RelayCursor>,
90        limit: u32,
91        forward: bool,
92        where_clause: Option<&WhereClause>,
93        order_by: Option<&[OrderByClause]>,
94        include_total_count: bool,
95        session_vars: &[(&str, &str)],
96        routing: ReadRouting,
97    ) -> Result<RelayPageResult> {
98        // Fast path: no session vars => identical to execute_relay_page.
99        if session_vars.is_empty() {
100            return self
101                .execute_relay_page(
102                    view,
103                    cursor_column,
104                    after,
105                    before,
106                    limit,
107                    forward,
108                    where_clause,
109                    order_by,
110                    include_total_count,
111                )
112                .await;
113        }
114
115        // Apply set_config and run BOTH the page and count queries inside one
116        // transaction on one connection so RLS sees the session variables.
117        // Read-only and standby-safe: replica-eligible (#407).
118        let mut client = self.acquire_read_connection_with_retry(routing).await?;
119        let txn = client.build_transaction().start().await.map_err(|e| {
120            crate::postgres::database_error(
121                format!("Failed to start relay session-var transaction: {}", pg_detail(&e)),
122                &e,
123            )
124        })?;
125        super::database::apply_session_vars(&txn, session_vars).await?;
126        let active = if forward {
127            after.clone()
128        } else {
129            before.clone()
130        };
131        let page = self
132            .run_relay_page(
133                &*txn,
134                view,
135                cursor_column,
136                after,
137                before,
138                limit,
139                forward,
140                where_clause,
141                order_by,
142                include_total_count,
143            )
144            .await;
145        if page.is_err() {
146            // The transaction is aborted; the probe runs on a connection of its own.
147            drop(txn);
148            drop(client);
149            return self
150                .refuse_a_cursor_the_page_could_not_read(page, active.as_ref(), order_by)
151                .await;
152        }
153        let result = page?;
154        txn.commit().await.map_err(|e| {
155            crate::postgres::database_error(
156                format!("Failed to commit relay session-var transaction: {}", pg_detail(&e)),
157                &e,
158            )
159        })?;
160        Ok(result)
161    }
162}
163
164impl PostgresAdapter {
165    /// `page`, unless it failed on a data exception that the cursor it resumed from caused
166    /// (#1521): then a refusal of the cursor, saying how to recover.
167    ///
168    /// A cursor is client data, and a forged value reaches PostgreSQL as a bound parameter
169    /// cast to its key's type, where it fails as `22P02` and the like. Only on that failure
170    /// path, PostgreSQL's own input parser is asked about each of the cursor's typed values
171    /// ([`cursor_values_probe`](crate::keyset::cursor_values_probe)), on a fresh connection.
172    /// When every value is valid the data raised the exception, and it is returned as it
173    /// was; so is the original error if the probe itself cannot run.
174    async fn refuse_a_cursor_the_page_could_not_read(
175        &self,
176        page: Result<RelayPageResult>,
177        cursor: Option<&RelayCursor>,
178        order_by: Option<&[OrderByClause]>,
179    ) -> Result<RelayPageResult> {
180        let error = match page {
181            Err(error @ FraiseQLError::Database { .. }) => error,
182            other => return other,
183        };
184        let data_exception = matches!(
185            &error,
186            FraiseQLError::Database { sql_state: Some(state), .. } if state.starts_with("22")
187        );
188        let (Some(cursor), true) = (cursor, data_exception) else {
189            return Err(error);
190        };
191        let Ok(keys) = crate::keyset::keyset_keys(order_by) else {
192            return Err(error);
193        };
194        let uuid_position = match &cursor.position {
195            crate::traits::CursorValue::Uuid(uuid) => Some(uuid.as_str()),
196            crate::traits::CursorValue::Int64(_) => None,
197        };
198        let Some((sql, params)) =
199            crate::keyset::cursor_values_probe(&keys, &cursor.sort_keys, uuid_position)
200        else {
201            return Err(error);
202        };
203        let Ok(client) = self.acquire_read_connection_with_retry(ReadRouting::Any).await else {
204            return Err(error);
205        };
206        let params: Vec<QueryParam> = params.into_iter().map(QueryParam::Text).collect();
207        let refs = crate::types::as_sql_param_refs(&params);
208        let Ok(row) = client.query_one(&sql, &refs).await else {
209            return Err(error);
210        };
211        let valid: Vec<bool> = row.get("valid");
212        if valid.iter().all(|v| *v) {
213            return Err(error);
214        }
215        Err(FraiseQLError::validation(
216            "the `after`/`before` cursor is not one of this connection: a value it carries is \
217             not of its key's type; request the first page again, without `after`/`before`",
218        ))
219    }
220
221    /// Build and run the relay page (and optional total-count) queries against
222    /// an arbitrary client — a pooled connection for the plain path, or a
223    /// transaction for the connection-affine `*_with_session` path.
224    #[allow(clippy::too_many_arguments)] // Reason: relay pagination requires all cursor/filter/sort/count arguments; no natural grouping
225    async fn run_relay_page<C>(
226        &self,
227        client: &C,
228        view: &str,
229        cursor_column: &str,
230        after: Option<RelayCursor>,
231        before: Option<RelayCursor>,
232        limit: u32,
233        forward: bool,
234        where_clause: Option<&WhereClause>,
235        order_by: Option<&[OrderByClause]>,
236        include_total_count: bool,
237    ) -> Result<RelayPageResult>
238    where
239        C: tokio_postgres::GenericClient + Sync,
240    {
241        let quoted_view = quote_postgres_identifier(view);
242        let quoted_col = quote_postgres_identifier(cursor_column);
243
244        // #1284: a relevance-ranked read cannot be paged by cursor (refused by
245        // `keyset_keys`). Each key is rendered by the same rules the offset path's
246        // `ORDER BY` uses (#832): storage key, declared type's cast, native column,
247        // collation, localized label.
248        let keys = crate::keyset::keyset_keys(order_by)?;
249
250        // ── Keyset condition (page query only, NOT the count query) ────────────
251        //
252        // Per the Relay spec, totalCount ignores cursor position, so the count query
253        // leaves this out. The page resumes past the cursor row's sort-key values,
254        // then past its position (#1521): its parameters lead the page query's list,
255        // the key values (as text) then the position.
256        let mut page_typed_params: Vec<QueryParam> = Vec::new();
257        let active_cursor = if forward { after } else { before };
258        let keyset_where = match active_cursor {
259            None => None,
260            Some(cursor) => {
261                let (position, placeholder) = crate::keyset::position_param(&cursor.position);
262                let (predicate, key_params, _) = crate::keyset::keyset_predicate(
263                    &keys,
264                    &cursor.sort_keys,
265                    &quoted_col,
266                    placeholder,
267                    forward,
268                    1,
269                )?;
270                page_typed_params.extend(key_params.into_iter().map(QueryParam::Text));
271                page_typed_params.push(position);
272                Some(predicate)
273            },
274        };
275        let keyset_param_count = page_typed_params.len();
276
277        // ── User WHERE clause ──────────────────────────────────────────────────
278        //
279        // Used in BOTH the count query (offset 0) and the page query (offset by
280        // the keyset's parameters so indices don't collide).
281        let page_user_where_sql: Option<String> = if let Some(clause) = where_clause {
282            let generator = PostgresWhereGenerator::new(PostgresDialect);
283            let (sql, params) = generator.generate_with_param_offset(clause, keyset_param_count)?;
284            page_typed_params.extend(params.into_iter().map(QueryParam::from));
285            Some(sql)
286        } else {
287            None
288        };
289
290        // ── Page WHERE SQL ─────────────────────────────────────────────────────
291        let conditions: Vec<String> = keyset_where
292            .into_iter()
293            .chain(page_user_where_sql.map(|s| format!("({s})")))
294            .collect();
295        let page_where_sql = if conditions.is_empty() {
296            String::new()
297        } else {
298            format!(" WHERE {}", conditions.join(" AND "))
299        };
300
301        // ── LIMIT parameter ────────────────────────────────────────────────────
302        page_typed_params.push(QueryParam::BigInt(i64::from(limit)));
303        let limit_idx = page_typed_params.len();
304
305        // ── Page SQL ───────────────────────────────────────────────────────────
306        //
307        // The ordering ends with the cursor column, which makes it total, and nothing
308        // after it: the next page resumes from exactly these terms (#1287). Each row's
309        // document carries its sort-key values, which the next page's cursor is built
310        // from. A backward page reads the ordering reversed from the cursor, then the
311        // outer query restores the requested order.
312        let document = crate::keyset::with_sort_keys("data", &keys);
313        let order_sql = crate::keyset::keyset_order(&keys, &quoted_col, forward);
314        let page_sql = if forward {
315            format!(
316                "SELECT {document} AS data FROM {quoted_view}{page_where_sql} \
317                 ORDER BY {order_sql} LIMIT ${limit_idx}"
318            )
319        } else {
320            let (key_columns, outer_keys) =
321                crate::keyset::carried_keys(&keys, "_relay_k", "_relay_page");
322            let inner = format!(
323                "SELECT {document} AS data, {quoted_col} AS _relay_cursor{key_columns} \
324                 FROM {quoted_view}{page_where_sql} ORDER BY {order_sql} LIMIT ${limit_idx}"
325            );
326            let outer_order =
327                crate::keyset::keyset_order(&outer_keys, "_relay_page._relay_cursor", true);
328            format!("SELECT data FROM ({inner}) _relay_page ORDER BY {outer_order}")
329        };
330
331        // ── Execute page query (on the caller-provided client / transaction) ────
332        let page_param_refs = crate::types::as_sql_param_refs(&page_typed_params);
333
334        let page_rows = client
335            .query(&page_sql, &page_param_refs)
336            .await
337            .map_err(|e| crate::postgres::database_error(pg_detail(&e), &e))?;
338
339        let rows: Vec<crate::types::JsonbValue> = page_rows
340            .iter()
341            .map(|row| super::jsonb_cell(row, "data", &page_sql))
342            .collect::<Result<Vec<_>>>()?;
343
344        // ── Count query (Relay spec: totalCount ignores cursor position) ────────
345        //
346        // The WHERE clause is regenerated with offset 0 (no cursor parameter prefix)
347        // because this is a standalone query. Using the same connection avoids an
348        // extra pool acquisition.
349        let total_count = if include_total_count {
350            let (count_sql, count_typed_params) = if let Some(clause) = where_clause {
351                let generator = PostgresWhereGenerator::new(PostgresDialect);
352                let (where_sql, params) = generator.generate_with_param_offset(clause, 0)?;
353                let sql = format!("SELECT COUNT(*) FROM {quoted_view} WHERE ({where_sql})");
354                let typed: Vec<QueryParam> = params.into_iter().map(QueryParam::from).collect();
355                (sql, typed)
356            } else {
357                (format!("SELECT COUNT(*) FROM {quoted_view}"), Vec::<QueryParam>::new())
358            };
359
360            let count_param_refs = crate::types::as_sql_param_refs(&count_typed_params);
361
362            let count_row = client
363                .query_one(&count_sql, &count_param_refs)
364                .await
365                .map_err(|e| crate::postgres::database_error(pg_detail(&e), &e))?;
366
367            let total: i64 = count_row.get(0);
368            // cast_unsigned() is the clippy-recommended alternative to `as u64` for i64;
369            // it has the same bit-pattern semantics but makes the sign-loss intent explicit.
370            // Row counts from COUNT(*) are always non-negative so sign loss is impossible.
371            Some(total.cast_unsigned())
372        } else {
373            None
374        };
375
376        Ok(RelayPageResult::new(rows, total_count))
377    }
378}