Skip to content

fix: allow COMMIT/ROLLBACK in nested PL/iSQL procedure calls #1125

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
gaoxueyu merged 1 commit into from
Feb 10, 2026
Merged

fix: allow COMMIT/ROLLBACK in nested PL/iSQL procedure calls #1125

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
38 changes: 38 additions & 0 deletions src/oracle_test/regress/expected/ora_package.out
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -6278,6 +6278,44 @@ call test_pkg.test_p1(NULL, 23);
(1 row)

DROP package test_pkg;
-- Test COMMIT in nested package procedure calls (Issue #1007)
CREATE TABLE test_nested_commit (id int);
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
INSERT INTO test_nested_commit VALUES (1);
COMMIT;
INSERT INTO test_nested_commit VALUES (2);
END;
PROCEDURE main IS
BEGIN
INSERT INTO test_nested_commit VALUES (0);
do_commit();
INSERT INTO test_nested_commit VALUES (3);
END;
END;
/
TRUNCATE test_nested_commit;
BEGIN
pkg_commit_test.main();
END;
/
SELECT * FROM test_nested_commit ORDER BY id;
id
----
0
1
2
3
(4 rows)

DROP PACKAGE pkg_commit_test;
DROP TABLE test_nested_commit;
--clean data
RESET ivorysql.allow_out_parameter_const;
DROP FUNCTION test_event_trigger;
Expand Down
36 changes: 36 additions & 0 deletions src/oracle_test/regress/sql/ora_package.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -6094,6 +6094,42 @@ call test_pkg.test_p1(NULL, 23);

DROP package test_pkg;

-- Test COMMIT in nested package procedure calls (Issue #1007)
CREATE TABLE test_nested_commit (id int);

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
INSERT INTO test_nested_commit VALUES (1);
COMMIT;
INSERT INTO test_nested_commit VALUES (2);
END;

PROCEDURE main IS
BEGIN
INSERT INTO test_nested_commit VALUES (0);
do_commit();
INSERT INTO test_nested_commit VALUES (3);
END;
END;
/

TRUNCATE test_nested_commit;
BEGIN
pkg_commit_test.main();
END;
/
SELECT * FROM test_nested_commit ORDER BY id;

DROP PACKAGE pkg_commit_test;
DROP TABLE test_nested_commit;

--clean data
RESET ivorysql.allow_out_parameter_const;
DROP FUNCTION test_event_trigger;
Expand Down
160 changes: 158 additions & 2 deletions src/pl/plisql/src/expected/plisql_transaction.out
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -472,6 +472,7 @@ INSERT INTO test4 SELECT repeat('xyzzy', 2000);
-- that will return a still-toasted value
CREATE FUNCTION data_source(i int) RETURNS TEXT LANGUAGE sql
AS 'select f1 from test4' IMMUTABLE;
/
DO $$
declare x text;
begin
Expand All @@ -481,9 +482,8 @@ begin
end loop;
raise notice 'length(x) = %', length(x);
end $$;
NOTICE: length(x) = 10000
/
ERROR: invalid transaction termination
CONTEXT: PL/iSQL function inline_code_block line 6 at COMMIT
-- operations on composite types vs. internal transactions
DO LANGUAGE plisql $$
declare
Expand Down Expand Up @@ -748,6 +748,162 @@ SELECT * FROM test1;
2 |
(2 rows)

-- Test nested procedure calls with COMMIT/ROLLBACK (Issue #1007)
--
-- Note: Oracle-syntax procedures (CREATE PROCEDURE ... IS) default to
-- AUTHID DEFINER (prosecdef=true), which forces atomic mode and blocks
-- COMMIT/ROLLBACK. Use AUTHID CURRENT_USER to allow transaction control.
-- This matches Oracle behavior where COMMIT is allowed regardless of AUTHID.
-- Tests below verify COMMIT/ROLLBACK in nested procedure calls
-- using AUTHID CURRENT_USER (Oracle-compatible syntax).
-- Without AUTHID CURRENT_USER, Oracle-syntax procedures default to
-- SECURITY DEFINER (prosecdef=true), which forces atomic mode and
-- blocks COMMIT/ROLLBACK. This is a known limitation (see Test 0).
CREATE TABLE test_nested_commit (id int);
-- Inner procedure with COMMIT
CREATE OR REPLACE PROCEDURE nested_inner_commit AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (1);
COMMIT;
INSERT INTO test_nested_commit VALUES (2);
END;
/
-- Outer procedure calling inner with CALL keyword
CREATE OR REPLACE PROCEDURE nested_outer_commit AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (0);
CALL nested_inner_commit();
INSERT INTO test_nested_commit VALUES (3);
END;
/
-- Test 1: Basic nested call with COMMIT
TRUNCATE test_nested_commit;
CALL nested_outer_commit();
SELECT * FROM test_nested_commit ORDER BY id;
id
----
0
1
2
3
(4 rows)

-- Test 2: Oracle-style call (without CALL keyword) with COMMIT
CREATE OR REPLACE PROCEDURE nested_outer_oracle_style AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (10);
nested_inner_commit(); -- Oracle-style call
INSERT INTO test_nested_commit VALUES (13);
END;
/
TRUNCATE test_nested_commit;
CALL nested_outer_oracle_style();
SELECT * FROM test_nested_commit ORDER BY id;
id
----
1
2
10
13
(4 rows)

-- Test 3: Deeply nested Oracle-style calls (4 levels) with COMMIT
CREATE OR REPLACE PROCEDURE nested_level4 AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (104);
COMMIT;
INSERT INTO test_nested_commit VALUES (105);
END;
/
CREATE OR REPLACE PROCEDURE nested_level3 AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (103);
nested_level4(); -- Oracle-style call
INSERT INTO test_nested_commit VALUES (106);
END;
/
CREATE OR REPLACE PROCEDURE nested_level2 AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (102);
nested_level3(); -- Oracle-style call
INSERT INTO test_nested_commit VALUES (107);
END;
/
CREATE OR REPLACE PROCEDURE nested_level1 AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (101);
nested_level2(); -- Oracle-style call
INSERT INTO test_nested_commit VALUES (108);
END;
/
TRUNCATE test_nested_commit;
CALL nested_level1();
SELECT * FROM test_nested_commit ORDER BY id;
id
-----
101
102
103
104
105
106
107
108
(8 rows)

-- Test 4: ROLLBACK in nested procedure with CALL keyword
CREATE OR REPLACE PROCEDURE nested_inner_rollback AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (201);
ROLLBACK;
INSERT INTO test_nested_commit VALUES (202);
END;
/
CREATE OR REPLACE PROCEDURE nested_outer_rollback AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (200);
CALL nested_inner_rollback();
INSERT INTO test_nested_commit VALUES (203);
END;
/
TRUNCATE test_nested_commit;
CALL nested_outer_rollback();
SELECT * FROM test_nested_commit ORDER BY id;
id
-----
202
203
(2 rows)

-- Test 5: Oracle-style call (without CALL keyword) with ROLLBACK
CREATE OR REPLACE PROCEDURE nested_outer_rollback_oracle_style AUTHID CURRENT_USER IS
BEGIN
INSERT INTO test_nested_commit VALUES (300);
nested_inner_rollback(); -- Oracle-style call
INSERT INTO test_nested_commit VALUES (303);
END;
/
TRUNCATE test_nested_commit;
CALL nested_outer_rollback_oracle_style();
SELECT * FROM test_nested_commit ORDER BY id;
id
-----
202
303
(2 rows)

-- Clean up nested commit tests
DROP PROCEDURE nested_inner_commit;
DROP PROCEDURE nested_outer_commit;
DROP PROCEDURE nested_outer_oracle_style;
DROP PROCEDURE nested_level1;
DROP PROCEDURE nested_level2;
DROP PROCEDURE nested_level3;
DROP PROCEDURE nested_level4;
DROP PROCEDURE nested_inner_rollback;
DROP PROCEDURE nested_outer_rollback;
DROP PROCEDURE nested_outer_rollback_oracle_style;
DROP TABLE test_nested_commit;
DROP TABLE test1;
DROP TABLE test2;
DROP TABLE test3;
6 changes: 6 additions & 0 deletions src/pl/plisql/src/pl_gram.y
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2737,6 +2737,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;
Copy link
Copy Markdown
Collaborator

@yuanyl630 yuanyl630 Jan 26, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Good catch！requires_procedure_resowner is necessary for stmt_execsql;


$$ = (PLiSQL_stmt *)new;
}
else
Expand Down Expand Up @@ -2764,6 +2767,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
Expand Down
Loading