Stored procedures and out parameters

On Mon, Jul 23, 2018 at 2:23 AM, Shay Rojansky <roji@roji.org> wrote:

Hi hackers, I've encountered some odd behavior with the new stored procedure
feature, when using INOUT parameters, running PostgreSQL 11-beta2.

With the following procedure:

CREATE OR REPLACE PROCEDURE my_proc(INOUT results text)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
select 'test' into results;
END;
$BODY$;

executing CALL my_proc('whatever') yields a resultset with a "results"
column and a single row, containing "test". This is expected and is also how
functions work.

However, connecting via Npgsql, which uses the extended protocol, I see
something quite different. As a response to a Describe PostgreSQL message, I
get back a NoData response rather than a RowDescription message, In other
words, it would seem that the behavior of stored procedures differs between
the simple and extended protocols, when INOUT parameters are involved.

I might be wrong, but that sounds like a bug.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

"Robert" == Robert Haas <robertmhaas@gmail.com> writes:

However, connecting via Npgsql, which uses the extended protocol, I
see something quite different. As a response to a Describe
PostgreSQL message, I get back a NoData response rather than a
RowDescription message, In other words, it would seem that the
behavior of stored procedures differs between the simple and
extended protocols, when INOUT parameters are involved.

Robert> I might be wrong, but that sounds like a bug.

Completely off the cuff, I'd expect 59a85323d might have fixed that;
does it fail on the latest 11-stable?

--
Andrew (irc:RhodiumToad)

Andrew>does it fail on the latest 11-stable

1) Current "build from Git/master PostgreSQL" produces the data row for
both simple and extended queries.

2) Just a side note: `CALL my_proc()` is not suitable for functions. That
looks weird.
Is the client expected to lookup system catalogs in order to tell if
`my_proc` is procedure or function and use either `call my_proc` or `select
* from my_proc()`?
Issuing `call my_function()` fails with 42883 ERROR: my_func(unknown) is
not a procedure

Note: JDBC defines two options to call a stored procedure:
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
{call <procedure-name>[(<arg1>,<arg2>, ...)]}
see
https://docs.oracle.com/javase/9/docs/api/java/sql/CallableStatement.html

There's no notion if the called object is a procedure or function.
Note: PostgreSQL can have a function that `returns void`, and it is hard to
tell if {call test()} refers to a function or procedure.

Can functions and procedures be unified at the backend level?
For instance, support "CALL" for both of them.
Or support "select * ..." for both of them.

Vladimir​

On 23.07.18 19:38, Andrew Gierth wrote:

"Robert" == Robert Haas <robertmhaas@gmail.com> writes:

However, connecting via Npgsql, which uses the extended protocol, I
see something quite different. As a response to a Describe
PostgreSQL message, I get back a NoData response rather than a
RowDescription message, In other words, it would seem that the
behavior of stored procedures differs between the simple and
extended protocols, when INOUT parameters are involved.

Robert> I might be wrong, but that sounds like a bug.

Completely off the cuff, I'd expect 59a85323d might have fixed that;
does it fail on the latest 11-stable?

Yes, that's supposed to address that.

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

Vladimir Sitnikov wrote:

There's no notion if the called object is a procedure or function.
Note: PostgreSQL can have a function that `returns void`, and it is hard to
tell if {call test()} refers to a function or procedure.

Can functions and procedures be unified at the backend level?
For instance, support "CALL" for both of them.
Or support "select * ..." for both of them.

But there's a reason why CALL exists in the first place.
It's not a synonym of SELECT and not supposed to do the
same thing.
In a SELECT or in a DML query in general you must
be able to determine the structure of the resultset
without executing the query.

In a CALL you're not supposed to be able to do that,
because:
1. A stored procedure should be able to return multiple
resultsets with different structures.
2. A stored procedure can decide dynamically of
the structure of the resultset(s) it returns,
and the caller will discover it as they're returned, not
before.

