[PATCH] Add NESTED_STATEMENTS option to EXPLAIN

First seen: 2026-05-16 06:48:58+00:00 · Messages: 8 · Participants: 2

Latest Update

2026-06-04 · claude-opus-4-6

Incremental Analysis: v5 Patch Addressing All Review Feedback

Summary

Mohamed Ali responded to all five of Zsolt Parragi's review objections with a comprehensive v5 patch that fixes both logic bugs, reworks structured output, switches to start-time ordering, and adds significant new features (security model, high-volume warnings, option inheritance). The test suite grew from 41 to 55 cases.

Bug Fixes (Responding to Review)

Invalid structured output (Critical fix): JSON/XML/YAML now produce a single valid parseable document. Nested plans are structured sub-objects inside a "Nested Plans" array, with "Execution Time Percentage" as a typed field. This was the most serious architectural issue from the review.
Slowest Statement tracking (Logic bug fix): Now tracks across ALL nesting levels and types, not just level-1. The reviewer's reproduction case (trigger-only scenarios) is now covered.
Total Trigger Time calculation (Logic bug fix): Now sums ALL trigger statements regardless of nesting level, fixing the cascading-trigger-inside-function scenario.
Statement ordering switched to chronological: Parents now appear before children (start-time order). The author acknowledges completion order was an implementation convenience from hook firing order and agrees start-time is more intuitive.
Trigger label scope: Author explicitly defends the current GetMyTriggerDepth() > 0 approach — labels ALL statements in trigger context — arguing that nesting level already distinguishes direct vs. cascade. No change made here.

New Features in v5

SECURITY DEFINER Protection (New Security Model)

Nested plan capture is skipped entirely when effective user ≠ session user (SECURITY DEFINER context)
Exception: superusers and pg_read_all_stats members always see nested plans
NOTICE emitted explaining the restriction
Uses same privilege gate as pg_stat_statements for query text visibility
This addresses the previously-unresolved security concern from the initial analysis

High-Volume Warning System

NOTICE emitted when >1000 nested statements captured without explicit SHOW_NESTED
Targets per-row SQL function calls (SELECT func(id) FROM large_table) which generate one capture per invocation
NOTICE suppressed when SHOW_NESTED is explicitly set
Threshold is a compile-time constant
Memory bounded by SHOW_NESTED — beyond limit, only counters/timing accumulated

Option Inheritance

IO option: INSTRUMENT_IO now propagates to nested plan capture
SETTINGS option: Nested plans show when functions internally change planner settings (e.g., SET enable_seqscan = off inside a function)

Reentrancy Logging

Upgraded from silent degradation to DEBUG1 log message when nested EXPLAIN NESTED_STATEMENTS is encountered

Parallel Query Behavior (Documented, Not Changed)

The author documents the parallel interaction model:

Hooks are process-local (leader only) — workers don't inherit them
PL/pgSQL functions (PARALLEL UNSAFE) always run in leader — fully captured
SQL functions pushed below Gather into workers: only leader executions captured
Explicitly noted as same limitation as auto_explain
No crashes in 6 parallel scenarios tested

Multi-Language Confirmation

Tested PL/pgSQL, PL/Tcl, PL/Perl — all captured identically via SPI. Architecture diagram provided showing hook interception below SPI layer. PL/Python mentioned as also supported but not explicitly tested.

Edge Cases Documented

Prepared statements: SPI plan caching (custom→generic transition) doesn't affect capture
Cursors: FOR loop = one capture per cursor query; explicit OPEN/FETCH/CLOSE captured at CLOSE time
Cross-database (dblink): Not captured (separate backend) — same as auto_explain
DDL inside functions: ProcessUtility path not captured; DML on created tables IS captured; no crash on mixed DDL+DML

Assessment

This is a substantial v5 that addresses all critical review feedback. The structured output rework and bug fixes resolve the blocking issues. The security model is a significant new design element that wasn't previously discussed. The patch is now considerably more production-ready, though it still awaits response from the reviewer (Zsolt) and hasn't had committer attention yet. The lack of auto_explain code sharing remains unaddressed.

History (2 prior analyses)

2026-06-01 · claude-opus-4-6

Monthly Summary: Add NESTED_STATEMENTS Option to EXPLAIN (May 2026)

Overview

This thread saw rapid iteration from v1 through v4 of a patch adding NESTED_STATEMENTS as a new EXPLAIN option, transforming from a focused proof-of-concept into a comprehensive nested execution profiling system. The patch addresses a fundamental observability gap: PostgreSQL's EXPLAIN cannot show execution plans for SQL statements executed inside PL/pgSQL functions and procedures. The only existing mechanism (auto_explain) requires server restart, GUC configuration, and log parsing — hostile to routine development workflows.

Architecture

The implementation leverages PostgreSQL's executor hook infrastructure (the same mechanism used by auto_explain). During EXPLAIN (ANALYZE, NESTED_STATEMENTS) execution, four hooks (ExecutorStart_hook, ExecutorRun_hook, ExecutorFinish_hook, ExecutorEnd_hook) are temporarily installed to intercept and capture plans for all nested SQL statements.

Patch Evolution This Month

v1 → v2: Robustness Fixes

Error path hook leakage (crash bug): Added PG_TRY/PG_FINALLY wrapping to guarantee hook removal on error
Reentrancy guard: Prevents crashes when a function itself executes EXPLAIN (ANALYZE, NESTED_STATEMENTS)
Memory management: Replaced TopMemoryContext allocations with a dedicated "Nested EXPLAIN plans" context, unconditionally deleted in PG_FINALLY

