comprehensive_nested_statements_test_v2.sql
application/octet-stream
Filename: comprehensive_nested_statements_test_v2.sql
Type: application/octet-stream
Part: 2
-- ============================================================================
-- NESTED_STATEMENTS Feature - Comprehensive Test Suite
-- ============================================================================
-- This script tests the NESTED_STATEMENTS feature and demonstrates
-- how nesting levels reflect the actual PostgreSQL executor call stack.
--
-- KEY INSIGHT: Nesting levels depend on HOW a function is called:
-- PERFORM func() / SELECT func() INTO var → creates new executor level
-- result := func() → same executor level as caller
-- ============================================================================
\echo '============================================================================'
\echo 'NESTED_STATEMENTS Feature - Comprehensive Test Suite'
\echo '============================================================================'
\echo ''
\echo 'This test suite demonstrates:'
\echo ' 1. Validation (NESTED_STATEMENTS requires ANALYZE)'
\echo ' 2. Simple PL/pgSQL function (all level 1)'
\echo ' 3. PERFORM pattern (creates deeper nesting levels)'
\echo ' 4. Expression assignment pattern (stays at same level)'
\echo ' 5. Comparison: PERFORM vs expression assignment'
\echo ' 6. SQL function nesting (true SQL nesting)'
\echo ' 7. Three-level chain with PERFORM'
\echo ' 8. Recursive function (increasing levels)'
\echo ' 9. Exception handling blocks'
\echo ' 10. No nested statements (plain query)'
\echo ' 11. Trigger-fired nested statements'
\echo ' 12. Combined with VERBOSE and BUFFERS options'
\echo ' 13. Statement numbering = completion order (triggers demo)'
\echo ' 14. BEGIN/ROLLBACK safety pattern'
\echo ' 15. Error during EXPLAIN does not crash (Bug 1 fix)'
\echo ' 16. Nested EXPLAIN does not crash (Bug 2 fix)'
\echo ' 17. Memory context cleanup (Bug 3 fix)'
\echo ' 18. Memory context does not grow across repeated calls'
\echo ' 19. Stress test - 50 nested statements'
\echo ''
-- ============================================================================
-- Setup: Create shared test table
-- ============================================================================
DROP TABLE IF EXISTS products CASCADE;
CREATE TABLE products (id INT, name TEXT, price NUMERIC, category TEXT);
INSERT INTO products VALUES
(1, 'Laptop', 999, 'Electronics'),
(2, 'Mouse', 29, 'Electronics'),
(3, 'Book', 19, 'Books');
-- ============================================================================
-- TEST 1: Validation
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 1: Validation - NESTED_STATEMENTS requires ANALYZE'
\echo '============================================================================'
\echo ''
\echo 'Expected: ERROR message'
\echo ''
EXPLAIN (NESTED_STATEMENTS) SELECT 1;
\echo ''
-- ============================================================================
-- TEST 2: Simple PL/pgSQL Function (All Level 1)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 2: Simple PL/pgSQL Function - All Statements at Level 1'
\echo '============================================================================'
\echo ''
\echo 'Purpose: SQL statements in a single function all execute at level 1'
\echo ' because they run sequentially in the same executor context.'
\echo ''
CREATE OR REPLACE FUNCTION simple_function() RETURNS INTEGER AS $$
DECLARE cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM products;
INSERT INTO products VALUES (10, 'Temp', 1, 'Temp');
UPDATE products SET price = price + 1 WHERE id = 1;
DELETE FROM products WHERE id = 10;
RETURN cnt;
END;
$$ LANGUAGE plpgsql;
\echo 'Expected: All 4 statements at level 1'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT simple_function();
\echo ''
-- ============================================================================
-- TEST 3: PERFORM Pattern - Creates Deeper Nesting
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 3: PERFORM Pattern - Creates Deeper Nesting Levels'
\echo '============================================================================'
\echo ''
\echo 'Purpose: PERFORM func() creates a new executor call (SELECT func()),'
\echo ' so statements inside the called function run at a deeper level.'
\echo ''
\echo 'How it works internally:'
\echo ' PERFORM func() → executes "SELECT func()" → ExecutorRun increments level'
\echo ' → func() body runs its SQL at the elevated level'
\echo ''
CREATE OR REPLACE FUNCTION inner_perform() RETURNS void AS $$
BEGIN
UPDATE products SET price = price + 1 WHERE id = 2;
INSERT INTO products VALUES (20, 'Inner', 50, 'Test');
DELETE FROM products WHERE id = 20;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION outer_perform() RETURNS void AS $$
DECLARE cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM products; -- level 1
PERFORM inner_perform(); -- creates new executor → inner runs at level 2
UPDATE products SET price = price - 1 WHERE id = 2; -- level 1
END;
$$ LANGUAGE plpgsql;
\echo 'Expected:'
\echo ' Statement #1 (level 1): SELECT COUNT in outer_perform'
\echo ' Statement #2 (level 2): UPDATE in inner_perform'
\echo ' Statement #3 (level 2): INSERT in inner_perform'
\echo ' Statement #4 (level 2): DELETE in inner_perform'
\echo ' Statement #5 (level 1): SELECT inner_perform() [the PERFORM call]'
\echo ' Statement #6 (level 1): UPDATE in outer_perform'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT outer_perform();
\echo ''
-- ============================================================================
-- TEST 4: Expression Assignment - Same Level
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 4: Expression Assignment (result := func()) - Same Level'
\echo '============================================================================'
\echo ''
\echo 'Purpose: result := func() evaluates the function as an expression via'
\echo ' ExecEvalFunc WITHOUT creating a new executor call. Statements'
\echo ' inside the called function run at the SAME level as the caller.'
\echo ''
\echo 'How it works internally:'
\echo ' result := func() → ExecEvalFunc(func) → NO new ExecutorRun'
\echo ' → func() body runs its SQL at the SAME level as caller'
\echo ''
CREATE OR REPLACE FUNCTION inner_expr() RETURNS NUMERIC AS $$
DECLARE total NUMERIC;
BEGIN
SELECT SUM(price) INTO total FROM products;
UPDATE products SET price = price + 1 WHERE id = 3;
RETURN total;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION outer_expr() RETURNS TEXT AS $$
DECLARE
result NUMERIC;
cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM products; -- level 1
result := inner_expr(); -- expression eval, inner runs at level 1 too!
DELETE FROM products WHERE price > 9999; -- level 1
RETURN 'Total: ' || result;
END;
$$ LANGUAGE plpgsql;
\echo 'Expected: ALL statements at level 1 (no deeper nesting)'
\echo ' Statement #1 (level 1): SELECT COUNT in outer_expr'
\echo ' Statement #2 (level 1): SELECT SUM in inner_expr ← same level!'
\echo ' Statement #3 (level 1): UPDATE in inner_expr ← same level!'
\echo ' Statement #4 (level 1): DELETE in outer_expr'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT outer_expr();
\echo ''
-- ============================================================================
-- TEST 5: Side-by-Side Comparison
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 5: PERFORM vs Expression Assignment - Side by Side'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Same inner function called two different ways to show the'
\echo ' nesting level difference.'
\echo ''
CREATE OR REPLACE FUNCTION shared_inner() RETURNS INTEGER AS $$
DECLARE cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM products WHERE category = 'Electronics';
RETURN cnt;
END;
$$ LANGUAGE plpgsql;
-- Calls via PERFORM (creates new executor level)
CREATE OR REPLACE FUNCTION caller_perform() RETURNS void AS $$
BEGIN
PERFORM shared_inner(); -- new executor → inner at level 2
END;
$$ LANGUAGE plpgsql;
-- Calls via expression assignment (same executor level)
CREATE OR REPLACE FUNCTION caller_assign() RETURNS INTEGER AS $$
DECLARE result INTEGER;
BEGIN
result := shared_inner(); -- expression eval → inner at level 1
RETURN result;
END;
$$ LANGUAGE plpgsql;
\echo '--- Via PERFORM (expect level 2 for inner SELECT): ---'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT caller_perform();
\echo ''
\echo '--- Via expression assignment (expect level 1 for inner SELECT): ---'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT caller_assign();
\echo ''
\echo 'Notice: Same function, different nesting levels depending on call pattern.'
\echo ''
-- ============================================================================
-- TEST 6: SQL Function Nesting (True SQL Nesting)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 6: SQL Functions - True SQL Execution Nesting'
\echo '============================================================================'
\echo ''
\echo 'Purpose: SQL functions execute DURING the parent query, creating true'
\echo ' SQL nesting where inner functions complete before outer.'
\echo ''
DROP TABLE IF EXISTS t1, t2, t3 CASCADE;
CREATE TABLE t1 (id INT, data TEXT);
CREATE TABLE t2 (id INT, data TEXT);
CREATE TABLE t3 (id INT, data TEXT);
INSERT INTO t1 VALUES (1, 'A');
INSERT INTO t2 VALUES (1, 'B');
INSERT INTO t3 VALUES (1, 'C');
CREATE OR REPLACE FUNCTION sql_level3() RETURNS TEXT AS $$
SELECT data FROM t3 WHERE id = 1;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION sql_level2() RETURNS TEXT AS $$
SELECT data || '+' || sql_level3() FROM t2 WHERE id = 1;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION plpgsql_sql_caller() RETURNS TEXT AS $$
DECLARE result TEXT;
BEGIN
SELECT data || '+' || sql_level2() INTO result FROM t1 WHERE id = 1;
RETURN result;
END;
$$ LANGUAGE plpgsql;
\echo 'Expected: Deepest-first execution order (3→2→1)'
\echo ' Statement #1 (level 3): SELECT from t3 (sql_level3)'
\echo ' Statement #2 (level 2): SELECT from t2 (sql_level2)'
\echo ' Statement #3 (level 1): SELECT from t1 (plpgsql_sql_caller)'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT plpgsql_sql_caller();
\echo ''
-- ============================================================================
-- TEST 7: Three-Level Chain with PERFORM
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 7: Three-Level PL/pgSQL Chain (via PERFORM)'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Each PERFORM adds one executor level'
\echo ''
CREATE OR REPLACE FUNCTION chain_level3() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products WHERE category = 'Books';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION chain_level2() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products WHERE id = 1;
PERFORM chain_level3();
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION chain_level1() RETURNS void AS $$
BEGIN
PERFORM COUNT(*) FROM products;
PERFORM chain_level2();
END;
$$ LANGUAGE plpgsql;
\echo 'Expected:'
\echo ' Statement #1 (level 1): SELECT COUNT(*) FROM products'
\echo ' Statement #2 (level 2): SELECT COUNT(*) WHERE id = 1'
\echo ' Statement #3 (level 3): SELECT COUNT(*) WHERE category = Books'
\echo ' Statement #4 (level 2): SELECT chain_level3()'
\echo ' Statement #5 (level 1): SELECT chain_level2()'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT chain_level1();
\echo ''
-- ============================================================================
-- TEST 8: Recursive Function
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 8: Recursive Function - Increasing Nesting Levels'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Recursive PERFORM calls increase the nesting level each time'
\echo ''
DROP TABLE IF EXISTS counter_log;
CREATE TABLE counter_log (depth INT, val INT);
CREATE OR REPLACE FUNCTION recursive_func(n INT) RETURNS VOID AS $$
BEGIN
INSERT INTO counter_log VALUES (n, n * 10);
IF n > 1 THEN
PERFORM recursive_func(n - 1);
END IF;
END;
$$ LANGUAGE plpgsql;
\echo 'Expected:'
\echo ' Statement #1 (level 1): INSERT with n=3'
\echo ' Statement #2 (level 2): INSERT with n=2'
\echo ' Statement #3 (level 3): INSERT with n=1'
\echo ' + PERFORM calls at levels 2 and 1'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT recursive_func(3);
\echo ''
-- ============================================================================
-- TEST 9: Exception Handling
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 9: Exception Handling - Statements in BEGIN/EXCEPTION Blocks'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Verify statements in exception handlers are captured'
\echo ''
DROP TABLE IF EXISTS safe_table;
CREATE TABLE safe_table (id INT PRIMARY KEY, data TEXT);
INSERT INTO safe_table VALUES (1, 'existing');
CREATE OR REPLACE FUNCTION exception_func() RETURNS TEXT AS $$
BEGIN
UPDATE safe_table SET data = 'updated' WHERE id = 1;
BEGIN
INSERT INTO safe_table VALUES (1, 'duplicate'); -- will fail
EXCEPTION WHEN unique_violation THEN
UPDATE safe_table SET data = 'recovered' WHERE id = 1;
END;
RETURN 'done';
END;
$$ LANGUAGE plpgsql;
\echo 'Expected:'
\echo ' - UPDATE (level 1): initial update'
\echo ' - UPDATE (level 1): recovery in exception handler'
\echo ' - The failed INSERT is rolled back and may not appear'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT exception_func();
\echo ''
-- ============================================================================
-- TEST 10: No Nested Statements
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 10: No Nested Statements - Plain Query'
\echo '============================================================================'
\echo ''
\echo 'Purpose: When no nested statements execute, no "Nested Plans:" section'
\echo ''
\echo 'Expected: Normal EXPLAIN output, no Nested Plans section'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM products WHERE id = 1;
\echo ''
-- ============================================================================
-- TEST 11: Trigger-Fired Nested Statements
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 11: Trigger-Fired Nested Statements'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Triggers fire DURING the triggering statement, creating'
\echo ' deeper nesting. Note: trigger statements may appear BEFORE'
\echo ' their parent (completion order, not start order).'
\echo ''
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS audit_log CASCADE;
CREATE TABLE orders (id INT PRIMARY KEY, amount NUMERIC, status TEXT);
CREATE TABLE audit_log (order_id INT, action TEXT, logged_at TIMESTAMP DEFAULT now());
CREATE OR REPLACE FUNCTION log_order_change() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (order_id, action)
VALUES (NEW.id, TG_OP || ': ' || NEW.status);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER order_audit_trigger
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION log_order_change();
CREATE OR REPLACE FUNCTION process_order(p_id INT) RETURNS VOID AS $$
BEGIN
INSERT INTO orders VALUES (p_id, 99.99, 'new');
UPDATE orders SET status = 'processed' WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;
\echo 'Expected:'
\echo ' - INSERT INTO audit_log (level 2): trigger from INSERT'
\echo ' - INSERT INTO orders (level 1): the triggering statement'
\echo ' - INSERT INTO audit_log (level 2): trigger from UPDATE'
\echo ' - UPDATE orders (level 1): the triggering statement'
\echo ''
\echo 'Note: Level 2 statements appear before level 1 because triggers'
\echo ' complete (ExecutorEnd) before the parent statement completes.'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT process_order(1);
\echo ''
-- ============================================================================
-- TEST 12: Combined with VERBOSE and BUFFERS
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 12: NESTED_STATEMENTS with VERBOSE and BUFFERS'
\echo '============================================================================'
\echo ''
\echo 'Purpose: VERBOSE and BUFFERS options are inherited by nested plans'
\echo ''
CREATE OR REPLACE FUNCTION verbose_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
SELECT COUNT(*) INTO cnt FROM products;
RETURN cnt;
END;
$$ LANGUAGE plpgsql;
\echo 'Expected: Schema-qualified names (public.products) and Output columns'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, VERBOSE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT verbose_func();
\echo ''
-- ============================================================================
-- TEST 13: Statement Numbering = Completion Order
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 13: Statement Numbering Shows Completion Order'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Statement #N reflects when a statement FINISHES (ExecutorEnd),'
\echo ' not when it starts. This is most visible with triggers:'
\echo ''
\echo ' Timeline for INSERT with an AFTER trigger:'
\echo ' 1. Parent INSERT starts executing'
\echo ' 2. Trigger fires → trigger INSERT starts → trigger INSERT FINISHES → gets #1'
\echo ' 3. Parent INSERT FINISHES → gets #2'
\echo ''
\echo ' The trigger statement finishes INSIDE the parent, so it gets a lower number.'
\echo ''
DROP TABLE IF EXISTS demo_orders CASCADE;
DROP TABLE IF EXISTS demo_log CASCADE;
CREATE TABLE demo_orders (id INT, item TEXT);
CREATE TABLE demo_log (msg TEXT);
CREATE OR REPLACE FUNCTION demo_log_trigger() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO demo_log VALUES ('order placed: ' || NEW.item);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER demo_after_insert
AFTER INSERT ON demo_orders
FOR EACH ROW EXECUTE FUNCTION demo_log_trigger();
CREATE OR REPLACE FUNCTION place_order() RETURNS void AS $$
BEGIN
INSERT INTO demo_orders VALUES (1, 'Widget'); -- has trigger
INSERT INTO demo_orders VALUES (2, 'Gadget'); -- has trigger
END;
$$ LANGUAGE plpgsql;
\echo 'Expected numbering (completion order):'
\echo ' #1 (level 2): INSERT INTO demo_log ← trigger finishes first'
\echo ' #2 (level 1): INSERT INTO demo_orders (Widget) ← parent finishes second'
\echo ' #3 (level 2): INSERT INTO demo_log ← second trigger finishes'
\echo ' #4 (level 1): INSERT INTO demo_orders (Gadget) ← second parent finishes'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT place_order();
\echo ''
\echo 'Key takeaway: Lower statement number = finished earlier.'
\echo 'Triggers finish inside their parent, so they always get lower numbers.'
\echo ''
-- ============================================================================
-- TEST 14: BEGIN/ROLLBACK Safety Pattern
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 14: BEGIN/ROLLBACK - Safe Analysis of Data-Modifying Functions'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Demonstrate the recommended pattern for safely analyzing'
\echo ' functions that modify data without persisting changes.'
\echo ''
\echo 'Pattern:'
\echo ' BEGIN;'
\echo ' EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT my_function();'
\echo ' ROLLBACK;'
\echo ''
\echo 'The function executes (so we get real plans with actual rows),'
\echo 'but ROLLBACK undoes all changes.'
\echo ''
DROP TABLE IF EXISTS safe_orders CASCADE;
CREATE TABLE safe_orders (id INT, item TEXT, status TEXT);
INSERT INTO safe_orders VALUES (1, 'Widget', 'pending'), (2, 'Gadget', 'pending');
CREATE OR REPLACE FUNCTION process_all_orders() RETURNS INT AS $$
DECLARE processed INT := 0;
BEGIN
UPDATE safe_orders SET status = 'processing' WHERE status = 'pending';
INSERT INTO safe_orders VALUES (3, 'Bonus', 'new');
DELETE FROM safe_orders WHERE item = 'Gadget';
GET DIAGNOSTICS processed = ROW_COUNT;
RETURN processed;
END;
$$ LANGUAGE plpgsql;
\echo '--- Before: ---'
SELECT * FROM safe_orders;
\echo ''
\echo '--- EXPLAIN inside BEGIN/ROLLBACK: ---'
BEGIN;
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT process_all_orders();
ROLLBACK;
\echo ''
\echo '--- After ROLLBACK (data unchanged): ---'
SELECT * FROM safe_orders;
\echo ''
\echo 'Result: We got full execution plans with actual row counts,'
\echo ' but the data is unchanged after ROLLBACK.'
\echo ''
-- ============================================================================
-- TEST 15: Error During EXPLAIN Does Not Crash (Bug 1 Fix)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 15: Error During EXPLAIN Does Not Crash Server'
\echo '============================================================================'
\echo ''
\echo 'Purpose: If EXPLAIN errors (e.g., division by zero), hooks must be'
\echo ' cleaned up so subsequent queries do not crash the backend.'
\echo ''
CREATE OR REPLACE FUNCTION divz_plpgsql(x int) RETURNS int LANGUAGE plpgsql AS $$
DECLARE r int;
BEGIN
SELECT 1/x INTO r;
RETURN r;
END;
$$;
-- This errors - hooks must be cleaned up via PG_FINALLY
EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT divz_plpgsql(0);
\echo 'After error - next query (should not crash):'
SELECT 1 AS post_error_test;
\echo 'After error - EXPLAIN NESTED_STATEMENTS again:'
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT 1;
\echo 'TEST 15: Server survived the error — hooks cleaned up correctly'
\echo ''
-- ============================================================================
-- TEST 16: Nested EXPLAIN Does Not Crash (Bug 2 Fix)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 16: Nested EXPLAIN Does Not Crash Server'
\echo '============================================================================'
\echo ''
\echo 'Purpose: A function that internally runs EXPLAIN (NESTED_STATEMENTS)'
\echo ' should not corrupt the outer EXPLAIN state (reentrancy guard).'
\echo ''
CREATE OR REPLACE FUNCTION f_nested_explain() RETURNS void LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE 'EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT 1'
LOOP NULL; END LOOP;
END;
$$;
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT f_nested_explain();
\echo 'TEST 16: Server survived nested EXPLAIN — reentrancy guard works'
\echo ''
-- ============================================================================
-- TEST 17: Memory Context Cleanup (Bug 3 Fix)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 17: Memory Context Properly Freed After EXPLAIN'
\echo '============================================================================'
\echo ''
\echo 'Purpose: The dedicated memory context for nested plans should not'
\echo ' persist after EXPLAIN completes (no memory leak).'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT 1;
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT 1;
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT 1;
\echo 'Memory contexts named "Nested EXPLAIN plans" (should be 0 rows):'
SELECT name FROM pg_backend_memory_contexts WHERE name = 'Nested EXPLAIN plans';
\echo 'TEST 17: 0 rows above = memory context freed after EXPLAIN'
\echo ''
-- ============================================================================
-- TEST 18: Memory Context Does Not Grow Across Repeated Calls
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 18: Memory Context Does Not Grow Across Repeated Calls'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Run EXPLAIN NESTED_STATEMENTS 20 times and verify no memory'
\echo ' context persists or accumulates between calls.'
\echo ''
CREATE TABLE IF NOT EXISTS mem_test (id INT, val TEXT);
INSERT INTO mem_test VALUES (1, 'a'), (2, 'b');
CREATE OR REPLACE FUNCTION mem_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
SELECT COUNT(*) INTO cnt FROM mem_test;
UPDATE mem_test SET val = val || 'x' WHERE id = 1;
RETURN cnt;
END;
$$ LANGUAGE plpgsql;
-- Run 20 times
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
\echo 'After 20 runs - "Nested EXPLAIN plans" contexts (should be 0 rows):'
SELECT name FROM pg_backend_memory_contexts WHERE name = 'Nested EXPLAIN plans';
\echo 'TEST 18: 0 rows above = no memory accumulation after 20 runs'
\echo ''
-- ============================================================================
-- TEST 19: Stress Test - 50 Nested Statements
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 19: Stress Test - Function With 50 Nested Statements'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Verify the feature handles many nested statements without'
\echo ' crashing or corrupting memory.'
\echo ''
DROP TABLE IF EXISTS stress_table;
CREATE TABLE stress_table (id INT, val TEXT);
INSERT INTO stress_table SELECT g, 'row_' || g FROM generate_series(1, 100) g;
CREATE OR REPLACE FUNCTION stress_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 1;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 2;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 3;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 4;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 5;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 6;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 7;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 8;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 9;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 10;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 11;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 12;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 13;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 14;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 15;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 16;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 17;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 18;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 19;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 20;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 21;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 22;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 23;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 24;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 25;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 26;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 27;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 28;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 29;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 30;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 31;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 32;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 33;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 34;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 35;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 36;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 37;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 38;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 39;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 40;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 41;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 42;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 43;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 44;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 45;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 46;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 47;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 48;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 49;
SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 50;
RETURN cnt;
END;
$$ LANGUAGE plpgsql;
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT stress_func();
\echo ''
\echo 'Server healthy after stress:'
SELECT 1 AS server_healthy;
\echo 'No memory leak after stress:'
SELECT name FROM pg_backend_memory_contexts WHERE name = 'Nested EXPLAIN plans';
\echo 'TEST 19: 50 nested statements captured, server healthy, no leak'
\echo ''
-- ============================================================================
-- Cleanup
-- ============================================================================
DROP FUNCTION IF EXISTS simple_function();
DROP FUNCTION IF EXISTS inner_perform();
DROP FUNCTION IF EXISTS outer_perform();
DROP FUNCTION IF EXISTS inner_expr();
DROP FUNCTION IF EXISTS outer_expr();
DROP FUNCTION IF EXISTS shared_inner();
DROP FUNCTION IF EXISTS caller_perform();
DROP FUNCTION IF EXISTS caller_assign();
DROP FUNCTION IF EXISTS sql_level3();
DROP FUNCTION IF EXISTS sql_level2();
DROP FUNCTION IF EXISTS plpgsql_sql_caller();
DROP FUNCTION IF EXISTS chain_level1();
DROP FUNCTION IF EXISTS chain_level2();
DROP FUNCTION IF EXISTS chain_level3();
DROP FUNCTION IF EXISTS recursive_func(INT);
DROP FUNCTION IF EXISTS exception_func();
DROP FUNCTION IF EXISTS process_order(INT);
DROP TRIGGER IF EXISTS order_audit_trigger ON orders;
DROP FUNCTION IF EXISTS log_order_change();
DROP FUNCTION IF EXISTS verbose_func();
DROP FUNCTION IF EXISTS place_order();
DROP TRIGGER IF EXISTS demo_after_insert ON demo_orders;
DROP FUNCTION IF EXISTS demo_log_trigger();
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS t1, t2, t3 CASCADE;
DROP TABLE IF EXISTS counter_log;
DROP TABLE IF EXISTS safe_table;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS audit_log;
DROP TABLE IF EXISTS demo_orders CASCADE;
DROP TABLE IF EXISTS demo_log;
DROP TABLE IF EXISTS safe_orders CASCADE;
DROP FUNCTION IF EXISTS process_all_orders();
DROP FUNCTION IF EXISTS divz_plpgsql(int);
DROP FUNCTION IF EXISTS f_nested_explain();
DROP FUNCTION IF EXISTS mem_func();
DROP FUNCTION IF EXISTS stress_func();
DROP TABLE IF EXISTS mem_test;
DROP TABLE IF EXISTS stress_table;
\echo '============================================================================'
\echo 'Test Suite Complete'
\echo '============================================================================'
\echo ''
\echo 'KEY FINDING: Nesting levels reflect executor call stack depth.'
\echo ''
\echo ' PERFORM func() / SELECT func() INTO var:'
\echo ' → Creates new ExecutorRun → deeper nesting level'
\echo ''
\echo ' result := func():'
\echo ' → ExecEvalFunc (no new ExecutorRun) → SAME nesting level'
\echo ''
\echo 'Tests Covered:'
\echo ' 1. Validation (requires ANALYZE)'
\echo ' 2. Simple PL/pgSQL (all level 1)'
\echo ' 3. PERFORM pattern (creates deeper levels)'
\echo ' 4. Expression assignment (stays at same level)'
\echo ' 5. Side-by-side comparison of both patterns'
\echo ' 6. SQL function nesting (true SQL nesting)'
\echo ' 7. Three-level chain with PERFORM'
\echo ' 8. Recursive function (increasing levels)'
\echo ' 9. Exception handling blocks'
\echo ' 10. No nested statements (plain query)'
\echo ' 11. Trigger-fired nested statements'
\echo ' 12. Combined with VERBOSE and BUFFERS'
\echo ' 13. Statement numbering = completion order (triggers demo)'
\echo ' 14. BEGIN/ROLLBACK safety pattern'
\echo ' 15. Error during EXPLAIN does not crash (Bug 1 fix)'
\echo ' 16. Nested EXPLAIN does not crash (Bug 2 fix)'
\echo ' 17. Memory context cleanup (Bug 3 fix)'
\echo ' 18. Memory context does not grow across repeated calls'
\echo ' 19. Stress test - 50 nested statements'
\echo '============================================================================'