Thread: Procedure support improvements

>(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
examplecode 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
differencebetween 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,
nobegin transaction is silently sent from driver, and calling a procedure and calling a function would be very similar
(onlydiffering 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
connectionsetting to control this change, and make default false, so that default stays backwards compatible with pre
pg11functionality. 
>
> Thoughts?
>
>
>
>

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@fastcrypt.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.htmlspecifically "Hibernate disables, or
expectsthe application server to disable, auto-commit mode immediately. Database transactions are never optional. All
communicationwith 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
withthe same syntax. 
>
> I think we need to be pressing the people who committed procedures to complete the work they started. Fixing this in
thedrivers will just end up being a kludge at best. 
>
> Dave Cramer
>
> davec@postgresintl.com
> www.postgresintl.com
>
>
> 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
examplecode 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()
styleretrieval. Instead, outputs are left in the result set and app code must retrieve the result and pull, creating a
bigdifference 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
verysimilar (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
aconnection setting to control this change, and make default false, so that default stays backwards compatible with pre
pg11functionality. 
>> >
>> > Thoughts?
>> >
>> >
>> >
>> >
>>
>>

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

What I was getting at here is that stored procedures can participate
in transactions, without having to control them (i.e. without issuing
COMMIT/ROLLBACK themselves).
For example, a client JDBC-based application might start a transaction
(auto-commit=FALSE), and invoke a couple of stored procedures as part
of the transaction, and then COMMIT the transaction (or ROLLBACK if an
exception is raised). The stored procedures in this case might
UPDATE/INSERT records; they are participating in the transaction, but
not explicitly controlling it.

Greg Nancarrow
Fujitsu Australia

Dave Cramer wrote:
> As I said, I'd entertain a connection parameter that switched the
> CALL to call procedures but ideally you'd complain to the server
> folks to make Procedures useful.

Apart from the obvious problem that procedures make life hard for the
JDBC driver, because it does not know if it shall render a call as
SELECT or CALL:
What is missing in PostgreSQL procedures to make them useful?

Yours,
Laurenz Albe

[CC to -hackers]
Dave Cramer wrote:
> On Mon, 26 Aug 2019 at 13:43, Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
> > Dave Cramer wrote:
> > > As I said, I'd entertain a connection parameter that switched the
> > > CALL to call procedures but ideally you'd complain to the server
> > > folks to make Procedures useful.
> > 
> > Apart from the obvious problem that procedures make life hard for 
> > the JDBC driver, because it does not know if it shall render a call
> > as SELECT or CALL:
> > What is missing in PostgreSQL procedures to make them useful?
> 
> being able to use transactions inside a procedure inside a 
> transaction.

test=> CREATE OR REPLACE PROCEDURE testproc() LANGUAGE plpgsql AS
       $$BEGIN PERFORM 42; COMMIT; PERFORM 'x'; END;$$;
CREATE PROCEDURE
test=> CALL testproc();
CALL
test=> BEGIN;
BEGIN
test=> CALL testproc();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function testproc() line 1 at COMMIT

Oops.
I find that indeed surprising.

What is the rationale for this?

Yours,
Laurenz Albe

[CC to -hackers]
Dave Cramer wrote:
> On Mon, 26 Aug 2019 at 13:43,
Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
> > Dave Cramer wrote:
>
> > As I said, I'd entertain a connection parameter that switched the
>
> > CALL to call procedures but ideally you'd complain to the server
> >
> folks to make Procedures useful.
> > 
> > Apart from the obvious
problem that procedures make life hard for 
> > the JDBC driver, because
it does not know if it shall render a call
> > as SELECT or CALL:
> >
What is missing in PostgreSQL procedures to make them useful?
> 
> being
able to use transactions inside a procedure inside a 
> transaction.

test=> CREATE OR REPLACE PROCEDURE testproc() LANGUAGE plpgsql AS
       $$BEGIN PERFORM 42; COMMIT; PERFORM 'x'; END;$$;
CREATE PROCEDURE
test=> CALL testproc();
CALL
test=> BEGIN;
BEGIN
test=> CALL testproc();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function testproc() line 1 at COMMIT

Oops.
I find that indeed surprising.

What is the rationale for this?

Yours,
Laurenz Albe

[CC to -hackers]
Dave Cramer wrote:
> On Mon, 26 Aug 2019 at 13:43,
Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
> > Dave Cramer wrote:
>
> > As I said, I'd entertain a connection parameter that switched the
>
> > CALL to call procedures but ideally you'd complain to the server
> >
> folks to make Procedures useful.
> > 
> > Apart from the obvious
problem that procedures make life hard for 
> > the JDBC driver, because
it does not know if it shall render a call
> > as SELECT or CALL:
> >
What is missing in PostgreSQL procedures to make them useful?
> 
> being
able to use transactions inside a procedure inside a 
> transaction.

test=> CREATE OR REPLACE PROCEDURE testproc() LANGUAGE plpgsql AS
       $$BEGIN PERFORM 42; COMMIT; PERFORM 'x'; END;$$;
CREATE PROCEDURE
test=> CALL testproc();
CALL
test=> BEGIN;
BEGIN
test=> CALL testproc();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function testproc() line 1 at COMMIT

Oops.
I find that indeed surprising.

What is the rationale for this?

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Dave Cramer wrote:
> test=> BEGIN;
> BEGIN
> test=> CALL testproc();
> ERROR:  invalid transaction termination
> CONTEXT:  PL/pgSQL function testproc() line 1 at COMMIT

> What is the rationale for this?

A procedure shouldn't be able to force commit of the surrounding
transaction.

As Dave noted, what would be nicer is for procedures to be able
to start and commit autonomous transactions, without affecting
the state of the outer transaction.  We haven't got that though,
and it looks like a lot of work to get there.

            regards, tom lane

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Dave Cramer wrote:
> test=> BEGIN;
> BEGIN
> test=> CALL testproc();
> ERROR:  invalid transaction termination
> CONTEXT:  PL/pgSQL function testproc() line 1 at COMMIT

> What is the rationale for this?

A procedure shouldn't be able to force commit of the surrounding
transaction.

As Dave noted, what would be nicer is for procedures to be able
to start and commit autonomous transactions, without affecting
the state of the outer transaction.  We haven't got that though,
and it looks like a lot of work to get there.

            regards, tom lane

On 2019-08-26 20:08, Laurenz Albe wrote:
> test=> CREATE OR REPLACE PROCEDURE testproc() LANGUAGE plpgsql AS
>        $$BEGIN PERFORM 42; COMMIT; PERFORM 'x'; END;$$;
> CREATE PROCEDURE
> test=> CALL testproc();
> CALL
> test=> BEGIN;
> BEGIN
> test=> CALL testproc();
> ERROR:  invalid transaction termination
> CONTEXT:  PL/pgSQL function testproc() line 1 at COMMIT
> 
> Oops.
> I find that indeed surprising.
> 
> What is the rationale for this?

It's mostly an implementation restriction.  You would need to teach
SPI_commit() and SPI_rollback() to manipulate the top-level transaction
block state appropriately and carefully.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-08-26 20:08, Laurenz Albe wrote:
> test=> CREATE OR REPLACE PROCEDURE testproc() LANGUAGE plpgsql AS
>        $$BEGIN PERFORM 42; COMMIT; PERFORM 'x'; END;$$;
> CREATE PROCEDURE
> test=> CALL testproc();
> CALL
> test=> BEGIN;
> BEGIN
> test=> CALL testproc();
> ERROR:  invalid transaction termination
> CONTEXT:  PL/pgSQL function testproc() line 1 at COMMIT
> 
> Oops.
> I find that indeed surprising.
> 
> What is the rationale for this?

It's mostly an implementation restriction.  You would need to teach
SPI_commit() and SPI_rollback() to manipulate the top-level transaction
block state appropriately and carefully.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services