During the development of procedures, Peter Eisentraut
posted an experimental patch [1]/messages/by-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com that demonstrated
#1 and #2 and told he didn't have the time to pursue
this direction, but hopefully that doesn't mean
we'll never be able to do that in PG12 or after.

Functions cannot do #1 or #2, but they can be called
anywhere in a query. Procedures should be able
to do #1 or #2, but they cannot be called
within DML queries.

I fear like what is being asked here, to blur the distinction
between functions and procedures in terms how the
client-side workflow expects and handle results,
would mean that we're going to loose the ability to
do #1 and #2 in the future.

Personally I understood it as an important conceptual distinction
between functions and procedures that the function exposes a
contract to return a specific datatype that can be unambiguously
determined at PREPARE time, whereas the
procedure is specifically not bound by any such contract,
with the consequence that CALL proc(..) cannot be
prepared, or possibly that it cannot be run through
prepare/bind/execute steps with the extended query protocol,
unlike SELECT or more generally DML queries.

Is that understanding of procedures vs functions incorrect?

[1]: /messages/by-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com
/messages/by-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

On Tue, Jul 24, 2018 at 6:58 AM, Daniel Verite <daniel@manitou-mail.org>
wrote:

Vladimir Sitnikov wrote:

There's no notion if the called object is a procedure or function.
Note: PostgreSQL can have a function that `returns void`, and it is hard

to

tell if {call test()} refers to a function or procedure.

Can functions and procedures be unified at the backend level?
For instance, support "CALL" for both of them.
Or support "select * ..." for both of them.

But there's a reason why CALL exists in the first place.
It's not a synonym of SELECT and not supposed to do the
same thing.
In a SELECT or in a DML query in general you must
be able to determine the structure of the resultset
without executing the query.

In a CALL you're not supposed to be able to do that,

​I disagree: "In a CALL you're are not required to determine the resultset
structure prior to execution"

because:
1. A stored procedure should be able to return multiple
resultsets with different structures.

​But it may only return one

2. A stored procedure can decide dynamically of
the structure of the resultset(s) it returns,
and the caller will discover it as they're returned, not
before.

The function itself doesn't care - this concern is about SELECT vs CALL
invocation only, not the script definition.

Procedures should be able
to do #1 or #2, but they cannot be called
within DML queries.

​Immaterial, we are talking about a straight "CALL proc()" statement here.
​

I fear like what is being asked here, to blur the distinction
between functions and procedures in terms how the
client-side workflow expects and handle results,
would mean that we're going to loose the ability to
do #1 and #2 in the future.

​I don't see how allowing a function to be used as the object of CALL, but
enforcing the existing CALL dynamics, will lead to that situation.

What I don't know is whether the limitations that are being imposed for
CALL will break JDBC if existing SELECT statements are changed to CALL
statements. Since JDBC has to be able to deal with CALL statements
manually issued anyway it should be fairly straight forward for someone
knowledgeable with the JDBC codebase to make that determination.

So, while its not really incumbent upon PostgreSQL to compensate for the
decisions made by the JDBC driver PostgreSQL does bear some responsibility
for the current situation due to its long period of non-implementation of
the SQL Standard CALL (and stored procedure) feature. Loosening up the
blanket restriction on functions not being a valid target of a CALL seems
like something that should be strongly considered. Runtime failures for
unsupported situations can still be thrown but to the extent that functions
are effectively a subset of stored procedures it seems like most uses of a
function as a target should be fully compatible with CALL semantics.

David J.

On Mon, Jul 23, 2018 at 12:07 PM, Vladimir Sitnikov <
sitnikov.vladimir@gmail.com> wrote:

2) Just a side note: `CALL my_proc()` is not suitable for functions. That
looks weird.
Is the client expected to lookup system catalogs in order to tell if
`my_proc` is procedure or function and use either `call my_proc` or `select
* from my_proc()`?
Issuing `call my_function()` fails with 42883 ERROR: my_func(unknown) is
not a procedure

