Re: "stored procedures"

Peter Eisentraut wrote:
> So the topic of "real" "stored procedures" came up again. Meaning a
> function-like object that executes outside of a regular transaction,
> with the ability to start and stop SQL transactions itself.
>
> 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.

I've thought a lot about this too.

The general case of a stored procedure should be all powerful, and be able to
directly invoke any code written in SQL or other languages that a DBMS client
can directly invoke on the DBMS, as if it were a client, but that the procedure
is stored and executed entirely in the DBMS. But the stored procedure also has
its own lexical variables and supports conditionals and iteration and recursion.

A stored procedure is invoked as a statement and doesn't have a "return" value;
in contrast, a function has a return value and is invoked within a value
expression of a statement. A stored procedure can see and update the database,
and can have IN/INOUT/OUT parameters. A stored procedure can have side-effects
out of band, such as user I/O, if Pg supports that.

The general stored procedure should be orthogonal to other concerns, in
particular to transactions and savepoints; executing one should not should not
implicitly start or commit or rollback a transaction or savepoint. However, it
should be possible to explicitly declare that procedure is a transaction, so
that starts and ends are neatly paired regardless of how the procedure exits,
that is a transaction lifetime is attached to its lexical scope, but this would
be optional.

A stored procedure should be able to do data manipulation, data definition,
explicit transaction control (except perhaps when defined to be a transaction),
privilege control, message passing, and so on.

As for semantics, lets say that when a stored procedure is invoked, its
definition will be pulled from the system catalog in a snapshot and be compiled,
then run normally no matter what it does, even if the definition of the
procedure itself is changed during its execution; in the latter case, it just
means that once the execution finishes, subsequent calls to it would then call
the updated version or fail. So just compiling the procedure may need a catalog
lock or whatever, but when it starts executing a transaction isn't required.

Any stored procedure in general should be able to invoke stored procedures, to
any level of nesting, just like in any normal programming language. There might
be restrictions on what individual procedures can do depending on how they're
declared; for example, if one is declared to have a scope-bound transaction,
then it or ones it invokes can't have explicit transaction control statements.
But such restrictions are an orthogonal or case-dependent matter.

(When we have a distinct stored procedure, I also believe that a stored function
should be more restricted, such as only having IN parameters and not being able
to see the database but by way of parameters, and that it should be
deterministic. But that ship has sailed and I'm not going to argue for any
changes to functions.)

-- Darren Duncan