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