uqa_sql/semantics/
effects.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Classify SQL plans by database, session, and transaction effects.
8
9use super::builtin_function_dispatch_name;
10use crate::{
11    ast::RelationPersistence,
12    catalog::domain::StoredDomain,
13    plan::{QueryPlan, UnifiedPlan},
14};
15use crate::{expr::EngineHook, routines::RoutineResolution, SQLError};
16use std::collections::BTreeSet;
17
18/// Read-only definitions required to classify plan effects before execution.
19pub trait QueryEffectCatalog: RoutineResolution + EngineHook {
20    fn registered_runtime_function_may_mutate_engine(&self, name: &str) -> bool;
21    fn domain_by_oid(&self, oid: u32) -> Option<StoredDomain>;
22    fn sequence_persistence(&self, name: &str) -> Result<Option<RelationPersistence>, String>;
23    fn table_persistence(&self, name: &str) -> Result<Option<RelationPersistence>, String>;
24    fn view_plan(&self, name: &str) -> Result<Option<QueryPlan>, SQLError>;
25    fn lookup_prepared(&self, name: &str) -> Option<UnifiedPlan>;
26}
27
28mod domains;
29/// Compose SQL definitions with the effects of planner rewrites and the embedded graph language.
30pub struct QueryEffectContext<'a> {
31    pub catalog: &'a dyn QueryEffectCatalog,
32    pub optimizer_effects: fn(&QueryPlan) -> bool,
33    pub graph_effects: fn(&str) -> Result<bool, SQLError>,
34}
35
36pub mod read_only;
37mod routines;
38
39use routines::plpgsql_function_may_mutate_engine;
40
41#[derive(Clone, Copy)]
42struct MutabilityClassification {
43    include_session_mutations: bool,
44    procedural_state_requires_transaction: bool,
45}
46
47impl MutabilityClassification {
48    const DATABASE_WRITES: Self = Self {
49        include_session_mutations: false,
50        procedural_state_requires_transaction: false,
51    };
52    const ENGINE_MUTATIONS: Self = Self {
53        include_session_mutations: true,
54        procedural_state_requires_transaction: false,
55    };
56    const STATEMENT_TRANSACTION: Self = Self {
57        include_session_mutations: true,
58        procedural_state_requires_transaction: true,
59    };
60}
61
62/// SELECT is not synonymous with read-only: UQA exposes a small set of state-changing scalar functions, and SQL/PLpgSQL routines invoked from a projection can contain commands. Classify those plans before choosing the transaction mode so memory execution takes a rollback snapshot and `SQLite` opens a write transaction. Cloning the plan is bounded by query size and avoids the database-sized deep copy paid by a full memory snapshot.
63pub fn query_may_mutate_engine(
64    context: &QueryEffectContext<'_>,
65    query: &crate::plan::QueryPlan,
66) -> Result<bool, SQLError> {
67    query_may_mutate_engine_inner(
68        context,
69        query,
70        &mut BTreeSet::new(),
71        &mut BTreeSet::new(),
72        MutabilityClassification::ENGINE_MUTATIONS,
73    )
74}
75
76/// Some read-only operations still need a statement transaction. In particular, a PL/pgSQL block with an `EXCEPTION` arm opens a subtransaction even when neither branch writes data.
77pub fn query_requires_statement_transaction(
78    context: &QueryEffectContext<'_>,
79    query: &crate::plan::QueryPlan,
80) -> Result<bool, SQLError> {
81    query_may_mutate_engine_inner(
82        context,
83        query,
84        &mut BTreeSet::new(),
85        &mut BTreeSet::new(),
86        MutabilityClassification::STATEMENT_TRANSACTION,
87    )
88}
89
90/// Classify only database writes forbidden by `PostgreSQL` read-only transactions. Session-local effects such as `random()` and `setseed()` still require statement rollback bookkeeping but remain legal in read-only mode.
91pub fn query_may_write_database(
92    context: &QueryEffectContext<'_>,
93    query: &crate::plan::QueryPlan,
94) -> Result<bool, SQLError> {
95    query_may_mutate_engine_inner(
96        context,
97        query,
98        &mut BTreeSet::new(),
99        &mut BTreeSet::new(),
100        MutabilityClassification::DATABASE_WRITES,
101    )
102}
103
104/// Detect database-writing expressions and query sources embedded in an otherwise legal temporary-table DML command.
105pub fn command_payload_may_write_database(
106    context: &QueryEffectContext<'_>,
107    command: &crate::plan::CommandPlan,
108) -> Result<bool, SQLError> {
109    let mut plan = crate::plan::UnifiedPlan::Command(Box::new(command.clone()));
110    let mut writes = false;
111    let mut classification_error = None;
112    plan.rewrite_scalar_expressions(&mut |expression| {
113        if classification_error.is_some() {
114            return;
115        }
116        match scalar_node_may_mutate_engine(
117            context,
118            expression,
119            &mut BTreeSet::new(),
120            &mut BTreeSet::new(),
121            MutabilityClassification::DATABASE_WRITES,
122        ) {
123            Ok(value) => writes |= value,
124            Err(error) => classification_error = Some(error),
125        }
126    });
127    if let Some(error) = classification_error {
128        return Err(error);
129    }
130    if writes {
131        return Ok(true);
132    }
133    match command {
134        crate::plan::CommandPlan::Insert(plan) => {
135            if ctes_may_write_database(context, &plan.ctes)? {
136                return Ok(true);
137            }
138            if let Some(source) = plan.source.as_deref() {
139                if query_may_write_database(context, source)? {
140                    return Ok(true);
141                }
142            }
143            queries_may_write_database(context, &plan.subqueries)
144        }
145        crate::plan::CommandPlan::Update(plan) => {
146            if ctes_may_write_database(context, &plan.ctes)? {
147                return Ok(true);
148            }
149            if let Some(source) = plan.source.as_deref() {
150                if source_may_mutate_engine(
151                    context,
152                    source,
153                    &mut BTreeSet::new(),
154                    &mut BTreeSet::new(),
155                    MutabilityClassification::DATABASE_WRITES,
156                )? {
157                    return Ok(true);
158                }
159            }
160            queries_may_write_database(context, &plan.subqueries)
161        }
162        crate::plan::CommandPlan::Delete(plan) => {
163            if ctes_may_write_database(context, &plan.ctes)? {
164                return Ok(true);
165            }
166            if let Some(source) = plan.source.as_deref() {
167                if source_may_mutate_engine(
168                    context,
169                    source,
170                    &mut BTreeSet::new(),
171                    &mut BTreeSet::new(),
172                    MutabilityClassification::DATABASE_WRITES,
173                )? {
174                    return Ok(true);
175                }
176            }
177            queries_may_write_database(context, &plan.subqueries)
178        }
179        crate::plan::CommandPlan::Merge(plan) => {
180            if ctes_may_write_database(context, &plan.ctes)? {
181                return Ok(true);
182            }
183            if source_may_mutate_engine(
184                context,
185                &plan.source,
186                &mut BTreeSet::new(),
187                &mut BTreeSet::new(),
188                MutabilityClassification::DATABASE_WRITES,
189            )? {
190                return Ok(true);
191            }
192            queries_may_write_database(context, &plan.subqueries)
193        }
194        _ => Ok(false),
195    }
196}
197
198fn command_may_write_database(
199    context: &QueryEffectContext<'_>,
200    command: &crate::plan::CommandPlan,
201) -> Result<bool, SQLError> {
202    read_only::forbidden_command(
203        context,
204        &crate::plan::UnifiedPlan::Command(Box::new(command.clone())),
205    )
206    .map(|forbidden| forbidden.is_some())
207}
208
209fn ctes_may_write_database(
210    context: &QueryEffectContext<'_>,
211    ctes: &[crate::plan::CtePlan],
212) -> Result<bool, SQLError> {
213    for cte in ctes {
214        let writes = match &cte.body {
215            crate::plan::CtePlanBody::Query(query) => query_may_write_database(context, query)?,
216            crate::plan::CtePlanBody::Command(command) => {
217                command_may_write_database(context, command)?
218            }
219        };
220        if writes {
221            return Ok(true);
222        }
223    }
224    Ok(false)
225}
226
227fn queries_may_write_database<'a>(
228    context: &QueryEffectContext<'_>,
229    queries: impl IntoIterator<Item = &'a crate::plan::QueryPlan>,
230) -> Result<bool, SQLError> {
231    for query in queries {
232        if query_may_write_database(context, query)? {
233            return Ok(true);
234        }
235    }
236    Ok(false)
237}
238
239fn query_may_mutate_engine_inner(
240    context: &QueryEffectContext<'_>,
241    query: &crate::plan::QueryPlan,
242    visiting_views: &mut BTreeSet<String>,
243    visiting_routines: &mut BTreeSet<String>,
244    classification: MutabilityClassification,
245) -> Result<bool, SQLError> {
246    if query_source_may_mutate_engine(
247        context,
248        query,
249        visiting_views,
250        visiting_routines,
251        classification,
252    )? {
253        return Ok(true);
254    }
255    let mut plan = crate::plan::UnifiedPlan::Query(Box::new(query.clone()));
256    let mut mutates = (context.optimizer_effects)(query);
257    let mut classification_error = None;
258    plan.rewrite_scalar_expressions(&mut |expression| {
259        if classification_error.is_some() {
260            return;
261        }
262        match scalar_node_may_mutate_engine(
263            context,
264            expression,
265            visiting_views,
266            visiting_routines,
267            classification,
268        ) {
269            Ok(value) => mutates |= value,
270            Err(error) => classification_error = Some(error),
271        }
272    });
273    classification_error.map_or(Ok(mutates), Err)
274}
275
276fn scalar_node_may_mutate_engine(
277    context: &QueryEffectContext<'_>,
278    expression: &crate::ScalarExpr,
279    visiting_views: &mut BTreeSet<String>,
280    visiting_routines: &mut BTreeSet<String>,
281    classification: MutabilityClassification,
282) -> Result<bool, SQLError> {
283    match expression {
284        crate::ScalarExpr::Func {
285            name,
286            binding,
287            args,
288            ..
289        } => function_may_mutate_engine(
290            context,
291            name,
292            binding.as_ref(),
293            args,
294            visiting_views,
295            visiting_routines,
296            classification,
297        ),
298        crate::ScalarExpr::Cast { ty, .. } => {
299            crate::expr::EngineHook::resolve_type_name(context.catalog, ty)
300                .ok()
301                .flatten()
302                .map_or(Ok(false), |ty| {
303                    domains::domain_cast_may_mutate(
304                        context,
305                        &ty,
306                        visiting_views,
307                        visiting_routines,
308                        classification,
309                    )
310                })
311        }
312        _ => Ok(false),
313    }
314}
315
316fn function_may_mutate_engine(
317    context: &QueryEffectContext<'_>,
318    name: &str,
319    binding: Option<&crate::ast::FunctionBinding>,
320    args: &[crate::ScalarExpr],
321    visiting_views: &mut BTreeSet<String>,
322    visiting_routines: &mut BTreeSet<String>,
323    classification: MutabilityClassification,
324) -> Result<bool, SQLError> {
325    let identity = name.to_ascii_lowercase();
326    let dispatch_name = builtin_function_dispatch_name(&identity);
327    let cypher_mutates = if dispatch_name == "cypher" {
328        match args.get(1) {
329            Some(crate::ScalarExpr::Literal(uqa_core::Value::Str(query))) => {
330                (context.graph_effects)(query)?
331            }
332            _ => classification.include_session_mutations,
333        }
334    } else {
335        false
336    };
337    let mutates_database_directly = cypher_mutates
338        || matches!(
339            dispatch_name.as_str(),
340            "create_analyzer"
341                | "drop_analyzer"
342                | "set_table_analyzer"
343                | "graph_create"
344                | "graph_drop"
345                | "create_graph"
346                | "drop_graph"
347                | "create_vlabel"
348                | "create_elabel"
349                | "drop_label"
350                | "alter_graph"
351                | "deep_learn"
352                | "bayesian_match"
353                | "bayesian_match_with_prior"
354        )
355        || context
356            .catalog
357            .registered_runtime_function_may_mutate_engine(&identity);
358    let temporary_sequence_call = matches!(dispatch_name.as_str(), "nextval" | "setval")
359        && sequence_function_targets_temporary(context, args)?;
360    let builtin_requires_mutating_execution =
361        matches!(
362            dispatch_name.as_str(),
363            "random" | "setseed" | "pg_notify" | "fts_match" | "multi_field_match"
364        ) || (matches!(dispatch_name.as_str(), "nextval" | "setval") && !temporary_sequence_call);
365    let sql_routine_mutates = classification.include_session_mutations
366        && sql_routine_may_mutate_engine(
367            context,
368            &identity,
369            binding,
370            visiting_views,
371            visiting_routines,
372            classification,
373        )?;
374    Ok(mutates_database_directly
375        || (classification.include_session_mutations
376            && (builtin_requires_mutating_execution || sql_routine_mutates)))
377}
378
379fn sequence_function_targets_temporary(
380    context: &QueryEffectContext<'_>,
381    args: &[crate::ScalarExpr],
382) -> Result<bool, SQLError> {
383    fn literal_reference(expression: &crate::ScalarExpr) -> Option<&str> {
384        match expression {
385            crate::ScalarExpr::Literal(uqa_core::Value::Str(reference)) => Some(reference),
386            crate::ScalarExpr::Cast { expr, .. } => literal_reference(expr),
387            _ => None,
388        }
389    }
390
391    let Some(reference) = args.first().and_then(literal_reference) else {
392        return Ok(false);
393    };
394    Ok(context
395        .catalog
396        .sequence_persistence(reference)
397        .map_err(|error| {
398            SQLError::Internal(format!(
399                "resolve sequence `{reference}` while classifying query mutability: {error}"
400            ))
401        })?
402        .is_some_and(|persistence| persistence == crate::ast::RelationPersistence::Temporary))
403}
404
405fn sql_routine_may_mutate_engine(
406    context: &QueryEffectContext<'_>,
407    name: &str,
408    binding: Option<&crate::ast::FunctionBinding>,
409    visiting_views: &mut BTreeSet<String>,
410    visiting_routines: &mut BTreeSet<String>,
411    classification: MutabilityClassification,
412) -> Result<bool, SQLError> {
413    if binding.is_some_and(|binding| binding.builtin) {
414        return Ok(false);
415    }
416    let overloads = match binding {
417        Some(binding) => context
418            .catalog
419            .lookup_bound_sql_functions_by_binding(binding),
420        None => context
421            .catalog
422            .lookup_visible_sql_functions_for_analysis(name)?,
423    };
424    let Some(overloads) = overloads else {
425        return Ok(false);
426    };
427    for function in overloads {
428        if function.def.is_procedure
429            || binding.is_some_and(|binding| {
430                crate::routines::routine_signature_types(&function.def) != binding.argument_types
431            })
432        {
433            continue;
434        }
435        let signature = crate::routines::routine_signature_types(&function.def);
436        let key = format!("{}({})", function.def.name, signature.join(","));
437        if !visiting_routines.insert(key.clone()) {
438            continue;
439        }
440        let result = (|| {
441            if domains::routine_coercions_may_mutate(
442                context,
443                &function.def,
444                visiting_views,
445                visiting_routines,
446                classification,
447            )? {
448                return Ok(true);
449            }
450            match &function.compiled {
451                crate::routines::CompiledFunctionBody::SQL(plans) => (|| {
452                    let mut mutates = false;
453                    for plan in plans {
454                        match plan {
455                            crate::plan::UnifiedPlan::Query(query) => {
456                                if query_may_mutate_engine_inner(
457                                    context,
458                                    query,
459                                    visiting_views,
460                                    visiting_routines,
461                                    classification,
462                                )? {
463                                    mutates = true;
464                                    break;
465                                }
466                            }
467                            crate::plan::UnifiedPlan::Command(_) => {
468                                mutates = true;
469                                break;
470                            }
471                        }
472                    }
473                    Ok(mutates)
474                })(),
475                crate::routines::CompiledFunctionBody::PLpgSQL(function) => {
476                    plpgsql_function_may_mutate_engine(
477                        context,
478                        function,
479                        visiting_views,
480                        visiting_routines,
481                        classification,
482                    )
483                }
484            }
485        })();
486        visiting_routines.remove(&key);
487        if result? {
488            return Ok(true);
489        }
490    }
491    Ok(false)
492}
493
494fn query_source_may_mutate_engine(
495    context: &QueryEffectContext<'_>,
496    query: &crate::plan::QueryPlan,
497    visiting_views: &mut BTreeSet<String>,
498    visiting_routines: &mut BTreeSet<String>,
499    classification: MutabilityClassification,
500) -> Result<bool, SQLError> {
501    for cte in &query.ctes {
502        let mutates = match &cte.body {
503            crate::plan::CtePlanBody::Query(query) => query_source_may_mutate_engine(
504                context,
505                query,
506                visiting_views,
507                visiting_routines,
508                classification,
509            )?,
510            crate::plan::CtePlanBody::Command(command) => {
511                classification.include_session_mutations
512                    || classification.procedural_state_requires_transaction
513                    || command_may_write_database(context, command)?
514            }
515        };
516        if mutates {
517            return Ok(true);
518        }
519    }
520    match &query.root {
521        crate::plan::RelationalPlan::QueryBlock(block) => {
522            if let Some(source) = block.from.as_ref() {
523                if source_may_mutate_engine(
524                    context,
525                    source,
526                    visiting_views,
527                    visiting_routines,
528                    classification,
529                )? {
530                    return Ok(true);
531                }
532            }
533            for subquery in &block.subqueries {
534                if query_source_may_mutate_engine(
535                    context,
536                    subquery,
537                    visiting_views,
538                    visiting_routines,
539                    classification,
540                )? {
541                    return Ok(true);
542                }
543            }
544        }
545        crate::plan::RelationalPlan::SetOp {
546            left,
547            right,
548            subqueries,
549            ..
550        } => {
551            if query_source_may_mutate_engine(
552                context,
553                left,
554                visiting_views,
555                visiting_routines,
556                classification,
557            )? || query_source_may_mutate_engine(
558                context,
559                right,
560                visiting_views,
561                visiting_routines,
562                classification,
563            )? {
564                return Ok(true);
565            }
566            for subquery in subqueries {
567                if query_source_may_mutate_engine(
568                    context,
569                    subquery,
570                    visiting_views,
571                    visiting_routines,
572                    classification,
573                )? {
574                    return Ok(true);
575                }
576            }
577        }
578        crate::plan::RelationalPlan::Values { subqueries, .. } => {
579            for subquery in subqueries {
580                if query_source_may_mutate_engine(
581                    context,
582                    subquery,
583                    visiting_views,
584                    visiting_routines,
585                    classification,
586                )? {
587                    return Ok(true);
588                }
589            }
590        }
591    }
592    Ok(false)
593}
594
595fn source_may_mutate_engine(
596    context: &QueryEffectContext<'_>,
597    source: &crate::plan::SourcePlan,
598    visiting_views: &mut BTreeSet<String>,
599    visiting_routines: &mut BTreeSet<String>,
600    classification: MutabilityClassification,
601) -> Result<bool, SQLError> {
602    match source {
603        crate::plan::SourcePlan::Function {
604            name,
605            binding,
606            args,
607            ..
608        } => function_may_mutate_engine(
609            context,
610            name,
611            binding.as_ref(),
612            args,
613            visiting_views,
614            visiting_routines,
615            classification,
616        ),
617        crate::plan::SourcePlan::FunctionGroup { functions, .. } => {
618            for function in functions {
619                if function_may_mutate_engine(
620                    context,
621                    &function.name,
622                    function.binding.as_ref(),
623                    &function.args,
624                    visiting_views,
625                    visiting_routines,
626                    classification,
627                )? {
628                    return Ok(true);
629                }
630            }
631            Ok(false)
632        }
633        crate::plan::SourcePlan::Join { left, right, .. } => Ok(source_may_mutate_engine(
634            context,
635            left,
636            visiting_views,
637            visiting_routines,
638            classification,
639        )? || source_may_mutate_engine(
640            context,
641            right,
642            visiting_views,
643            visiting_routines,
644            classification,
645        )?),
646        crate::plan::SourcePlan::Subquery { body, .. } => query_source_may_mutate_engine(
647            context,
648            body,
649            visiting_views,
650            visiting_routines,
651            classification,
652        ),
653        crate::plan::SourcePlan::Table { name, .. } => {
654            let key = name.to_ascii_lowercase();
655            if !visiting_views.insert(key.clone()) {
656                return Ok(false);
657            }
658            let result = match context.catalog.view_plan(name) {
659                Ok(Some(view)) => query_may_mutate_engine_inner(
660                    context,
661                    &view,
662                    visiting_views,
663                    visiting_routines,
664                    classification,
665                ),
666                Ok(None) => Ok(false),
667                Err(error) => Err(error),
668            };
669            visiting_views.remove(&key);
670            result
671        }
672        crate::plan::SourcePlan::Values { .. } => Ok(false),
673    }
674}
675
676pub fn is_transaction_control(plan: &crate::plan::UnifiedPlan) -> bool {
677    matches!(
678        plan,
679        crate::plan::UnifiedPlan::Command(command)
680            if matches!(command.as_ref(), crate::plan::CommandPlan::Transaction(_))
681    )
682}
683
684pub mod transaction_blocks;
uqa_sql/semantics/effects.rs

uqa_sql/semantics/
effects.rs