Note: JDBC defines two options to call a stored procedure:
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
{call <procedure-name>[(<arg1>,<arg2>, ...)]}
see https://docs.oracle.com/javase/9/docs/api/java/sql/
CallableStatement.html

There's no notion if the called object is a procedure or function.
Note: PostgreSQL can have a function that `returns void`, and it is hard
to tell if {call test()} refers to a function or procedure.

Can functions and procedures be unified at the backend level?
For instance, support "CALL" for both of them.
Or support "select * ..." for both of them.

​IMO JDBC will need to version branch the textual transform of {call} to
"CALL" in v11 and continue with the transform to SELECT in v10 and
earlier. Recommend adding an override property to force SELECT syntax in
v11​. This regardless of whether the server accepts functions as the
object of a CALL; though if it does the override will then likely be a
fail-safe device instead of a fundamental decision point for the developer.

I'm not familiar with the JDBC enough to posit whether adding a catalog
lookup is something that should be done; but I'd probably not go down that
path without first trying the version+override solution alone.

David J.

to compensate for the decisions made by the JDBC driver PostgreSQL

It is not a decision by PostgreSQL JDBC driver. It is a decision by JDBC
(Java Database Connectivity) specification itself.
pgjdbc just follows the specification there.

Well, there's an initiative for a new JDBC specification (ADBA
https://blogs.oracle.com/java/jdbc-next:-a-new-asynchronous-api-for-connecting-to-a-database
),
and they seem to avoid "JDBC-specific syntax" in favour of
native-for-the-database syntax. However, ADBA is in its early development,
and there are lots of existing applications that use { call my_proc() }
syntax for a good reason.

IMO JDBC will need to version branch the textual transform of {call} to

"CALL" in v11 and continue with the transform to SELECT in v10 and earlier.

Just one more time: it will break clients who use JDBC's {call ...} syntax
to call functions in v11.
In other words, JDBC specification does not distinguish procedures and
functions, so pgjdbc would have to use either "CALL procedure()" or "SELECT
procedure()" kind of native syntax, however pgjdbc has no clue which one to
use. Current PostgreSQL 11 fails to execute functions via CALL, and it
fails to execute procedures via SELECT.

Of course, application developer can use native syntax directly so
application can use CALL vs SELECT, however that has portability issues
since native syntax is tied to a single DB.
JDBC {call my_proc()} automatically expands to select... for PostgreSQL
and to begin my_proc(); end; in Oracle DB.

Vladimir

David G. Johnston wrote:

2. A stored procedure can decide dynamically of
the structure of the resultset(s) it returns,
and the caller will discover it as they're returned, not
before.

The function itself doesn't care - this concern is about SELECT vs CALL
invocation only, not the script definition.

It does care, because CREATE FUNCTION has a RETURNS clause and
matching RETURN statements in the body, whereas CREATE PROCEDURE
doesn't and (will?) have a different syntax for producing resultsets.
For instance in the patch [1]/messages/by-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com I mentioned earlier, that particular syntax
was the DB2-inspired
"DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2"

As for the invocation, that's just the starting point. At this point
the driver doesn't know from '{call x}' whether x is a procedure or a
function in Postgres, hence the request for a syntax that would work
for both. Okay, but aside from that, if there are results, the driver
needs to get them in a way that works without knowing wether it's a
function or procedure. How would that happen?

Back to the first message of the thread, Shay Rojansky was saying:

"However, connecting via Npgsql, which uses the extended protocol, I
see something quite different. As a response to a Describe PostgreSQL
message, I get back a NoData response rather than a RowDescription
message"

Why would a Describe on a "CALL myproc()" even work if we
accept the premise that myproc() does not advertise what it may return,
contrary to a "SELECT * FROM function()"?
This issue goes beyond a "syntactic bridge" between CALL and SELECT.

[1]: /messages/by-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com
/messages/by-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

