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_coverage, ConstraintContext};
26
27/// A foreign key check or referential action queued with the AFTER ROW triggers of a written row.
28#[derive(Debug, Clone)]
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)]
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)]
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)]
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    key: Vec<Value>,
60    restrict: bool,
61}
62
63/// The prefix of an internal trigger's name that checks a referencing row.
64const CHECK_TRIGGER: &str = "RI_ConstraintTrigger_c_";
65/// The prefix of an internal trigger's name that acts on a referenced key.
66const ACTION_TRIGGER: &str = "RI_ConstraintTrigger_a_";
67
68impl ForeignKeyCheck {
69    fn trigger_prefix(&self) -> &'static str {
70        match self.kind {
71            CheckKind::Referencing(_) => CHECK_TRIGGER,
72            CheckKind::Referenced(_) | CheckKind::Action(_) => ACTION_TRIGGER,
73        }
74    }
75
76    /// 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.
77    pub fn precedes_trigger(&self, name: &str) -> bool {
78        self.trigger_prefix().as_bytes() <= name.as_bytes()
79    }
80
81    /// 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.
82    pub fn sort(checks: &mut [Self]) {
83        checks.sort_by(|left, right| {
84            (left.trigger_prefix(), left.ordinal).cmp(&(right.trigger_prefix(), right.ordinal))
85        });
86    }
87}
88
89fn referencing_key_changed(columns: &[String], old: &Document, new: &Document) -> bool {
90    columns
91        .iter()
92        .any(|column| old.get(column) != new.get(column))
93}
94
95/// 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.
96pub(super) fn referenced_key_changed(columns: &[String], old: &Document, new: &Document) -> bool {
97    columns.iter().any(|column| {
98        !old.get(column)
99            .unwrap_or(&Value::Null)
100            .has_same_representation(new.get(column).unwrap_or(&Value::Null))
101    })
102}
103
104/// 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.
105pub fn referencing_checks(
106    context: ConstraintContext<'_>,
107    table: &str,
108    doc_id: DocId,
109    new: &Document,
110    old: Option<&Document>,
111) -> Result<Vec<ForeignKeyCheck>, SQLError> {
112    if context.referrers.session_replication_role_is_replica() {
113        return Ok(Vec::new());
114    }
115    let foreign_keys = context
116        .catalog
117        .try_foreign_keys(table)
118        .map_err(|error| SQLError::Internal(format!("read foreign keys of `{table}`: {error}")))?;
119    let mut checks = Vec::new();
120    for (ordinal, foreign_key) in foreign_keys.into_iter().enumerate() {
121        if !foreign_key.enforced
122            || old.is_some_and(|old| !referencing_key_changed(&foreign_key.local_columns, old, new))
123        {
124            continue;
125        }
126        let nulls = foreign_key
127            .local_columns
128            .iter()
129            .filter(|column| matches!(new.get(*column).unwrap_or(&Value::Null), Value::Null))
130            .count();
131        let exempt = match foreign_key.match_type {
132            uqa_sql::ast::ForeignKeyMatch::Simple => nulls > 0,
133            uqa_sql::ast::ForeignKeyMatch::Full => nulls == foreign_key.local_columns.len(),
134        };
135        if exempt
136            || context
137                .transactions
138                .foreign_key_is_deferred(table, &foreign_key)?
139        {
140            continue;
141        }
142        checks.push(ForeignKeyCheck {
143            ordinal,
144            kind: CheckKind::Referencing(ReferencingRow {
145                table: table.to_string(),
146                doc_id,
147                foreign_key: Arc::new(foreign_key),
148            }),
149        });
150    }
151    Ok(checks)
152}
153
154/// Which of the foreign keys that reference a row's partition or its ancestors a change of the row fires.
155#[derive(Clone, Copy, PartialEq, Eq)]
156enum ReferencedKeys {
157    /// Every one: an ordinary delete or update.
158    All,
159    /// 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`).
160    OfPartition,
161    /// Those that reference an ancestor: the update a row's move to another partition fires on the table the `UPDATE` named (`ExecCrossPartitionUpdateForeignKey`).
162    OfAncestors,
163}
164
165/// 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.
166pub fn referenced_checks(
167    context: ConstraintContext<'_>,
168    table: &str,
169    old: &Document,
170    new: Option<&Document>,
171    moved_through: Option<&str>,
172) -> Result<Vec<ForeignKeyCheck>, SQLError> {
173    let keys = if moved_through.is_some() {
174        ReferencedKeys::OfAncestors
175    } else {
176        ReferencedKeys::All
177    };
178    collect_referenced_checks(context, table, old, new, moved_through, keys)
179}
180
181/// 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.
182pub fn moved_row_delete_checks(
183    context: ConstraintContext<'_>,
184    table: &str,
185    old: &Document,
186) -> Result<Vec<ForeignKeyCheck>, SQLError> {
187    collect_referenced_checks(context, table, old, None, None, ReferencedKeys::OfPartition)
188}
189
190fn collect_referenced_checks(
191    context: ConstraintContext<'_>,
192    table: &str,
193    old: &Document,
194    new: Option<&Document>,
195    moved_through: Option<&str>,
196    keys: ReferencedKeys,
197) -> Result<Vec<ForeignKeyCheck>, SQLError> {
198    let resolve = |name: &str| {
199        context
200            .partitions
201            .catalog
202            .try_resolve_table_name(name)
203            .map_err(SQLError::Internal)
204            .map(|resolved| resolved.unwrap_or_else(|| name.to_string()))
205    };
206    let partition = if keys == ReferencedKeys::All {
207        String::new()
208    } else {
209        resolve(table)?
210    };
211    let mut checks = Vec::new();
212    for (ordinal, (constraint_table, foreign_key)) in
213        referrers_to_for_actions(context.referrers, table)?
214            .into_iter()
215            .enumerate()
216    {
217        if keys != ReferencedKeys::All
218            && (resolve(&foreign_key.ref_table)? == partition)
219                != (keys == ReferencedKeys::OfPartition)
220        {
221            continue;
222        }
223        let action = if new.is_some() {
224            foreign_key.on_update
225        } else {
226            foreign_key.on_delete
227        };
228        if !foreign_key.enforced
229            || new.is_some_and(|new| !referenced_key_changed(&foreign_key.ref_columns, old, new))
230        {
231            continue;
232        }
233        let key = foreign_key
234            .ref_columns
235            .iter()
236            .map(|column| old.get(column).cloned().unwrap_or(Value::Null))
237            .collect::<Vec<_>>();
238        let null_key = key.iter().any(|value| matches!(value, Value::Null));
239        if matches!(
240            action,
241            ForeignKeyAction::Cascade | ForeignKeyAction::SetNull | ForeignKeyAction::SetDefault
242        ) {
243            // An update of a key holding a NULL needs no action; a delete queues one whatever the key.
244            if new.is_some() && null_key {
245                continue;
246            }
247            let new_key = new.map(|new| {
248                foreign_key
249                    .ref_columns
250                    .iter()
251                    .map(|column| new.get(column).cloned().unwrap_or(Value::Null))
252                    .collect()
253            });
254            checks.push(ForeignKeyCheck {
255                ordinal,
256                kind: CheckKind::Action(Box::new(super::cascades::ReferentialAction {
257                    constraint_table,
258                    foreign_key: Arc::new(foreign_key),
259                    key,
260                    new_key,
261                })),
262            });
263            continue;
264        }
265        if null_key {
266            continue;
267        }
268        let firing = uqa_sql::schema::referenced_partitions::firing_constraint(
269            context.partitions.catalog,
270            table,
271            moved_through,
272            &foreign_key,
273        )?;
274        let restrict = action == ForeignKeyAction::Restrict;
275        if !restrict
276            && context.transactions.referenced_key_is_deferred(
277                &constraint_table,
278                &foreign_key,
279                firing.derived,
280            )?
281        {
282            continue;
283        }
284        let relation = firing.relation.to_string();
285        let constraint_name = firing.name(&foreign_key).to_string();
286        checks.push(ForeignKeyCheck {
287            ordinal,
288            kind: CheckKind::Referenced(Box::new(ReferencedKey {
289                constraint_table,
290                relation,
291                constraint_name,
292                foreign_key: Arc::new(foreign_key),
293                key,
294                restrict,
295            })),
296        });
297    }
298    Ok(checks)
299}
300
301/// 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`.
302pub fn run_foreign_key_check<S: Clone + 'static>(
303    context: &crate::mutation::statement::MutationExecutionContext<'_, S>,
304    check: &ForeignKeyCheck,
305    queue: &crate::mutation::triggers::queue::AfterTriggerQueue,
306) -> Result<(), SQLError> {
307    let referential = &context.preparation.referential;
308    match &check.kind {
309        CheckKind::Referencing(row) => check_referencing_row(referential.constraints, row),
310        CheckKind::Referenced(key) => check_referenced_key(referential, key),
311        CheckKind::Action(action) => {
312            super::cascades::run_referential_action(context, action, queue)
313        }
314    }
315}
316
317/// `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`.
318fn check_referencing_row(
319    context: ConstraintContext<'_>,
320    row: &ReferencingRow,
321) -> Result<(), SQLError> {
322    let Some(document) = context.reads.get_document(&row.table, row.doc_id)? else {
323        return Ok(());
324    };
325    let Some(lookup) = foreign_key_lookup_values(
326        context.partitions.catalog,
327        &row.table,
328        &row.foreign_key,
329        &document,
330    )?
331    else {
332        return Ok(());
333    };
334    crate::mutation::constraints::require_foreign_key_parent(
335        context,
336        &row.table,
337        &row.foreign_key,
338        &lookup,
339        &document,
340    )
341}
342
343/// `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.
344fn check_referenced_key<S: Clone + 'static>(
345    context: &ReferentialContext<'_, S>,
346    key: &ReferencedKey,
347) -> Result<(), SQLError> {
348    let foreign_key = key.foreign_key.as_ref();
349    context.locking.session.lock_relation(
350        &key.constraint_table,
351        crate::row_locks::RelationLockMode::RowShare,
352    )?;
353    if !key.restrict
354        && !foreign_key.period
355        && crate::mutation::constraints::find_exact_foreign_key_parent(
356            context.constraints,
357            foreign_key,
358            &key.key,
359        )?
360        .is_some()
361    {
362        return Ok(());
363    }
364    let comparison = foreign_key_comparison_types(
365        context.constraints.partitions.catalog,
366        &key.constraint_table,
367        foreign_key,
368    )?;
369    let expected = comparison.normalize_referenced(key.key.clone())?;
370    let referenced = if foreign_key.period {
371        period_key_referenced(context, key, &expected)?
372    } else {
373        !super::referencing_rows(
374            context,
375            &key.constraint_table,
376            foreign_key,
377            &comparison,
378            &expected,
379            if key.restrict {
380                ForeignKeyAction::Restrict
381            } else {
382                ForeignKeyAction::NoAction
383            },
384        )?
385        .is_empty()
386    };
387    if referenced {
388        return Err(referenced_key_violation(context.constraints, key)?);
389    }
390    Ok(())
391}
392
393/// 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.
394fn period_key_referenced<S: Clone + 'static>(
395    context: &ReferentialContext<'_, S>,
396    key: &ReferencedKey,
397    expected: &[Value],
398) -> Result<bool, SQLError> {
399    let foreign_key = key.foreign_key.as_ref();
400    let ordinary = expected.len().saturating_sub(1);
401    let snapshot = super::snapshots::ReferenceSnapshot::new(context)?;
402    for table in uqa_sql::semantics::partition::foreign_key_scan_tables(
403        context.constraints.partitions.catalog,
404        &key.constraint_table,
405    )? {
406        let rows = snapshot.table(&table)?;
407        for doc_id in rows.doc_ids()? {
408            let Some(document) = rows.document(doc_id)? else {
409                continue;
410            };
411            let Some(lookup) = foreign_key_lookup_values(
412                context.constraints.partitions.catalog,
413                &table,
414                foreign_key,
415                &document,
416            )?
417            else {
418                continue;
419            };
420            if lookup.values[..ordinary] != expected[..ordinary] {
421                continue;
422            }
423            if !period_foreign_key_coverage(
424                context.constraints,
425                foreign_key,
426                &lookup.values,
427                &[],
428                None,
429            )?
430            .0
431            {
432                return Ok(true);
433            }
434        }
435    }
436    Ok(false)
437}
438
439/// `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.
440fn referenced_key_violation(
441    context: ConstraintContext<'_>,
442    key: &ReferencedKey,
443) -> Result<SQLError, SQLError> {
444    let relation = foreign_key_relation_name(&key.relation);
445    let referencing = foreign_key_relation_name(&key.constraint_table);
446    let columns = &key.foreign_key.ref_columns;
447    let shown = crate::mutation::constraints::foreign_key_key(
448        context,
449        &key.relation,
450        columns,
451        &key.relation,
452        columns,
453        &key.key,
454    )?;
455    let (sqlstate, message, reference) = if key.restrict {
456        (
457            "23001",
458            format!(
459                "update or delete on table \"{relation}\" violates RESTRICT setting of foreign key constraint \"{}\" on table \"{referencing}\"",
460                key.constraint_name
461            ),
462            "is referenced",
463        )
464    } else {
465        (
466            "23503",
467            format!(
468                "update or delete on table \"{relation}\" violates foreign key constraint \"{}\" on table \"{referencing}\"",
469                key.constraint_name
470            ),
471            "is still referenced",
472        )
473    };
474    Ok(SQLError::Diagnostic {
475        sqlstate: sqlstate.into(),
476        message,
477        detail: Some(match shown {
478            Some(shown) => format!("{shown} {reference} from table \"{referencing}\"."),
479            None => format!("Key {reference} from table \"{referencing}\"."),
480        }),
481        hint: None,
482    })
483}