v2 → v3: Output Quality

Per-statement execution timing sourced from query_instr->total (same instrumentation as auto_explain), controlled by existing SUMMARY option
Structured output formats: Nested plans emitted as proper structured objects in JSON/XML/YAML with typed fields, rather than opaque text strings — a major improvement for programmatic tooling
Test suite grew from 19 to 24 tests

v3 → v4: Major Feature Expansion (41 tests)

SHOW_NESTED integer option: Display-only limiter (all statements still captured internally for summary accuracy). Default -1 shows all; value 0 enables summary-only mode
Nested Statements Summary section: Aggregate metrics including total count (direct/trigger-fired breakdown), max nesting depth, total nested time as percentage of execution time, slowest statement identification. Critically, sums only level-1 statements to avoid double-counting
Trigger detection via new GetMyTriggerDepth() C getter in trigger.c, exposing existing MyTriggerDepth static. Annotates trigger-fired statements and shows trigger cascade chains
Direct DML trigger visibility: EXPLAIN (ANALYZE, NESTED_STATEMENTS) UPDATE ... reveals entire trigger cascade — expanding beyond PL/pgSQL to general trigger observability
Query Identifier integration: With VERBOSE + compute_query_id = on, nested statements show queryId for pg_stat_statements correlation (zero additional code needed)

Unresolved Issues

Security: No discussion of privileged query text exposure when unprivileged callers invoke superuser-owned functions
Shared infrastructure with auto_explain: Two code paths for overlapping functionality; no refactoring proposed
Regression test integration: Tests remain a standalone SQL file, not integrated into src/test/regress
MAX_DEPTH option: Explicitly deferred; would complement SHOW_NESTED for depth-based filtering
JSON percentage fields: Whether structured output should include "Execution Time Percentage" as a separate typed field

Design Decisions of Note

Level-1 percentage attribution avoids double-counting (deeper levels included in parents)
SHOW_NESTED is presentation-only, not an execution limiter — ensures summary accuracy
Trigger detection uses single GetMyTriggerDepth() > 0 check covering both BEFORE/AFTER triggers
Reentrancy silently degrades (inner NESTED_STATEMENTS calls skipped) rather than erroring

2026-06-01 · claude-opus-4-6

Incremental Analysis: First External Review (Zsolt Parragi, Percona)

Summary

The first substantive external review has arrived from Zsolt Parragi (Percona), raising five distinct technical objections to the v4 design. These are not minor nits — several challenge fundamental architectural decisions about output format, ordering semantics, and trigger tracking.

New Technical Issues Raised

1. Structured Format Output is Invalid (Critical Design Flaw)

The most significant objection: EXPLAIN (ANALYZE, NESTED_STATEMENTS, FORMAT JSON) does not produce valid JSON. The output is a concatenation of multiple JSON documents interspersed with plain text (metadata headers, summary). This means standard JSON parsers cannot consume the output, defeating the purpose of structured formats.

This directly contradicts the v3 analysis claim that "nested plans are now emitted as proper structured objects." While individual nested plan blocks may be valid JSON internally, the overall document is not. The same problem applies to XML and YAML output. This is a significant usability regression for tooling integration — the primary motivator for structured formats.

2. Slowest Statement Limited to Level-1 Only (Logic Bug)

The reviewer identifies that nested_slowest_time tracking is gated by nested_exec_level == 1, meaning trigger-fired statements at deeper nesting levels are never candidates for "slowest statement." The reviewer provides a concrete reproduction: a slow trigger function (slow_trig) that performs expensive computation — the summary reports no slowest statement at all because all execution happens at level > 1.

This appears to be a consequence of the "sum only level-1" design decision being incorrectly extended to the "slowest" metric. While summing level-1 for time attribution makes mathematical sense (avoiding double-counting), the "slowest individual statement" metric should logically consider all levels.

3. Trigger Time Calculation Broken for Multi-Level Nesting

A second concrete reproduction shows trigger time calculation fails when triggers fire inside functions (not directly on the top-level statement). The test case: f() → INSERT INTO t → trigger trg() → pg_sleep(0.05). The trigger time isn't properly attributed because the calculation only considers direct trigger-fired statements at level 1.

This undermines the v4 feature of "Direct DML Trigger Visibility" — cascading trigger chains inside functions, which was highlighted as a key new use case, apparently don't work correctly.

4. Statement Ordering: Completion vs. Start Time

The current design numbers statements in completion order, which means child statements appear before their parents. The reviewer argues this is confusing: when a level-2 statement appears after a level-1 statement, the visual implication is that the preceding level-1 called it — but actually the following level-1 did.

Start-time ordering would produce a more intuitive parent-then-children reading order. This is a UX/presentation question but affects interpretability of complex multi-level traces.

5. Trigger Label Scope Too Broad

The current implementation labels ALL statements executing within a trigger context as "(trigger)". The reviewer suggests only the directly trigger-executed statement should get this label, not its descendants. This is a semantic precision question — should the label indicate "this statement was fired by a trigger" or "this statement is executing somewhere in a trigger call stack"?

Significance Assessment

This review is highly substantive. Issue #1 (invalid JSON) likely requires architectural rework of how nested plans are serialized in structured formats — possibly wrapping everything in a single top-level JSON object with arrays for nested plans and summary fields. Issues #2 and #3 are logic bugs with clear reproductions. Issues #4 and #5 are design questions that need author response.

The review suggests the patch needs at minimum a v5 addressing the format validity and calculation bugs before it could be considered for commit.