Re: "stored procedures"

On Wed, Aug 31, 2011 at 9:00 AM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 9 May 2011 20:52, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Mon, May 9, 2011 at 1:41 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>> Josh Berkus wrote:
>>>> Peter,
>>>>
>>>> > I would like to collect some specs on this feature.  So does anyone have
>>>> > links to documentation of existing implementations, or their own spec
>>>> > writeup?  A lot of people appear to have a very clear idea of this
>>>> > concept in their own head, so let's start collecting those.
>>>>
>>>> Delta between SPs and Functions for PostgreSQL:
>>>>
>>>> * SPs are executed using CALL or EXECUTE, and not SELECT.
>>>>
>>>> * SPs do not return a value
>>>> ** optional: SPs *may* have OUT parameters.
>>>
>>> [ Late reply.]
>>>
>>> What is it about stored procedures that would require it not to return a
>>> value or use CALL?  I am trying to understand what part of this is
>>> "procedures" (doesn't return a values, we decided there isn't much value
>>> for that syntax vs. functions), and anonymous transactions.
>>
>> FWICT the sql standard.  The only summary of standard behaviors I can
>> find outside of the standard itself is here:
>> http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html.
>>  Peter's synopsis of how the standard works is murky at best and
>> competing implementations are all over the place...SQL server's
>> 'CALL'  feature is basically what I personally would like to see. It
>> would complement our functions nicely.
>>
>> Procedures return values and are invoked with CALL.  Functions return
>> values and are in-query callable.
>>
>> The fact that 'CALL' is not allowed inside a query seems to make it
>> pretty darn convenient to make the additional distinction of allowing
>> transactional control statements there and not in functions.  You
>> don't *have* to allow transactional control statements and could offer
>> this feature as an essentially syntax sugar enhancement, but then run
>> the risk of boxing yourself out of a useful properties of this feature
>> later on because of backwards compatibility issues (in particular, the
>> assumption that your are in a running transaction in the procedure
>> body).
>
> I've seen no mention of SQL/PSM.  Isn't all of this covered by that?

That's the 64k$ question. My take is that 'CALL' doesn't implicitly
set up a transaction state, and a proper PSM implementation would
allow transaction control mid-procedure. Functions will always be
called in-transaction, since there is no way I can see to execute a
function except from an outer query (or the special case of DO). I
think there's zero point in making CALL work without dealing with the
transaction issue -- in fact it could end up being a huge mistake to
do so.

Pavel's PSM implementation (see:
http://www.pgsql.cz/index.php/SQL/PSM_Manual) works under the
constraints of pg's understanding of what functions should and should
not be allowed to do. It allows creation of PSM *functions* --
that's all.

IMNSHO, stored procedures should run in-process, and the execution
engine needs to be modified to not automatically spin up a transaction
and a snapshot when running them, but most allow a pl to do that at
appropriate times. plpgsql and the other pls fwict make no
assumptions that strictly invalidate their use in that fashion outside
of some unfortunate ambiguity issues around 'begin', 'end', etc. If
there is no current transaction, each statement should create one if
it's determined that the statement is interfacing with the sql engine
in such a way a transaction would be required, and immediately tear it
down, exactly as if an sql script was run inside the backend. The SPI
interface can probably work 'as-is', and should probably return an
error if you arrive into certain functions while not in transaction.

An out of process, autonomous transaction type implementation should
probably not sit under stored procedures for a number of reasons --
mainly that it's going to expose too many implementation details to
the user. For example, does a SP heavy app have 2*N running
processes? Or do we slot them into a defined number of backends for
that purpose? Yuck & yuck. I like the AT feature, and kludge it
frequently via dblink, but it's a solution for a different set of
problems.

merlin