Re: Procedure support improvements

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?
> >
> >
> >
> >
>
>
>