Oracle incompatibility: COMMIT inside EXCEPTION-enabled blocks fails

[rophy]

Bug Report

IvorySQL Version

master (commit 7261f1c) as container

OS Version (uname -a)

Linux 6.8.0-87-generic #88-Ubuntu SMP PREEMPT_DYNAMIC x86_64 GNU/Linux

Configuration options (config.status --config)

'--prefix=/home/ivorysql/ivorysql' '--enable-debug' '--enable-cassert' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt'

Current Behavior

COMMIT and ROLLBACK statements fail with the error:

cannot commit while a subtransaction is active

or

cannot roll back while a subtransaction is active

This occurs when the COMMIT/ROLLBACK is inside a PL/pgSQL or PL/iSQL block that has an EXCEPTION handler, even if the COMMIT is in the main BEGIN block (not inside the EXCEPTION handler itself).

Expected behavior/code

In Oracle PL/SQL, COMMIT and ROLLBACK work correctly inside procedures that have EXCEPTION handlers. The expected behavior is that COMMIT should succeed in the main block regardless of whether an EXCEPTION handler exists.

Oracle example that works:

CREATE OR REPLACE PROCEDURE my_proc AS
BEGIN
 INSERT INTO log_table VALUES ('test');
 COMMIT; -- Works in Oracle
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
END;
/

Steps to reproduce

-- Setup
DROP TABLE IF EXISTS test_log CASCADE;
CREATE TABLE test_log (id SERIAL, msg TEXT);

-- Test 1: COMMIT without EXCEPTION block - WORKS
DO $$
BEGIN
 INSERT INTO test_log(msg) VALUES ('no exception block');
 COMMIT;
END;
$$;
-- Result: SUCCESS

-- Test 2: COMMIT with EXCEPTION block - FAILS
DO $$
BEGIN
 INSERT INTO test_log(msg) VALUES ('with exception block');
 COMMIT; -- ERROR: cannot commit while a subtransaction is active
EXCEPTION
 WHEN OTHERS THEN
 NULL;
END;
$$;
-- Result: ERROR

-- Test 3: Procedure with EXCEPTION handler - FAILS
CREATE OR REPLACE PROCEDURE proc_with_exception(p_msg TEXT)
LANGUAGE plpgsql AS $$
BEGIN
 INSERT INTO test_log(msg) VALUES (p_msg);
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 INSERT INTO test_log(msg) VALUES ('caught: ' || SQLERRM);
END;
$$;

CALL proc_with_exception('test');
-- Result: Error is caught, but COMMIT failed

Additional context that can be helpful for identifying the problem

Root Cause: PostgreSQL creates an implicit savepoint (subtransaction) when entering any block that has an EXCEPTION handler. This savepoint is created at the START of the block, not when the exception is raised. COMMIT and ROLLBACK cannot execute while inside a subtransaction.

Impact on Oracle Compatibility: This affects:

1. Any procedure using COMMIT/ROLLBACK with error handling
2. Migration of Oracle PL/SQL code that uses COMMIT in procedures with exception handlers

Difference from Oracle:

Feature	Oracle	PostgreSQL/IvorySQL
EXCEPTION block implementation	No subtransaction	Creates subtransaction (savepoint)
COMMIT in block with EXCEPTION	Works	Fails
ROLLBACK in block with EXCEPTION	Works	Fails

Workaround: Remove EXCEPTION handlers from procedures that need COMMIT/ROLLBACK, or restructure code to move COMMIT outside of exception-handling blocks.

References:

• PostgreSQL documentation on exception handling: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
• Related PostgreSQL behavior: Subtransactions are used to implement savepoints and exception handling

Oracle Database Test Results (Verified)

Tested on Oracle Database Free 23.26.0.0-lite to confirm expected Oracle behavior.

Test Setup (Oracle):

CREATE TABLE test_log (id NUMBER GENERATED ALWAYS AS IDENTITY, msg VARCHAR2(200));

Test 1: COMMIT without EXCEPTION block

BEGIN
 INSERT INTO test_log(msg) VALUES ('no exception block');
 COMMIT;
END;
/
-- Result: SUCCESS - row inserted

Test 2: COMMIT with EXCEPTION block

BEGIN
 INSERT INTO test_log(msg) VALUES ('with exception block');
 DBMS_OUTPUT.PUT_LINE('Before COMMIT');
 COMMIT;
 DBMS_OUTPUT.PUT_LINE('After COMMIT');
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Caught exception: ' || SQLERRM);
END;
/
-- Result: SUCCESS - prints "Before COMMIT", "After COMMIT", row inserted

Test 3: Procedure with EXCEPTION handler

CREATE OR REPLACE PROCEDURE proc_with_exception(p_msg VARCHAR2) AS
 v_err VARCHAR2(200);
BEGIN
 INSERT INTO test_log(msg) VALUES (p_msg);
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 v_err := 'caught: ' || SQLERRM;
 INSERT INTO test_log(msg) VALUES (v_err);
 COMMIT;
END;
/

EXEC proc_with_exception('test');
-- Result: SUCCESS - row inserted with msg='test'

Summary of Test Results:

Test	IvorySQL	Oracle
Test 1: COMMIT without EXCEPTION block	✅ SUCCESS	✅ SUCCESS
Test 2: COMMIT with EXCEPTION block	❌ FAILS	✅ SUCCESS
Test 3: Procedure with EXCEPTION handler	❌ FAILS	✅ SUCCESS

