Skip to main content

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