Re: Procedure support improvements

On Fri, 23 Aug 2019 at 01:29, Greg Nancarrow <gregn4422(at)gmail(dot)com> wrote:

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

Yes I mis-spoke, David R pointed this out to me off list.

>
> 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.
>
Historically functions in PostgreSQL have done both.

> 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).
>

I presume you have use cases that do not do transactions ?

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

Well we have successfully been doing that for a number of years now.

I'd still like to see pressure put on the server to fix this problem. If
the interfaces continually work around deficiencies then nothing gets done
in the server. It's my (and others) opinion that this "feature" never
should have been committed in the half baked state it was.

That said I'd consider a PR that used a connection parameter to force
calling procedures.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

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