On Tue, Jul 24, 2018 at 11:31 AM, Daniel Verite <daniel@manitou-mail.org>
wrote:

David G. Johnston wrote:

2. A stored procedure can decide dynamically of
the structure of the resultset(s) it returns,
and the caller will discover it as they're returned, not
before.

The function itself doesn't care - this concern is about SELECT vs CALL
invocation only, not the script definition.

It does care, because CREATE FUNCTION has a RETURNS clause and
matching RETURN statements in the body, whereas CREATE PROCEDURE
doesn't and (will?) have a different syntax for producing resultsets.

​But why does whatever code that implements CALL have to care?

In Object Oriented terms why can not both procedures and functions
implement a "EXECUTE_VIA_CALL" interface; while functions additionally
implement a "EXECUTE_VIA_SELECT" interface - the one that they do today.

ISTM that any (most) function could be trivially ​rewritten into a
procedure (or wrapped by one) in a mechanical fashion which could then be
executed via CALL. I'm proposing that instead of having people write their
own wrappers we figure out what the mechanical wrapper looks like, ideally
based upon the public API of the function, and create it on-the-fly
whenever said function is executed via a CALL statement.

As for the invocation, that's just the starting point. At this point

the driver doesn't know from '{call x}' whether x is a procedure or a
function in Postgres, hence the request for a syntax that would work
for both. Okay, but aside from that, if there are results, the driver
needs to get them in a way that works without knowing wether it's a
function or procedure. How would that happen?

I'm saying that the driver needs to rewrite {call x} as "CALL x()" and
expect optional resultsets and optional output arguments. For functions
invoked as procedures this would be a single resultset with zero output
arguments. Which is exactly the same end-user result that is received
today when "SELECT * FROM x()" is used.

Back to the first message of the thread, Shay Rojansky was saying:

"However, connecting via Npgsql, which uses the extended protocol, I
see something quite different. As a response to a Describe PostgreSQL
message, I get back a NoData response rather than a RowDescription
message"

Why would a Describe on a "CALL myproc()" even work if we
accept the premise that myproc() does not advertise what it may return,
contrary to a "SELECT * FROM function()"?
This issue goes beyond a "syntactic bridge" between CALL and SELECT.

​If you "DESCRIBE CALL my_func()" you get back a NoData response; it
doesn't try to inspect the RETURNS clause of the function even though in
theory it could. The client is using CALL so that is it should expect to
receive. That said I'm not entirely clear whether the NoData response is a
fixed bug or not...

David J.

Apologies for disappearing from this conversation for a week.

First off, on the .NET side I have the exact same issue that Vladimir
Sitnikov described for the Java side. The .NET database API (ADO.NET) has a
standard, portable way for calling "server-side code". Since stored
procedures are in PostgreSQL, this portable API was implemented years ago
to invoke functions, which were the only thing in existence (so Npgsql
issues SELECT * FROM function()). Now that stored procedures have been
introduced, it's impossible to change what the portable API means without
massive backwards compatibility issues for all programs which already rely
on the API calling *functions*.

In other words, I really do hope that on the PostgreSQL side you consider
allowing both functions and procedures to be invoked via CALL. Npgsql (and
likely pgjdbc) would then be able to change the portable API to send CALL
instead of SELECT, avoiding all backwards compatibility issues (they would
do that only for PostgreSQL 11 and above). For now I'm telling users on the
beta version to avoid the API altogether (writing CALL SQL manually), which
as Vladimir wrote above is bad for portability.

If you "DESCRIBE CALL my_func()" you get back a NoData response; it

doesn't try to inspect the RETURNS clause of the function even though in
theory it could. The client is using CALL so that is it should expect to
receive. That said I'm not entirely clear whether the NoData response is a
fixed bug or not...

