Bug Report

IvorySQL Version

master

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

There are two related issues when calling a procedure containing COMMIT from within a PL/iSQL anonymous block:

Issue 1: Standalone procedures - "invalid transaction termination" error:

ERROR:  invalid transaction termination
CONTEXT:  PL/iSQL function do_commit() line 3 at COMMIT

Issue 2: Package procedures - plancache resource leak (more severe):

WARNING:  resource was not closed: plancache reference 0x...
ERROR:  plancache reference 0x... is not owned by resource owner PL/iSQL simple expressions
CONTEXT:  SQL statement "CALL do_commit()"
PL/iSQL function main line 8 at CALL

The root cause is that PostgreSQL does not allow COMMIT/ROLLBACK inside subtransactions (which anonymous blocks create). Oracle PL/SQL allows this freely.

Expected behavior/code

Oracle 23c allows COMMIT inside nested procedure calls from anonymous blocks:

Logged: Step 1 - before work
Logged: Step 2 - after work
main_proc completed successfully

PL/SQL procedure successfully completed.

Step to reproduce

Test 1: Standalone procedures (clean error)

CREATE OR REPLACE PROCEDURE do_commit IS
BEGIN
  COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE main_proc IS
BEGIN
  do_commit();
END;
/

-- This fails with "invalid transaction termination"
BEGIN
  main_proc();
END;
/

Test 2: Package procedures (plancache leak)

CREATE OR REPLACE PACKAGE pkg_commit_test IS
  PROCEDURE do_commit;
  PROCEDURE main;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_commit_test IS
  PROCEDURE do_commit IS
  BEGIN
    COMMIT;
  END;

  PROCEDURE main IS
  BEGIN
    do_commit();
  END;
END;
/

-- This fails with plancache resource leak
BEGIN
  pkg_commit_test.main();
END;
/

Additional context that can be helpful for identifying the problem

1. PostgreSQL limitation - PostgreSQL does not allow COMMIT/ROLLBACK inside subtransactions. Anonymous blocks (BEGIN...END;) create subtransactions, so any nested procedure call containing COMMIT fails.

2. Oracle compatibility gap - Oracle PL/SQL allows COMMIT inside nested calls freely. This is a common pattern for:

   • Autonomous logging procedures
   • Partial commits during long-running processes
   • Error handling with transaction control

3. Package vs standalone difference - Standalone procedures get a clean "invalid transaction termination" error, but package procedures cause a plancache resource leak which is a bug in error handling.

4. Related feature - The PRAGMA AUTONOMOUS_TRANSACTION (PR PRAGMA AUTONOMOUS_TRANSACTION Implementation #986) would provide a workaround by allowing procedures to run in independent transactions, similar to Oracle's autonomous transactions.

5. Inline COMMIT works - Placing COMMIT directly in the anonymous block (not via nested call) works without error, confirming the issue is specific to nested calls.