uqa_sql/schema/indexes/
system_columns.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! CREATE INDEX over a system column, which `PostgreSQL` resolves like any attribute and then refuses to index.
8
9use crate::ast::{ColumnDef, CreateIndex, Expr, IndexKey, PartitionSpec};
10use crate::schema::columns::POSTGRES_SYSTEM_COLUMNS;
11use crate::schema::keys::definition::{
12    is_system_column, resolve_system_key_attribute, system_column_index,
13};
14use crate::SQLError;
15
16fn reads_system_column(expression: &Expr) -> bool {
17    POSTGRES_SYSTEM_COLUMNS.iter().any(|column| {
18        crate::schema::dependencies::schema_expr_references_column(expression, column)
19    })
20}
21
22/// Fail a CREATE INDEX that reads a system column as `DefineIndex` fails it: once the access method and each attribute are resolved and a unique index is matched against the partition key. `columns` are the table's columns and `partition` its partition key; a statement that reads no system column passes.
23pub fn reject_system_column_index(
24    columns: &[ColumnDef],
25    statement: &CreateIndex,
26    partition: Option<&PartitionSpec>,
27) -> Result<(), SQLError> {
28    let system = statement.columns.iter().any(|key| match key {
29        IndexKey::Column(name) => is_system_column(name),
30        IndexKey::Expression(expression) => reads_system_column(expression),
31    }) || statement
32        .included_columns
33        .iter()
34        .any(|name| is_system_column(name))
35        || statement
36            .predicate
37            .as_deref()
38            .is_some_and(reads_system_column);
39    if !system {
40        return Ok(());
41    }
42    let access_method = super::options::index_access_method(statement)?;
43    if statement.unique {
44        super::unique::validate_unique_index_method(statement)?;
45    }
46    let declared = |name: &str| columns.iter().any(|column| column.name == name);
47    for key in &statement.columns {
48        let IndexKey::Column(name) = key else {
49            continue;
50        };
51        if is_system_column(name) {
52            resolve_system_key_attribute(name, &access_method)?;
53        } else if !declared(name) {
54            return Err(SQLError::UnknownColumn(name.clone()));
55        }
56    }
57    if let Some(name) = statement
58        .included_columns
59        .iter()
60        .find(|name| !declared(name) && !is_system_column(name))
61    {
62        return Err(SQLError::UnknownColumn(name.clone()));
63    }
64    if let Some(partition) = partition.filter(|_| statement.unique) {
65        super::unique::validate_unique_index_partition_key(statement, partition)?;
66    }
67    Err(system_column_index())
68}
69
70#[cfg(test)]
71mod tests {
72    use super::reject_system_column_index;
73    use crate::ast::{CreateIndex, Expr, PartitionSpec, PartitionStrategy, Statement};
74
75    const SYSTEM: &str = "index creation on system columns is not supported";
76
77    fn index(sql: &str) -> CreateIndex {
78        let Statement::CreateIndex(statement) = crate::compiler::compile(sql).unwrap().remove(0)
79        else {
80            panic!("index statement");
81        };
82        statement
83    }
84
85    fn columns() -> Vec<crate::ast::ColumnDef> {
86        let Statement::CreateTable(table) =
87            crate::compiler::compile("CREATE TABLE t (a int, b int)")
88                .unwrap()
89                .remove(0)
90        else {
91            panic!("table statement");
92        };
93        table.columns
94    }
95
96    fn partition() -> PartitionSpec {
97        PartitionSpec {
98            strategy: PartitionStrategy::Range,
99            keys: vec![Expr::Column("a".into())],
100        }
101    }
102
103    fn rejected(sql: &str, partition: Option<&PartitionSpec>) -> (String, String) {
104        let error = reject_system_column_index(&columns(), &index(sql), partition).unwrap_err();
105        (
106            error.sqlstate().unwrap_or_default().to_string(),
107            error.to_string(),
108        )
109    }
110
111    fn unordered(ty: &str, method: &str) -> (String, String) {
112        (
113            "42704".into(),
114            format!("data type {ty} has no default operator class for access method \"{method}\""),
115        )
116    }
117
118    #[test]
119    fn an_index_over_a_system_column_fails_after_its_attributes_are_resolved() {
120        let system = ("0A000".to_string(), SYSTEM.to_string());
121        let missing = (
122            "42703".to_string(),
123            "column \"zz\" does not exist".to_string(),
124        );
125        for (sql, expected) in [
126            ("CREATE INDEX ON t (ctid)", system.clone()),
127            ("CREATE INDEX ON t (tableoid)", system.clone()),
128            ("CREATE INDEX ON t (xmin)", unordered("xid", "btree")),
129            ("CREATE INDEX ON t (cmax)", unordered("cid", "btree")),
130            (
131                "CREATE INDEX ON t USING gin (ctid)",
132                unordered("tid", "gin"),
133            ),
134            ("CREATE INDEX ON t (a) INCLUDE (xmin)", system.clone()),
135            (
136                "CREATE INDEX ON t (a) WHERE ctid IS NOT NULL",
137                system.clone(),
138            ),
139            ("CREATE INDEX ON t (a, (ctid::text))", system.clone()),
140            ("CREATE INDEX ON t (zz, ctid)", missing.clone()),
141            ("CREATE INDEX ON t (xmin, zz)", unordered("xid", "btree")),
142            ("CREATE UNIQUE INDEX ON t (b) INCLUDE (ctid, zz)", missing),
143        ] {
144            assert_eq!(rejected(sql, None), expected, "{sql}");
145        }
146        for sql in [
147            "CREATE INDEX ON t (a)",
148            "CREATE UNIQUE INDEX ON t (b, (a + 1)) INCLUDE (a) WHERE a > 0",
149        ] {
150            reject_system_column_index(&columns(), &index(sql), Some(&partition())).unwrap();
151        }
152    }
153
154    #[test]
155    fn a_unique_index_is_matched_against_the_partition_key_before_its_system_columns() {
156        let partition = partition();
157        assert_eq!(
158            rejected("CREATE UNIQUE INDEX ON t (ctid)", Some(&partition)),
159            (
160                "0A000".into(),
161                "unique constraint on partitioned table must include all partitioning columns"
162                    .into()
163            )
164        );
165        for sql in [
166            "CREATE UNIQUE INDEX ON t (a, ctid)",
167            "CREATE INDEX ON t (ctid)",
168        ] {
169            assert_eq!(
170                rejected(sql, Some(&partition)),
171                ("0A000".into(), SYSTEM.into()),
172                "{sql}"
173            );
174        }
175    }
176}
uqa_sql/schema/indexes/system_columns.rs

uqa_sql/schema/indexes/
system_columns.rs
