Re: Procedure support improvements

On Tue, Jul 23, 2019 at 4:37 PM Dave Cramer <[email protected]> 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. "
https://www.postgresql.org/docs/11/plpgsql-transactions.html


Which means to be able to call procedures that use commit or rollback you
have to be able to call them without a begin...




> Dave Cramer
>
> [email protected]
> www.postgresintl.com
>
>
> On Sun, 21 Jul 2019 at 13:31, David Rader <[email protected]> wrote:
>
>> Hello -
>>
>> 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.
>>
>> Thoughts?
>>
>>
>>
>>
>>