IvorySQL Failure Details:

• Test 2: COMMIT raises "cannot commit while a subtransaction is active", exception caught, insert rolled back
• Test 3: COMMIT fails, EXCEPTION handler catches it and logs "caught: cannot commit while a subtransaction is active"

[rophy]

Investigation Summary

Root Cause

PostgreSQL/IvorySQL uses subtransactions (via BeginInternalSubTransaction()) to implement EXCEPTION block semantics in PL/iSQL. When a block has an EXCEPTION handler, a subtransaction is created at block entry (src/pl/plisql/src/pl_exec.c:1956).

The SPI layer (src/backend/executor/spi.c:258-261) checks IsSubTransaction() and rejects COMMIT/ROLLBACK when inside a subtransaction:

if (IsSubTransaction())
 ereport(ERROR,
 (errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
 errmsg("cannot commit while a subtransaction is active")));

Oracle vs PostgreSQL/IvorySQL Semantics

Aspect	Oracle	PostgreSQL/IvorySQL
Exception block atomicity	Statement-level (only failed statement rolls back)	Block-level (entire block rolls back)
COMMIT in EXCEPTION block	✅ Allowed	❌ Blocked
Rollback scope	Per-statement implicit savepoints	Per-block subtransaction

Verified with Oracle 23.26 Free container - Oracle allows COMMIT/ROLLBACK inside EXCEPTION blocks and uses statement-level rollback.

Prototype Attempt: Remove Subtransactions

Attempted to remove BeginInternalSubTransaction() from exception block handling:

• ✅ COMMIT works in EXCEPTION blocks
• ❌ Server crashes when a SQL error occurs (e.g., duplicate key violation)

The crash occurs because PostgreSQL's error recovery relies on subtransaction rollback for:

1. Snapshot stack cleanup (portal->portalSnapshot == GetActiveSnapshot() assertion)
2. Resource owner cleanup
3. Memory context management
4. Expression state cleanup

Technical Challenges

1. Snapshot Management: Without subtransaction rollback, error handling leaves the snapshot stack inconsistent, causing assertion failures in pquery.c

2. Resource Cleanup: The subtransaction automatically cleans up expression states, tuple tables, and memory contexts on rollback - this would need manual handling

3. Nested Exception Blocks: Multiple nested EXCEPTION blocks each create their own subtransaction, complicating any solution that tries to release them before COMMIT

Possible Approaches

1. Release subtransactions before COMMIT - Track exception block nesting depth, release all before COMMIT, handle cleanup manually. After COMMIT, exception handlers would lose rollback capability (matches Oracle semantics).

2. Implement statement-level savepoints - Replace block-level subtransactions with per-statement implicit savepoints (major architectural change to match Oracle exactly).

3. Fix snapshot/resource cleanup - Keep the no-subtransaction approach but add proper cleanup in the PG_CATCH block to handle snapshot stack, resource owners, etc.

Next Steps

Need to investigate whether approach #1 or #3 is more feasible. The core challenge is that PostgreSQL's error recovery infrastructure assumes subtransactions exist for proper cleanup.

[rophy]

EDB Postgres Advanced Server Research

Researched how EDB Postgres Advanced Server (the leading Oracle-compatible PostgreSQL) handles this issue.

Key Finding: EDB Has the Same Limitation

EDB does NOT solve the COMMIT-in-EXCEPTION-block problem. Their documentation explicitly states the same error occurs:

"If a block statement has an exception section, then executing either 'commit' or 'rollback' from the block statement's executable section will cause this error: 'ERROR: 2D000: cannot commit while a subtransaction is active'"

Source: PostgreSQL mailing list discussion

EDB's Workarounds

EDB provides two mechanisms for partial Oracle compatibility:

1. edb_stmt_level_tx Parameter

• When TRUE, exceptions don't automatically roll back prior uncommitted updates
• Emulates Oracle's statement-level transaction isolation (only the failing statement rolls back)
• Does NOT allow COMMIT inside EXCEPTION blocks - just changes rollback behavior

Source: EDB Docs - edb_stmt_level_tx

2. PRAGMA AUTONOMOUS_TRANSACTION

• Allows a procedure/block to run in a completely separate transaction
• Can COMMIT independently of the calling transaction
• Limitations vs Oracle:
  • Behaves like a separate session (uses separate connection slot)
  • Limited to 16 levels of nesting
  • Not semantically identical to Oracle

Source: EDB Docs - PRAGMA AUTONOMOUS_TRANSACTION

EDB's Recommended Workaround

From EDB documentation:

"Rather than have a loop inside the BEGIN / END, you could put the BEGIN EXCEPTION END inside the loop, catch the error, store the important parts of the exception in a variable, and then do the COMMIT after the END statement but before the next iteration of the loop."

Implications for IvorySQL

1. This is a known PostgreSQL architectural limitation - even EDB with their extensive Oracle compatibility work hasn't solved it
2. Possible enhancements:
   • Implement edb_stmt_level_tx equivalent for statement-level rollback
   • Improve autonomous transaction support (already exists in feat/auto-tx branch)
3. True Oracle compatibility for COMMIT in EXCEPTION blocks would require fundamental changes to PostgreSQL's transaction architecture

[bigplaice]

Hi, Rophy, thanks for reporting issue. We'll investigate it.