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    include_transaction_scopes: bool,
45}
46
47impl MutabilityClassification {
48    const DATABASE_WRITES: Self = Self {
49        include_session_mutations: false,
50        include_transaction_scopes: false,
51    };
52    const ENGINE_MUTATIONS: Self = Self {
53        include_session_mutations: true,
54        include_transaction_scopes: false,
55    };
56    const STATEMENT_TRANSACTION: Self = Self {
57        include_session_mutations: true,
58        include_transaction_scopes: 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. 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.
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    let sequence_lock_scope = classification.include_transaction_scopes
375        && matches!(
376            dispatch_name.as_str(),
377            "nextval" | "currval" | "lastval" | "setval"
378        );
379    Ok(mutates_database_directly
380        || sequence_lock_scope
381        || (classification.include_session_mutations
382            && (builtin_requires_mutating_execution || sql_routine_mutates)))
383}
384
385fn sequence_function_targets_temporary(
386    context: &QueryEffectContext<'_>,
387    args: &[crate::ScalarExpr],
388) -> Result<bool, SQLError> {
389    fn literal_reference(expression: &crate::ScalarExpr) -> Option<&str> {
390        match expression {
391            crate::ScalarExpr::Literal(uqa_core::Value::Str(reference)) => Some(reference),
392            crate::ScalarExpr::Cast { expr, .. } => literal_reference(expr),
393            _ => None,
394        }
395    }
396
397    let Some(reference) = args.first().and_then(literal_reference) else {
398        return Ok(false);
399    };
400    Ok(context
401        .catalog
402        .sequence_persistence(reference)
403        .map_err(|error| {
404            SQLError::Internal(format!(
405                "resolve sequence `{reference}` while classifying query mutability: {error}"
406            ))
407        })?
408        .is_some_and(|persistence| persistence == crate::ast::RelationPersistence::Temporary))
409}
410
411fn sql_routine_may_mutate_engine(
412    context: &QueryEffectContext<'_>,
413    name: &str,
414    binding: Option<&crate::ast::FunctionBinding>,
415    visiting_views: &mut BTreeSet<String>,
416    visiting_routines: &mut BTreeSet<String>,
417    classification: MutabilityClassification,
418) -> Result<bool, SQLError> {
419    if binding.is_some_and(|binding| binding.builtin) {
420        return Ok(false);
421    }
422    let overloads = match binding {
423        Some(binding) => context
424            .catalog
425            .lookup_bound_sql_functions_by_binding(binding),
426        None => context
427            .catalog
428            .lookup_visible_sql_functions_for_analysis(name)?,
429    };
430    let Some(overloads) = overloads else {
431        return Ok(false);
432    };
433    for function in overloads {
434        if function.def.is_procedure
435            || binding.is_some_and(|binding| {
436                crate::routines::routine_signature_types(&function.def) != binding.argument_types
437            })
438        {
439            continue;
440        }
441        let signature = crate::routines::routine_signature_types(&function.def);
442        let key = format!("{}({})", function.def.name, signature.join(","));
443        if !visiting_routines.insert(key.clone()) {
444            continue;
445        }
446        let result = (|| {
447            if domains::routine_coercions_may_mutate(
448                context,
449                &function.def,
450                visiting_views,
451                visiting_routines,
452                classification,
453            )? {
454                return Ok(true);
455            }
456            match &function.compiled {
457                crate::routines::CompiledFunctionBody::SQL(plans) => (|| {
458                    let mut mutates = false;
459                    for plan in plans {
460                        match plan {
461                            crate::plan::UnifiedPlan::Query(query) => {
462                                if query_may_mutate_engine_inner(
463                                    context,
464                                    query,
465                                    visiting_views,
466                                    visiting_routines,
467                                    classification,
468                                )? {
469                                    mutates = true;
470                                    break;
471                                }
472                            }
473                            crate::plan::UnifiedPlan::Command(_) => {
474                                mutates = true;
475                                break;
476                            }
477                        }
478                    }
479                    Ok(mutates)
480                })(),
481                crate::routines::CompiledFunctionBody::PLpgSQL(function) => {
482                    plpgsql_function_may_mutate_engine(
483                        context,
484                        function,
485                        visiting_views,
486                        visiting_routines,
487                        classification,
488                    )
489                }
490            }
491        })();
492        visiting_routines.remove(&key);
493        if result? {
494            return Ok(true);
495        }
496    }
497    Ok(false)
498}
499
500fn query_source_may_mutate_engine(
501    context: &QueryEffectContext<'_>,
502    query: &crate::plan::QueryPlan,
503    visiting_views: &mut BTreeSet<String>,
504    visiting_routines: &mut BTreeSet<String>,
505    classification: MutabilityClassification,
506) -> Result<bool, SQLError> {
507    for cte in &query.ctes {
508        let mutates = match &cte.body {
509            crate::plan::CtePlanBody::Query(query) => query_source_may_mutate_engine(
510                context,
511                query,
512                visiting_views,
513                visiting_routines,
514                classification,
515            )?,
516            crate::plan::CtePlanBody::Command(command) => {
517                classification.include_session_mutations
518                    || classification.include_transaction_scopes
519                    || command_may_write_database(context, command)?
520            }
521        };
522        if mutates {
523            return Ok(true);
524        }
525    }
526    match &query.root {
527        crate::plan::RelationalPlan::QueryBlock(block) => {
528            if let Some(source) = block.from.as_ref() {
529                if source_may_mutate_engine(
530                    context,
531                    source,
532                    visiting_views,
533                    visiting_routines,
534                    classification,
535                )? {
536                    return Ok(true);
537                }
538            }
539            for subquery in &block.subqueries {
540                if query_source_may_mutate_engine(
541                    context,
542                    subquery,
543                    visiting_views,
544                    visiting_routines,
545                    classification,
546                )? {
547                    return Ok(true);
548                }
549            }
550        }
551        crate::plan::RelationalPlan::SetOp {
552            left,
553            right,
554            subqueries,
555            ..
556        } => {
557            if query_source_may_mutate_engine(
558                context,
559                left,
560                visiting_views,
561                visiting_routines,
562                classification,
563            )? || query_source_may_mutate_engine(
564                context,
565                right,
566                visiting_views,
567                visiting_routines,
568                classification,
569            )? {
570                return Ok(true);
571            }
572            for subquery in subqueries {
573                if query_source_may_mutate_engine(
574                    context,
575                    subquery,
576                    visiting_views,
577                    visiting_routines,
578                    classification,
579                )? {
580                    return Ok(true);
581                }
582            }
583        }
584        crate::plan::RelationalPlan::Values { subqueries, .. } => {
585            for subquery in subqueries {
586                if query_source_may_mutate_engine(
587                    context,
588                    subquery,
589                    visiting_views,
590                    visiting_routines,
591                    classification,
592                )? {
593                    return Ok(true);
594                }
595            }
596        }
597    }
598    Ok(false)
599}
600
601fn source_may_mutate_engine(
602    context: &QueryEffectContext<'_>,
603    source: &crate::plan::SourcePlan,
604    visiting_views: &mut BTreeSet<String>,
605    visiting_routines: &mut BTreeSet<String>,
606    classification: MutabilityClassification,
607) -> Result<bool, SQLError> {
608    match source {
609        crate::plan::SourcePlan::Function {
610            name,
611            binding,
612            args,
613            ..
614        } => function_may_mutate_engine(
615            context,
616            name,
617            binding.as_ref(),
618            args,
619            visiting_views,
620            visiting_routines,
621            classification,
622        ),
623        crate::plan::SourcePlan::FunctionGroup { functions, .. } => {
624            for function in functions {
625                if function_may_mutate_engine(
626                    context,
627                    &function.name,
628                    function.binding.as_ref(),
629                    &function.args,
630                    visiting_views,
631                    visiting_routines,
632                    classification,
633                )? {
634                    return Ok(true);
635                }
636            }
637            Ok(false)
638        }
639        crate::plan::SourcePlan::Join { left, right, .. } => Ok(source_may_mutate_engine(
640            context,
641            left,
642            visiting_views,
643            visiting_routines,
644            classification,
645        )? || source_may_mutate_engine(
646            context,
647            right,
648            visiting_views,
649            visiting_routines,
650            classification,
651        )?),
652        crate::plan::SourcePlan::Subquery { body, .. } => query_source_may_mutate_engine(
653            context,
654            body,
655            visiting_views,
656            visiting_routines,
657            classification,
658        ),
659        crate::plan::SourcePlan::Table { name, .. } => {
660            let key = name.to_ascii_lowercase();
661            if !visiting_views.insert(key.clone()) {
662                return Ok(false);
663            }
664            let result = match context.catalog.view_plan(name) {
665                Ok(Some(view)) => query_may_mutate_engine_inner(
666                    context,
667                    &view,
668                    visiting_views,
669                    visiting_routines,
670                    classification,
671                ),
672                Ok(None) => Ok(false),
673                Err(error) => Err(error),
674            };
675            visiting_views.remove(&key);
676            result
677        }
678        crate::plan::SourcePlan::Values { .. } => Ok(false),
679    }
680}
681
682pub fn is_transaction_control(plan: &crate::plan::UnifiedPlan) -> bool {
683    matches!(
684        plan,
685        crate::plan::UnifiedPlan::Command(command)
686            if matches!(command.as_ref(), crate::plan::CommandPlan::Transaction(_))
687    )
688}
689
690pub mod transaction_blocks;
uqa_sql/semantics/effects.rs

uqa_sql/semantics/
effects.rs
