fraiseql-db 2.19.1

Database abstraction layer for FraiseQL v2
Documentation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
//! `RelayDatabaseAdapter` implementation for `PostgresAdapter`.

use fraiseql_error::{FraiseQLError, Result};

use super::PostgresAdapter;
use crate::{
    dialect::PostgresDialect,
    identifier::quote_postgres_identifier,
    postgres::{pg_detail, where_generator::PostgresWhereGenerator},
    traits::{DecimalShape, RelayCursor, RelayDatabaseAdapter, RelayPageResult},
    types::{QueryParam, ReadRouting, sql_hints::OrderByClause},
    where_clause::WhereClause,
};

impl RelayDatabaseAdapter for PostgresAdapter {
    /// Execute keyset (cursor-based) pagination against a JSONB view.
    ///
    /// # `totalCount` semantics
    ///
    /// When `include_total_count` is `true`, **two queries** are issued on the same
    /// connection:
    ///
    /// 1. A count query — `SELECT COUNT(*) FROM {view} WHERE {user_filter}` — that reflects the
    ///    **full connection** size, ignoring cursor position. This is required by the Relay Cursor
    ///    Connections spec, which defines `totalCount` as the count of all objects in the
    ///    connection, regardless of `after`/`before`.
    ///
    /// 2. A page query — the cursor-filtered, limited result set.
    ///
    /// The two-query approach fixes a previous bug where `COUNT(*) OVER()` ran
    /// inside the cursor-filtered subquery, causing `totalCount` to shrink as the
    /// cursor advanced.  It also handles the edge case where the current page is
    /// empty but the total count is non-zero (e.g., cursor past the last row).
    ///
    /// When `include_total_count` is `false`, only the page query is issued.
    ///
    /// # Performance note
    ///
    /// The count query scans all rows matching the user filter without LIMIT. On
    /// large unfiltered tables this may be slow. Mitigations:
    /// - Only enable `totalCount` when the client explicitly requests it (enforced by the executor
    ///   via `include_total_count`).
    /// - Add a `statement_timeout` on the connection for relay queries on very large datasets.
    /// - Maintain a denormalised count table or materialised view for hot paths.
    async fn execute_relay_page(
        &self,
        view: &str,
        cursor_column: &str,
        after: Option<RelayCursor>,
        before: Option<RelayCursor>,
        limit: u32,
        forward: bool,
        where_clause: Option<&WhereClause>,
        order_by: Option<&[OrderByClause]>,
        include_total_count: bool,
    ) -> Result<RelayPageResult> {
        // Relay pagination is a compiled SELECT pair: replica-eligible (#407).
        self.relay_page_routed(
            view,
            cursor_column,
            after,
            before,
            limit,
            forward,
            where_clause,
            order_by,
            include_total_count,
            ReadRouting::Any,
            None,
        )
        .await
    }

