On Tue, Jul 23, 2019 at 4:37 PM Dave Cramer <pg@fastcrypt.com> wrote:
Hmmm who knew you couldn't call a procedure inside a transaction. That just seems broken
Yeah, the description in the docs is:
"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. "
Since Procedures were introduced in PG 11, the workaround to invoke them with JDBC is to send the native "CALL proc()" SQL and let it be treated as a SQL statement, not a specific stored routine invocation.
1) When using transaction control inside the stored proc, an exception is generated if autoCommit is false - see example code attached.
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: invalid transaction termination
2) Output parameters are not mapped as parameters, and app code cannot use registerOutputParameter or getInt() style retrieval. Instead, outputs are left in the result set and app code must retrieve the result and pull, creating a big difference between how Procedures and Functions are invoked.
I propose improving support for procedures. Either:
(1) add support for "CALL proc()" to be treated as a routine invocation so that output parameters can be registered, no begin transaction is silently sent from driver, and calling a procedure and calling a function would be very similar (only differing in function still using the {call} escape syntax.
or
(2) change the {call} syntax to optionally support procedures. {? = call} would still be mapped to functions. Add a connection setting to control this change, and make default false, so that default stays backwards compatible with pre pg11 functionality.