Re: Procedure support improvements

Dave,

Thanks for responding.

You said that "Currently you cannot call a procedure inside a transaction".
That doesn't seem to be true. You CAN call a procedure inside a
transaction, provided that the procedure doesn't execute transaction
control statements (e.g. COMMIT/ROLLBACK).

From the Notes section of the PostgreSQL CALL documentation:

"If CALL is executed in a transaction block, then the called procedure
cannot execute transaction control statements. Transaction control
statements are only allowed if CALL is executed in its own
transaction."

So you can definitely call a procedure inside a transaction.

A stored procedure is the natural fit for complex reusable processing
(complex logic and data access), whereas a stored function is a
routine that returns values.
I'm sure that new users who start using PostgreSQL 11+, and those
migrating from other DBMSs, would have that kind of viewpoint. They'd
naturally be creating stored procedures for various complex reusable
processing (that does not necessarily need to commit/rollback
transactions within the procedure).
Currently, they wouldn't be able to successfully invoke those stored
procedures with PGJDBC using the escape call syntax ("ERROR: xxxx is a
procedure Hint: To call a procedure, use CALL"), and there would be
problems (already stated) with resorting to using native CALL with
PGJDBC. It's not a great user experience.
Forcing the user to use a (void) function instead of a stored
procedure for such cases, in order to be able to invoke it from
PGJDBC, could be seen as more of a kludge!

Greg

On Thu, Aug 22, 2019 at 8:45 PM Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
> Greg,
>
> While I understand the frustration I think more work needs to be done by the server to make this a useful feature.
> Currently you cannot call a procedure inside a transaction and from what I can see here https://docs.jboss.org/hibernate/core/3.3/reference/en/html/transactions.html specifically "Hibernate disables, or expects the application server to disable, auto-commit mode immediately. Database transactions are never optional. All communication with a database has to occur inside a transaction." I fail to see how this would work?
>
> AFAIK we need autonomous transactions to be implemented and ideally some mechanism to call functions or procedures with the same syntax.
>
> I think we need to be pressing the people who committed procedures to complete the work they started. Fixing this in the drivers will just end up being a kludge at best.
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
>
> On Thu, 22 Aug 2019 at 01:39, Greg Nancarrow <gregn4422(at)gmail(dot)com> wrote:
>>
>> >(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.
>>
>> Given that stored procedures were added in PG11, but PGJDBC doesn't
>> support calling them using JDBC's escape call syntax ("{call ...}"), I
>> agree that an option to allow it is required, and would be beneficial.
>>
>> Resorting to using the Postgres-native "CALL ..." is not always
>> viable, for reasons such as:
>> - It's not really desirable to use "non-portable" JDBC code.
>> - You can't use PGJDBC and native "CALL ..." to invoke PostgreSQL
>> stored procedures that have INOUT arguments.
>> For example, if you attempt to invoke registerOutParameter() on a
>> CallableStatement in this case, it results in the following error:
>> This statement does not declare an OUT parameter. Use { ?=
>> call ... } to declare one.
>> - Some software such as ORMs (e.g. JPA implementations like Hibernate,
>> and similar) generate JDBC code that uses the JDBC escape call syntax
>> (with the expectation that it will work), but attempted invocation of
>> PostgreSQL stored procedures using such code fails (since PGJDBC
>> always transforms the JDBC escape call syntax into a SELECT statement,
>> which can only invoke functions, not stored procedures).
>>
>> Inability to support stored procedure invocation via the JDBC escape
>> call syntax might also be viewed as a(nother) migration issue, for
>> those wishing to migrate to PostgreSQL from another database vendor.
>>
>> The suggested optional connection setting for JDBC escape call syntax
>> could be more granular than true/false.
>> For example, it could support different modes to:
>> - map to SELECT always (default)
>> - map to CALL if no return value
>> i.e. when "{call …}" is specified
>> - map to CALL if no return or output parameters
>> i.e. when "{call …}" is specified, and no out parameters are registered
>> - map to CALL always
>>
>>
>> Greg Nancarrow
>> Fujitsu Australia
>>
>>
>> On Thu, Aug 22, 2019 at 3:03 PM David Rader <david(dot)rader(at)gmail(dot)com> 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?
>> >
>> >
>> >
>> >
>>
>>