Uh, this sounds like something we really need to understand... How is a
driver supposed to know what data types are being returned if it can't use
Describe? DataRow messages contain only field lengths and values, so having
a type OID is critical for knowing how to interpret the data, and that
currently is only available by sending a Describe on a statement... Npgsql
currently always sends a describe as part of statement execution (for
server-prepared messages the describe is done only once, at
preparation-time). Vladimir, are you doing things differently here?

On Tue, Jul 24, 2018 at 7:57 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

Shay>Npgsql currently always sends a describe as part of statement
execution (for server-prepared messages the describe is done only once, at
preparation-time). Vladimir, are you doing things differently here?

The same thing is for pgjdbc. It does use describe to identify result row
format.
However, "CALL my_proc()" works just fine with current git master for both
simple and extended protocol.

The missing part is "invoke functions via CALL statement".

Vladimir

Shay>Npgsql currently always sends a describe as part of statement
execution (for server-prepared messages the describe is done only once, at
preparation-time). Vladimir, are you doing things differently here?

The same thing is for pgjdbc. It does use describe to identify result row
format.
However, "CALL my_proc()" works just fine with current git master for both
simple and extended protocol.

In one way that's good, but I wonder how this squares with the following
written by David above:

1. A stored procedure should be able to return multiple resultsets with

different structures.

2. A stored procedure can decide dynamically of the structure of the

resultset(s) it returns, and the caller will discover it as they're
returned, not before.

Both of the above seem to be simply incompatible with the current
PostgreSQL protocol. Describe currently returns a single RowDescription,
which describes a single resultset, not more. And as I wrote before, I
don't see how it's possible with the current protocol for the caller to
discover the structure of the resultset(s) "as they're returned" - type
information simply isn't included in the responses to Execute, only field
lengths and values. It also leads me to wonder what exactly is returned in
the current implementation when Describe is send on a stored procedure
call: something *is* returned as Vladimir wrote, meaning that stored
procedures aren't as dynamic as they're made out to be?

To summarize, it seems to me that if the multiple resultsets and/or dynamic
resultset structure are a real feature of stored procedure, attention must
be given to possible impact on the protocol and especially how client-side
drivers are supposed to interact with the resultsets.

The missing part is "invoke functions via CALL statement".

I agree. This is definitely not a JDBC-specific issue - I'm guessing most
database APIs out there have their (single) way to invoke server-side code,
and that way is currently set to send SELECT because only functions existed
before. The distinction between stored functions and stored procedures
seems to be PostgreSQL-specific, and the different invocation syntax causes
a mismatch. Hope you consider allowing invoking the new stored procedures
with CALL.

Shay Rojansky wrote:

In one way that's good, but I wonder how this squares with the following
written by David above:

1. A stored procedure should be able to return multiple resultsets with

different structures.

2. A stored procedure can decide dynamically of the structure of the

resultset(s) it returns, and the caller will discover it as they're
returned, not before.

Both of the above seem to be simply incompatible with the current
PostgreSQL protocol. Describe currently returns a single RowDescription,
which describes a single resultset, not more. And as I wrote before, I
don't see how it's possible with the current protocol for the caller to
discover the structure of the resultset(s) "as they're returned"

It works at least with the simple query mode, where it's similar
to handling results from a query string containing multiple
statements separated by semicolons.

But it's not clear whether this could work with the extended query
protocol. The doc says that the necessary RowDescription message(s)
would be missing:

"The possible responses to Execute are the same as those described
above for queries issued via simple query protocol, except that
Execute doesn't cause ReadyForQuery or RowDescription to be issued",

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

On Sat, Aug 04, 2018 at 07:03:47AM +0100, Shay Rojansky wrote:

Shay>Npgsql currently always sends a describe as part of statement
execution (for server-prepared messages the describe is done only once, at
preparation-time). Vladimir, are you doing things differently here?

The same thing is for pgjdbc. It does use describe to identify result row
format.
However, "CALL my_proc()" works just fine with current git master for both
simple and extended protocol.

In one way that's good, but I wonder how this squares with the following
written by David above:

