Skip to main content

uqa_execution/schema/
ctas.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! CREATE TABLE AS source execution, lock ordering, and physical row publication.
8use crate::mutation::{constraints::context::ConstraintCatalog, publication::DocumentVectors};
9use crate::query::CteScope;
10use uqa_core::DocId;
11use uqa_sql::schema::table_creation::{create_table_as_columns, validate_create_table_as_columns};
12use uqa_sql::{
13    ast::{ColumnDef, ColumnType, OnCommitAction, RelationPersistence},
14    plan::QueryPlan,
15    routines::RoutineResolution,
16    SQLError, SQLParam, SQLResult,
17};
18use uqa_storage::document_store::Document;
19
20pub mod entry;
21mod source;
22
23/// Optimize and execute at the call boundary so writer promotion cannot leave a stale query generation.
24pub trait TableAsQuerySource {
25    fn optimize(&self, plan: &QueryPlan) -> Result<uqa_sql::plan::OwnedQueryPlan, SQLError>;
26    fn execute(
27        &self,
28        plan: &QueryPlan,
29        params: &[SQLParam],
30        measurement: Option<&crate::query::measurement::QueryMeasurement>,
31    ) -> Result<SQLResult, SQLError>;
32}
33pub trait TableAsNamespace {
34    fn relation_exists(&self, name: &str) -> Result<bool, SQLError>;
35    fn prepare_writer(&self) -> Result<bool, SQLError>;
36}
37pub trait TableAsPublication {
38    fn create_relation(
39        &self,
40        name: &str,
41        persistence: RelationPersistence,
42        on_commit: OnCommitAction,
43        owner: &crate::catalog::security::roles::locking::RoleBinding,
44    ) -> Result<(), SQLError>;
45    fn create_vector_field(
46        &self,
47        name: &str,
48        column: &str,
49        dimensions: u32,
50    ) -> Result<bool, SQLError>;
51    fn publish_columns(&self, name: &str, columns: &[ColumnDef]) -> Result<(), SQLError>;
52    fn insert_document(
53        &self,
54        table: &str,
55        id: DocId,
56        document: Document,
57        vectors: DocumentVectors,
58    ) -> Result<(), SQLError>;
59}
60pub struct CreateTableAsContext<'a, S: Clone> {
61    pub creation: crate::schema::namespaces::relations::RelationCreationContext<'a>,
62    pub analysis_scope: &'a CteScope<S>,
63    pub routines: &'a dyn RoutineResolution,
64    pub queries: &'a dyn TableAsQuerySource,
65    pub namespace: &'a dyn TableAsNamespace,
66    pub publication: &'a dyn TableAsPublication,
67    pub vectors: &'a dyn ConstraintCatalog,
68    pub notices: &'a crate::query::NoticeQueue,
69}
70pub struct CreateTableAsExecution<'a> {
71    pub name: &'a str,
72    pub if_not_exists: bool,
73    pub column_names: &'a [String],
74    pub with_no_data: bool,
75    pub persistence: uqa_sql::ast::RelationPersistence,
76    pub on_commit: uqa_sql::ast::OnCommitAction,
77    pub query: &'a uqa_sql::plan::QueryPlan,
78    /// EXPLAIN's selected execution; the original query still supplies the declared schema.
79    pub explained: Option<crate::query::measurement::ExplainedQuery<'a>>,
80    pub params: &'a [SQLParam],
81}
82
83pub fn run_create_table_as<S: Clone>(
84    context: &CreateTableAsContext<'_, S>,
85    execution: &CreateTableAsExecution<'_>,
86) -> Result<SQLResult, SQLError> {
87    let owner = context.creation.bind_owner()?;
88    // PostgreSQL analyzes the CTAS source before target namespace resolution, collisions, or schema CREATE. Source execution still follows target validation, so an existing target wins over runtime expression errors and row locks.
89    let temporary = context
90        .creation
91        .targets_temporary_namespace(execution.name, execution.persistence)?;
92    let temporary_privilege_error = if temporary {
93        context.creation.ensure_temporary_privilege().err()
94    } else {
95        None
96    };
97    let query_schema = crate::query::binding::analyze_query_plan_schema(
98        context.routines,
99        execution.query,
100        execution.params,
101        context.analysis_scope,
102        None,
103    )?;
104    if let Some(error) = temporary_privilege_error {
105        return Err(error);
106    }
107    let preliminary_name =
108        create_table_as_target_name(&context.creation, execution.name, temporary)?;
109    if should_skip_existing_create_table_as(context, &preliminary_name, execution)? {
110        return Ok(SQLResult::empty().with_command_tag("CREATE TABLE AS"));
111    }
112    let columns = create_table_as_columns(&query_schema, execution.column_names)?;
113    if !temporary {
114        context.creation.ensure_create(&preliminary_name)?;
115    }
116    // `DefineRelation` adjusts the persistence after the namespace's CREATE check and before it describes the columns.
117    let persistence = context
118        .creation
119        .adjusted_persistence(&preliminary_name, execution.persistence)?;
120    // create_ctas_internal describes the relation before a source tuple is read.
121    validate_create_table_as_columns(context.routines, &columns)?;
122    context.creation.retain_owner(&owner)?;
123    let source = source::SelectedSource::new(context.queries, execution)?;
124    // A locking source must acquire and recheck every tuple before this session promotes its deferred backend transaction. Promoting first would invert the global writer and tuple-lock order against a concurrent updater. The target is checked again after promotion so a concurrent relation create still wins atomically.
125    let locking_result = if !execution.with_no_data && source.has_row_locks() {
126        Some(source.execute()?)
127    } else {
128        None
129    };
130    if !temporary {
131        context.namespace.prepare_writer()?;
132    }
133    let name = create_table_as_target_name(&context.creation, execution.name, temporary)?;
134    if should_skip_existing_create_table_as(context, &name, execution)? {
135        return Ok(SQLResult::empty().with_command_tag("CREATE TABLE AS"));
136    }
137    let name = if temporary {
138        name
139    } else {
140        context.creation.persistent_relation_name(execution.name)?
141    };
142    let result = if execution.with_no_data {
143        None
144    } else if let Some(result) = locking_result {
145        Some(result)
146    } else {
147        Some(source.execute()?)
148    };
149    if let Some(result) = &result {
150        if result.columns.len() != columns.len() {
151            return Err(SQLError::Internal(format!(
152                "CREATE TABLE AS query schema width {} changed to {} during execution",
153                columns.len(),
154                result.columns.len()
155            )));
156        }
157    }
158    create_table_as_relation(
159        context.publication,
160        &name,
161        &columns,
162        persistence,
163        execution.on_commit,
164        &owner,
165    )?;
166    let affected = result.as_ref().map_or(Ok(0), |result| {
167        materialize_create_table_as_rows(
168            context.publication,
169            context.vectors,
170            &name,
171            &columns,
172            result,
173        )
174    })?;
175    let tag = if execution.with_no_data {
176        "CREATE TABLE AS".to_string()
177    } else {
178        format!("SELECT {affected}")
179    };
180    Ok(SQLResult::from_affected(affected).with_command_tag(tag))
181}
182
183fn create_table_as_target_name(
184    namespace: &crate::schema::namespaces::relations::RelationCreationContext<'_>,
185    name: &str,
186    temporary: bool,
187) -> Result<String, SQLError> {
188    if temporary {
189        namespace.temporary_name(name)
190    } else {
191        namespace.resolve_persistent_name(name)
192    }
193}
194
195/// `CreateTableAsRelExists`: whether an existing relation of the target's name skips the statement under `IF NOT EXISTS`, which reports it with a notice; otherwise the name is taken. Both name the relation as written.
196fn should_skip_existing_create_table_as<S: Clone>(
197    context: &CreateTableAsContext<'_, S>,
198    name: &str,
199    execution: &CreateTableAsExecution<'_>,
200) -> Result<bool, SQLError> {
201    existing_target(context, name, execution.name, execution.if_not_exists)
202}
203
204/// EXPLAIN observes destination collisions before optimizing or running the source, including when ANALYZE is absent.
205pub fn explain_existing_target<S: Clone>(
206    context: &CreateTableAsContext<'_, S>,
207    name: &str,
208    if_not_exists: bool,
209    persistence: RelationPersistence,
210) -> Result<bool, SQLError> {
211    let temporary = context
212        .creation
213        .targets_temporary_namespace(name, persistence)?;
214    let resolved = create_table_as_target_name(&context.creation, name, temporary)?;
215    existing_target(context, &resolved, name, if_not_exists)
216}
217
218fn existing_target<S: Clone>(
219    context: &CreateTableAsContext<'_, S>,
220    name: &str,
221    requested_name: &str,
222    if_not_exists: bool,
223) -> Result<bool, SQLError> {
224    if !context.namespace.relation_exists(name)? {
225        return Ok(false);
226    }
227    context
228        .notices
229        .push(uqa_sql::schema::table_creation::existing_create_as_target(
230            requested_name,
231            if_not_exists,
232        )?);
233    Ok(true)
234}
235
236fn create_table_as_relation(
237    publication: &dyn TableAsPublication,
238    name: &str,
239    columns: &[uqa_sql::ast::ColumnDef],
240    persistence: uqa_sql::ast::RelationPersistence,
241    on_commit: uqa_sql::ast::OnCommitAction,
242    owner: &crate::catalog::security::roles::locking::RoleBinding,
243) -> Result<(), SQLError> {
244    publication.create_relation(name, persistence, on_commit, owner)?;
245    for column in columns {
246        if let ColumnType::Vector(dimensions) | ColumnType::Tensor(dimensions) = column.ty {
247            publication.create_vector_field(name, &column.name, dimensions)?;
248        }
249    }
250    publication.publish_columns(name, columns)?;
251    Ok(())
252}
253
254fn materialize_create_table_as_rows(
255    publication: &dyn TableAsPublication,
256    vectors: &dyn ConstraintCatalog,
257    name: &str,
258    columns: &[uqa_sql::ast::ColumnDef],
259    result: &SQLResult,
260) -> Result<u64, SQLError> {
261    for (row_index, _) in result.rows.iter().enumerate() {
262        let doc_id = u64::try_from(row_index)
263            .ok()
264            .and_then(|index| index.checked_add(1))
265            .ok_or_else(|| SQLError::Internal("CREATE TABLE AS row count overflow".into()))?;
266        let mut document = Document::new();
267        for (column_index, column) in columns.iter().enumerate() {
268            let value = result
269                .value_at(row_index, column_index)
270                .cloned()
271                .ok_or_else(|| {
272                    SQLError::Internal(format!(
273                        "CREATE TABLE AS row {row_index} is missing column {column_index}"
274                    ))
275                })?;
276            document.insert(column.name.clone(), value);
277        }
278        let vectors = crate::mutation::vectors::document_vectors(vectors, name, &document)?;
279        publication.insert_document(name, doc_id, document, vectors)?;
280    }
281    u64::try_from(result.rows.len())
282        .map_err(|_| SQLError::Internal("CREATE TABLE AS row count overflow".into()))
283}