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::{production::with_metadata, AssignmentColumnCatalog},
11    ast::{AutoIncrementKind, OverridingKind},
12    SQLError,
13};
14
15use uqa_core::memory::{Produced, ProductionControl, ProductionMap, ProductionVec};
16
17/// Retain user-supplied value provenance after a rule rewrites VALUES into a query source.
18pub fn insert_has_written_value(insert: &crate::plan::InsertPlan, position: usize) -> bool {
19    if let Some(values) = &insert.rule_values_input {
20        return values.has_written_value(position);
21    }
22    insert.source.is_some()
23        || insert.rows.iter().any(|row| {
24            row.get(position)
25                .is_some_and(|value| !matches!(value, crate::ScalarExpr::Default))
26        })
27}
28
29/// 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.
30#[must_use]
31pub fn stored_columns_for_update(
32    columns: &[crate::ast::ColumnDef],
33    updated_columns: &[String],
34) -> Vec<String> {
35    columns
36        .iter()
37        .filter(|column| {
38            column.generated.as_ref().is_some_and(|generated| {
39                generated.kind == crate::ast::GeneratedColumnKind::Stored
40                    && updated_columns.iter().any(|updated| {
41                        crate::schema::dependencies::schema_expr_references_column(
42                            &generated.expression,
43                            updated,
44                        )
45                    })
46            })
47        })
48        .map(|column| column.name.clone())
49        .collect()
50}
51
52/// How a table generates a column's value.
53#[derive(Debug, Clone, Copy, PartialEq, Eq)]
54enum Generation {
55    IdentityAlways,
56    IdentityByDefault,
57    Expression,
58}
59
60/// 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.
61pub struct GeneratedValueColumns {
62    columns: Produced<Vec<(String, Generation)>>,
63}
64
65impl GeneratedValueColumns {
66    /// The identity and generated columns of `table`; none for a relation the catalog does not describe as a table.
67    pub fn of(catalog: &dyn AssignmentColumnCatalog, table: &str) -> Result<Self, SQLError> {
68        Self::of_with_control(catalog, table, &ProductionControl::uncontrolled())
69    }
70
71    pub fn of_with_control(
72        catalog: &dyn AssignmentColumnCatalog,
73        table: &str,
74        control: &ProductionControl<'_>,
75    ) -> Result<Self, SQLError> {
76        control.check()?;
77        let mut columns = ProductionVec::new(*control);
78        with_metadata(
79            catalog,
80            table,
81            control,
82            |error| match error {
83                SQLError::Internal(message) => {
84                    match control.format(format_args!("read generated columns: {message}")) {
85                        Ok(message) => SQLError::Internal(message.into_parts().0),
86                        Err(error) => error.into(),
87                    }
88                }
89                error => error,
90            },
91            |declarations| {
92                for column in declarations {
93                    control.check()?;
94                    let generation = match column.auto_increment.as_ref() {
95                        Some(provenance)
96                            if provenance.kind == AutoIncrementKind::IdentityAlways =>
97                        {
98                            Generation::IdentityAlways
99                        }
100                        Some(provenance) if provenance.is_identity() => {
101                            Generation::IdentityByDefault
102                        }
103                        _ if column.generated.is_some() => Generation::Expression,
104                        _ => continue,
105                    };
106                    let (name, memory) = control.copy_text(&column.name)?.into_parts();
107                    columns.push_produced(control.finish((name, generation), memory)?)?;
108                }
109                Ok(())
110            },
111        )?;
112        Ok(Self {
113            columns: columns.finish()?,
114        })
115    }
116
117    /// Whether `column` is an identity column, whose value `OVERRIDING USER VALUE` leaves to its sequence.
118    #[must_use]
119    pub fn contains(&self, column: &str) -> bool {
120        self.contains_with_control(column, &ProductionControl::uncontrolled())
121            .expect("ordinary generated-column lookup")
122    }
123
124    pub fn contains_with_control(
125        &self,
126        column: &str,
127        control: &ProductionControl<'_>,
128    ) -> Result<bool, SQLError> {
129        control.check()?;
130        for (name, generation) in self.columns.iter() {
131            control.check()?;
132            if name == column
133                && matches!(
134                    generation,
135                    Generation::IdentityAlways | Generation::IdentityByDefault
136                )
137            {
138                return Ok(true);
139            }
140        }
141        Ok(false)
142    }
143
144    /// 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.
145    pub fn validate_insert<'a>(
146        &self,
147        targets: impl IntoIterator<Item = (&'a str, bool)>,
148        overriding: Option<OverridingKind>,
149    ) -> Result<(), SQLError> {
150        self.validate_insert_with_control(targets, overriding, &ProductionControl::uncontrolled())
151    }
152
153    pub fn validate_insert_with_control<'a>(
154        &self,
155        targets: impl IntoIterator<Item = (&'a str, bool)>,
156        overriding: Option<OverridingKind>,
157        control: &ProductionControl<'_>,
158    ) -> Result<(), SQLError> {
159        control.check()?;
160        let mut supplied = ProductionMap::new(*control);
161        for (column, written) in targets {
162            control.check()?;
163            if written {
164                supplied.insert_copy(column, ())?;
165            }
166        }
167        for (column, generation) in self.columns.iter() {
168            control.check()?;
169            if supplied.get(column.as_str()).is_none() {
170                continue;
171            }
172            match generation {
173                Generation::Expression => return Err(value_error(column, true, false, control)?),
174                Generation::IdentityAlways if overriding.is_none() => {
175                    return Err(value_error(column, true, true, control)?)
176                }
177                Generation::IdentityAlways | Generation::IdentityByDefault => {}
178            }
179        }
180        Ok(())
181    }
182
183    /// 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`.
184    pub fn validate_update<'a>(
185        &self,
186        assignments: impl IntoIterator<Item = (&'a str, bool)>,
187    ) -> Result<(), SQLError> {
188        self.validate_update_with_control(assignments, &ProductionControl::uncontrolled())
189    }
190
191    pub fn validate_update_with_control<'a>(
192        &self,
193        assignments: impl IntoIterator<Item = (&'a str, bool)>,
194        control: &ProductionControl<'_>,
195    ) -> Result<(), SQLError> {
196        control.check()?;
197        let mut assigned = ProductionMap::new(*control);
198        for (column, default) in assignments {
199            control.check()?;
200            if !default {
201                assigned.insert_copy(column, ())?;
202            }
203        }
204        for (column, generation) in self.columns.iter() {
205            control.check()?;
206            if assigned.get(column.as_str()).is_none() {
207                continue;
208            }
209            match generation {
210                Generation::Expression => return Err(value_error(column, false, false, control)?),
211                Generation::IdentityAlways => {
212                    return Err(value_error(column, false, true, control)?)
213                }
214                Generation::IdentityByDefault => {}
215            }
216        }
217        Ok(())
218    }
219}
220
221fn value_error(
222    column: &str,
223    insert: bool,
224    identity: bool,
225    control: &ProductionControl<'_>,
226) -> Result<SQLError, uqa_core::ValueRetentionError> {
227    let sqlstate = control.copy_text("428C9")?;
228    let message = if insert {
229        control.format(format_args!(
230            "cannot insert a non-DEFAULT value into column \"{column}\""
231        ))?
232    } else {
233        control.format(format_args!(
234            "column \"{column}\" can only be updated to DEFAULT"
235        ))?
236    };
237    let detail = if identity {
238        control.format(format_args!(
239            "Column \"{column}\" is an identity column defined as GENERATED ALWAYS."
240        ))?
241    } else {
242        control.format(format_args!("Column \"{column}\" is a generated column."))?
243    };
244    let hint = (insert && identity)
245        .then(|| control.copy_text("Use OVERRIDING SYSTEM VALUE to override."))
246        .transpose()?;
247    Ok(SQLError::Diagnostic {
248        sqlstate: sqlstate.into_parts().0,
249        message: message.into_parts().0,
250        detail: Some(detail.into_parts().0),
251        hint: hint.map(|hint| hint.into_parts().0),
252    })
253}
254
255/// The `428C9` error of a value an `INSERT` supplies for a `GENERATED ALWAYS` identity column.
256#[must_use]
257pub fn generated_always_insert_error(column: &str) -> SQLError {
258    value_error(column, true, true, &ProductionControl::uncontrolled()).unwrap_or_else(Into::into)
259}
260
261/// The `428C9` error of an assignment other than `DEFAULT` to a `GENERATED ALWAYS` identity column.
262#[must_use]
263pub fn generated_always_update_error(column: &str) -> SQLError {
264    value_error(column, false, true, &ProductionControl::uncontrolled()).unwrap_or_else(Into::into)
265}
266
267/// The `428C9` error of a value an `INSERT` supplies for a generated column.
268#[must_use]
269pub fn generated_column_insert_error(column: &str) -> SQLError {
270    value_error(column, true, false, &ProductionControl::uncontrolled()).unwrap_or_else(Into::into)
271}
272
273/// The `428C9` error of an assignment other than `DEFAULT` to a generated column.
274#[must_use]
275pub fn generated_column_update_error(column: &str) -> SQLError {
276    value_error(column, false, false, &ProductionControl::uncontrolled()).unwrap_or_else(Into::into)
277}
278
279#[cfg(test)]
280mod tests {
281    #[test]
282    fn stored_update_selection_follows_expression_inputs() {
283        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") };
284        for (targets, expected) in [
285            (vec!["a"], vec!["ga"]),
286            (vec!["b"], vec!["gb"]),
287            (vec!["a", "b"], vec!["ga", "gb"]),
288            (vec!["unrelated"], vec![]),
289            (vec!["ga", "c", "v"], vec![]),
290        ] {
291            let targets = targets.into_iter().map(str::to_owned).collect::<Vec<_>>();
292            assert_eq!(
293                super::stored_columns_for_update(&table.columns, &targets),
294                expected
295            );
296        }
297    }
298}