fix: allow COMMIT in nested procedure calls for Oracle compatibility

rophy · rophy · commit 307b0665fa14 · 2025-12-07T11:34:14.000Z
- Make anonymous blocks (DO) and CALL statements non-atomic in Oracle mode
- Set requires_procedure_resowner for Oracle-style procedure calls
- Update expected test output for plisql_transaction
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
@@ -67,6 +67,7 @@
 #include "utils/acl.h"
 #include "utils/guc.h"
 #include "utils/lsyscache.h"
+#include "utils/ora_compatible.h"
 
 /* Hook for plugins to get control in ProcessUtility() */
 ProcessUtility_hook_type ProcessUtility_hook = NULL;
@@ -713,7 +714,25 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 			break;
 
 		case T_DoStmt:
-			ExecuteDoStmt(pstate, (DoStmt *) parsetree, isAtomicContext, params, dest);
+			/*
+			 * In Oracle compatibility mode, anonymous blocks are non-atomic
+			 * by default when executed at the top level or in a non-atomic
+			 * context, allowing COMMIT/ROLLBACK within the block and in
+			 * any procedures called from it.  However, if we're already in
+			 * an atomic context (e.g., called from a function), we must
+			 * respect that.
+			 */
+			{
+				bool	doAtomicContext = isAtomicContext;
+
+				if (compatible_db == ORA_PARSER &&
+					(context == PROCESS_UTILITY_TOPLEVEL ||
+					 context == PROCESS_UTILITY_QUERY_NONATOMIC))
+					doAtomicContext = false;
+
+				ExecuteDoStmt(pstate, (DoStmt *) parsetree,
+							  doAtomicContext, params, dest);
+			}
 			break;
 
 		case T_CreateTableSpaceStmt:
@@ -857,7 +876,24 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 			break;
 
 		case T_CallStmt:
-			ExecuteCallStmt(castNode(CallStmt, parsetree), params, isAtomicContext, dest);
+			/*
+			 * In Oracle compatibility mode, CALL statements are non-atomic
+			 * by default when executed at the top level or in a non-atomic
+			 * context, allowing COMMIT/ROLLBACK within the called procedure
+			 * and any nested calls.  However, if we're already in an atomic
+			 * context (e.g., called from a function), we must respect that.
+			 */
+			{
+				bool	callAtomicContext = isAtomicContext;
+
+				if (compatible_db == ORA_PARSER &&
+					(context == PROCESS_UTILITY_TOPLEVEL ||
+					 context == PROCESS_UTILITY_QUERY_NONATOMIC))
+					callAtomicContext = false;
+
+				ExecuteCallStmt(castNode(CallStmt, parsetree), params,
+								callAtomicContext, dest);
+			}
 			break;
 
 		case T_ClusterStmt:
diff --git a/src/pl/plisql/src/expected/plisql_transaction.out b/src/pl/plisql/src/expected/plisql_transaction.out
@@ -55,8 +55,6 @@ CONTEXT:  PL/iSQL function transaction_test1(pg_catalog.int4,text) line 6 at COM
 COMMIT;
 START TRANSACTION;
 DO LANGUAGE plisql $$ BEGIN COMMIT; END $$;
-ERROR:  invalid transaction termination
-CONTEXT:  PL/iSQL function inline_code_block line 1 at COMMIT
 COMMIT;
 TRUNCATE test1;
 -- not allowed in a function
@@ -482,8 +480,7 @@ begin
   raise notice 'length(x) = %', length(x);
 end $$;
 /
-ERROR:  invalid transaction termination
-CONTEXT:  PL/iSQL function inline_code_block line 6 at COMMIT
+NOTICE:  length(x) = 10000
 -- operations on composite types vs. internal transactions
 DO LANGUAGE plisql $$
 declare
diff --git a/src/pl/plisql/src/pl_gram.y b/src/pl/plisql/src/pl_gram.y
@@ -2717,6 +2717,9 @@ stmt_execsql	: K_IMPORT
 							new->expr = build_call_expr(T_WORD, @1, &yylval, &yylloc, yyscanner);
 							new->is_call = true;
 
+							/* Remember we may need a procedure resource owner */
+							plisql_curr_compile->requires_procedure_resowner = true;
+
 							$$ = (PLiSQL_stmt *)new;
 						}
 						else
@@ -2744,6 +2747,9 @@ stmt_execsql	: K_IMPORT
 							new->expr = build_call_expr(T_CWORD, @1, &yylval, &yylloc, yyscanner);
 							new->is_call = true;
 
+							/* Remember we may need a procedure resource owner */
+							plisql_curr_compile->requires_procedure_resowner = true;
+
 							$$ = (PLiSQL_stmt *)new;
 						}
 						else
