Skip to main content

uqa_execution/mutation/insert/
table.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Table INSERT scheduling, snapshot reads, row staging, and publication.
8
9mod query;
10use super::{
11    codec::{
12        decode_prepared_insert_spill_row, prepared_insert_spill_schema,
13        push_prepared_insert_spill_row, PreparedInsertSpillRow,
14    },
15    known_new::KnownNewInserts,
16    rows::prepare_values_insert_row,
17    source::{InsertSelectConsumer, InsertSelectIdentity, PreparedInsertSelect},
18    supplied_identities::SuppliedIdentities,
19    triggers::insert_statement_events,
20};
21use crate::mutation::output::MutationOutput;
22use crate::mutation::statement::context::{with_mutation_snapshot, MutationStatementContext};
23use crate::{
24    mutation::{
25        assignment::{
26            apply_missing_column_defaults, eval_mutation_assignment, MutationAssignmentTarget,
27        },
28        command_scope::MutationOverlayScope,
29        conflict::update::InsertConflictLocks,
30        constraints::{partition_insert_target, ConstraintStatement},
31        errors::dml_storage_error,
32        identity::{
33            insert_identity_columns, persist_auto_increment_identity,
34            prepare_auto_increment_identity, prepare_insert_identity,
35        },
36        publication::{apply_validated_prepared_insert, finish_mutation_publication},
37        returning::{dml_returning_result, DmlReturningShape},
38        statement_end,
39        triggers::queue::StatementEvent,
40    },
41    query::CteScope,
42};
43use std::{rc::Rc, sync::Arc};
44use uqa_sql::assignment::columns::validate_mutation_targets;
45use uqa_sql::{
46    plan::{ConflictActionPlan, ConflictPlan, InsertPlan, QueryPlan},
47    semantics::{
48        conflict::InferenceContext,
49        returning::{validate_returning_alias_relations, ReturningAnalysisContext},
50    },
51    SQLError, SQLParam,
52};
53use uqa_storage::document_store::Document;
54
55/// SQL analysis inputs and the planner's positional source-output rewrite for one INSERT.
56pub struct InsertPlanning<'a> {
57    pub inference: InferenceContext<'a>,
58    pub returning: ReturningAnalysisContext<'a>,
59    pub prune_source_outputs: crate::mutation::views::commands::SourceOutputPruning,
60}
61
62#[expect(clippy::too_many_lines, reason = "preserves DML lock and event order")]
63pub fn run_table_insert<S: Clone + Send + Sync + 'static>(
64    context: &MutationStatementContext<'_, S>,
65    planning: InsertPlanning<'_>,
66    stmt: &InsertPlan,
67    params: &[SQLParam],
68    inherited_ctes: Option<&CteScope<S>>,
69) -> Result<MutationOutput, SQLError> {
70    let mutation = &context.mutation;
71    let preparation = mutation.preparation;
72    let assignment = preparation.referential.assignment;
73    let triggers = preparation.referential.triggers;
74    let (statement_commands, _running_statement) =
75        statement_end::statement_commands(inherited_ctes);
76    let _trigger_scope = crate::mutation::triggers::TriggerStatementScope::enter();
77    preparation.referential.locking.session.lock_relation(
78        &stmt.table,
79        crate::row_locks::RelationLockMode::RowExclusive,
80    )?;
81    let insert_rules = mutation
82        .rules
83        .rules
84        .analysis
85        .rules
86        .rules_for(&stmt.table, uqa_sql::ast::RuleEvent::Insert)?;
87    let has_insert_rules = !insert_rules.is_empty();
88    let has_view_insert_rules = !stmt.view_rule_relations.is_empty();
89    let has_any_insert_rules = has_insert_rules || has_view_insert_rules;
90    if stmt.on_conflict.is_some()
91        && (has_insert_rules
92            || !mutation
93                .rules
94                .rules
95                .analysis
96                .rules
97                .rules_for(&stmt.table, uqa_sql::ast::RuleEvent::Update)?
98                .is_empty())
99    {
100        return Err(SQLError::Routine {
101            sqlstate: "0A000".into(),
102            message: "INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules".into(),
103        });
104    }
105    validate_returning_alias_relations(&stmt.target_qualifier, &stmt.returning_aliases, None)?;
106    uqa_sql::semantics::rules::validate_rule_returning_contract(
107        mutation.rules.rules.analysis.rules,
108        &stmt.table,
109        uqa_sql::ast::RuleEvent::Insert,
110        !stmt.returning.is_empty(),
111    )?;
112    if let Some(view_returning) = &stmt.view_rule_returning {
113        uqa_sql::semantics::rules::validate_rule_returning_contract(
114            mutation.rules.rules.analysis.rules,
115            &view_returning.relation,
116            uqa_sql::ast::RuleEvent::Insert,
117            !view_returning.returning.is_empty(),
118        )?;
119    }
120    let prepared_statement = uqa_sql::semantics::conflict::prepare_inference_predicate(
121        planning.inference,
122        stmt,
123        params,
124    )?;
125    let stmt = prepared_statement.as_ref();
126    if let Some(conflict) = stmt.on_conflict.as_ref() {
127        uqa_sql::semantics::conflict::validate_conflict_target(
128            planning.inference.catalog,
129            &stmt.table,
130            conflict,
131        )?;
132    }
133    let conflict_update_columns = if let Some(ConflictPlan {
134        action: ConflictActionPlan::Update { assignments, .. },
135        ..
136    }) = stmt.on_conflict.as_ref()
137    {
138        validate_mutation_targets(
139            assignment.columns,
140            &stmt.table,
141            assignments
142                .iter()
143                .flat_map(|assignment| assignment.target.targets()),
144            "INSERT ON CONFLICT DO UPDATE",
145            false,
146        )?;
147        Some(
148            assignments
149                .iter()
150                .flat_map(|assignment| assignment.target.column_names())
151                .map(str::to_owned)
152                .collect::<Vec<_>>(),
153        )
154    } else {
155        None
156    };
157    if stmt.view_rule_relations.is_empty() && !stmt.columns.is_empty() {
158        validate_mutation_targets(
159            assignment.columns,
160            &stmt.table,
161            stmt.columns.iter(),
162            "INSERT",
163            true,
164        )?;
165    }
166    if stmt.view_rule_relations.is_empty() {
167        super::identity_targets::validate_insert_identity_targets(mutation.identities, stmt)?;
168    }
169    uqa_sql::semantics::returning::validate_insert_returning(
170        planning.returning,
171        stmt,
172        params,
173        inherited_ctes.map(|scope| scope as &dyn uqa_sql::semantics::returning::ReturningScope),
174    )?;
175    uqa_sql::semantics::mutation_privileges::ensure_insert_target_privileges(
176        mutation.privileges,
177        stmt,
178        conflict_update_columns.as_deref(),
179    )?;
180    // The columns the statement supplies, which its constraint violations show to a role that may not read the table.
181    let supplied_columns =
182        uqa_sql::semantics::mutation_privileges::insert_target_columns(mutation.privileges, stmt)?
183            .into_iter()
184            .chain(conflict_update_columns.iter().flatten().cloned())
185            .collect::<Vec<_>>();
186    let statement_relation = crate::mutation::constraints::statement_relation(
187        preparation.referential.constraints,
188        &stmt.table,
189    )?;
190    let statement = ConstraintStatement::new(&statement_relation, &supplied_columns);
191    let view_original_query = !stmt.view_rule_relations.iter().try_fold(
192        false,
193        |suppressed, relation| -> Result<bool, SQLError> {
194            Ok(suppressed
195                || mutation
196                    .rules
197                    .rules
198                    .analysis
199                    .rules
200                    .rules_for(relation, uqa_sql::ast::RuleEvent::Insert)?
201                    .iter()
202                    .any(|rule| rule.definition.instead && rule.definition.condition.is_none()))
203        },
204    )?;
205    let insert_original_query = view_original_query
206        && !insert_rules
207            .iter()
208            .any(|rule| rule.definition.instead && rule.definition.condition.is_none());
209    if insert_original_query {
210        if !stmt.view_rule_relations.is_empty() {
211            super::identity_targets::validate_insert_identity_targets(mutation.identities, stmt)?;
212        }
213        crate::mutation::routine_calls::insert(context, stmt, params, inherited_ctes)?;
214    }
215    let has_before_insert_statement_trigger = insert_original_query
216        && !triggers
217            .catalog
218            .triggers_for(
219                &stmt.table,
220                uqa_sql::ast::TriggerTiming::Before,
221                uqa_sql::ast::TriggerEvent::Insert,
222                false,
223                &[],
224            )?
225            .is_empty();
226    let has_before_update_statement_trigger =
227        if let Some(columns) = conflict_update_columns.as_deref() {
228            !triggers
229                .catalog
230                .triggers_for(
231                    &stmt.table,
232                    uqa_sql::ast::TriggerTiming::Before,
233                    uqa_sql::ast::TriggerEvent::Update,
234                    false,
235                    columns,
236                )?
237                .is_empty()
238        } else {
239            false
240        };
241    let statement_snapshot = match inherited_ctes.and_then(CteScope::command_cte_snapshot) {
242        Some(snapshot) => Some(snapshot),
243        None if has_before_insert_statement_trigger
244            || has_before_update_statement_trigger
245            || stmt.ctes.iter().any(|cte| cte.body.modifies_data()) =>
246        {
247            Some(std::sync::Arc::new(context.snapshots.capture()?))
248        }
249        None => None,
250    };
251    if insert_original_query {
252        statement_commands.after_triggers().fire_before_statement(
253            &triggers,
254            &StatementEvent::new(&stmt.table, uqa_sql::ast::TriggerEvent::Insert, &[]),
255        )?;
256    }
257    if let Some(columns) = conflict_update_columns.as_deref() {
258        statement_commands.after_triggers().fire_before_statement(
259            &triggers,
260            &StatementEvent::new(&stmt.table, uqa_sql::ast::TriggerEvent::Update, columns),
261        )?;
262    }
263    let mut statement_scope = None;
264    let mut execute_read =
265        |read_context: &MutationStatementContext<'_, S>| -> Result<MutationOutput, SQLError> {
266            let read_assignment = read_context.mutation.preparation.referential.assignment;
267            let scope = statement_scope.insert(read_context.mutation.scopes.command_scope(
268                stmt.statement_privilege_subject.as_ref(),
269                stmt.relations_bound,
270            )?);
271            if let Some(parent) = inherited_ctes {
272                scope.inherit_cte_bindings(parent);
273            }
274            scope.set_statement_commands(Arc::clone(&statement_commands));
275            scope.set_command_cte_snapshot(statement_snapshot.clone());
276            crate::query::cte::materialize_command_ctes(
277                context.query.source.ctes,
278                &stmt.ctes,
279                |control| {
280                    uqa_sql::semantics::primary_command_cte_references_with_control(
281                        &stmt.ctes,
282                        stmt.query_inputs(),
283                        None,
284                        control,
285                    )
286                },
287                params,
288                scope,
289            )?;
290            scope.scalar_subqueries = stmt.subqueries.as_slice().into();
291            let query_rules = (has_any_insert_rules || stmt.rule_values_input.is_some())
292                .then(|| {
293                    uqa_sql::semantics::rules::binding::InsertRuleQueries::bind(
294                        mutation.rules.rules.analysis,
295                        stmt,
296                        &|table| mutation.rules.rules.security.privilege_subject(table),
297                        scope.privilege_subject()?,
298                    )
299                })
300                .transpose()?;
301            if let Some(queries) = &query_rules {
302                if queries.original.is_none() {
303                    return crate::mutation::rules::run_insert_query_rules(
304                        &mutation.rules.rules,
305                        preparation.returning,
306                        queries,
307                        params,
308                        scope,
309                        planning.prune_source_outputs,
310                    );
311                }
312            }
313            // Resolve the column that names an inserted row: the table's single PRIMARY KEY column, whether a sequence generates its values or not, and otherwise the conventional legacy `id` slot of a table without declared columns. Both VALUES and SELECT sources must derive the internal doc id from this same column or later primary-key rewrites can address a different row than the one that was inserted.
314            let (auto_id_col, id_column, identity_source) =
315                insert_identity_columns(mutation.identities, &stmt.table, "INSERT")?;
316            // INSERT ... SELECT: the query executor feeds each positional physical row directly into the INSERT sink. Ordinary source scans and scalar subqueries retain the statement snapshot, while a VOLATILE callback observes the logical mutations staged by preceding rows of this command.
317            if let Some(source) = stmt.source.as_deref().or_else(|| {
318                query_rules
319                    .as_ref()
320                    .and_then(|queries| queries.original.as_deref())
321            }) {
322                let output = query::run(query::InsertQueryExecution {
323                    context,
324                    read_context,
325                    stmt,
326                    source: query_rules
327                        .as_ref()
328                        .and_then(|queries| queries.original.as_deref())
329                        .unwrap_or(source),
330                    params,
331                    scope,
332                    identity: InsertSelectIdentity {
333                        auto_id_column: auto_id_col,
334                        id_column,
335                        identity_source,
336                    },
337                    conflict_update_columns: conflict_update_columns.as_deref(),
338                    statement_commands: &statement_commands,
339                })?;
340                if let Some(queries) = &query_rules {
341                    crate::mutation::rules::run_insert_query_rules(
342                        &mutation.rules.rules,
343                        preparation.returning,
344                        queries,
345                        params,
346                        scope,
347                        planning.prune_source_outputs,
348                    )?;
349                }
350                return Ok(output);
351            }
352
353            let implicit_columns = stmt.columns.is_empty();
354            let columns: Vec<uqa_sql::ast::AssignmentTarget<uqa_sql::ScalarExpr>> =
355                if implicit_columns {
356                    // INSERT without explicit column list: project the table schema.
357                    preparation
358                        .returning
359                        .catalog
360                        .try_table_columns(&stmt.table)
361                        .map_err(|error| dml_storage_error("INSERT", error))?
362                        .into_iter()
363                        .map(Into::into)
364                        .collect()
365                } else {
366                    stmt.columns.clone()
367                };
368            if view_original_query {
369                validate_mutation_targets(
370                    assignment.columns,
371                    &stmt.table,
372                    columns.iter(),
373                    "INSERT",
374                    true,
375                )?;
376            }
377
378            // No explicit id and no auto-increment column: allocate a synthetic u64 doc_id at insert time. Every table has an implicit doc_id even when the schema declares no primary key.
379
380            let mut affected = 0u64;
381            let mut returning_rows = crate::mutation::returning::ReturningRows::new(
382                context.query.source.relational.runtime,
383            )?;
384            let cancel = context.query.source.relational.runtime.cancellation_token();
385            // Evaluate, validate, and stage every VALUES row before writer promotion. A scalar subquery inside VALUES may carry FOR UPDATE, so holding the backend writer during that wait would fabricate a deadlock. Ordinary subqueries retain the statement snapshot while VOLATILE functions read the logical overlay left by preceding rows, matching PostgreSQL 18 command visibility.
386            let snapshot_scope = scope.returning_statement_snapshot_scope();
387            let overlay = MutationOverlayScope::new(mutation.state);
388            let mut conflict_locks = InsertConflictLocks::new(&preparation.referential);
389            let prepared_schema = prepared_insert_spill_schema();
390            let mut prepared_rows = crate::SpillBuffer::new(
391                crate::query::projection::physical_work_mem_bytes(
392                    context.query.source.relational.runtime,
393                )?
394                .max(1),
395            );
396            let mut supplied_identities = SuppliedIdentities::default();
397            let mut events = crate::mutation::events::MutationEventQueue::default();
398            let mut has_prepared_effect = false;
399            let mut has_prepared_auto_identity = false;
400            let discarded_identities = super::identity_targets::user_value_identity_columns(
401                mutation.identities,
402                stmt,
403                columns.iter().map(|column| column.column.as_str()),
404            )?;
405            for row in &stmt.rows {
406                cancel.check()?;
407                if row.len() > columns.len() || (!implicit_columns && row.len() != columns.len()) {
408                    return Err(SQLError::Routine {
409                        sqlstate: "42601".into(),
410                        message: if row.len() > columns.len() {
411                            "INSERT has more expressions than target columns"
412                        } else {
413                            "INSERT has more target columns than expressions"
414                        }
415                        .into(),
416                    });
417                }
418                let mut document = Document::new();
419                for (i, col) in columns.iter().take(row.len()).enumerate() {
420                    if discarded_identities.contains(&col.column) {
421                        continue;
422                    }
423                    let value = eval_mutation_assignment(
424                        read_assignment,
425                        &snapshot_scope,
426                        MutationAssignmentTarget {
427                            table: &stmt.table,
428                            target: col,
429                            current: document.get(&col.column),
430                            final_column_write: !columns[i + 1..]
431                                .iter()
432                                .any(|next| next.column == col.column),
433                            action: "INSERT",
434                            new_row: true,
435                        },
436                        &row[i],
437                        None,
438                        params,
439                    )?;
440                    if let Some(value) = value {
441                        document.insert(col.column.clone(), value);
442                    }
443                }
444                apply_missing_column_defaults(assignment, &stmt.table, &mut document, params)?;
445                let prepared_auto_identity = prepare_auto_increment_identity(
446                    mutation.identities,
447                    &stmt.table,
448                    &id_column,
449                    identity_source,
450                    auto_id_col.as_deref(),
451                    &mut document,
452                    "prepare INSERT identity",
453                )?;
454                has_prepared_auto_identity |= prepared_auto_identity.is_some();
455                let target_table = partition_insert_target(
456                    preparation.referential.constraints,
457                    statement,
458                    &stmt.table,
459                    &document,
460                    params,
461                    stmt.include_descendants,
462                )?;
463                preparation.referential.locking.session.lock_relation(
464                    &target_table,
465                    crate::row_locks::RelationLockMode::RowExclusive,
466                )?;
467                let insert_identity = match prepared_auto_identity {
468                    Some(identity) => identity,
469                    None => prepare_insert_identity(
470                        mutation.identities,
471                        &target_table,
472                        &id_column,
473                        identity_source,
474                        None,
475                        &mut document,
476                        "prepare INSERT identity",
477                    )?,
478                };
479                if let Some(staged) = prepare_values_insert_row(
480                    preparation,
481                    stmt,
482                    statement,
483                    params,
484                    &snapshot_scope,
485                    conflict_update_columns.as_deref().unwrap_or(&[]),
486                    &id_column,
487                    identity_source,
488                    target_table,
489                    document,
490                    insert_identity,
491                    &mut conflict_locks,
492                )? {
493                    if let Some(returning) = staged.returning {
494                        returning_rows.push_to(
495                            returning,
496                            context.mutation.preparation.returning.destination,
497                        )?;
498                    }
499                    events.append_after_rows(staged.after_row_events, &triggers)?;
500                    if staged.prepared_effect {
501                        affected += 1;
502                        has_prepared_effect = true;
503                    }
504                    supplied_identities.note(&staged.target_table, &staged.prepared);
505                    push_prepared_insert_spill_row(
506                        &mut prepared_rows,
507                        &prepared_schema,
508                        PreparedInsertSpillRow {
509                            target_table: staged.target_table,
510                            document: Arc::unwrap_or_clone(staged.document),
511                            conflict: staged.prepared,
512                        },
513                    )?;
514                }
515            }
516            let published = overlay.finish();
517            if has_prepared_effect || has_prepared_auto_identity {
518                mutation.state.prepare_writer()?;
519                persist_auto_increment_identity(
520                    mutation.identities,
521                    &stmt.table,
522                    auto_id_col.as_deref(),
523                    "persist INSERT identity",
524                )?;
525            }
526            drop(conflict_locks);
527            let observed = supplied_identities.observe(mutation.publication.storage)?;
528            let mut publication =
529                statement_end::publication_batch(&statement_commands).with_published(published);
530            let mut known_new = KnownNewInserts::new(
531                preparation.referential.constraints.catalog,
532                &id_column,
533                stmt.on_conflict.is_some(),
534            );
535            for row in prepared_rows
536                .drain_rows()
537                .map_err(crate::physical::physical_exec_error)?
538            {
539                cancel.check()?;
540                let PreparedInsertSpillRow {
541                    target_table,
542                    document,
543                    conflict: prepared,
544                } = decode_prepared_insert_spill_row(
545                    row.map_err(crate::physical::physical_exec_error)?,
546                )?;
547                let inserted = known_new.identity(
548                    &target_table,
549                    &prepared,
550                    &observed,
551                    mutation.publication.identifiers,
552                )?;
553                apply_validated_prepared_insert(
554                    mutation.publication,
555                    &target_table,
556                    document,
557                    prepared,
558                    inserted,
559                    &mut publication,
560                )?;
561            }
562            finish_mutation_publication(mutation.publication, &mut publication)?;
563            statement_end::note_written_rows(&statement_commands, &mut publication);
564            statement_end::end_command(
565                &statement_commands,
566                &triggers,
567                &insert_statement_events(
568                    &stmt.table,
569                    insert_original_query,
570                    conflict_update_columns.as_deref(),
571                ),
572                events.into_after_rows(),
573            )?;
574            if !stmt.returning.is_empty() {
575                let shape = DmlReturningShape {
576                    table: &stmt.table,
577                    target_qualifier: &stmt.target_qualifier,
578                    aliases: &stmt.returning_aliases,
579                    returning: &stmt.returning,
580                    params,
581                    ctes: scope,
582                    supplemental_schema: None,
583                };
584                return dml_returning_result(
585                    preparation.returning,
586                    shape,
587                    returning_rows,
588                    affected,
589                );
590            }
591            Ok(MutationOutput::from_affected(affected))
592        };
593    let result = match statement_snapshot.as_deref() {
594        Some(snapshot) => with_mutation_snapshot(context.snapshots, snapshot, execute_read),
595        None => execute_read(context),
596    };
597    statement_end::finish_statement(
598        context,
599        params,
600        &stmt.ctes,
601        statement_scope.as_mut(),
602        result,
603    )
604}