1. A stored procedure should be able to return multiple resultsets with

different structures.

2. A stored procedure can decide dynamically of the structure of the

resultset(s) it returns, and the caller will discover it as they're
returned, not before.

Both of the above seem to be simply incompatible with the current
PostgreSQL protocol. Describe currently returns a single RowDescription,
which describes a single resultset, not more.

Long ago, when I was trying to simulate this behavior, I created
functions which returned SETOF REFCURSOR. It worked at least up to the
extent of being able to use multiple result sets. I don't recall
whether I had a good way to describe the rowtypes, but I suspect one
could be hacked together by having one refcursor be of a specific
rowtype whose job is to describe all the rest.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Peter, Tom,

Would it be possible for you to review the following two questions? Some
assertions have been made in this thread about the new stored procedures
(support for dynamic and multiple resultsets) whose compatibility with the
current PostgreSQL protocol are unclear to me as a client driver
maintainer... Some clarification would really help.

Also another request by Vladimir and myself to consider allowing functions
to be invoked with CALL, in order to provide a single way to call both
procedures and functions - this is important as language database APIs
typically have a single, database-independent way to invoke server-side
code that does not distinguish between functions and procedures.

Thanks for your time!

Hi,

On 2018-08-12 08:51:28 +0100, Shay Rojansky wrote:

Peter, Tom,

Would it be possible for you to review the following two questions? Some
assertions have been made in this thread about the new stored procedures
(support for dynamic and multiple resultsets) whose compatibility with the
current PostgreSQL protocol are unclear to me as a client driver
maintainer... Some clarification would really help.

I've not yet discussed this with the rest of the RMT, but to me it
sounds like we should treat this an open item for the release. We
shouldn't have the wire protocol do something nonsensical and then do
something different in the next release.

- Andres

On Aug 14, 2018, at 1:30 PM, Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2018-08-12 08:51:28 +0100, Shay Rojansky wrote:

Peter, Tom,

Would it be possible for you to review the following two questions? Some
assertions have been made in this thread about the new stored procedures
(support for dynamic and multiple resultsets) whose compatibility with the
current PostgreSQL protocol are unclear to me as a client driver
maintainer... Some clarification would really help.

I've not yet discussed this with the rest of the RMT, but to me it
sounds like we should treat this an open item for the release. We
shouldn't have the wire protocol do something nonsensical and then do
something different in the next release.

The RMT has now discussed and concluded that we should treat this
as an open item. It has been added to the list.

Jonathan

On 12/08/2018 09:51, Shay Rojansky wrote:

Would it be possible for you to review the following two questions? Some
assertions have been made in this thread about the new stored procedures
(support for dynamic and multiple resultsets) whose compatibility with
the current PostgreSQL protocol are unclear to me as a client driver
maintainer... Some clarification would really help.

Stored procedures in PostgreSQL currently do not support dynamic or
multiple result sets. Multiple result sets is a possible future
feature, which would work within the existing protocol. Dynamic result
sets in the sense that the structure of the result set is determined at
execution is not something I'm planning, so I can't comment on how that
might work. (In the SQL standard, the term "dynamic result sets" is
used in the sense of "possibly multiple result sets".)

Also another request by Vladimir and myself to consider allowing
functions to be invoked with CALL, in order to provide a single way to
call both procedures and functions - this is important as language
database APIs typically have a single, database-independent way to
invoke server-side code that does not distinguish between functions and
procedures.

I am familiar with the Java {call} escape. But I think it's pretty
useless. You're not going to get any compatibility with anything from
it, since every SQL implementation does something different with it, for
the exact reason that you allude to: functions and procedures are
different objects in SQL, and this interface is trying to jam them both
into one.

If you are currently mapping {call foo()} to SELECT * FROM foo(), I
think that's fine and you can continue doing that. If you want to call
a procedure (created with CREATE PROCEDURE), just invoke CALL directly
without any escape syntax.

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