Skip to main content

uqa_execution/mutation/referential/
checks.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! The foreign key checks and referential actions that `PostgreSQL` queues as internal AFTER ROW triggers of a written row and runs once the statement has written its rows: `RI_FKey_check_ins` and `RI_FKey_check_upd` on a referencing row, the `NO ACTION` and `RESTRICT` checks of a referenced key that a delete or an update removed, and the `CASCADE`, `SET NULL` and `SET DEFAULT` actions that write the rows referencing such a key. A check therefore sees every row of the statement: a row may reference a row that the same statement writes later, and a later row's unique or NOT NULL violation is reported first.
8
9use std::sync::Arc;
10
11use uqa_core::{DocId, Value};
12use uqa_sql::{
13    ast::{ForeignKey, ForeignKeyAction},
14    semantics::{
15        foreign_keys::{
16            foreign_key_comparison_types, foreign_key_lookup_values, foreign_key_relation_name,
17        },
18        referential::referrers_to_for_actions,
19    },
20    SQLError,
21};
22use uqa_storage::document_store::Document;
23
24use super::ReferentialContext;
25use crate::mutation::constraints::{period::period_foreign_key_is_covered, ConstraintContext};
26
27/// A foreign key check or referential action queued with the AFTER ROW triggers of a written row.
28#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
29pub struct ForeignKeyCheck {
30    /// The position of the foreign key among the foreign keys the check is drawn from, which orders the internal triggers of one row as their object identifiers do.
31    ordinal: usize,
32    kind: CheckKind,
33}
34
35#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
36enum CheckKind {
37    Referencing(ReferencingRow),
38    Referenced(Box<ReferencedKey>),
39    Action(Box<super::cascades::ReferentialAction>),
40}
41
42/// The row that `table` holds at `doc_id` references a row through `foreign_key`, unless a later change of the statement removed it.
43#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
44struct ReferencingRow {
45    table: String,
46    doc_id: DocId,
47    foreign_key: Arc<ForeignKey>,
48}
49
50/// A delete or an update of a row of `relation` removed the referenced key `key`, which no row of `constraint_table` may still reference: under `NO ACTION` unless another row holds the key again, under `RESTRICT` whatever row holds it.
51#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
52struct ReferencedKey {
53    constraint_table: String,
54    /// The relation whose internal trigger fires: the partition that held the row, or the table an `UPDATE` named for a row it moved to another partition.
55    relation: String,
56    /// The constraint the check reports: the foreign key's, or the one it derives on `relation`.
57    constraint_name: String,
58    foreign_key: Arc<ForeignKey>,
59    #[serde(with = "crate::mutation::triggers::events::values::list")]
60    key: Vec<Value>,
61    restrict: bool,
62}
63
64/// The prefix of an internal trigger's name that checks a referencing row.
65const CHECK_TRIGGER: &str = "RI_ConstraintTrigger_c_";
66/// The prefix of an internal trigger's name that acts on a referenced key.
67const ACTION_TRIGGER: &str = "RI_ConstraintTrigger_a_";
68
69impl ForeignKeyCheck {
70    fn trigger_prefix(&self) -> &'static str {
71        match self.kind {
72            CheckKind::Referencing(_) => CHECK_TRIGGER,
73            CheckKind::Referenced(_) | CheckKind::Action(_) => ACTION_TRIGGER,
74        }
75    }
76
77    /// Whether the check runs before the user trigger `name` of the same row. A row's AFTER triggers fire in the order of their names, and an internal trigger's name is its prefix followed by its object identifier, so it precedes every name that sorts after the prefix; a user trigger whose name begins with the prefix itself follows it.
78    pub fn precedes_trigger(&self, name: &str) -> bool {
79        self.trigger_prefix().as_bytes() <= name.as_bytes()
80    }
81
82    /// The checks of one row in the order of their internal triggers' names: the actions on referenced keys before the checks of referencing rows, each in the order of the foreign keys.
83    pub fn sort(checks: &mut [Self]) {
84        checks.sort_by(|left, right| {
85            (left.trigger_prefix(), left.ordinal).cmp(&(right.trigger_prefix(), right.ordinal))
86        });
87    }
88}
89
90fn referencing_key_changed(columns: &[String], old: &Document, new: &Document) -> bool {
91    columns
92        .iter()
93        .any(|column| old.get(column) != new.get(column))
94}
95
96/// Referenced-key triggers observe representation changes even when SQL equality holds, so actions propagate numeric scale, signed zero and nested stored values. Referencing-row checks keep their equality rule.
97pub(super) fn referenced_key_changed(columns: &[String], old: &Document, new: &Document) -> bool {
98    columns.iter().any(|column| {
99        !old.get(column)
100            .unwrap_or(&Value::Null)
101            .has_same_representation(new.get(column).unwrap_or(&Value::Null))
102    })
103}
104
105/// The checks of the referencing row that `table` holds at `doc_id` after an insert, or after an update when `old` holds the row it replaced, as `RI_FKey_check_ins` and `RI_FKey_check_upd` are queued: an update checks only a foreign key whose columns it changed, a key that NULLs exempt needs no check, and a deferred foreign key waits for its transaction instead.
106pub fn referencing_checks(
107    context: ConstraintContext<'_>,
108    table: &str,
109    doc_id: DocId,
110    new: &Document,
111    old: Option<&Document>,
112) -> Result<Vec<ForeignKeyCheck>, SQLError> {
113    if context.referrers.session_replication_role_is_replica() {
114        return Ok(Vec::new());
115    }
116    let foreign_keys = context
117        .catalog
118        .try_foreign_keys(table)
119        .map_err(|error| SQLError::Internal(format!("read foreign keys of `{table}`: {error}")))?;
120    let mut checks = Vec::new();
121    for (ordinal, foreign_key) in foreign_keys.into_iter().enumerate() {
122        if !foreign_key.enforced
123            || old.is_some_and(|old| !referencing_key_changed(&foreign_key.local_columns, old, new))
124        {
125            continue;
126        }
127        let nulls = foreign_key
128            .local_columns
129            .iter()
130            .filter(|column| matches!(new.get(*column).unwrap_or(&Value::Null), Value::Null))
131            .count();
132        let exempt = match foreign_key.match_type {
133            uqa_sql::ast::ForeignKeyMatch::Simple => nulls > 0,
134            uqa_sql::ast::ForeignKeyMatch::Full => nulls == foreign_key.local_columns.len(),
135        };
136        if exempt
137            || context
138                .transactions
139                .foreign_key_is_deferred(table, &foreign_key)?
140        {
141            continue;
142        }
143        checks.push(ForeignKeyCheck {
144            ordinal,
145            kind: CheckKind::Referencing(ReferencingRow {
146                table: table.to_string(),
147                doc_id,
148                foreign_key: Arc::new(foreign_key),
149            }),
150        });
151    }
152    Ok(checks)
153}
154
155/// Which of the foreign keys that reference a row's partition or its ancestors a change of the row fires.
156#[derive(Clone, Copy, PartialEq, Eq)]
157enum ReferencedKeys {
158    /// Every one: an ordinary delete or update.
159    All,
160    /// Those that reference the partition itself: the delete half of a row's move to another partition, whose cloned triggers of an ancestor's foreign keys do not fire (`AfterTriggerSaveEvent`).
161    OfPartition,
162    /// Those that reference an ancestor: the update a row's move to another partition fires on the table the `UPDATE` named (`ExecCrossPartitionUpdateForeignKey`).
163    OfAncestors,
164}
165
166/// The checks and actions of the referenced keys that a delete of the row `old` of `table` removes, or an update that replaces it with `new`, as `RI_FKey_noaction_*`, `RI_FKey_restrict_*`, `RI_FKey_cascade_*`, `RI_FKey_setnull_*` and `RI_FKey_setdefault_*` are queued. An update queues them only when it changes the key (`RI_FKey_pk_upd_check_required`), a check of a key holding a NULL finds no referencing row, and a deferred `NO ACTION` key waits for its transaction. `moved_through` names the table an `UPDATE` named for a row it moved to another partition, whose update fires the foreign keys of the partition's ancestors.
167pub fn referenced_checks(
168    context: ConstraintContext<'_>,
169    table: &str,
170    old: &Document,
171    new: Option<&Document>,
172    moved_through: Option<&str>,
173) -> Result<Vec<ForeignKeyCheck>, SQLError> {
174    let keys = if moved_through.is_some() {
175        ReferencedKeys::OfAncestors
176    } else {
177        ReferencedKeys::All
178    };
179    collect_referenced_checks(context, table, old, new, moved_through, keys)
180}
181
182/// The checks and actions that the delete half of a move of the row `old` out of the partition `table` fires: those of the foreign keys that reference the partition itself.
183pub fn moved_row_delete_checks(
184    context: ConstraintContext<'_>,
185    table: &str,
186    old: &Document,
187) -> Result<Vec<ForeignKeyCheck>, SQLError> {
188    collect_referenced_checks(context, table, old, None, None, ReferencedKeys::OfPartition)
189}
190
191fn collect_referenced_checks(
192    context: ConstraintContext<'_>,
193    table: &str,
194    old: &Document,
195    new: Option<&Document>,
196    moved_through: Option<&str>,
197    keys: ReferencedKeys,
198) -> Result<Vec<ForeignKeyCheck>, SQLError> {
199    let resolve = |name: &str| {
200        context
201            .partitions
202            .catalog
203            .try_resolve_table_name(name)
204            .map_err(SQLError::Internal)
205            .map(|resolved| resolved.unwrap_or_else(|| name.to_string()))
206    };
207    let partition = if keys == ReferencedKeys::All {
208        String::new()
209    } else {
210        resolve(table)?
211    };
212    let mut checks = Vec::new();
213    for (ordinal, (constraint_table, foreign_key)) in
214        referrers_to_for_actions(context.referrers, table)?
215            .into_iter()
216            .enumerate()
217    {
218        if keys != ReferencedKeys::All
219            && (resolve(&foreign_key.ref_table)? == partition)
220                != (keys == ReferencedKeys::OfPartition)
221        {
222            continue;
223        }
224        let action = if new.is_some() {
225            foreign_key.on_update
226        } else {
227            foreign_key.on_delete
228        };
229        if !foreign_key.enforced
230            || new.is_some_and(|new| !referenced_key_changed(&foreign_key.ref_columns, old, new))
231        {
232            continue;
233        }
234        let key = foreign_key
235            .ref_columns
236            .iter()
237            .map(|column| old.get(column).cloned().unwrap_or(Value::Null))
238            .collect::<Vec<_>>();
239        let null_key = key.iter().any(|value| matches!(value, Value::Null));
240        if matches!(
241            action,
242            ForeignKeyAction::Cascade | ForeignKeyAction::SetNull | ForeignKeyAction::SetDefault
243        ) {
244            // An update of a key holding a NULL needs no action; a delete queues one whatever the key.
245            if new.is_some() && null_key {
246                continue;
247            }
248            let new_key = new.map(|new| {
249                foreign_key
250                    .ref_columns
251                    .iter()
252                    .map(|column| new.get(column).cloned().unwrap_or(Value::Null))
253                    .collect()
254            });
255            checks.push(ForeignKeyCheck {
256                ordinal,
257                kind: CheckKind::Action(Box::new(super::cascades::ReferentialAction {
258                    constraint_table,
259                    foreign_key: Arc::new(foreign_key),
260                    key,
261                    new_key,
262                })),
263            });
264            continue;
265        }
266        if null_key {
267            continue;
268        }
269        let firing = uqa_sql::schema::referenced_partitions::firing_constraint(
270            context.partitions.catalog,
271            table,
272            moved_through,
273            &foreign_key,
274        )?;
275        let restrict = action == ForeignKeyAction::Restrict;
276        if !restrict
277            && context.transactions.referenced_key_is_deferred(
278                &constraint_table,
279                &foreign_key,
280                firing.derived,
281            )?
282        {
283            continue;
284        }
285        let relation = firing.relation.to_string();
286        let constraint_name = firing.name(&foreign_key).to_string();
287        checks.push(ForeignKeyCheck {
288            ordinal,
289            kind: CheckKind::Referenced(Box::new(ReferencedKey {
290                constraint_table,
291                relation,
292                constraint_name,
293                foreign_key: Arc::new(foreign_key),
294                key,
295                restrict,
296            })),
297        });
298    }
299    Ok(checks)
300}
301
302/// Run a queued check or take a queued action once the statement has written its rows. An action queues the events of the rows it writes in `queue`.
303pub fn run_foreign_key_check<S: Clone + 'static>(
304    context: &crate::mutation::statement::MutationExecutionContext<'_, S>,
305    check: &ForeignKeyCheck,
306    queue: &crate::mutation::triggers::queue::AfterTriggerQueue,
307) -> Result<(), SQLError> {
308    let referential = &context.preparation.referential;
309    match &check.kind {
310        CheckKind::Referencing(row) => check_referencing_row(referential.constraints, row),
311        CheckKind::Referenced(key) => check_referenced_key(referential, key),
312        CheckKind::Action(action) => {
313            super::cascades::run_referential_action(context, action, queue)
314        }
315    }
316}
317
318/// `RI_FKey_check`: a row that the statement removed again is not checked, and the row as it stands must reference an existing row, which the check locks `FOR KEY SHARE`.
319fn check_referencing_row(
320    context: ConstraintContext<'_>,
321    row: &ReferencingRow,
322) -> Result<(), SQLError> {
323    let Some(document) = context.reads.get_document(&row.table, row.doc_id)? else {
324        return Ok(());
325    };
326    let Some(lookup) = foreign_key_lookup_values(
327        context.partitions.catalog,
328        &row.table,
329        &row.foreign_key,
330        &document,
331    )?
332    else {
333        return Ok(());
334    };
335    crate::mutation::constraints::require_foreign_key_parent(
336        context,
337        &row.table,
338        &row.foreign_key,
339        &lookup,
340        &document,
341    )
342}
343
344/// `ri_restrict`: under `NO ACTION` a key that another row of the referenced table holds again is satisfied, and otherwise no row may reference the removed key. The check reads the foreign key's table `FOR KEY SHARE`, which takes its `ROW SHARE` lock.
345fn check_referenced_key<S: Clone + 'static>(
346    context: &ReferentialContext<'_, S>,
347    key: &ReferencedKey,
348) -> Result<(), SQLError> {
349    let foreign_key = key.foreign_key.as_ref();
350    context.locking.session.lock_relation(
351        &key.constraint_table,
352        crate::row_locks::RelationLockMode::RowShare,
353    )?;
354    if !key.restrict
355        && !foreign_key.period
356        && crate::mutation::constraints::find_exact_foreign_key_parent(
357            context.constraints,
358            foreign_key,
359            &key.key,
360        )?
361        .is_some()
362    {
363        return Ok(());
364    }
365    let comparison = foreign_key_comparison_types(
366        context.constraints.partitions.catalog,
367        &key.constraint_table,
368        foreign_key,
369    )?;
370    let expected = comparison.normalize_referenced(key.key.clone())?;
371    let referenced = if foreign_key.period {
372        period_key_referenced(context, key, &expected)?
373    } else {
374        !super::referencing_rows(
375            context,
376            &key.constraint_table,
377            foreign_key,
378            &comparison,
379            &expected,
380            if key.restrict {
381                ForeignKeyAction::Restrict
382            } else {
383                ForeignKeyAction::NoAction
384            },
385        )?
386        .is_empty()
387    };
388    if referenced {
389        return Err(referenced_key_violation(context.constraints, key)?);
390    }
391    Ok(())
392}
393
394/// Whether a row of the foreign key's table holds the removed key's leading values over a period that the referenced table no longer covers.
395fn period_key_referenced<S: Clone + 'static>(
396    context: &ReferentialContext<'_, S>,
397    key: &ReferencedKey,
398    expected: &[Value],
399) -> Result<bool, SQLError> {
400    let foreign_key = key.foreign_key.as_ref();
401    let ordinary = expected.len().saturating_sub(1);
402    let snapshot = super::snapshots::ReferenceSnapshot::new(context)?;
403    for table in uqa_sql::semantics::partition::foreign_key_scan_tables(
404        context.constraints.partitions.catalog,
405        &key.constraint_table,
406    )? {
407        let rows = snapshot.table(&table)?;
408        let mut referenced = false;
409        rows.visit_doc_ids(
410            context.constraints.memory.work_mem_bytes()?,
411            context.triggers.runtime.cancellation,
412            &mut |doc_id| {
413                let Some(document) = rows.document(doc_id)? else {
414                    return Ok(true);
415                };
416                let Some(lookup) = foreign_key_lookup_values(
417                    context.constraints.partitions.catalog,
418                    &table,
419                    foreign_key,
420                    &document,
421                )?
422                else {
423                    return Ok(true);
424                };
425                if !super::references::normalized_keys_equal(
426                    context.constraints.values,
427                    &lookup.values[..ordinary],
428                    &expected[..ordinary],
429                )? {
430                    return Ok(true);
431                }
432                if !period_foreign_key_is_covered(
433                    context.constraints,
434                    foreign_key,
435                    &lookup.values,
436                    &[],
437                    None,
438                )? {
439                    referenced = true;
440                    return Ok(false);
441                }
442                Ok(true)
443            },
444        )?;
445        if referenced {
446            return Ok(true);
447        }
448    }
449    Ok(false)
450}
451
452/// `ri_ReportViolation` for a removed key that a row still references: `23503` under `NO ACTION`, `23001` under `RESTRICT`, with the key unless the current role may not read its columns.
453fn referenced_key_violation(
454    context: ConstraintContext<'_>,
455    key: &ReferencedKey,
456) -> Result<SQLError, SQLError> {
457    let relation = foreign_key_relation_name(&key.relation);
458    let referencing = foreign_key_relation_name(&key.constraint_table);
459    let columns = &key.foreign_key.ref_columns;
460    let shown = crate::mutation::constraints::foreign_key_key(
461        context,
462        &key.relation,
463        columns,
464        &key.relation,
465        columns,
466        &key.key,
467    )?;
468    let (sqlstate, message, reference) = if key.restrict {
469        (
470            "23001",
471            format!(
472                "update or delete on table \"{relation}\" violates RESTRICT setting of foreign key constraint \"{}\" on table \"{referencing}\"",
473                key.constraint_name
474            ),
475            "is referenced",
476        )
477    } else {
478        (
479            "23503",
480            format!(
481                "update or delete on table \"{relation}\" violates foreign key constraint \"{}\" on table \"{referencing}\"",
482                key.constraint_name
483            ),
484            "is still referenced",
485        )
486    };
487    Ok(SQLError::Diagnostic {
488        sqlstate: sqlstate.into(),
489        message,
490        detail: Some(match shown {
491            Some(shown) => format!("{shown} {reference} from table \"{referencing}\"."),
492            None => format!("Key {reference} from table \"{referencing}\"."),
493        }),
494        hint: None,
495    })
496}