    #[allow(clippy::too_many_arguments)] // Reason: relay pagination requires all cursor/filter/sort/count arguments plus session vars; no natural grouping
    async fn execute_relay_page_with_session(
        &self,
        view: &str,
        cursor_column: &str,
        after: Option<RelayCursor>,
        before: Option<RelayCursor>,
        limit: u32,
        forward: bool,
        where_clause: Option<&WhereClause>,
        order_by: Option<&[OrderByClause]>,
        include_total_count: bool,
        session_vars: &[(&str, &str)],
        routing: ReadRouting,
        decimals: Option<&DecimalShape>,
    ) -> Result<RelayPageResult> {
        // Fast path: no session variables to apply, so no transaction; same routing.
        if session_vars.is_empty() {
            return self
                .relay_page_routed(
                    view,
                    cursor_column,
                    after,
                    before,
                    limit,
                    forward,
                    where_clause,
                    order_by,
                    include_total_count,
                    routing,
                    decimals,
                )
                .await;
        }

        // Apply set_config and run BOTH the page and count queries inside one
        // transaction on one connection so RLS sees the session variables.
        // Read-only and standby-safe: replica-eligible (#407).
        let mut client = self.acquire_read_connection_with_retry(routing).await?;
        let txn = client.build_transaction().start().await.map_err(|e| {
            crate::postgres::database_error(
                format!("Failed to start relay session-var transaction: {}", pg_detail(&e)),
                &e,
            )
        })?;
        super::database::apply_session_vars(&txn, session_vars).await?;
        let active = if forward {
            after.clone()
        } else {
            before.clone()
        };
        let page = self
            .run_relay_page(
                &*txn,
                view,
                cursor_column,
                after,
                before,
                limit,
                forward,
                where_clause,
                order_by,
                include_total_count,
                decimals,
            )
            .await;
        if page.is_err() {
            // The transaction is aborted; the probe runs on a connection of its own.
            drop(txn);
            drop(client);
            return self
                .refuse_a_cursor_the_page_could_not_read(page, active.as_ref(), order_by)
                .await;
        }
        let result = page?;
        txn.commit().await.map_err(|e| {
            crate::postgres::database_error(
                format!("Failed to commit relay session-var transaction: {}", pg_detail(&e)),
                &e,
            )
        })?;
        Ok(result)
    }
}

impl PostgresAdapter {
    /// One relay page with no session variables, on a connection `routing` chooses. Both
    /// `execute_relay_page` (`Any`) and the session path's fast path go through it, so the
    /// fast path keeps the query's routing
    /// (`a_relay_page_routed_to_the_primary_never_reads_a_replica`).
    #[allow(clippy::too_many_arguments)] // Reason: the relay page arguments plus its routing
    async fn relay_page_routed(
        &self,
        view: &str,
        cursor_column: &str,
        after: Option<RelayCursor>,
        before: Option<RelayCursor>,
        limit: u32,
        forward: bool,
        where_clause: Option<&WhereClause>,
        order_by: Option<&[OrderByClause]>,
        include_total_count: bool,
        routing: ReadRouting,
        decimals: Option<&DecimalShape>,
    ) -> Result<RelayPageResult> {
        let client = self.acquire_read_connection_with_retry(routing).await?;
        let active = if forward {
            after.clone()
        } else {
            before.clone()
        };
        let page = self
            .run_relay_page(
                &**client,
                view,
                cursor_column,
                after,
                before,
                limit,
                forward,
                where_clause,
                order_by,
                include_total_count,
                decimals,
            )
            .await;
        drop(client);
        self.refuse_a_cursor_the_page_could_not_read(page, active.as_ref(), order_by)
            .await
    }

    /// `page`, unless it failed on a data exception that the cursor it resumed from caused
    /// (#1521): then a refusal of the cursor, saying how to recover.
    ///
    /// A cursor is client data, and a forged value reaches PostgreSQL as a bound parameter
    /// cast to its key's type, where it fails as `22P02` and the like. Only on that failure
    /// path, PostgreSQL's own input parser is asked about each of the cursor's typed values
    /// ([`cursor_values_probe`](crate::keyset::cursor_values_probe)), on a fresh connection.
    /// When every value is valid the data raised the exception, and it is returned as it
    /// was; so is the original error if the probe itself cannot run.
    async fn refuse_a_cursor_the_page_could_not_read(
        &self,
        page: Result<RelayPageResult>,
        cursor: Option<&RelayCursor>,
        order_by: Option<&[OrderByClause]>,
    ) -> Result<RelayPageResult> {
        let error = match page {
            Err(error @ FraiseQLError::Database { .. }) => error,
            other => return other,
        };
        let data_exception = matches!(
            &error,
            FraiseQLError::Database { sql_state: Some(state), .. } if state.starts_with("22")
        );
        let (Some(cursor), true) = (cursor, data_exception) else {
            return Err(error);
        };
        let Ok(keys) = crate::keyset::keyset_keys(order_by) else {
            return Err(error);
        };
        let uuid_position = match &cursor.position {
            crate::traits::CursorValue::Uuid(uuid) => Some(uuid.as_str()),
            crate::traits::CursorValue::Int64(_) => None,
        };
        let Some((sql, params)) =
            crate::keyset::cursor_values_probe(&keys, &cursor.sort_keys, uuid_position)
        else {
            return Err(error);
        };
        let Ok(client) = self.acquire_read_connection_with_retry(ReadRouting::Any).await else {
            return Err(error);
        };
        let params: Vec<QueryParam> = params.into_iter().map(QueryParam::Text).collect();
        let refs = crate::types::as_sql_param_refs(&params);
        let Ok(row) = client.query_one(&sql, &refs).await else {
            return Err(error);
        };
        let valid: Vec<bool> = row.get("valid");
        if valid.iter().all(|v| *v) {
            return Err(error);
        }
        Err(FraiseQLError::validation(
            "the `after`/`before` cursor is not one of this connection: a value it carries is \
             not of its key's type; request the first page again, without `after`/`before`",
        ))
    }

