Procedure support improvements
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?
Attachments:
Hmmm who knew you couldn't call a procedure inside a transaction. That just
seems broken
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
On Sun, 21 Jul 2019 at 13:31, David Rader <david.rader@gmail.com> wrote:
Show quoted text
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 termination2) 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?
On Tue, Jul 23, 2019 at 4:37 PM Dave Cramer <pg@fastcrypt.com> wrote:
Hmmm who knew you couldn't call a procedure inside a transaction. That
just seems broken
Yeah, the description in the docs is:
"Transaction control is only possible in CALL or DO invocations from the
top level or nested CALL or DO invocations without any other intervening
command. "
https://www.postgresql.org/docs/11/plpgsql-transactions.html
Which means to be able to call procedures that use commit or rollback you
have to be able to call them without a begin...
Show quoted text
Dave Cramer
davec@postgresintl.com
www.postgresintl.comOn Sun, 21 Jul 2019 at 13:31, 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 termination2) 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?
On Tue, 23 Jul 2019 at 22:00, David Rader <david.rader@gmail.com> wrote:
On Tue, Jul 23, 2019 at 4:37 PM Dave Cramer <pg@fastcrypt.com> wrote:
Hmmm who knew you couldn't call a procedure inside a transaction. That
just seems brokenYeah, the description in the docs is:
"Transaction control is only possible in CALL or DO invocations from the
top level or nested CALL or DO invocations without any other intervening
command. "
https://www.postgresql.org/docs/11/plpgsql-transactions.htmlWhich means to be able to call procedures that use commit or rollback you
have to be able to call them without a begin...
This makes calling procedures a mostly useless feature IMO. What's the
motivation to make this work?
Dave
(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
Show quoted text
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 termination2) 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?
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@postgresintl.com
www.postgresintl.com
On Thu, 22 Aug 2019 at 01:39, Greg Nancarrow <gregn4422@gmail.com> wrote:
Show quoted text
(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 alwaysGreg Nancarrow
Fujitsu AustraliaOn 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 invocationso 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?
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
Show quoted text
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.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@postgresintl.com
www.postgresintl.comOn 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 alwaysGreg Nancarrow
Fujitsu AustraliaOn 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 termination2) 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?
On Fri, 23 Aug 2019 at 01:29, Greg Nancarrow <gregn4422@gmail.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@postgresintl.com
www.postgresintl.com
Show quoted text
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.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@postgresintl.com
www.postgresintl.comOn 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 areregistered
- map to CALL always
Greg Nancarrow
Fujitsu AustraliaOn 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 routineinvocation 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?
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
On Mon, 26 Aug 2019 at 04:01, Greg Nancarrow <gregn4422@gmail.com> wrote:
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.
Yes, I do understand that. My issue is that without autonomous transactions
procedures are just functions with a different syntax.
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.
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
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
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.
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
Show quoted text
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
On Mon, 26 Aug 2019 at 14:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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 COMMITWhat 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.
I'm less than motivated to hack the driver to make something work here
until we finish the server feature.
Who knows what that might bring ?
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
Show quoted text
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 COMMITOops.
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