Hi hackers, I'd like to propose adding NESTED_STATEMENTS as a core EXPLAIN option that displays execution plans for SQL statements executed within called functions and procedures.
Motivation
----------
Currently, to see execution plans for nested statements inside
PL/pgSQL functions, users must:
1. Add auto_explain to shared_preload_libraries
2. Restart the database
3. Set session-level GUCs:
SET client_min_messages TO log;
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_min_duration = 0;
SELECT my_function();
4. Check server logs for the output
With this patch, users can simply run:
EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT my_function();
Note: Requires ANALYZE (validated with clear error message)
and see the plans for all nested statements directly in the EXPLAIN
output, with no extension loading or configuration required.
How It Works
------------
The feature temporarily installs executor hooks (ExecutorStart, Run,
Finish, End) during the EXPLAIN execution to track query nesting depth
and capture plans for nested statements. After the main query plan is
displayed, nested plans are appended with:
- Sequential statement number
- Nesting level (executor call stack depth)
- Query text
- Complete execution plan (inheriting VERBOSE, BUFFERS, etc. options)
Example:
-- Setup
CREATE TABLE products (id INT, name TEXT, price NUMERIC, category TEXT);
INSERT INTO products VALUES
(1, 'Laptop', 999, 'Electronics'),
(2, 'Phone', 699, 'Electronics'),
(3, 'Book', 19, 'Books');
CREATE FUNCTION update_products() RETURNS void AS $$
DECLARE cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM products WHERE category = 'Electronics';
UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';
INSERT INTO products (name, price, category) VALUES ('Keyboard',
79, 'Electronics');
DELETE FROM products WHERE price < 5;
END;
$$ LANGUAGE plpgsql;
-- Run
EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT update_products();
QUERY PLAN
---------------------------------------------------------------
Result (actual time=1.234..1.235 rows=1.00 loops=1)
Buffers: shared hit=50
Planning Time: 0.050 ms
Execution Time: 2.500 ms
Nested Plans:
Nested Statement #1 (level 1):
Query Text: SELECT COUNT(*) FROM products WHERE category = 'Electronics'
Aggregate (actual time=0.015..0.016 rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual time=0.010..0.011 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: UPDATE products SET price = price * 1.10 WHERE
category = 'Electronics'
Update on products (actual time=0.050..0.050 rows=0.00 loops=1)
Buffers: shared hit=5
-> Seq Scan on products (actual time=0.020..0.022 rows=2.00 loops=1)
Filter: (category = 'Electronics'::text)
Rows Removed by Filter: 1
Buffers: shared hit=1
Nested Statement #3 (level 1):
Query Text: INSERT INTO products (name, price, category) VALUES
('Keyboard', 79, 'Electronics')
Insert on products (actual rows=0.00 loops=1)
Buffers: shared hit=1
-> Result (actual rows=1.00 loops=1)
Nested Statement #4 (level 1):
Query Text: DELETE FROM products WHERE price < 5
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 < '5'::numeric)
Rows Removed by Filter: 4
Buffers: shared hit=1
Nesting Level Semantics
-----------------------
The nesting level reflects the executor call stack depth. Here's a
visual representation of the level1_func example above:
EXPLAIN SELECT level1_func(); ← Level 0 (top-level, not shown)
│
└─→ level1_func() executes
│
├─→ PERFORM COUNT(*) FROM products → Level 1, Statement #1
│
├─→ PERFORM level2_func() → creates new executor
│ │
│ └─→ level2_func() executes
│ │
│ ├─→ PERFORM COUNT(*) WHERE id = 1 → Level 2, Statement #2
│ │
│ └─→ PERFORM level3_func() → creates new executor
│ │
│ └─→ level3_func() executes
│ │
│ └─→ PERFORM COUNT(*) WHERE category = 'Books'
│ → Level 3, Statement #3
│
│ (level3_func returns) → Level 2, Statement #4
│ (level2_func returns) → Level 1, Statement #5
│
└─→ (done)
The level number = how many PERFORM/SELECT calls are on the stack.
The nesting level reflects the executor call stack depth:
- Statements in a function called via PERFORM or SELECT INTO run at a
deeper level than the caller.
- Statements in a function called via expression assignment (result :=
func()) run at the same level as the caller, because no new executor
call is created.
- Trigger-fired statements appear at a deeper level than the
triggering statement.
- SQL functions create true nesting since they execute during the parent query.
This is consistent with how auto_explain tracks nesting internally.
Example showing multiple nesting levels:
CREATE FUNCTION level3_func() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products WHERE category = 'Books';
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION level2_func() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products WHERE id = 1;
PERFORM level3_func();
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION level1_func() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products;
PERFORM level2_func();
END;
$$ LANGUAGE plpgsql;
EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT level1_func();
QUERY PLAN
---------------------------------------------------------------
Result (actual time=1.701..1.702 rows=1.00 loops=1)
Buffers: shared hit=35
Planning Time: 0.036 ms
Execution Time: 3.820 ms
Nested Plans:
Nested Statement #1 (level 1):
Query Text: SELECT COUNT(*) FROM products
Aggregate (actual time=0.011..0.012 rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual time=0.008..0.009 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 time=0.006..0.006 rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual time=0.005..0.005 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 time=0.003..0.003 rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on products (actual time=0.002..0.002 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 level3_func()
Result (actual time=0.049..0.049 rows=1.00 loops=1)
Buffers: shared hit=1
Nested Statement #5 (level 1):
Query Text: SELECT level2_func()
Result (actual time=0.217..0.217 rows=1.00 loops=1)
Buffers: shared hit=28
Each PERFORM creates a new executor level, so the nesting depth
increases with each function call in the chain.
Testing
-------
I've also included a comprehensive test script
(comprehensive_nested_statements_test.sql) that covers 14 test cases:
1. Validation (NESTED_STATEMENTS requires ANALYZE)
2. Simple PL/pgSQL function (all statements at level 1)
3. PERFORM pattern (creates deeper nesting levels)
4. Expression assignment (stays at same level)
5. Side-by-side comparison of PERFORM vs expression assignment
6. SQL function nesting (true SQL nesting, levels 2-3)
7. Three-level PL/pgSQL chain with PERFORM
8. Recursive function (increasing levels)
9. Exception handling blocks
10. No nested statements (plain query, no Nested Plans section)
11. Trigger-fired nested statements
12. Combined with VERBOSE and BUFFERS options
13. Statement numbering = completion order (triggers demo)
14. BEGIN/ROLLBACK safety pattern
To run the test script and save output:
psql -f comprehensive_nested_statements_test.sql > test_output_all.txt 2>&1
The test output file (test_output_all.txt) shows the full EXPLAIN
output for each test case with explanations of expected behavior.
Structured output formats (JSON, XML, YAML) have been tested and work
correctly. In structured formats, the nested plans appear as a text
string within the Plan field rather than as structured plan nodes.
This could be improved in a future version.
--
Mohamed Ali
AWS RDS
============================================================================
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
psql:comprehensive_nested_statements_test.sql:36: 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.sql:53: 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=186
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=62
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=203
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=30
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=21
(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.sql:245: NOTICE: table "t1" does not
exist, skipping
psql:comprehensive_nested_statements_test.sql:245: NOTICE: table "t2" does not
exist, skipping
psql:comprehensive_nested_statements_test.sql:245: 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=36
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=24
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=11
(39 rows)
============================================================================
TEST 8: Recursive Function - Increasing Nesting Levels
============================================================================
Purpose: Recursive PERFORM calls increase the nesting level each time
psql:comprehensive_nested_statements_test.sql:333: 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=44 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.sql:367: 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.sql:424: NOTICE: table "orders" does
not exist, skipping
DROP TABLE
psql:comprehensive_nested_statements_test.sql:425: 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=76 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.sql:507: NOTICE: table "demo_orders"
does not exist, skipping
DROP TABLE
psql:comprehensive_nested_statements_test.sql:508: 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=41 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.sql:564: 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.
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
============================================================================
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
============================================================================
v1-0001-Add-NESTED_STATEMENTS-option-to-EXPLAIN.patch
Description: Binary data
comprehensive_nested_statements_test.sql
Description: Binary data
