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;
21
22/// Optimize and execute at the call boundary so writer promotion cannot leave a stale query generation.
23pub trait TableAsQuerySource {
24    fn optimize(&self, plan: &QueryPlan) -> Result<QueryPlan, SQLError>;
25    fn execute(&self, plan: &QueryPlan, params: &[SQLParam]) -> Result<SQLResult, SQLError>;
26}
27pub trait TableAsNamespace {
28    fn relation_exists(&self, name: &str) -> Result<bool, SQLError>;
29    fn prepare_writer(&self) -> Result<bool, SQLError>;
30}
31pub trait TableAsPublication {
32    fn create_relation(
33        &self,
34        name: &str,
35        persistence: RelationPersistence,
36        on_commit: OnCommitAction,
37        owner: &crate::catalog::security::roles::locking::RoleBinding,
38    ) -> Result<(), SQLError>;
39    fn create_vector_field(
40        &self,
41        name: &str,
42        column: &str,
43        dimensions: u32,
44    ) -> Result<bool, SQLError>;
45    fn publish_columns(&self, name: &str, columns: &[ColumnDef]) -> Result<(), SQLError>;
46    fn insert_document(
47        &self,
48        table: &str,
49        id: DocId,
50        document: Document,
51        vectors: DocumentVectors,
52    ) -> Result<(), SQLError>;
53}
54pub struct CreateTableAsContext<'a, S: Clone> {
55    pub creation: crate::schema::namespaces::relations::RelationCreationContext<'a>,
56    pub analysis_scope: &'a CteScope<S>,
57    pub routines: &'a dyn RoutineResolution,
58    pub queries: &'a dyn TableAsQuerySource,
59    pub namespace: &'a dyn TableAsNamespace,
60    pub publication: &'a dyn TableAsPublication,
61    pub vectors: &'a dyn ConstraintCatalog,
62    pub notices: &'a crate::query::NoticeQueue,
63}
64pub struct CreateTableAsExecution<'a> {
65    pub name: &'a str,
66    pub if_not_exists: bool,
67    pub column_names: &'a [String],
68    pub with_no_data: bool,
69    pub persistence: uqa_sql::ast::RelationPersistence,
70    pub on_commit: uqa_sql::ast::OnCommitAction,
71    pub query: &'a uqa_sql::plan::QueryPlan,
72    pub params: &'a [SQLParam],
73}
74
75pub fn run_create_table_as<S: Clone>(
76    context: &CreateTableAsContext<'_, S>,
77    execution: &CreateTableAsExecution<'_>,
78) -> Result<SQLResult, SQLError> {
79    let owner = context.creation.bind_owner()?;
80    // 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.
81    let temporary = context
82        .creation
83        .targets_temporary_namespace(execution.name, execution.persistence)?;
84    let temporary_privilege_error = if temporary {
85        context.creation.ensure_temporary_privilege().err()
86    } else {
87        None
88    };
89    let query_schema = crate::query::binding::analyze_query_plan_schema(
90        context.routines,
91        execution.query,
92        execution.params,
93        context.analysis_scope,
94        None,
95    )?;
96    if let Some(error) = temporary_privilege_error {
97        return Err(error);
98    }
99    let preliminary_name =
100        create_table_as_target_name(&context.creation, execution.name, temporary)?;
101    if should_skip_existing_create_table_as(context, &preliminary_name, execution)? {
102        return Ok(SQLResult::empty().with_command_tag("CREATE TABLE AS"));
103    }
104    let columns = create_table_as_columns(&query_schema, execution.column_names)?;
105    if !temporary {
106        context.creation.ensure_create(&preliminary_name)?;
107    }
108    // `DefineRelation` adjusts the persistence after the namespace's CREATE check and before it describes the columns.
109    let persistence = context
110        .creation
111        .adjusted_persistence(&preliminary_name, execution.persistence)?;
112    // create_ctas_internal describes the relation before a source tuple is read.
113    validate_create_table_as_columns(context.routines, &columns)?;
114    context.creation.retain_owner(&owner)?;
115    let executable = if execution.with_no_data {
116        None
117    } else {
118        Some(context.queries.optimize(execution.query)?)
119    };
120    let executable = executable.as_ref().unwrap_or(execution.query);
121    // 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.
122    let locking_result =
123        if !execution.with_no_data && crate::query::locking::query_has_row_locks(executable) {
124            Some(context.queries.execute(executable, execution.params)?)
125        } else {
126            None
127        };
128    if !temporary {
129        context.namespace.prepare_writer()?;
130    }
131    let name = create_table_as_target_name(&context.creation, execution.name, temporary)?;
132    if should_skip_existing_create_table_as(context, &name, execution)? {
133        return Ok(SQLResult::empty().with_command_tag("CREATE TABLE AS"));
134    }
135    let name = if temporary {
136        name
137    } else {
138        context.creation.persistent_relation_name(execution.name)?
139    };
140    let result = if execution.with_no_data {
141        None
142    } else if let Some(result) = locking_result {
143        Some(result)
144    } else {
145        Some(context.queries.execute(executable, execution.params)?)
146    };
147    if let Some(result) = &result {
148        if result.columns.len() != columns.len() {
149            return Err(SQLError::Internal(format!(
150                "CREATE TABLE AS query schema width {} changed to {} during execution",
151                columns.len(),
152                result.columns.len()
153            )));
154        }
155    }
156    create_table_as_relation(
157        context.publication,
158        &name,
159        &columns,
160        persistence,
161        execution.on_commit,
162        &owner,
163    )?;
164    let affected = result.as_ref().map_or(Ok(0), |result| {
165        materialize_create_table_as_rows(
166            context.publication,
167            context.vectors,
168            &name,
169            &columns,
170            result,
171        )
172    })?;
173    let tag = if execution.with_no_data {
174        "CREATE TABLE AS".to_string()
175    } else {
176        format!("SELECT {affected}")
177    };
178    Ok(SQLResult::from_affected(affected).with_command_tag(tag))
179}
180
181fn create_table_as_target_name(
182    namespace: &crate::schema::namespaces::relations::RelationCreationContext<'_>,
183    name: &str,
184    temporary: bool,
185) -> Result<String, SQLError> {
186    if temporary {
187        namespace.temporary_name(name)
188    } else {
189        namespace.resolve_persistent_name(name)
190    }
191}
192
193/// `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.
194fn should_skip_existing_create_table_as<S: Clone>(
195    context: &CreateTableAsContext<'_, S>,
196    name: &str,
197    execution: &CreateTableAsExecution<'_>,
198) -> Result<bool, SQLError> {
199    if !context.namespace.relation_exists(name)? {
200        return Ok(false);
201    }
202    context
203        .notices
204        .push(uqa_sql::schema::table_creation::existing_create_as_target(
205            execution.name,
206            execution.if_not_exists,
207        )?);
208    Ok(true)
209}
210
211fn create_table_as_relation(
212    publication: &dyn TableAsPublication,
213    name: &str,
214    columns: &[uqa_sql::ast::ColumnDef],
215    persistence: uqa_sql::ast::RelationPersistence,
216    on_commit: uqa_sql::ast::OnCommitAction,
217    owner: &crate::catalog::security::roles::locking::RoleBinding,
218) -> Result<(), SQLError> {
219    publication.create_relation(name, persistence, on_commit, owner)?;
220    for column in columns {
221        if let ColumnType::Vector(dimensions) | ColumnType::Tensor(dimensions) = column.ty {
222            publication.create_vector_field(name, &column.name, dimensions)?;
223        }
224    }
225    publication.publish_columns(name, columns)?;
226    Ok(())
227}
228
229fn materialize_create_table_as_rows(
230    publication: &dyn TableAsPublication,
231    vectors: &dyn ConstraintCatalog,
232    name: &str,
233    columns: &[uqa_sql::ast::ColumnDef],
234    result: &SQLResult,
235) -> Result<u64, SQLError> {
236    for (row_index, _) in result.rows.iter().enumerate() {
237        let doc_id = u64::try_from(row_index)
238            .ok()
239            .and_then(|index| index.checked_add(1))
240            .ok_or_else(|| SQLError::Internal("CREATE TABLE AS row count overflow".into()))?;
241        let mut document = Document::new();
242        for (column_index, column) in columns.iter().enumerate() {
243            let value = result
244                .value_at(row_index, column_index)
245                .cloned()
246                .ok_or_else(|| {
247                    SQLError::Internal(format!(
248                        "CREATE TABLE AS row {row_index} is missing column {column_index}"
249                    ))
250                })?;
251            document.insert(column.name.clone(), value);
252        }
253        let vectors = crate::mutation::vectors::document_vectors(vectors, name, &document)?;
254        publication.insert_document(name, doc_id, document, vectors)?;
255    }
256    u64::try_from(result.rows.len())
257        .map_err(|_| SQLError::Internal("CREATE TABLE AS row count overflow".into()))
258}