============================================================================ NESTED_STATEMENTS Feature - Comprehensive Test Suite ============================================================================ This test suite demonstrates: 1. Validation (NESTED_STATEMENTS requires ANALYZE) 2. Simple PL/pgSQL function (all level 1) 3. PERFORM pattern (creates deeper nesting levels) 4. Expression assignment pattern (stays at same level) 5. Comparison: PERFORM vs expression assignment 6. SQL function nesting (true SQL nesting) 7. Three-level chain with PERFORM 8. Recursive function (increasing levels) 9. Exception handling blocks 10. No nested statements (plain query) 11. Trigger-fired nested statements 12. Combined with VERBOSE and BUFFERS options 13. Statement numbering = completion order (triggers demo) 14. BEGIN/ROLLBACK safety pattern 15. Error during EXPLAIN does not crash (Bug 1 fix) 16. Nested EXPLAIN does not crash (Bug 2 fix) 17. Memory context cleanup (Bug 3 fix) 18. Memory context does not grow across repeated calls 19. Stress test - 50 nested statements 20. Execution Time per nested statement (SUMMARY default) 21. Execution Time hidden with SUMMARY OFF 22. Structured output - JSON format 23. Structured output - XML format 24. Structured output - YAML format -- ============================================================================ psql:comprehensive_nested_statements_test_v3.sql:44: NOTICE: table "products" does not exist, skipping DROP TABLE CREATE TABLE INSERT 0 3 ============================================================================ TEST 1: Validation - NESTED_STATEMENTS requires ANALYZE ============================================================================ Expected: ERROR message psql:comprehensive_nested_statements_test_v3.sql:61: ERROR: EXPLAIN option NESTED_STATEMENTS requires ANALYZE ============================================================================ TEST 2: Simple PL/pgSQL Function - All Statements at Level 1 ============================================================================ Purpose: SQL statements in a single function all execute at level 1 because they run sequentially in the same executor context. CREATE FUNCTION Expected: All 4 statements at level 1 QUERY PLAN ------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=170 read=16 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM products Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=3.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: INSERT INTO products VALUES (10, 'Temp', 1, 'Temp') Insert on products (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Result (actual rows=1.00 loops=1) Nested Statement #3 (level 1): Query Text: UPDATE products SET price = price + 1 WHERE id = 1 Update on products (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on products (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 3 Buffers: shared hit=1 Nested Statement #4 (level 1): Query Text: DELETE FROM products WHERE id = 10 Delete on products (actual rows=0.00 loops=1) Buffers: shared hit=2 -> Seq Scan on products (actual rows=1.00 loops=1) Filter: (id = 10) Rows Removed by Filter: 3 Buffers: shared hit=1 (35 rows) ============================================================================ TEST 3: PERFORM Pattern - Creates Deeper Nesting Levels ============================================================================ Purpose: PERFORM func() creates a new executor call (SELECT func()), so statements inside the called function run at a deeper level. How it works internally: PERFORM func() → executes "SELECT func()" → ExecutorRun increments level → func() body runs its SQL at the elevated level CREATE FUNCTION CREATE FUNCTION Expected: Statement #1 (level 1): SELECT COUNT in outer_perform Statement #2 (level 2): UPDATE in inner_perform Statement #3 (level 2): INSERT in inner_perform Statement #4 (level 2): DELETE in inner_perform Statement #5 (level 1): SELECT inner_perform() [the PERFORM call] Statement #6 (level 1): UPDATE in outer_perform QUERY PLAN --------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=58 read=1 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM products Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=3.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 2): Query Text: UPDATE products SET price = price + 1 WHERE id = 2 Update on products (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on products (actual rows=1.00 loops=1) Filter: (id = 2) Rows Removed by Filter: 2 Buffers: shared hit=1 Nested Statement #3 (level 2): Query Text: INSERT INTO products VALUES (20, 'Inner', 50, 'Test') Insert on products (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Result (actual rows=1.00 loops=1) Nested Statement #4 (level 2): Query Text: DELETE FROM products WHERE id = 20 Delete on products (actual rows=0.00 loops=1) Buffers: shared hit=2 -> Seq Scan on products (actual rows=1.00 loops=1) Filter: (id = 20) Rows Removed by Filter: 3 Buffers: shared hit=1 Nested Statement #5 (level 1): Query Text: SELECT inner_perform() Result (actual rows=1.00 loops=1) Buffers: shared hit=6 Nested Statement #6 (level 1): Query Text: UPDATE products SET price = price - 1 WHERE id = 2 Update on products (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on products (actual rows=1.00 loops=1) Filter: (id = 2) Rows Removed by Filter: 2 Buffers: shared hit=1 (49 rows) ============================================================================ TEST 4: Expression Assignment (result := func()) - Same Level ============================================================================ Purpose: result := func() evaluates the function as an expression via ExecEvalFunc WITHOUT creating a new executor call. Statements inside the called function run at the SAME level as the caller. How it works internally: result := func() → ExecEvalFunc(func) → NO new ExecutorRun → func() body runs its SQL at the SAME level as caller CREATE FUNCTION CREATE FUNCTION Expected: ALL statements at level 1 (no deeper nesting) Statement #1 (level 1): SELECT COUNT in outer_expr Statement #2 (level 1): SELECT SUM in inner_expr ← same level! Statement #3 (level 1): UPDATE in inner_expr ← same level! Statement #4 (level 1): DELETE in outer_expr QUERY PLAN ------------------------------------------------------------------ Result (actual rows=1.00 loops=1) Buffers: shared hit=191 read=9 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM products Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=3.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: SELECT SUM(price) FROM products Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=3.00 loops=1) Buffers: shared hit=1 Nested Statement #3 (level 1): Query Text: UPDATE products SET price = price + 1 WHERE id = 3 Update on products (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on products (actual rows=1.00 loops=1) Filter: (id = 3) Rows Removed by Filter: 2 Buffers: shared hit=1 Nested Statement #4 (level 1): Query Text: DELETE FROM products WHERE price > 9999 Delete on products (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=0.00 loops=1) Filter: (price > '9999'::numeric) Rows Removed by Filter: 3 Buffers: shared hit=1 (36 rows) ============================================================================ TEST 5: PERFORM vs Expression Assignment - Side by Side ============================================================================ Purpose: Same inner function called two different ways to show the nesting level difference. CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION --- Via PERFORM (expect level 2 for inner SELECT): --- QUERY PLAN ------------------------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=25 read=2 Nested Plans: Nested Statement #1 (level 2): Query Text: SELECT COUNT(*) FROM products WHERE category = 'Electronics' Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=2.00 loops=1) Filter: (category = 'Electronics'::text) Rows Removed by Filter: 1 Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: SELECT shared_inner() Result (actual rows=1.00 loops=1) Buffers: shared hit=19 read=2 (18 rows) --- Via expression assignment (expect level 1 for inner SELECT): --- QUERY PLAN ------------------------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=1 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM products WHERE category = 'Electronics' Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=2.00 loops=1) Filter: (category = 'Electronics'::text) Rows Removed by Filter: 1 Buffers: shared hit=1 (13 rows) Notice: Same function, different nesting levels depending on call pattern. ============================================================================ TEST 6: SQL Functions - True SQL Execution Nesting ============================================================================ Purpose: SQL functions execute DURING the parent query, creating true SQL nesting where inner functions complete before outer. psql:comprehensive_nested_statements_test_v3.sql:253: NOTICE: table "t1" does not exist, skipping psql:comprehensive_nested_statements_test_v3.sql:253: NOTICE: table "t2" does not exist, skipping psql:comprehensive_nested_statements_test_v3.sql:253: NOTICE: table "t3" does not exist, skipping DROP TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 1 INSERT 0 1 INSERT 0 1 CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION Expected: Deepest-first execution order (3→2→1) Statement #1 (level 3): SELECT from t3 (sql_level3) Statement #2 (level 2): SELECT from t2 (sql_level2) Statement #3 (level 1): SELECT from t1 (plpgsql_sql_caller) QUERY PLAN ----------------------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=30 Nested Plans: Nested Statement #1 (level 3): Query Text: SELECT data FROM t3 WHERE id = 1; Seq Scan on t3 (actual rows=1.00 loops=1) Filter: (id = 1) Buffers: shared hit=1 Nested Statement #2 (level 2): Query Text: SELECT data || '+' || sql_level3() FROM t2 WHERE id = 1; Seq Scan on t2 (actual rows=1.00 loops=1) Filter: (id = 1) Buffers: shared hit=8 Nested Statement #3 (level 1): Query Text: SELECT data || '+' || sql_level2() FROM t1 WHERE id = 1 Seq Scan on t1 (actual rows=1.00 loops=1) Filter: (id = 1) Buffers: shared hit=15 (26 rows) ============================================================================ TEST 7: Three-Level PL/pgSQL Chain (via PERFORM) ============================================================================ Purpose: Each PERFORM adds one executor level CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION Expected: Statement #1 (level 1): SELECT COUNT(*) FROM products Statement #2 (level 2): SELECT COUNT(*) WHERE id = 1 Statement #3 (level 3): SELECT COUNT(*) WHERE category = Books Statement #4 (level 2): SELECT chain_level3() Statement #5 (level 1): SELECT chain_level2() QUERY PLAN ---------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=18 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM products Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=3.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 2): Query Text: SELECT COUNT(*) FROM products WHERE id = 1 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 2 Buffers: shared hit=1 Nested Statement #3 (level 3): Query Text: SELECT COUNT(*) FROM products WHERE category = 'Books' Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on products (actual rows=1.00 loops=1) Filter: (category = 'Books'::text) Rows Removed by Filter: 2 Buffers: shared hit=1 Nested Statement #4 (level 2): Query Text: SELECT chain_level3() Result (actual rows=1.00 loops=1) Buffers: shared hit=1 Nested Statement #5 (level 1): Query Text: SELECT chain_level2() Result (actual rows=1.00 loops=1) Buffers: shared hit=8 (39 rows) ============================================================================ TEST 8: Recursive Function - Increasing Nesting Levels ============================================================================ Purpose: Recursive PERFORM calls increase the nesting level each time psql:comprehensive_nested_statements_test_v3.sql:341: NOTICE: table "counter_log" does not exist, skipping DROP TABLE CREATE TABLE CREATE FUNCTION Expected: Statement #1 (level 1): INSERT with n=3 Statement #2 (level 2): INSERT with n=2 Statement #3 (level 3): INSERT with n=1 + PERFORM calls at levels 2 and 1 QUERY PLAN ---------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=39 read=2 dirtied=1 written=1 Nested Plans: Nested Statement #1 (level 1): Query Text: INSERT INTO counter_log VALUES (n, n * 10) Insert on counter_log (actual rows=0.00 loops=1) Buffers: shared dirtied=1 written=1 -> Result (actual rows=1.00 loops=1) Nested Statement #2 (level 2): Query Text: INSERT INTO counter_log VALUES (n, n * 10) Insert on counter_log (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Result (actual rows=1.00 loops=1) Nested Statement #3 (level 3): Query Text: INSERT INTO counter_log VALUES (n, n * 10) Insert on counter_log (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Result (actual rows=1.00 loops=1) Nested Statement #4 (level 2): Query Text: SELECT recursive_func(n - 1) Result (actual rows=1.00 loops=1) Buffers: shared hit=1 Nested Statement #5 (level 1): Query Text: SELECT recursive_func(n - 1) Result (actual rows=1.00 loops=1) Buffers: shared hit=2 (32 rows) ============================================================================ TEST 9: Exception Handling - Statements in BEGIN/EXCEPTION Blocks ============================================================================ Purpose: Verify statements in exception handlers are captured psql:comprehensive_nested_statements_test_v3.sql:375: NOTICE: table "safe_table" does not exist, skipping DROP TABLE CREATE TABLE INSERT 0 1 CREATE FUNCTION Expected: - UPDATE (level 1): initial update - UPDATE (level 1): recovery in exception handler - The failed INSERT is rolled back and may not appear QUERY PLAN ----------------------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=23 Nested Plans: Nested Statement #1 (level 1): Query Text: UPDATE safe_table SET data = 'updated' WHERE id = 1 Update on safe_table (actual rows=0.00 loops=1) Buffers: shared hit=4 -> Index Scan using safe_table_pkey on safe_table (actual rows=1.00 loops=1) Index Cond: (id = 1) Index Searches: 1 Buffers: shared hit=2 Nested Statement #2 (level 1): Query Text: UPDATE safe_table SET data = 'recovered' WHERE id = 1 Update on safe_table (actual rows=0.00 loops=1) Buffers: shared hit=4 -> Index Scan using safe_table_pkey on safe_table (actual rows=1.00 loops=1) Index Cond: (id = 1) Index Searches: 1 Buffers: shared hit=2 (22 rows) ============================================================================ TEST 10: No Nested Statements - Plain Query ============================================================================ Purpose: When no nested statements execute, no "Nested Plans:" section Expected: Normal EXPLAIN output, no Nested Plans section QUERY PLAN ------------------------------------------------- Seq Scan on products (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 2 Buffers: shared hit=1 (4 rows) ============================================================================ TEST 11: Trigger-Fired Nested Statements ============================================================================ Purpose: Triggers fire DURING the triggering statement, creating deeper nesting. Note: trigger statements may appear BEFORE their parent (completion order, not start order). psql:comprehensive_nested_statements_test_v3.sql:432: NOTICE: table "orders" does not exist, skipping DROP TABLE psql:comprehensive_nested_statements_test_v3.sql:433: NOTICE: table "audit_log" does not exist, skipping DROP TABLE CREATE TABLE CREATE TABLE CREATE FUNCTION CREATE TRIGGER CREATE FUNCTION Expected: - INSERT INTO audit_log (level 2): trigger from INSERT - INSERT INTO orders (level 1): the triggering statement - INSERT INTO audit_log (level 2): trigger from UPDATE - UPDATE orders (level 1): the triggering statement Note: Level 2 statements appear before level 1 because triggers complete (ExecutorEnd) before the parent statement completes. QUERY PLAN --------------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=68 read=1 dirtied=4 written=3 Nested Plans: Nested Statement #1 (level 2): Query Text: INSERT INTO audit_log (order_id, action) VALUES (NEW.id, TG_OP || ': ' || NEW.status) Insert on audit_log (actual rows=0.00 loops=1) Buffers: shared dirtied=1 written=1 -> Result (actual rows=1.00 loops=1) Nested Statement #2 (level 1): Query Text: INSERT INTO orders VALUES (p_id, 99.99, 'new') Insert on orders (actual rows=0.00 loops=1) Buffers: shared hit=14 read=1 dirtied=3 written=2 -> Result (actual rows=1.00 loops=1) Nested Statement #3 (level 2): Query Text: INSERT INTO audit_log (order_id, action) VALUES (NEW.id, TG_OP || ': ' || NEW.status) Insert on audit_log (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Result (actual rows=1.00 loops=1) Nested Statement #4 (level 1): Query Text: UPDATE orders SET status = 'processed' WHERE id = p_id Update on orders (actual rows=0.00 loops=1) Buffers: shared hit=5 -> Index Scan using orders_pkey on orders (actual rows=1.00 loops=1) Index Cond: (id = 1) Index Searches: 1 Buffers: shared hit=2 (33 rows) ============================================================================ TEST 12: NESTED_STATEMENTS with VERBOSE and BUFFERS ============================================================================ Purpose: VERBOSE and BUFFERS options are inherited by nested plans CREATE FUNCTION Expected: Schema-qualified names (public.products) and Output columns QUERY PLAN ----------------------------------------------------------------------------------------------------- Result (cost=0.00..0.26 rows=1 width=4) (actual rows=1.00 loops=1) Output: verbose_func() Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM products Aggregate (cost=17.88..17.89 rows=1 width=8) (actual rows=1.00 loops=1) Output: count(*) Buffers: shared hit=1 -> Seq Scan on public.products (cost=0.00..16.30 rows=630 width=0) (actual rows=3.00 loops=1) Output: id, name, price, category Buffers: shared hit=1 (14 rows) ============================================================================ TEST 13: Statement Numbering Shows Completion Order ============================================================================ Purpose: Statement #N reflects when a statement FINISHES (ExecutorEnd), not when it starts. This is most visible with triggers: Timeline for INSERT with an AFTER trigger: 1. Parent INSERT starts executing 2. Trigger fires → trigger INSERT starts → trigger INSERT FINISHES → gets #1 3. Parent INSERT FINISHES → gets #2 The trigger statement finishes INSIDE the parent, so it gets a lower number. psql:comprehensive_nested_statements_test_v3.sql:515: NOTICE: table "demo_orders" does not exist, skipping DROP TABLE psql:comprehensive_nested_statements_test_v3.sql:516: NOTICE: table "demo_log" does not exist, skipping DROP TABLE CREATE TABLE CREATE TABLE CREATE FUNCTION CREATE TRIGGER CREATE FUNCTION Expected numbering (completion order): #1 (level 2): INSERT INTO demo_log ← trigger finishes first #2 (level 1): INSERT INTO demo_orders (Widget) ← parent finishes second #3 (level 2): INSERT INTO demo_log ← second trigger finishes #4 (level 1): INSERT INTO demo_orders (Gadget) ← second parent finishes QUERY PLAN -------------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=36 dirtied=2 written=2 Nested Plans: Nested Statement #1 (level 2): Query Text: INSERT INTO demo_log VALUES ('order placed: ' || NEW.item) Insert on demo_log (actual rows=0.00 loops=1) Buffers: shared dirtied=1 written=1 -> Result (actual rows=1.00 loops=1) Nested Statement #2 (level 1): Query Text: INSERT INTO demo_orders VALUES (1, 'Widget') Insert on demo_orders (actual rows=0.00 loops=1) Buffers: shared dirtied=1 written=1 -> Result (actual rows=1.00 loops=1) Nested Statement #3 (level 2): Query Text: INSERT INTO demo_log VALUES ('order placed: ' || NEW.item) Insert on demo_log (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Result (actual rows=1.00 loops=1) Nested Statement #4 (level 1): Query Text: INSERT INTO demo_orders VALUES (2, 'Gadget') Insert on demo_orders (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Result (actual rows=1.00 loops=1) (28 rows) Key takeaway: Lower statement number = finished earlier. Triggers finish inside their parent, so they always get lower numbers. ============================================================================ TEST 14: BEGIN/ROLLBACK - Safe Analysis of Data-Modifying Functions ============================================================================ Purpose: Demonstrate the recommended pattern for safely analyzing functions that modify data without persisting changes. Pattern: BEGIN; EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT my_function(); ROLLBACK; The function executes (so we get real plans with actual rows), but ROLLBACK undoes all changes. psql:comprehensive_nested_statements_test_v3.sql:572: NOTICE: table "safe_orders" does not exist, skipping DROP TABLE CREATE TABLE INSERT 0 2 CREATE FUNCTION --- Before: --- id | item | status ----+--------+--------- 1 | Widget | pending 2 | Gadget | pending (2 rows) --- EXPLAIN inside BEGIN/ROLLBACK: --- BEGIN QUERY PLAN ------------------------------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=11 Nested Plans: Nested Statement #1 (level 1): Query Text: UPDATE safe_orders SET status = 'processing' WHERE status = 'pending' Update on safe_orders (actual rows=0.00 loops=1) Buffers: shared hit=5 -> Seq Scan on safe_orders (actual rows=2.00 loops=1) Filter: (status = 'pending'::text) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: INSERT INTO safe_orders VALUES (3, 'Bonus', 'new') Insert on safe_orders (actual rows=0.00 loops=1) Buffers: shared hit=1 -> Result (actual rows=1.00 loops=1) Nested Statement #3 (level 1): Query Text: DELETE FROM safe_orders WHERE item = 'Gadget' Delete on safe_orders (actual rows=0.00 loops=1) Buffers: shared hit=2 -> Seq Scan on safe_orders (actual rows=1.00 loops=1) Filter: (item = 'Gadget'::text) Rows Removed by Filter: 2 Buffers: shared hit=1 (27 rows) ROLLBACK --- After ROLLBACK (data unchanged): --- id | item | status ----+--------+--------- 1 | Widget | pending 2 | Gadget | pending (2 rows) Result: We got full execution plans with actual row counts, but the data is unchanged after ROLLBACK. ============================================================================ TEST 15: Error During EXPLAIN Does Not Crash Server ============================================================================ Purpose: If EXPLAIN errors (e.g., division by zero), hooks must be cleaned up so subsequent queries do not crash the backend. CREATE FUNCTION psql:comprehensive_nested_statements_test_v3.sql:627: ERROR: division by zero CONTEXT: SQL statement "SELECT 1/x" PL/pgSQL function divz_plpgsql(integer) line 4 at SQL statement After error - next query (should not crash): post_error_test ----------------- 1 (1 row) After error - EXPLAIN NESTED_STATEMENTS again: QUERY PLAN ----------------------------------- Result (actual rows=1.00 loops=1) (1 row) TEST 15: Server survived the error — hooks cleaned up correctly ============================================================================ TEST 16: Nested EXPLAIN Does Not Crash Server ============================================================================ Purpose: A function that internally runs EXPLAIN (NESTED_STATEMENTS) should not corrupt the outer EXPLAIN state (reentrancy guard). CREATE FUNCTION QUERY PLAN ------------------------------------------------------------- Result (actual rows=1.00 loops=1) Nested Plans: Nested Statement #1 (level 1): Query Text: EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT 1 Result (actual rows=1.00 loops=1) (7 rows) TEST 16: Server survived nested EXPLAIN — reentrancy guard works ============================================================================ TEST 17: Memory Context Properly Freed After EXPLAIN ============================================================================ Purpose: The dedicated memory context for nested plans should not persist after EXPLAIN completes (no memory leak). QUERY PLAN ----------------------------------- Result (actual rows=1.00 loops=1) (1 row) QUERY PLAN ----------------------------------- Result (actual rows=1.00 loops=1) (1 row) QUERY PLAN ----------------------------------- Result (actual rows=1.00 loops=1) (1 row) Memory contexts named "Nested EXPLAIN plans" (should be 0 rows): name ------ (0 rows) TEST 17: 0 rows above = memory context freed after EXPLAIN ============================================================================ TEST 18: Memory Context Does Not Grow Across Repeated Calls ============================================================================ Purpose: Run EXPLAIN NESTED_STATEMENTS 20 times and verify no memory context persists or accumulates between calls. CREATE TABLE INSERT 0 2 CREATE FUNCTION QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=16 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) QUERY PLAN ----------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM mem_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on mem_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE mem_test SET val = val || 'x' WHERE id = 1 Update on mem_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on mem_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) After 20 runs - "Nested EXPLAIN plans" contexts (should be 0 rows): name ------ (0 rows) TEST 18: 0 rows above = no memory accumulation after 20 runs ============================================================================ TEST 19: Stress Test - Function With 50 Nested Statements ============================================================================ Purpose: Verify the feature handles many nested statements without crashing or corrupting memory. psql:comprehensive_nested_statements_test_v3.sql:739: NOTICE: table "stress_table" does not exist, skipping DROP TABLE CREATE TABLE INSERT 0 100 CREATE FUNCTION QUERY PLAN ------------------------------------------------------------------------ Result (actual rows=1.00 loops=1) Buffers: shared hit=59 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 1 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 2 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 2) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #3 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 3 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 3) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #4 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 4 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 4) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #5 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 5 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 5) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #6 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 6 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 6) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #7 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 7 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 7) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #8 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 8 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 8) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #9 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 9 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 9) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #10 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 10 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 10) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #11 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 11 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 11) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #12 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 12 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 12) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #13 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 13 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 13) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #14 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 14 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 14) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #15 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 15 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 15) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #16 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 16 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 16) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #17 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 17 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 17) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #18 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 18 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 18) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #19 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 19 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 19) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #20 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 20 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 20) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #21 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 21 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 21) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #22 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 22 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 22) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #23 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 23 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 23) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #24 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 24 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 24) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #25 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 25 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 25) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #26 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 26 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 26) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #27 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 27 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 27) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #28 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 28 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 28) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #29 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 29 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 29) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #30 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 30 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 30) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #31 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 31 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 31) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #32 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 32 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 32) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #33 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 33 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 33) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #34 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 34 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 34) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #35 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 35 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 35) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #36 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 36 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 36) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #37 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 37 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 37) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #38 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 38 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 38) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #39 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 39 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 39) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #40 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 40 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 40) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #41 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 41 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 41) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #42 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 42 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 42) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #43 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 43 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 43) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #44 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 44 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 44) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #45 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 45 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 45) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #46 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 46 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 46) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #47 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 47 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 47) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #48 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 48 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 48) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #49 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 49 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 49) Rows Removed by Filter: 99 Buffers: shared hit=1 Nested Statement #50 (level 1): Query Text: SELECT COUNT(*) FROM stress_table WHERE id = 50 Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on stress_table (actual rows=1.00 loops=1) Filter: (id = 50) Rows Removed by Filter: 99 Buffers: shared hit=1 (454 rows) Server healthy after stress: server_healthy ---------------- 1 (1 row) No memory leak after stress: name ------ (0 rows) TEST 19: 50 nested statements captured, server healthy, no leak ============================================================================ TEST 20: Execution Time Per Nested Statement ============================================================================ Purpose: Each nested statement shows its Execution Time when SUMMARY is enabled (default with ANALYZE). Uses query_instr->total. psql:comprehensive_nested_statements_test_v3.sql:821: NOTICE: table "et_test" does not exist, skipping DROP TABLE CREATE TABLE INSERT 0 2 CREATE FUNCTION Expected: Each nested statement shows "Execution Time: X.XXX ms" QUERY PLAN -------------------------------------------------------------------------- Result (actual time=0.200..0.200 rows=1.00 loops=1) Buffers: shared hit=16 Planning Time: 0.013 ms Execution Time: 0.206 ms Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM et_test Aggregate (actual time=0.009..0.009 rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on et_test (actual time=0.006..0.006 rows=2.00 loops=1) Buffers: shared hit=1 Execution Time: 0.010 ms Nested Statement #2 (level 1): Query Text: UPDATE et_test SET val = 'updated' WHERE id = 1 Update on et_test (actual time=0.013..0.013 rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on et_test (actual time=0.003..0.003 rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 Execution Time: 0.014 ms (24 rows) ============================================================================ TEST 21: Execution Time Hidden with SUMMARY OFF ============================================================================ Purpose: When SUMMARY OFF is specified, nested statements should NOT show Execution Time (consistent with main query behavior). Expected: No "Execution Time" lines in nested plans QUERY PLAN --------------------------------------------------------------- Result (actual rows=1.00 loops=1) Buffers: shared hit=4 Nested Plans: Nested Statement #1 (level 1): Query Text: SELECT COUNT(*) FROM et_test Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=1 -> Seq Scan on et_test (actual rows=2.00 loops=1) Buffers: shared hit=1 Nested Statement #2 (level 1): Query Text: UPDATE et_test SET val = 'updated' WHERE id = 1 Update on et_test (actual rows=0.00 loops=1) Buffers: shared hit=3 -> Seq Scan on et_test (actual rows=1.00 loops=1) Filter: (id = 1) Rows Removed by Filter: 1 Buffers: shared hit=1 (20 rows) DROP FUNCTION DROP TABLE ============================================================================ TEST 22: Structured Output - JSON Format ============================================================================ Purpose: Nested plans are output as proper structured JSON with Node Type, Plans array, costs, timing, and execution time. psql:comprehensive_nested_statements_test_v3.sql:870: NOTICE: table "json_t" does not exist, skipping DROP TABLE CREATE TABLE INSERT 0 2 CREATE FUNCTION QUERY PLAN ------------------------------------------------------------ [ + { + "Plan": { + "Node Type": "Result", + "Parallel Aware": false, + "Async Capable": false, + "Startup Cost": 0.00, + "Total Cost": 0.26, + "Plan Rows": 1, + "Plan Width": 4, + "Actual Startup Time": 0.260, + "Actual Total Time": 0.260, + "Actual Rows": 1.00, + "Actual Loops": 1, + "Disabled": false, + "Shared Hit Blocks": 16, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0 + }, + "Planning": { + "Shared Hit Blocks": 0, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0 + }, + "Planning Time": 0.015, + "Triggers": [ + ], + "Execution Time": 0.266 + } + ] + + Nested Plans: + + Nested Statement #1 (level 1): + Query Text: SELECT COUNT(*) FROM json_t + [ + { + "Plan": { + "Node Type": "Aggregate", + "Strategy": "Plain", + "Partial Mode": "Simple", + "Parallel Aware": false, + "Async Capable": false, + "Startup Cost": 25.88, + "Total Cost": 25.89, + "Plan Rows": 1, + "Plan Width": 8, + "Actual Startup Time": 0.010, + "Actual Total Time": 0.010, + "Actual Rows": 1.00, + "Actual Loops": 1, + "Disabled": false, + "Shared Hit Blocks": 1, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "Plans": [ + { + "Node Type": "Seq Scan", + "Parent Relationship": "Outer", + "Parallel Aware": false, + "Async Capable": false, + "Relation Name": "json_t", + "Alias": "json_t", + "Startup Cost": 0.00, + "Total Cost": 22.70, + "Plan Rows": 1270, + "Plan Width": 0, + "Actual Startup Time": 0.007, + "Actual Total Time": 0.007, + "Actual Rows": 2.00, + "Actual Loops": 1, + "Disabled": false, + "Shared Hit Blocks": 1, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0 + } + ] + }, + "Execution Time": 0.011 + } + ] + + Nested Statement #2 (level 1): + Query Text: UPDATE json_t SET val = 'updated' WHERE id = 1+ [ + { + "Plan": { + "Node Type": "ModifyTable", + "Operation": "Update", + "Parallel Aware": false, + "Async Capable": false, + "Relation Name": "json_t", + "Alias": "json_t", + "Startup Cost": 0.00, + "Total Cost": 25.88, + "Plan Rows": 0, + "Plan Width": 0, + "Actual Startup Time": 0.013, + "Actual Total Time": 0.013, + "Actual Rows": 0.00, + "Actual Loops": 1, + "Disabled": false, + "Shared Hit Blocks": 3, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0, + "Plans": [ + { + "Node Type": "Seq Scan", + "Parent Relationship": "Outer", + "Parallel Aware": false, + "Async Capable": false, + "Relation Name": "json_t", + "Alias": "json_t", + "Startup Cost": 0.00, + "Total Cost": 25.88, + "Plan Rows": 6, + "Plan Width": 38, + "Actual Startup Time": 0.003, + "Actual Total Time": 0.003, + "Actual Rows": 1.00, + "Actual Loops": 1, + "Disabled": false, + "Filter": "(id = 1)", + "Rows Removed by Filter": 1, + "Shared Hit Blocks": 1, + "Shared Read Blocks": 0, + "Shared Dirtied Blocks": 0, + "Shared Written Blocks": 0, + "Local Hit Blocks": 0, + "Local Read Blocks": 0, + "Local Dirtied Blocks": 0, + "Local Written Blocks": 0, + "Temp Read Blocks": 0, + "Temp Written Blocks": 0 + } + ] + }, + "Execution Time": 0.013 + } + ] + (1 row) DROP FUNCTION DROP TABLE ============================================================================ TEST 23: Structured Output - XML Format ============================================================================ Purpose: Nested plans are output as proper XML with Node-Type, Plans elements, costs, timing, and execution time. psql:comprehensive_nested_statements_test_v3.sql:899: NOTICE: table "xml_t" does not exist, skipping DROP TABLE CREATE TABLE INSERT 0 2 CREATE FUNCTION QUERY PLAN -------------------------------------------------------------- + + + Result + false + false + 0.00 + 0.26 + 1 + 4 + 0.282 + 0.283 + 1.00 + 1 + false + 16 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + + + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + + 0.010 + + + 0.324 + + + + Nested Plans: + + Nested Statement #1 (level 1): + Query Text: SELECT COUNT(*) FROM xml_t + + + + Aggregate + Plain + Simple + false + false + 25.88 + 25.89 + 1 + 8 + 0.009 + 0.010 + 1.00 + 1 + false + 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + + + Seq Scan + Outer + false + false + xml_t + xml_t + 0.00 + 22.70 + 1270 + 0 + 0.006 + 0.007 + 2.00 + 1 + false + 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + + + + 0.010 + + + + Nested Statement #2 (level 1): + Query Text: UPDATE xml_t SET val = 'updated' WHERE id = 1 + + + + ModifyTable + Update + false + false + xml_t + xml_t + 0.00 + 25.88 + 0 + 0 + 0.013 + 0.013 + 0.00 + 1 + false + 3 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + + + Seq Scan + Outer + false + false + xml_t + xml_t + 0.00 + 25.88 + 6 + 38 + 0.003 + 0.004 + 1.00 + 1 + false + (id = 1) + 1+ 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + + + + 0.013 + + + (1 row) DROP FUNCTION DROP TABLE ============================================================================ TEST 24: Structured Output - YAML Format ============================================================================ Purpose: Nested plans are output as proper YAML with Node Type, Plans list, costs, timing, and execution time. psql:comprehensive_nested_statements_test_v3.sql:928: NOTICE: table "yaml_t" does not exist, skipping DROP TABLE CREATE TABLE INSERT 0 2 CREATE FUNCTION QUERY PLAN ------------------------------------------------------------ - Plan: + Node Type: "Result" + Parallel Aware: false + Async Capable: false + Startup Cost: 0.00 + Total Cost: 0.26 + Plan Rows: 1 + Plan Width: 4 + Actual Startup Time: 0.179 + Actual Total Time: 0.179 + Actual Rows: 1.00 + Actual Loops: 1 + Disabled: false + Shared Hit Blocks: 16 + Shared Read Blocks: 0 + Shared Dirtied Blocks: 0 + Shared Written Blocks: 0 + Local Hit Blocks: 0 + Local Read Blocks: 0 + Local Dirtied Blocks: 0 + Local Written Blocks: 0 + Temp Read Blocks: 0 + Temp Written Blocks: 0 + Planning: + Shared Hit Blocks: 0 + Shared Read Blocks: 0 + Shared Dirtied Blocks: 0 + Shared Written Blocks: 0 + Local Hit Blocks: 0 + Local Read Blocks: 0 + Local Dirtied Blocks: 0 + Local Written Blocks: 0 + Temp Read Blocks: 0 + Temp Written Blocks: 0 + Planning Time: 0.010 + Triggers: + Execution Time: 0.185 + + Nested Plans: + + Nested Statement #1 (level 1): + Query Text: SELECT COUNT(*) FROM yaml_t + - Plan: + Node Type: "Aggregate" + Strategy: "Plain" + Partial Mode: "Simple" + Parallel Aware: false + Async Capable: false + Startup Cost: 25.88 + Total Cost: 25.89 + Plan Rows: 1 + Plan Width: 8 + Actual Startup Time: 0.007 + Actual Total Time: 0.007 + Actual Rows: 1.00 + Actual Loops: 1 + Disabled: false + Shared Hit Blocks: 1 + Shared Read Blocks: 0 + Shared Dirtied Blocks: 0 + Shared Written Blocks: 0 + Local Hit Blocks: 0 + Local Read Blocks: 0 + Local Dirtied Blocks: 0 + Local Written Blocks: 0 + Temp Read Blocks: 0 + Temp Written Blocks: 0 + Plans: + - Node Type: "Seq Scan" + Parent Relationship: "Outer" + Parallel Aware: false + Async Capable: false + Relation Name: "yaml_t" + Alias: "yaml_t" + Startup Cost: 0.00 + Total Cost: 22.70 + Plan Rows: 1270 + Plan Width: 0 + Actual Startup Time: 0.005 + Actual Total Time: 0.005 + Actual Rows: 2.00 + Actual Loops: 1 + Disabled: false + Shared Hit Blocks: 1 + Shared Read Blocks: 0 + Shared Dirtied Blocks: 0 + Shared Written Blocks: 0 + Local Hit Blocks: 0 + Local Read Blocks: 0 + Local Dirtied Blocks: 0 + Local Written Blocks: 0 + Temp Read Blocks: 0 + Temp Written Blocks: 0 + Execution Time: 0.008 + + Nested Statement #2 (level 1): + Query Text: UPDATE yaml_t SET val = 'updated' WHERE id = 1+ - Plan: + Node Type: "ModifyTable" + Operation: "Update" + Parallel Aware: false + Async Capable: false + Relation Name: "yaml_t" + Alias: "yaml_t" + Startup Cost: 0.00 + Total Cost: 25.88 + Plan Rows: 0 + Plan Width: 0 + Actual Startup Time: 0.011 + Actual Total Time: 0.011 + Actual Rows: 0.00 + Actual Loops: 1 + Disabled: false + Shared Hit Blocks: 3 + Shared Read Blocks: 0 + Shared Dirtied Blocks: 0 + Shared Written Blocks: 0 + Local Hit Blocks: 0 + Local Read Blocks: 0 + Local Dirtied Blocks: 0 + Local Written Blocks: 0 + Temp Read Blocks: 0 + Temp Written Blocks: 0 + Plans: + - Node Type: "Seq Scan" + Parent Relationship: "Outer" + Parallel Aware: false + Async Capable: false + Relation Name: "yaml_t" + Alias: "yaml_t" + Startup Cost: 0.00 + Total Cost: 25.88 + Plan Rows: 6 + Plan Width: 38 + Actual Startup Time: 0.003 + Actual Total Time: 0.004 + Actual Rows: 1.00 + Actual Loops: 1 + Disabled: false + Filter: "(id = 1)" + Rows Removed by Filter: 1 + Shared Hit Blocks: 1 + Shared Read Blocks: 0 + Shared Dirtied Blocks: 0 + Shared Written Blocks: 0 + Local Hit Blocks: 0 + Local Read Blocks: 0 + Local Dirtied Blocks: 0 + Local Written Blocks: 0 + Temp Read Blocks: 0 + Temp Written Blocks: 0 + Execution Time: 0.011 + (1 row) DROP FUNCTION DROP TABLE DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP TRIGGER DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP TRIGGER DROP FUNCTION DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP TABLE DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP FUNCTION DROP TABLE DROP TABLE ============================================================================ Test Suite Complete ============================================================================ KEY FINDING: Nesting levels reflect executor call stack depth. PERFORM func() / SELECT func() INTO var: → Creates new ExecutorRun → deeper nesting level result := func(): → ExecEvalFunc (no new ExecutorRun) → SAME nesting level Tests Covered: 1. Validation (requires ANALYZE) 2. Simple PL/pgSQL (all level 1) 3. PERFORM pattern (creates deeper levels) 4. Expression assignment (stays at same level) 5. Side-by-side comparison of both patterns 6. SQL function nesting (true SQL nesting) 7. Three-level chain with PERFORM 8. Recursive function (increasing levels) 9. Exception handling blocks 10. No nested statements (plain query) 11. Trigger-fired nested statements 12. Combined with VERBOSE and BUFFERS 13. Statement numbering = completion order (triggers demo) 14. BEGIN/ROLLBACK safety pattern 15. Error during EXPLAIN does not crash (Bug 1 fix) 16. Nested EXPLAIN does not crash (Bug 2 fix) 17. Memory context cleanup (Bug 3 fix) 18. Memory context does not grow across repeated calls 19. Stress test - 50 nested statements 20. Execution Time per nested statement (SUMMARY default) 21. Execution Time hidden with SUMMARY OFF 22. Structured output - JSON format 23. Structured output - XML format 24. Structured output - YAML format ============================================================================