Re: Procedure support improvements - Mailing list pgsql-jdbc

From Dave Cramer
Subject Re: Procedure support improvements
Date
Msg-id CADK3HHKhbUQey2-XKARdK2Uyx29QLcj1Es+4qGpWqVz+FfuCgw@mail.gmail.com
Whole thread Raw
In response to Re: Procedure support improvements  (Greg Nancarrow <gregn4422@gmail.com>)
Responses Re: Procedure support improvements  (Greg Nancarrow <gregn4422@gmail.com>)
List pgsql-jdbc
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.



On Thu, 22 Aug 2019 at 01:39, Greg Nancarrow <gregn4422@gmail.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.rader@gmail.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?
>
>
>
>


pgsql-jdbc by date:

Previous
From: Greg Nancarrow
Date:
Subject: Re: Procedure support improvements
Next
From: Greg Nancarrow
Date:
Subject: Re: Procedure support improvements