plpgsql: properly handle COMMIT/ROLLBACK in nested procedures

[DrewKimball]

Postgres allows using COMMIT/ROLLBACK in nested routines under the following conditions (from the docs):

Transaction control is only possible in CALL or DO invocations from the top level or
nested CALL or DO invocations without any other intervening command. For example,
if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and
third procedures can perform transaction control actions. But if the call stack is
CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do
transaction control, because of the SELECT in between.

In the long term, we should allow COMMIT/ROLLBACK in this case, and check for intervening statements to produce the correct error. For 24.1, we should just disallow a nested routine from using COMMIT/ROLLBACK entirely.

Jira issue: CRDB-37782

[DrewKimball]

Right now, this can lead to unexpected behavior since the deferred CALL execution isn't set up to handle nested routines:

CREATE PROCEDURE test_proc7c(x int, INOUT a int, INOUT b numeric)
LANGUAGE plpgsql
AS $$
BEGIN
  a := x * 10;
  b := x * 2;
  COMMIT;
END;
$$;

CREATE PROCEDURE test_proc7cc(_x int)
LANGUAGE plpgsql
AS $$
DECLARE _a int; _b numeric;
BEGIN
  CALL test_proc7c(_x, _a, _b);
  RAISE NOTICE '_x: %,_a: %, _b: %', _x, _a, _b;
END
$$;

CALL test_proc7cc(10);

Result:

NOTICE: _x: 10,_a: <NULL>, _b: <NULL>
CALL
ERROR: number of field descriptions must equal number of destinations, got 1 and 0
SQLSTATE: XX000
HINT: You have encountered an unexpected error.

Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.

If you would rather not post publicly, please contact us directly
using the support form.

We appreciate your feedback.