Skip to main content

uqa_sql/semantics/
generated_values.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! `PostgreSQL` rules for the values a statement writes to the columns whose values a table generates: identity columns and generated columns.
8
9use crate::{
10    assignment::columns::AssignmentColumnCatalog,
11    ast::{AutoIncrementKind, OverridingKind},
12    SQLError,
13};
14
15/// Retain user-supplied value provenance after a rule rewrites VALUES into a query source.
16pub fn insert_has_written_value(insert: &crate::plan::InsertPlan, position: usize) -> bool {
17    if let Some(values) = &insert.rule_values_input {
18        return values.has_written_value(position);
19    }
20    insert.source.is_some()
21        || insert.rows.iter().any(|row| {
22            row.get(position)
23                .is_some_and(|value| !matches!(value, crate::ScalarExpr::Default))
24        })
25}
26
27/// Stored expressions affected by an UPDATE's target columns. Explicitly assigning an unchanged input still selects its dependents; assigning a generated column to DEFAULT does not select it by itself. Callers with BEFORE ROW UPDATE triggers must recompute every stored expression instead.
28#[must_use]
29pub fn stored_columns_for_update(
30    columns: &[crate::ast::ColumnDef],
31    updated_columns: &[String],
32) -> Vec<String> {
33    columns
34        .iter()
35        .filter(|column| {
36            column.generated.as_ref().is_some_and(|generated| {
37                generated.kind == crate::ast::GeneratedColumnKind::Stored
38                    && updated_columns.iter().any(|updated| {
39                        crate::schema::dependencies::schema_expr_references_column(
40                            &generated.expression,
41                            updated,
42                        )
43                    })
44            })
45        })
46        .map(|column| column.name.clone())
47        .collect()
48}
49
50/// How a table generates a column's value.
51#[derive(Debug, Clone, Copy, PartialEq, Eq)]
52enum Generation {
53    IdentityAlways,
54    IdentityByDefault,
55    Expression,
56}
57
58/// The identity and generated columns of one table, in column order, read once for a statement that writes it. `PostgreSQL` applies these rules when it rewrites the statement, column by column in table order, before it reads or writes a row, so a statement whose source yields no row is rejected as well.
59pub struct GeneratedValueColumns {
60    columns: Vec<(String, Generation)>,
61}
62
63impl GeneratedValueColumns {
64    /// The identity and generated columns of `table`; none for a relation the catalog does not describe as a table.
65    pub fn of(catalog: &dyn AssignmentColumnCatalog, table: &str) -> Result<Self, SQLError> {
66        let columns = catalog
67            .try_describe_table(table)
68            .map_err(|error| SQLError::Internal(format!("read generated columns: {error}")))?
69            .unwrap_or_default()
70            .into_iter()
71            .filter_map(|column| {
72                let generation = match column.auto_increment.as_ref() {
73                    Some(provenance) if provenance.kind == AutoIncrementKind::IdentityAlways => {
74                        Generation::IdentityAlways
75                    }
76                    Some(provenance) if provenance.is_identity() => Generation::IdentityByDefault,
77                    _ if column.generated.is_some() => Generation::Expression,
78                    _ => return None,
79                };
80                Some((column.name, generation))
81            })
82            .collect();
83        Ok(Self { columns })
84    }
85
86    /// Whether `column` is an identity column, whose value `OVERRIDING USER VALUE` leaves to its sequence.
87    #[must_use]
88    pub fn contains(&self, column: &str) -> bool {
89        self.columns.iter().any(|(name, generation)| {
90            name == column
91                && matches!(
92                    generation,
93                    Generation::IdentityAlways | Generation::IdentityByDefault
94                )
95        })
96    }
97
98    /// Reject a value an `INSERT` supplies for a generated column, and for a `GENERATED ALWAYS` identity column without an `OVERRIDING` clause, which a generated column ignores. `targets` names each target column with whether some row supplies a value other than `DEFAULT` for it.
99    pub fn validate_insert<'a>(
100        &self,
101        targets: impl IntoIterator<Item = (&'a str, bool)>,
102        overriding: Option<OverridingKind>,
103    ) -> Result<(), SQLError> {
104        let supplied = targets
105            .into_iter()
106            .filter_map(|(column, supplied)| supplied.then_some(column))
107            .collect::<std::collections::BTreeSet<_>>();
108        for (column, generation) in &self.columns {
109            if !supplied.contains(column.as_str()) {
110                continue;
111            }
112            match generation {
113                Generation::Expression => return Err(generated_column_insert_error(column)),
114                Generation::IdentityAlways if overriding.is_none() => {
115                    return Err(generated_always_insert_error(column));
116                }
117                Generation::IdentityAlways | Generation::IdentityByDefault => {}
118            }
119        }
120        Ok(())
121    }
122
123    /// Reject an assignment other than `DEFAULT` to a generated column or a `GENERATED ALWAYS` identity column by an `UPDATE`, an `ON CONFLICT DO UPDATE` or a `MERGE` update. `assignments` names each assigned column with whether its value is `DEFAULT`.
124    pub fn validate_update<'a>(
125        &self,
126        assignments: impl IntoIterator<Item = (&'a str, bool)>,
127    ) -> Result<(), SQLError> {
128        let assigned = assignments
129            .into_iter()
130            .filter_map(|(column, default)| (!default).then_some(column))
131            .collect::<std::collections::BTreeSet<_>>();
132        for (column, generation) in &self.columns {
133            if !assigned.contains(column.as_str()) {
134                continue;
135            }
136            match generation {
137                Generation::Expression => return Err(generated_column_update_error(column)),
138                Generation::IdentityAlways => return Err(generated_always_update_error(column)),
139                Generation::IdentityByDefault => {}
140            }
141        }
142        Ok(())
143    }
144}
145
146/// The `428C9` error of a value an `INSERT` supplies for a `GENERATED ALWAYS` identity column.
147#[must_use]
148pub fn generated_always_insert_error(column: &str) -> SQLError {
149    SQLError::Diagnostic {
150        sqlstate: "428C9".into(),
151        message: format!("cannot insert a non-DEFAULT value into column \"{column}\""),
152        detail: Some(format!(
153            "Column \"{column}\" is an identity column defined as GENERATED ALWAYS."
154        )),
155        hint: Some("Use OVERRIDING SYSTEM VALUE to override.".into()),
156    }
157}
158
159/// The `428C9` error of an assignment other than `DEFAULT` to a `GENERATED ALWAYS` identity column.
160#[must_use]
161pub fn generated_always_update_error(column: &str) -> SQLError {
162    SQLError::Diagnostic {
163        sqlstate: "428C9".into(),
164        message: format!("column \"{column}\" can only be updated to DEFAULT"),
165        detail: Some(format!(
166            "Column \"{column}\" is an identity column defined as GENERATED ALWAYS."
167        )),
168        hint: None,
169    }
170}
171
172/// The `428C9` error of a value an `INSERT` supplies for a generated column.
173#[must_use]
174pub fn generated_column_insert_error(column: &str) -> SQLError {
175    SQLError::Diagnostic {
176        sqlstate: "428C9".into(),
177        message: format!("cannot insert a non-DEFAULT value into column \"{column}\""),
178        detail: Some(format!("Column \"{column}\" is a generated column.")),
179        hint: None,
180    }
181}
182
183/// The `428C9` error of an assignment other than `DEFAULT` to a generated column.
184#[must_use]
185pub fn generated_column_update_error(column: &str) -> SQLError {
186    SQLError::Diagnostic {
187        sqlstate: "428C9".into(),
188        message: format!("column \"{column}\" can only be updated to DEFAULT"),
189        detail: Some(format!("Column \"{column}\" is a generated column.")),
190        hint: None,
191    }
192}
193
194#[cfg(test)]
195mod tests {
196    #[test]
197    fn stored_update_selection_follows_expression_inputs() {
198        let crate::Statement::CreateTable(table) = crate::compile("CREATE TABLE t(a int,b int,unrelated int,ga int GENERATED ALWAYS AS(coalesce(a,1)) STORED,gb int GENERATED ALWAYS AS(b+2) STORED,c int GENERATED ALWAYS AS(42) STORED,v int GENERATED ALWAYS AS(b+3) VIRTUAL)").unwrap().remove(0) else { panic!("table") };
199        for (targets, expected) in [
200            (vec!["a"], vec!["ga"]),
201            (vec!["b"], vec!["gb"]),
202            (vec!["a", "b"], vec!["ga", "gb"]),
203            (vec!["unrelated"], vec![]),
204            (vec!["ga", "c", "v"], vec![]),
205        ] {
206            let targets = targets.into_iter().map(str::to_owned).collect::<Vec<_>>();
207            assert_eq!(
208                super::stored_columns_for_update(&table.columns, &targets),
209                expected
210            );
211        }
212    }
213}