    /// Build and run the relay page (and optional total-count) queries against
    /// an arbitrary client — a pooled connection for the plain path, or a
    /// transaction for the connection-affine `*_with_session` path.
    #[allow(clippy::too_many_arguments)] // Reason: relay pagination requires all cursor/filter/sort/count arguments; no natural grouping
    async fn run_relay_page<C>(
        &self,
        client: &C,
        view: &str,
        cursor_column: &str,
        after: Option<RelayCursor>,
        before: Option<RelayCursor>,
        limit: u32,
        forward: bool,
        where_clause: Option<&WhereClause>,
        order_by: Option<&[OrderByClause]>,
        include_total_count: bool,
        decimals: Option<&DecimalShape>,
    ) -> Result<RelayPageResult>
    where
        C: tokio_postgres::GenericClient + Sync,
    {
        let quoted_view = quote_postgres_identifier(view);
        let quoted_col = quote_postgres_identifier(cursor_column);

        // #1284: a relevance-ranked read cannot be paged by cursor (refused by
        // `keyset_keys`). Each key is rendered by the same rules the offset path's
        // `ORDER BY` uses (#832): storage key, declared type's cast, native column,
        // collation, localized label.
        let keys = crate::keyset::keyset_keys(order_by)?;

        // ── Keyset condition (page query only, NOT the count query) ────────────
        //
        // Per the Relay spec, totalCount ignores cursor position, so the count query
        // leaves this out. The page resumes past the cursor row's sort-key values,
        // then past its position (#1521): its parameters lead the page query's list,
        // the key values (as text) then the position.
        let mut page_typed_params: Vec<QueryParam> = Vec::new();
        let active_cursor = if forward { after } else { before };
        let keyset_where = match active_cursor {
            None => None,
            Some(cursor) => {
                let (position, placeholder) = crate::keyset::position_param(&cursor.position);
                let (predicate, key_params, _) = crate::keyset::keyset_predicate(
                    &keys,
                    &cursor.sort_keys,
                    &quoted_col,
                    placeholder,
                    forward,
                    1,
                )?;
                page_typed_params.extend(key_params.into_iter().map(QueryParam::Text));
                page_typed_params.push(position);
                Some(predicate)
            },
        };
        let keyset_param_count = page_typed_params.len();

        // ── User WHERE clause ──────────────────────────────────────────────────
        //
        // Used in BOTH the count query (offset 0) and the page query (offset by
        // the keyset's parameters so indices don't collide).
        let page_user_where_sql: Option<String> = if let Some(clause) = where_clause {
            let generator = PostgresWhereGenerator::new(PostgresDialect);
            let (sql, params) = generator.generate_with_param_offset(clause, keyset_param_count)?;
            page_typed_params.extend(params.into_iter().map(QueryParam::from));
            Some(sql)
        } else {
            None
        };

        // ── Page WHERE SQL ─────────────────────────────────────────────────────
        let conditions: Vec<String> = keyset_where
            .into_iter()
            .chain(page_user_where_sql.map(|s| format!("({s})")))
            .collect();
        let page_where_sql = if conditions.is_empty() {
            String::new()
        } else {
            format!(" WHERE {}", conditions.join(" AND "))
        };

        // ── LIMIT parameter ────────────────────────────────────────────────────
        page_typed_params.push(QueryParam::BigInt(i64::from(limit)));
        let limit_idx = page_typed_params.len();

        // ── Page SQL ───────────────────────────────────────────────────────────
        //
        // The ordering ends with the cursor column, which makes it total, and nothing
        // after it: the next page resumes from exactly these terms (#1287). Each row's
        // document carries its sort-key values, which the next page's cursor is built
        // from. A backward page reads the ordering reversed from the cursor, then the
        // outer query restores the requested order.
        let document = crate::keyset::with_sort_keys("data", &keys);
        let order_sql = crate::keyset::keyset_order(&keys, &quoted_col, forward);
        let page_sql = if forward {
            format!(
                "SELECT {document} AS data FROM {quoted_view}{page_where_sql} \
                 ORDER BY {order_sql} LIMIT ${limit_idx}"
            )
        } else {
            let (key_columns, outer_keys) =
                crate::keyset::carried_keys(&keys, "_relay_k", "_relay_page");
            let inner = format!(
                "SELECT {document} AS data, {quoted_col} AS _relay_cursor{key_columns} \
                 FROM {quoted_view}{page_where_sql} ORDER BY {order_sql} LIMIT ${limit_idx}"
            );
            let outer_order =
                crate::keyset::keyset_order(&outer_keys, "_relay_page._relay_cursor", true);
            format!("SELECT data FROM ({inner}) _relay_page ORDER BY {outer_order}")
        };

        // ── Execute page query (on the caller-provided client / transaction) ────
        let page_param_refs = crate::types::as_sql_param_refs(&page_typed_params);

        let page_rows = client
            .query(&page_sql, &page_param_refs)
            .await
            .map_err(|e| crate::postgres::database_error(pg_detail(&e), &e))?;

        let rows: Vec<crate::types::JsonbValue> = page_rows
            .iter()
            .map(|row| {
                super::document_cell(row, "data", &page_sql, super::Decimals::documents(decimals))
            })
            .collect::<Result<Vec<_>>>()?;

        // ── Count query (Relay spec: totalCount ignores cursor position) ────────
        //
        // The WHERE clause is regenerated with offset 0 (no cursor parameter prefix)
        // because this is a standalone query. Using the same connection avoids an
        // extra pool acquisition.
        let total_count = if include_total_count {
            let (count_sql, count_typed_params) = if let Some(clause) = where_clause {
                let generator = PostgresWhereGenerator::new(PostgresDialect);
                let (where_sql, params) = generator.generate_with_param_offset(clause, 0)?;
                let sql = format!("SELECT COUNT(*) FROM {quoted_view} WHERE ({where_sql})");
                let typed: Vec<QueryParam> = params.into_iter().map(QueryParam::from).collect();
                (sql, typed)
            } else {
                (format!("SELECT COUNT(*) FROM {quoted_view}"), Vec::<QueryParam>::new())
            };

            let count_param_refs = crate::types::as_sql_param_refs(&count_typed_params);

            let count_row = client
                .query_one(&count_sql, &count_param_refs)
                .await
                .map_err(|e| crate::postgres::database_error(pg_detail(&e), &e))?;

            let total: i64 = count_row.get(0);
            // cast_unsigned() is the clippy-recommended alternative to `as u64` for i64;
            // it has the same bit-pattern semantics but makes the sign-loss intent explicit.
            // Row counts from COUNT(*) are always non-negative so sign loss is impossible.
            Some(total.cast_unsigned())
        } else {
            None
        };

        Ok(RelayPageResult::new(rows, total_count))
    }
}