[DB-SIG] Would like to challenge DBAPI on callproc's return value

[Milosz Kosmider]

Hi Marc-Andre, Mike,

Thanks for responding and setting me straight on the spec! Being that
PostgreSQL is a little bit unique in that it always returns its INOUT/OUT
parameters in the result set and provides no other way to access their
values, would it be an acceptable compromise for callproc to return a null
value when backed by PostgreSQL?

Even the solution of copying inputs to outputs is not trivial. PostgreSQL
procedures are invoked using the input parameters only, so one possible
solution would be to store the input parameters in a map, then take the
first row of the result set and add the values to that map, replacing any
existing (INOUT) values. Lastly we would order the parameters according to
the function prototype, which I am not even sure is unambiguous, because
proc(IN a, OUT b) and proc(IN a) are equal as prototypes. This is very
hacky and probably impossible. Moreover it provides no value in PostgreSQL.
That's why I think a null return value would be best. It best reflects what
PostgreSQL stored procedures actually return: nothing, directly.

Cheers,
Milosz

On Fri, Dec 4, 2015, 16:05 Mike Bayer <mike_mp at zzzcomputing.com> wrote:

>
>
> On 12/01/2015 12:40 AM, Milosz Kosmider wrote:
> > Hi folks,
> >
> > As PostgreSQL has had named parameters in stored procedures since
> > version 9.0, I
> > have taken to adding support for the feature in the DBAPI-complicant
> Python
> > wrapper, psycopg2: https://github.com/psycopg/psycopg2/pull/16/files.
> >
> > The feature allows the "callproc" method to be invoked, in addition to
> the
> > standard, tuple-y way, like this: cur.callproc('some_proc', { some_param:
> > 'some_value', ... })
> >
> > While implementing the feature I stumbled upon the DBAPI spec for
> callproc:
> > http://legacy.python.org/dev/peps/pep-0249/#callproc. It states that
> > "the result
> > of the call is returned as modified copy of the input sequence. Input
> > parameters
> > are left untouched, output and input/output parameters replaced with
> > possibly
> > new values." It then goes onto say that "The procedure may also provide
> > a result
> > set as output."
> >
> > I would like to challenge the former requirement, and make the latter
> > statement a strict requirement.
> >
> > The former requirement of returning the modified input sequence
> effectively
> > forces cur.callproc to examine its result set and copy it over the input
> > sequence.
>
> I believe the term "input/output parameters" refers to OUT parameters
> specifically.   If Postgresql's stored procedure system is entirely
> result-set based, then "output parameters" just wouldn't apply here;
> you'd only need return the same list of inputs that was passed.
>
>
> > This is not only redundant, but in fact undefined for result
> > sets with
> > more than one row.
>
> again, this language refers to OUT parameters, which are not result
> rows, they are parameters that are writable, as in:
>
>
> https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:325417134618
>
>
> > The requirement's definition is moreover
> > self-contradictory,
> > because output parameters are not *in* the input sequence.
>
> In your case of Postgresql, they're not. In the case of Oracle, MS SQL
> Server, DB2, and others, they are (or can be). ;)
>
>
>
>
> > All stored procedures yield result sets.
>
> that is entirely untrue for many databases.
>
> SQL Server stored procedures for example usually return an integer
> return code.   They can also use OUT parameters, return multiple result
> sets, or a detached cursor object:
>
> https://technet.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx
>
>
>
> > Some queries yield empty result
> > sets.
> > Some stored procedures yield empty result sets. As such, I would argue
> that,
> > like a query (e.g. cur.execute on a SELECT statement) cur.callproc
> > *must* endow
> > the cursor with a result set.
> >
> > I propose changing the DBAPI spec on callproc to be as follows:
> >
> > (This method is optional since not all databases provide stored
> > procedures.)
> >
> > Call a stored database procedure with the given name. The sequence of
> > parameters
> > must contain one entry for each argument that the procedure expects.
> > Overloaded
> > procedures are supported. If the underlying database supports named
> > parameters
> > in stored procedures, the sequence of parameters may be given as a
> > dictionary-like object mapping parameter names to values.
> >
> > The procedure must provide a result set as output. This is then made
> > available
> > through the standard fetch* methods.
> >
> > Return values are not defined.
> >
> > Thank you,
> > Milosz
> >
> >
> > _______________________________________________
> > DB-SIG maillist  -  DB-SIG at python.org
> > https://mail.python.org/mailman/listinfo/db-sig
> >
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> https://mail.python.org/mailman/listinfo/db-sig
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20151206/bc318470/attachment.html>