Re: Stored procedures and out parameters - Mailing list pgsql-hackers

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