Обсуждение: Stored procedures and out parameters
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$;
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. Let me know if you need any more info.
It may be worth adding some more documentation in https://www.postgresql.org/docs/11/static/sql-createprocedure.html which doesn't mention OUT/INOUT parameters at all (for instance, the fact that OUT parameters aren't allowed, and an explanation why INOUT parameter are allowed etc.).
Thanks for your help!
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>, ...)]}
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] 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]
https://www.postgresql.org/message-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
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.
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 procedureNote: JDBC defines two options to call a stored procedure:{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} {call <procedure-name>[(<arg1>,<arg2>, ...)]} 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.
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] 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]
https://www.postgresql.org/message-id/4580ff7b-d610-eaeb-e06f-4d686896b93b@2ndquadrant.com
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
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:
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.
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.
> 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!
> 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.
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
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> 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.
Well, no, actually I think it wouldn't. Multiple rowsets coming back
from a single query is, to my mind anyway, forbidden in the extended query
mode. Yeah, we could probably get away with it in simple query mode
(PQexec), but it's very likely to break clients in extended mode, because
they're going to be expecting just a single PGresult from a single SQL
command. Moreover, there are aspects of the protocol, such as the
Describe command, that aren't capable of dealing with more than one
result row descriptor per query. It would take some investigation to
determine the consequences of changing that. Even if you can weasel-word
your way into claiming that it's not a complete protocol break, I for one
would not vote to allow it unless the client has specifically said it
could handle it.
The protocol extension features we recently put in could be used to tell
whether libpq or equivalent wire-level driver allows the case, but I'm
just as concerned about breaking application-layer logic above the driver,
and it's pretty unclear how we ought to deal with telling whether that
code is OK with this.
As long as we're sure that the case is prevented in v11, it's something
that we can leave to work on later.
>> 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.
I'd also be -1 on enabling this without a lot more thought. It might
be fine to allow it, but if it turns out it's not fine, we'd have painted
ourselves into a corner. I don't think that late in the release cycle
is the time to be making such decisions.
regards, tom lane
Well, no, actually I think it wouldn't. Multiple rowsets coming back
from a single query is, to my mind anyway, forbidden in the extended query
mode. Yeah, we could probably get away with it in simple query mode
(PQexec), but it's very likely to break clients in extended mode, because
they're going to be expecting just a single PGresult from a single SQL
command. Moreover, there are aspects of the protocol, such as the
Describe command, that aren't capable of dealing with more than one
result row descriptor per query. It would take some investigation to
determine the consequences of changing that. Even if you can weasel-word
your way into claiming that it's not a complete protocol break, I for one
would not vote to allow it unless the client has specifically said it
could handle it.
The protocol extension features we recently put in could be used to tell
whether libpq or equivalent wire-level driver allows the case, but I'm
just as concerned about breaking application-layer logic above the driver,
and it's pretty unclear how we ought to deal with telling whether that
code is OK with this.
As long as we're sure that the case is prevented in v11, it's something
that we can leave to work on later.
Just to say that from the perspective of a driver writer, this is absolutely true. The protocol docs explicitly say that the response to Describe is "a RowDescription message describing the rows that will be returned by executing the portal", and any deviation from this will likely cause significant breakage client-side. So a protocol version change is necessary in my opinion for this.
By the way, from a purely protocol point of view, if you allow stored procedures to return multiple resultsets, you may as well consider allowing regular statements to contain semicolons and return multiple resultsets as well - just like the simple protocol... This obviously would have consequence beyond a pure protocol change, but it would make thinks more consistent and would also simplify certain client-side implementation details.
>> 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.
It would be good to understand why you think it's useless (am not familiar at all with JDBC, am genuinely interested). On the .NET side it's a pretty common/simple API (CommandType.StoredProcedure) that most users expect coming from other databases, hence important for portability and user acquisition.
I'd also be -1 on enabling this without a lot more thought. It might
be fine to allow it, but if it turns out it's not fine, we'd have painted
ourselves into a corner. I don't think that late in the release cycle
is the time to be making such decisions.
So I hope you at least consider going through the thought process about allowing this.
On 16/08/2018 00:50, Shay Rojansky wrote:
> >> 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.
>
>
> It would be good to understand why you think it's useless (am not
> familiar at all with JDBC, am genuinely interested).
I think this is all coming from Microsoft. The JDBC driver API was
modeled after the ODBC API, and the ODBC specification also contains the
{call} escape. Microsoft SQL Server is also the only SQL implementation
to handle this stored function/procedure stuff totally differently: They
only have procedures, but they return values, and they are invoked by an
EXEC command. (They don't support transaction control AFAIK.) The .NET
stuff is obviously also from Microsoft.
So from Microsoft's perspective, this makes some sense: They only have
one invokable object type, and their invocation syntax is different from
everyone else's. So they made a compatibility wrapper in their client
libraries.
Everyone else, however, has two invokable object types and standard ways
to invoke them. And they all seemingly faced this problem of how to jam
these two into this one hole provided by the JDBC spec and ended up with
slightly different, and incompatible, solutions.
I think, if you want to write a portable-sans-Microsoft JDBC
application, you can just run CALL or SELECT directly. If you want to
write something that is compatible with Microsoft, you can map {call} to
a function invocation as before, which is actually more similar to a
procedure in MS SQL Server.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Peter>I am familiar with the Java {call} escape. But I think it's pretty
Peter>useless. You're not going to get any compatibility with anything from
Peter>it, since every SQL implementation does something different with it
Would you please be more specific?
Peter>useless. You're not going to get any compatibility with anything from
Peter>it, since every SQL implementation does something different with it
Would you please be more specific?
{ call ... } converts the SQL to the database-specific way of calling a stored procedure or whatever.
For instance, in Oracle DB one can have both procedures and functions.
{ ? := call my_fun() } is converted to something like begin :b0 := my_fun(); end;
{ call my_proc(?) } is converted to begin my_proc(:b0); end;
Then comes PostgreSQL, and says: you can't invoke functions/procedures like {call my_proc()}.
This not fun at all.
Note: it is not a speculation, but I have participated in extending OracleDB-kind-of-app to PostgreSQL, and {call ...} was used there for a good reason.
{call ...} are used in lots of places exactly for the reason to call procedures.
You don't need to correct application core, and one just needs to install the procedure/function at the database side,
then it should just work.
However, current state of PostgreSQL 11 blocks use of database-independent API.
Peter>If you want to call
Peter>a procedure (created with CREATE PROCEDURE), just invoke CALL directly
Peter>without any escape syntax.
Peter>a procedure (created with CREATE PROCEDURE), just invoke CALL directly
Peter>without any escape syntax.
That would truly surprise end users, and it would make application portability a pain.
Vladimir
Shay>On the other hand, releasing *without* this also has its consequence, namely setting in stone that the database-independent language API cannot be used for invoking the new stored procedures
Shay>So I hope you at least consider going through the thought process about allowing this.
+1
I wonder if "CALL ..." can be excluded from PostgreSQL 11 release.
It is really important feature for the release?
Vladimir
Hi, On 2018-08-16 20:08:20 +0300, Vladimir Sitnikov wrote: > Shay>On the other hand, releasing *without* this also has its consequence, > namely setting in stone that the database-independent language API cannot > be used for invoking the new stored procedures > Shay>So I hope you at least consider going through the thought process > about allowing this. > > +1 > > I wonder if "CALL ..." can be excluded from PostgreSQL 11 release. > It is really important feature for the release? Are you actually suggesting we effectively drop procedure soupport? Greetings, Andres Freund
Peter,
I think this is all coming from Microsoft. The JDBC driver API was
modeled after the ODBC API, and the ODBC specification also contains the
{call} escape. Microsoft SQL Server is also the only SQL implementation
to handle this stored function/procedure stuff totally differently: They
only have procedures, but they return values, and they are invoked by an
EXEC command. (They don't support transaction control AFAIK.) The .NET
stuff is obviously also from Microsoft.
So from Microsoft's perspective, this makes some sense: They only have
one invokable object type, and their invocation syntax is different from
everyone else's. So they made a compatibility wrapper in their client
libraries.
Everyone else, however, has two invokable object types and standard ways
to invoke them. And they all seemingly faced this problem of how to jam
these two into this one hole provided by the JDBC spec and ended up with
slightly different, and incompatible, solutions.
I think, if you want to write a portable-sans-Microsoft JDBC
application, you can just run CALL or SELECT directly. If you want to
write something that is compatible with Microsoft, you can map {call} to
a function invocation as before, which is actually more similar to a
procedure in MS SQL Server.
Am going to repeat some of Vladimir's responses here...
It's true that users will always be able to simply avoid the standard API altogether and do SELECT * FROM func() or CALL proc(), but it really isn't ideal to force users down this road, which once again, hurts portability and general adoption.
Andres,
> Are you actually suggesting we effectively drop procedure soupport?
The ideal solution here is to allow functions to be invoked with CALL, rather than rolling back the entire feature (which obviously nobody wants). This would allow drivers to simply change their API implementation to translate to CALL instead of SELECT * FROM. I have no idea what the risk of that is, what it would entail etc. - I'm just expressing the driver writer perspective here with Vladimir. Hopefully some satisfactory solution can be found here.
On Thu, 16 Aug 2018 at 13:54, Shay Rojansky <roji@roji.org> wrote:
Peter,I think this is all coming from Microsoft. The JDBC driver API was
modeled after the ODBC API, and the ODBC specification also contains the
{call} escape. Microsoft SQL Server is also the only SQL implementation
to handle this stored function/procedure stuff totally differently: They
only have procedures, but they return values, and they are invoked by an
EXEC command. (They don't support transaction control AFAIK.) The .NET
stuff is obviously also from Microsoft.
So from Microsoft's perspective, this makes some sense: They only have
one invokable object type, and their invocation syntax is different from
everyone else's. So they made a compatibility wrapper in their client
libraries.
Everyone else, however, has two invokable object types and standard ways
to invoke them. And they all seemingly faced this problem of how to jam
these two into this one hole provided by the JDBC spec and ended up with
slightly different, and incompatible, solutions.
I think, if you want to write a portable-sans-Microsoft JDBC
application, you can just run CALL or SELECT directly. If you want to
write something that is compatible with Microsoft, you can map {call} to
a function invocation as before, which is actually more similar to a
procedure in MS SQL Server.Am going to repeat some of Vladimir's responses here...I don't really know (or care much) about the history of how language database APIs evolved to where they are, I'm more concerned with what the introduction of stored procedures will do... The problem we're describing seems to go beyond JDBC or .NET. Looking at psycopg, for example, there's a callproc() function that internally translates to SELECT * FROM (http://www.postgresqltutorial.com/postgresql-python/call-stored-procedures/) - at the very least there are going to be some very confused users when callproc() becomes a way to only invoke functions, whereas calling procedures requires something else. I don't think there's anything really Microsoft-specific about any of this (except maybe in the history) - just like JDBC and psycopg, there's simply a single standard way in the database API for invoking server-side things, and not two ways.It's true that users will always be able to simply avoid the standard API altogether and do SELECT * FROM func() or CALL proc(), but it really isn't ideal to force users down this road, which once again, hurts portability and general adoption.Andres,> Are you actually suggesting we effectively drop procedure soupport?The ideal solution here is to allow functions to be invoked with CALL, rather than rolling back the entire feature (which obviously nobody wants). This would allow drivers to simply change their API implementation to translate to CALL instead of SELECT * FROM. I have no idea what the risk of that is, what it would entail etc. - I'm just expressing the driver writer perspective here with Vladimir. Hopefully some satisfactory solution can be found here.
I think this is the best solution. Without looking too deeply at the details it seems to me that calling functions would require some wrapper code around the function?
Regards,
Dave
On 16/08/2018 19:54, Shay Rojansky wrote: > I don't think there's anything really Microsoft-specific about any of > this (except maybe in the history) - just like JDBC and psycopg, there's > simply a single standard way in the database API for invoking > server-side things, and not two ways. Have you looked what those standard interfaces do in other SQL implementations (e.g., Oracle, DB2, Derby, MySQL)? AFAICT in no case does it involve allowing functions to be called as procedures or vice versa. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 16/08/2018 19:54, Shay Rojansky wrote:
> I don't think there's anything really Microsoft-specific about any of
> this (except maybe in the history) - just like JDBC and psycopg, there's
> simply a single standard way in the database API for invoking
> server-side things, and not two ways.
Have you looked what those standard interfaces do in other SQL
implementations (e.g., Oracle, DB2, Derby, MySQL)? AFAICT in no case
does it involve allowing functions to be called as procedures or vice versa.
That is all well and good except PostgreSQL took its sweet time implementing procedures and so effectively encouraged its community to dual-purpose functions. Now the community is simply asking core to recognize that history and dual-purpose the "CALL" command.
What others have done doesn't change the situation that has arisen for PostgreSQL due to its implementation history.
David J.
On 22/08/2018 18:49, David G. Johnston wrote: > What others have done doesn't change the situation that has arisen for > PostgreSQL due to its implementation history. What others have done seems relevant, because the whole reason these questionable interfaces exist is to achieve compatibility across SQL implementations. Otherwise you can just make a native SQL call directly. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Peter>AFAICT in no case does it involve allowing functions to be called as procedures or vice versa.
Oracle DB uses the same way to execute both procedures and functions: pl/sql block.
For instance:
procedure) begin my_proc(); end;
function) begin :result := my_fun(); end;
Call like begin my_fun(); end; would fail.
However there's no dedicated command to call procedures or a command to call functions.
Vladimir
On Wed, 22 Aug 2018 at 12:58, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
On 22/08/2018 18:49, David G. Johnston wrote:
> What others have done doesn't change the situation that has arisen for
> PostgreSQL due to its implementation history.
What others have done seems relevant, because the whole reason these
questionable interfaces exist is to achieve compatibility across SQL
implementations. Otherwise you can just make a native SQL call directly.
It seems to me that if we don't make it possible to call a function or a procedure using
the same mechanism the drivers will have to make a choice which one to implement.
That said the path of least resistance and regression for the drivers would be to not implement
calling procedures through each respective drivers mechanism. I would think given the importance of
this work it would be a shame not to make it easy to use.
I also agree with David that driver writers made the best out of the situation with functions and we are now asking for the server to dual purpose the call command.
Is there a technical reason why this is not possible ?
On 22/08/2018 20:22, Dave Cramer wrote:
> I also agree with David that driver writers made the best out of the
> situation with functions and we are now asking for the server to dual
> purpose the call command.
>
> Is there a technical reason why this is not possible ?
There are several areas of concern. These might not be grave issues
now, but they would impede future development in these areas.
First of all, what do you want to do with the function return value
when you use CALL? CALL doesn't have the capability to process
arbitrary shapes of return values, such as sets. It could perhaps be
implemented, but it's not. So right now, CALL could not be a general
replacement for all function invocations.
And would you expect a function that is invoked via CALL to have a
non-atomic execution context, that is, allow transactions? If not,
why not? If yes, how would this interact with set returning
functions? I don't think the implementation can support this.
Similar questions arise if we implement SQL standard dynamic result
sets. What would you do if a function invoked by CALL runs across one
of those?
Output parameter handling is not compatible between function calls and
procedure calls. Our implementation of output parameters in functions
is an extension of the SQL standard, and while it's been useful, it's
nonstandard, and I would like to make the output parameter handling in
CALL compatible with the SQL standard. For example, if you have a
function f1(IN a int, OUT b int), you would call it as SELECT f1(x)
and the "b" would somehow be the return value. But a procedure call
would be CALL p1(x, y), where x and y could be, say, PL/pgSQL
variables. So if you want to allow invoking functions using the CALL
statement, you're going to have a hard time defining semantics that
are not wildly confusing. Moreover, if the intention is to switch the
JDBC driver or similar drivers to use the CALL command always from
PG11 on, then the meaning of {call f1(a, b)} will have changed and a
lot of things will break in dangerous ways.
Always using CALL to invoke a function would also leave performance on
the table. CALL has to do certain additional work in case a
transaction commit happens in the middle of the procedure, such as
expanding TOAST values. You don't necessarily want to do that if you
don't have to.
There is also the semi-open question of whether functions and
procedures should be in separate namespaces. For PostgreSQL 11 we
have settled that they are in the same namespace, for simplicity and
because we ran out of time, but that choice should perhaps not be set
in stone for all times. In Oracle and DB2, functions and procedures
are in different namespaces, so SELECT x() and CALL x() invoke
different objects. Whether we ever want to do that is a different
question, but we shouldn't design ourselves into an incompatible
corner in haste.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tuesday, August 28, 2018, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
First of all, what do you want to do with the function return value
when you use CALL?
Place it in the result set.
And would you expect a function that is invoked via CALL to have a
non-atomic execution context, that is, allow transactions? If not,
why not?
No, because functions cannot exercise transaction control.
Similar questions arise if we implement SQL standard dynamic result
sets. What would you do if a function invoked by CALL runs across one
of those?
Runtime error that record returning results are not supported for CALL based execution.
Moreover, if the intention is to switch the
JDBC driver or similar drivers to use the CALL command always from
PG11 on, then the meaning of {call f1(a, b)} will have changed and a
lot of things will break in dangerous ways.
This seems like the main blocker.
Always using CALL to invoke a function would also leave performance on
the table. CALL has to do certain additional work in case a
transaction commit happens in the middle of the procedure, such as
expanding TOAST values. You don't necessarily want to do that if you
don't have to.
This seems solvable since we know if the invoked object is a function or procedure. And functions used as procedures are likely less sensitive to performance concerns than ones performing calculations in tlists. Not implementing this optimization in pg11 but supporting functions via call is something I could live with.
There is also the semi-open question of whether functions and
procedures should be in separate namespaces.
I have no problem calling this a closed question answered per the initial implementation choice. This matter is sufficient justification fevn if the original choice was done out of convenience.
David J.
David>JDBC driver or similar drivers to use the CALL command always from
PG11 on, then the meaning of {call f1(a, b)} will have changed and a
Vladimir
PG11 on, then the meaning of {call f1(a, b)} will have changed and a
Note: technically speaking, JDBC has two flavours of syntax (however standard does not clarify the distinction):
S1) {? := call my_proc(?,?) }
S2) { call my_proc(?, ?) }
Theoretically speaking, we could presume that S1 is for calling functions while S2 is for calling procedures.
However, does not looks like a way out since
1) It does not provide a way to call void returning functions. Users might have lots of void-returning functions since procedures were missing.
2) Other languages might happen to have single syntax only. For instance, CommandType in .NET seems to be in (StoredProcedure, TableDirect, Text). There's no room for procedure vs function.
Peter>JDBC driver or similar drivers to use the CALL command always from
Peter>PG11 on, then the meaning of {call f1(a, b)} will have changed and a
Peter>lot of things will break in dangerous ways.
Peter>PG11 on, then the meaning of {call f1(a, b)} will have changed and a
Peter>lot of things will break in dangerous ways.
PG10 did not have procedures, so only "functions" could theoretically break.
If CALL can be used to call functions, and the drivers could play their way to make the result-set look like before, then no breakage happens.
Note: driver does not blindly rewrite {call f1(a,b)} to call f1(a,b). It does send prepare/describe/etc/etc messages. At the end of the day, the purpose of having a DB driver is to have a consistent API that works across DB versions/DB products.
David>Not implementing this optimization in pg11 but supporting functions via call is something I could live with.
+1
Vladimir
On Tue, Aug 28, 2018 at 6:30 AM, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> Output parameter handling is not compatible between function calls and
> procedure calls. Our implementation of output parameters in functions
> is an extension of the SQL standard, and while it's been useful, it's
> nonstandard, and I would like to make the output parameter handling in
> CALL compatible with the SQL standard. For example, if you have a
> function f1(IN a int, OUT b int), you would call it as SELECT f1(x)
> and the "b" would somehow be the return value. But a procedure call
> would be CALL p1(x, y), where x and y could be, say, PL/pgSQL
> variables. So if you want to allow invoking functions using the CALL
> statement, you're going to have a hard time defining semantics that
> are not wildly confusing. Moreover, if the intention is to switch the
> JDBC driver or similar drivers to use the CALL command always from
> PG11 on, then the meaning of {call f1(a, b)} will have changed and a
> lot of things will break in dangerous ways.
The semantics you've chosen for procedures are more like Oracle that
the existing function semantics, which, as I can attest from my work
experience, can be very useful for users looking to migrate. Worth
noting, however, is Oracle also has those semantics for function
calls. So what you've ended up creating here is a situation where
procedures behave more or less like they do in Oracle and the SQL
standard, but functions behave the way they historically have in
PostgreSQL. That's kind of a weird incompatibility, and I think that
incompatibility is a significant part of what people are complaining
about.
In other words, being more like the SQL standard is probably good, but
breaking compatibility is bad. You've technically avoided a
*backward* compatibility break by deciding that functions and
procedures can work differently from each other, but that just moves
the problem around. Now instead of being unhappy that existing code
is broken, people are unhappy that the new thing doesn't work like the
existing thing. That may be the lesser of evils, but it's still
pretty evil. People are not being unreasonable to want to call some
code stored on the server without having to worry about whether that
code is in a box labelled PROCEDURE or a box labelled FUNCTION.
This probably should have been discussed in more detail before this
got committed, but I guess that's water under the bridge at this
point. Nevertheless, I predict that this is going to be an ongoing
source of pain for a long time to come.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
In other words, being more like the SQL standard is probably good, but
breaking compatibility is bad. You've technically avoided a
*backward* compatibility break by deciding that functions and
procedures can work differently from each other, but that just moves
the problem around. Now instead of being unhappy that existing code
is broken, people are unhappy that the new thing doesn't work like the
existing thing. That may be the lesser of evils, but it's still
pretty evil. People are not being unreasonable to want to call some
code stored on the server without having to worry about whether that
code is in a box labelled PROCEDURE or a box labelled FUNCTION.
Reading this from the (JDBC) drivers perspective, which is probably a fairly popular one,
We now have a standard that we can't really support. Either the driver will have to support
the new PROCEDURE with the {call } mechanism or stay with the existing FUNCTIONS.
This puts the drivers in a no win situation.
This probably should have been discussed in more detail before this
got committed, but I guess that's water under the bridge at this
point. Nevertheless, I predict that this is going to be an ongoing
source of pain for a long time to come.
Undoubtedly.. surely the opportunity to do something about this has not passed as this has not been
officially released ?
On 08/30/18 15:35, Robert Haas wrote: > On Tue, Aug 28, 2018 at 6:30 AM, Peter Eisentraut > <peter.eisentraut@2ndquadrant.com> wrote: >> CALL compatible with the SQL standard. For example, if you have a >> function f1(IN a int, OUT b int), you would call it as SELECT f1(x) >> and the "b" would somehow be the return value. But a procedure call >> would be CALL p1(x, y), where x and y could be, say, PL/pgSQL >> variables. I suppose the key question for most driver writers is going to be, what does that difference look like at the fe-be protocol level? PL/pgSQL might be an unrepresentative example for that question, as it lives in the backend and could have some other way of retrieving b to store in y. For any remote client, the result still needs to get back there before the client can apply any "this result gets assigned to my y variable" semantics, and is there any material difference between the protocol message sequences that return these results select foo(1,2); select * from foo(1,2); call bar(1,2); to the client? And, in the parallel universe where functions got implemented according to the standard, what in that picture would be different? -Chap
On Thu, Aug 30, 2018 at 4:14 PM, Dave Cramer <pg@fastcrypt.com> wrote:
> Reading this from the (JDBC) drivers perspective, which is probably a fairly
> popular one,
> We now have a standard that we can't really support. Either the driver will
> have to support
> the new PROCEDURE with the {call } mechanism or stay with the existing
> FUNCTIONS.
> This puts the drivers in a no win situation.
I understand and I agree.
> Undoubtedly.. surely the opportunity to do something about this has not
> passed as this has not been
> officially released ?
I agree with that, too, but I can't make other people do things they
don't want to do, and then there's the question of time. On the basis
of previous experience, there is going to be little appetite for
holding up the release to address this issue no matter how badly
busted it is. Ultimately, it's the RMT that must decide what to do in
cases like this. I have confidence that they are watching this
thread, but I don't know what they will decide to do about it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, Aug 30, 2018 at 7:45 PM, Chapman Flack <chap@anastigmatix.net> wrote: > On 08/30/18 15:35, Robert Haas wrote: >> On Tue, Aug 28, 2018 at 6:30 AM, Peter Eisentraut >> <peter.eisentraut@2ndquadrant.com> wrote: >>> CALL compatible with the SQL standard. For example, if you have a >>> function f1(IN a int, OUT b int), you would call it as SELECT f1(x) >>> and the "b" would somehow be the return value. But a procedure call >>> would be CALL p1(x, y), where x and y could be, say, PL/pgSQL >>> variables. > > I suppose the key question for most driver writers is going to be, > what does that difference look like at the fe-be protocol level? I don't think the issue is so much the FE-BE protocol level as the SQL syntax. > PL/pgSQL might be an unrepresentative example for that question, > as it lives in the backend and could have some other way of retrieving > b to store in y. For any remote client, the result still needs to get > back there before the client can apply any "this result gets assigned > to my y variable" semantics, and is there any material difference between > the protocol message sequences that return these results > > select foo(1,2); > select * from foo(1,2); > call bar(1,2); > > to the client? And, in the parallel universe where functions got > implemented according to the standard, what in that picture would > be different? You may (or may not) be missing the point here. Your first two examples do not obviously involve OUT parameters, although in theory they could, since whatever OUT parameters exist are going to show up in the third one. The third one definitely does not, since CALL apparently would require a variable that could be set to be passed as an argument. I'm not actually sure how it's supposed to work, actually, because the documentation for CALL addresses neither the proper usage nor the incompatibility as compared with functions: https://www.postgresql.org/docs/11/static/sql-call.html -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 09/02/18 16:37, Robert Haas wrote:
> On Thu, Aug 30, 2018 at 7:45 PM, Chapman Flack <chap@anastigmatix.net> wrote:
>> b to store in y. For any remote client, the result still needs to get
>> back there before the client can apply any "this result gets assigned
>> to my y variable" semantics, and is there any material difference between
>> the protocol message sequences that return these results
>>
>> select foo(1,2);
>> select * from foo(1,2);
>> call bar(1,2);
>
> You may (or may not) be missing the point here. Your first two
> examples do not obviously involve OUT parameters, although in theory
> they could,
A fair point, as I didn't include the declarations in the email.
They NON-obviously involve OUT parameters, or rather INOUT ones.
In 11beta3 you can't give a procedure OUT parameters:
# show server_version;
server_version
----------------
11beta3
# create procedure bar(IN a int, OUT b int) as 'select $1' language sql;
ERROR: procedures cannot have OUT arguments
HINT: INOUT arguments are permitted.
So I went with INOUT for the second param of both the procedure bar and
the function foo (even though a pure OUT parameter is accepted for foo).
# create procedure bar(IN a int, INOUT b int) as 'select 9*$1' language sql;
CREATE PROCEDURE
# create function foo(IN a int, INOUT b int) as 'select 9*$1' language sql;
CREATE FUNCTION
That requires passing something for b in the calls, though it isn't used:
# select foo(1,2); select * from foo(1,2); call bar(1,2);
foo
-----
9
(1 row)
b
---
9
(1 row)
b
---
9
Aside from the different column label in select foo vs select * from foo,
there seems to be little difference in how the result set gets back to
the client (I haven't snooped the protocol exchanges, though).
I understand that (part of) the issue is a common syntax that {call foo...}
should expand into to make the Right Thing happen, but I was trying to
take one step back and gauge how clear it is what the Right Thing should be.
-Chap
On 30/08/2018 21:35, Robert Haas wrote: > The semantics you've chosen for procedures are more like Oracle that > the existing function semantics, which, as I can attest from my work > experience, can be very useful for users looking to migrate. Worth > noting, however, is Oracle also has those semantics for function > calls. So what you've ended up creating here is a situation where > procedures behave more or less like they do in Oracle and the SQL > standard, but functions behave the way they historically have in > PostgreSQL. That's kind of a weird incompatibility, and I think that > incompatibility is a significant part of what people are complaining > about. > This probably should have been discussed in more detail before this > got committed, but I guess that's water under the bridge at this > point. Note that OUT parameters in procedures are not implemented yet, so you didn't miss any discussion, but it's a discussion I would like to have in the future, and not in haste. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi,
On 9/2/18 4:32 PM, Robert Haas wrote:
> On Thu, Aug 30, 2018 at 4:14 PM, Dave Cramer <pg@fastcrypt.com> wrote:
>> Reading this from the (JDBC) drivers perspective, which is probably a fairly
>> popular one,
>> We now have a standard that we can't really support. Either the driver will
>> have to support
>> the new PROCEDURE with the {call } mechanism or stay with the existing
>> FUNCTIONS.
>> This puts the drivers in a no win situation.
>
> I understand and I agree.
>
>> Undoubtedly.. surely the opportunity to do something about this has not
>> passed as this has not been
>> officially released ?
>
> I agree with that, too, but I can't make other people do things they
> don't want to do, and then there's the question of time. On the basis
> of previous experience, there is going to be little appetite for
> holding up the release to address this issue no matter how badly
> busted it is. Ultimately, it's the RMT that must decide what to do in
> cases like this. I have confidence that they are watching this
> thread, but I don't know what they will decide to do about it.
>
First, I want everyone to know that the RMT took this very seriously and
took time collect feedback and consult with as many people as we could
in order to make the best possible decision for v11 and ensure that any
decision we made did not hinder any future implementation for stored
procedures nor introduced something that would break backwards
compatibility.
Ultimately, the decision came down to one of four options:
#1: Do nothing and remove the open item
#2: Introduce nonstandard syntax for calling functions / procedures
#3: Have CALL support both functions & procedures (or SELECT support
calling functions)
#4: Disable CALL
The RMT has decided to go with option #1 and will be removing the open
item for the PostgreSQL 11 release.
We understand that this will impact how drivers such as JDBC & ODBC will
support stored procedures for v11 and this was a detail we took into
great consideration. Through our discussions, we also know that there
are other ways that users can call stored procedures, and understand
that for people who are used to programming with the JDBC/ODBC
interfaces that this is considered a "workaround."
We hope that the community can continue to improve the stored procedure
functionality for v12 and that there will be continued work on CALL such
that we can make it easier for our driver maintainers to make stored
procedures more easily available for our users. Personally, I know this
is a feature that many people are very excited for, and I look forward
to future work that will continue to improve upon what we are releasing
in v11.
While some of you may be disappointed that we are removing the open
item, we do hope this frees the group up to discuss, plan, and implement
a solution for v12 without the pressure of a release deadline.
Sincerely,
Jonathan
Вложения
On Mon, Sep 17, 2018 at 7:45 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> Hi,
>
> On 9/2/18 4:32 PM, Robert Haas wrote:
> > On Thu, Aug 30, 2018 at 4:14 PM, Dave Cramer <pg@fastcrypt.com> wrote:
> >> Reading this from the (JDBC) drivers perspective, which is probably a fairly
> >> popular one,
> >> We now have a standard that we can't really support. Either the driver will
> >> have to support
> >> the new PROCEDURE with the {call } mechanism or stay with the existing
> >> FUNCTIONS.
> >> This puts the drivers in a no win situation.
> >
> > I understand and I agree.
> >
> >> Undoubtedly.. surely the opportunity to do something about this has not
> >> passed as this has not been
> >> officially released ?
> >
> > I agree with that, too, but I can't make other people do things they
> > don't want to do, and then there's the question of time. On the basis
> > of previous experience, there is going to be little appetite for
> > holding up the release to address this issue no matter how badly
> > busted it is. Ultimately, it's the RMT that must decide what to do in
> > cases like this. I have confidence that they are watching this
> > thread, but I don't know what they will decide to do about it.
> >
>
> First, I want everyone to know that the RMT took this very seriously and
> took time collect feedback and consult with as many people as we could
> in order to make the best possible decision for v11 and ensure that any
> decision we made did not hinder any future implementation for stored
> procedures nor introduced something that would break backwards
> compatibility.
>
> Ultimately, the decision came down to one of four options:
>
> #1: Do nothing and remove the open item
> #2: Introduce nonstandard syntax for calling functions / procedures
> #3: Have CALL support both functions & procedures (or SELECT support
> calling functions)
> #4: Disable CALL
>
> The RMT has decided to go with option #1 and will be removing the open
> item for the PostgreSQL 11 release.
Hooray for making the right choice. This is exhibit "Z" as to why
abstracting the SQL language behind a programming API can lead to
problems. The workaround is to simply not do that and you can get
precise control of behavior. It's a little unfortunate that API
{call} maps to a select but such is life.
merlin
Merlin>The workaround is to simply not do that and you can get
Merlin>precise control of behavior
Merlin>precise control of behavior
You are absolutely right.
On top of that, the whole concept of DB-drivers and libpq is useless.
Users should just simply exchange wire messages for precise control of behavior.
Vladimir
On 9/17/18 11:47 AM, Vladimir Sitnikov wrote: > Merlin>The workaround is to simply not do that and you can get > Merlin>precise control of behavior > > You are absolutely right. > On top of that, the whole concept of DB-drivers and libpq is useless. > Users should just simply exchange wire messages for precise control of > behavior. .. Merlin>> Hooray for making the right choice. Just to chime in real quick: from the perspective of the RMT we did not look at these as a series of "right/wrong" options but what would make the most sense for the v11 release so the community could continue to improve support for stored procedures and make it easier for users to work with them going forward. I would suggest we move forward and figure out what needs to be implemented so people who use JDBC, ODBC, and other drivers are able to fully take advantage of stored procedures the way they are used to. Thanks, Jonathan
Вложения
"Jonathan S. Katz" <jkatz@postgresql.org> writes:
> Just to chime in real quick: from the perspective of the RMT we did not
> look at these as a series of "right/wrong" options but what would make
> the most sense for the v11 release so the community could continue to
> improve support for stored procedures and make it easier for users to
> work with them going forward.
Yeah. I think the real options we had here were:
1. Ship v11 with CALL as it stands, try to improve going forward.
2. Delay v11 for months while we figure out something better.
3. Remove CALL from v11, try again in v12.
Neither #2 nor #3 are at all attractive.
People will need to realize that CALL behavior is still a work in
progress. That may mean that JDBC etc shouldn't try to support it
yet, which isn't great, but none of the above options would have
led to near-term support of CALL in JDBC ...
regards, tom lane
po 17. 9. 2018 v 18:24 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
"Jonathan S. Katz" <jkatz@postgresql.org> writes:
> Just to chime in real quick: from the perspective of the RMT we did not
> look at these as a series of "right/wrong" options but what would make
> the most sense for the v11 release so the community could continue to
> improve support for stored procedures and make it easier for users to
> work with them going forward.
Yeah. I think the real options we had here were:
1. Ship v11 with CALL as it stands, try to improve going forward.
2. Delay v11 for months while we figure out something better.
3. Remove CALL from v11, try again in v12.
Neither #2 nor #3 are at all attractive.
People will need to realize that CALL behavior is still a work in
progress. That may mean that JDBC etc shouldn't try to support it
yet, which isn't great, but none of the above options would have
led to near-term support of CALL in JDBC ...
+1
Pavel
regards, tom lane