PRAGMA AUTONOMOUS_TRANSACTION fails in standalone procedures but works in packages

[rophy]

Summary

PRAGMA AUTONOMOUS_TRANSACTION with COMMIT fails in standalone procedures with "invalid transaction termination" error, but works correctly when the same code is in a package procedure.

Environment

• IvorySQL version: 5.0 (current HEAD)
• Database mode: Oracle compatibility mode (initdb -m oracle)

Reproduction

-- Setup
CREATE EXTENSION IF NOT EXISTS dblink;
CREATE TABLE auto_tx_test (id INT, msg TEXT);

-- Test 1: Package procedure - WORKS
CREATE OR REPLACE PACKAGE pkg_auto_tx IS
  PROCEDURE insert_row;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_auto_tx IS
  PROCEDURE insert_row IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO auto_tx_test VALUES (1, 'from_package');
    COMMIT;
  END;
END;
/

-- Test 2: Standalone procedure - FAILS
CREATE OR REPLACE PROCEDURE standalone_auto_tx IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO auto_tx_test VALUES (2, 'from_standalone');
  COMMIT;
END;
/

-- Run tests
TRUNCATE auto_tx_test;
CALL pkg_auto_tx.insert_row();
SELECT * FROM auto_tx_test;  -- Returns 1 row (works)

TRUNCATE auto_tx_test;
CALL standalone_auto_tx();   -- ERROR: invalid transaction termination
SELECT * FROM auto_tx_test;  -- Returns 0 rows (failed)

Expected Behavior

Both package procedures and standalone procedures with PRAGMA AUTONOMOUS_TRANSACTION should work identically, as they do in Oracle Database.

Actual Behavior

• Package procedure: Works correctly, row is inserted
• Standalone procedure: Fails with error:

  ERROR:  invalid transaction termination
  CONTEXT:  PL/iSQL function standalone_auto_tx() line 4 at COMMIT
            while executing command on unnamed dblink connection
            PL/iSQL function standalone_auto_tx() during function entry
  

Test Results Summary

Test Case	Result
Package procedure with autonomous TX	PASS
Standalone procedure with autonomous TX	FAIL
Package calling standalone with autonomous TX	FAIL
Package calling package with autonomous TX	PASS

Analysis

IvorySQL implements autonomous transactions using dblink. The issue appears to be specific to how standalone procedures handle the dblink-based transaction context vs. how package procedures handle it.

[rophy]

Root Cause Identified and Fixed

The issue was in src/backend/commands/functioncmds.c in the ExecuteCallStmt function.

Root Cause

• All PL/iSQL procedures have prosecdef=true by default
• For standalone procedures (FUNC_FROM_PG_PROC), line 3048-3049 checks prosecdef and forces atomic=true
• For package procedures (FUNC_FROM_PACKAGE), this check is skipped via SKIP_PG_PROC_CHECK
• When atomic=true, COMMIT/ROLLBACK are not allowed

Fix

Check the plisql.inside_autonomous_transaction GUC before forcing atomic=true. When running inside an autonomous transaction context (via dblink), the security context is already isolated in the new session, so transaction commands can be safely allowed.

The fix is in commit e6c5f4143b on branch test/merged.