Re: Where are we on stored procedures?

Hi Tom,

On Thu, 24 Feb 2005, Tom Lane wrote:

> Gavin and Neil made some noise in late September about implementing
> stored procedures for PG 8.1, but I haven't heard anything more about
> it since that thread died off. I've been getting some pressure inside
> Red Hat to see us support more of the JDBC CallableProcedure spec, so
> I'd like to reopen the discussion.
>
> In the previous discussion starting here:
> http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php
> it seemed that we were bandying around several different issues.
> People wanted "procedures" to differ from our current implementation
> of "functions" in such ways as:
>
> 1. Executing outside the database engine, and therefore being able to
> start/commit transactions. (This is *fundamentally* different from our
> current concept of functions, and I think that any implementation that
> tries to gloss over the difference will be doomed to failure.)

We have some ideas on this but haven't made in progress in terms of code.
The main reason that Josh and others were arguing strongly for transaction
level control within SPs was that they wanted to be able to VACUUM,
CLUSTER, etc from within an SP.

>
> 2. Having OUT parameters, and perhaps also INOUT parameters. At least
> in the JDBC spec, these are seen as scalar values, and so the feature
> is really just syntactic sugar for returning a composite type or row
> value. For instance, a procedure foo(x IN int, y OUT text, z OUT float)
> could perhaps be called via
> SELECT y, z FROM foo(42);
> where foo(x) is seen as returning the rowtype (y text, z float).

Again, ideas and design but not too much code. We got the parameter modes
into the grammar, stored them in a new system catalog, and some other
minor stuff.

The composite type stuff is precisely what the JDBC driver does at the
moment. I guess the feeling is that for those used to SPs in other
databases, having to use composite types is a bit of a hack.

>
> 3. Being able to return multiple result sets, ie, more than one SETOF
> something. In our previous discussion we tied this to OUT parameters
> but they're not necessarily the same thing --- the JDBC spec sees result
> sets as totally different objects.
>
> 4. Not having a distinguished function result, a/k/a returning void.
> While a function result is unnecessary given OUT parameters, this feels
> like an accidental thing rather than an essential aspect.
>
> 5. Matching parameters by name instead of by position.
>

This is kind of independent of SPs, I have some code which implements it
but I need to bring it up to HEAD.

> 6. Allowing parameters to be omitted from a call, with default values
> used instead.

Some code on this too.

>
> #5 and #6 would also be interesting for regular functions, but it's
> unclear how well we can support them without disallowing overloading
> of procedure/function names --- which of course is a nonstarter for
> the existing function facility.

We can support 5 for functions but not 6.

>
> (Gavin and Neil's first proposal also involved inventing a concept of
> variables at the SQL level, primarily so that there would be something
> to receive the results of OUT parameters. I found that distasteful and
> would prefer to avoid it. Another thing that came up was allowing a
> procedure -- in one or more of these senses -- to be used as a trigger,
> but I think that's a red herring. None of the above attributes are
> particularly relevant to a trigger.)

I'd like to hear what you had in mind for OUT parameters. Oracle and IBM
have host variables which is more or less what we were looking at but SQL
server just returns a result set as if it were a function. Strangely
enough, it can return multiple result sets with different descriptors (ie,
different row types).

I think there is some merit in supporting procedures with triggers but I'm
not sure its necessary for a first attempt.

>
> On looking at this list, it seems to me that #1 (transactionality) is
> largely orthogonal to the other points, which all have to do with
> passing and returning values. The main reason why we might consider
> all these things together is that no one is very excited about the idea
> of having even more than two kinds of objects, so there is a lot of
> temptation to press for having all these features in "procedures"
> rather than recognize that they are largely separable needs.
>
> The more I think about it, the more I think that #1 belongs outside the
> database entirely. The database engine more or less has to operate
> entirely within transactions --- heck, we cannot even look up a stored
> procedure's definition in a system catalog without starting a
> transaction. This is not to say that the facility can't exist
> physically within the backend, but that it would work a lot better if
> it weren't logically inside the database. What about inventing a
> protocol facility that lets clients send a chunk of, say, Perl or
> Python code to execute in an interpreter that can in turn send commands
> to the DB engine proper? The point here is that that interpreter is
> wrapped around the DB engine, not vice versa as occurs when executing a
> plperl or plpython function. (The only real difference between this
> idea and just executing the same code on the client side is avoiding
> network round trips.)
>
> BTW, using plpgsql in this fashion is a nonstarter, at least with
> anything resembling its current implementation. Because plpgsql relies
> on the database engine to do even simple expression evaluation, it's
> just hopeless to think of it doing anything useful outside a
> transaction. But we have plenty of external programming languages
> available that are perfectly capable of doing their own arithmetic and
> logic, and so could meaningfully drive the database engine through a
> series of transactions.

This is one of the real tough issues Neil and I were trying to work out. I
seem to remember you noting that without transaction control, SPs were
irrelevant :-).

> Having said all that, I don't have any personal interest in pursuing #1
> immediately. (Though anyone who does is welcome to.) What I would
> like to see is some forward movement on the other points, particularly
> #2 which is blocking my Red Hat coworkers from making progress. So the
> real bottom line to this overly long email is that I'd like to create
> a consensus that it's OK to work on #2 and perhaps #3 in the context of
> our existing function facility, without tackling #1. This'd involve
> work in both the server and the JDBC driver.
>
> Comments?

Well, Neil and I have at least looked over all the issues in a fair degree
of detail and made some plans. We've done the low hanging fruit: new
system catalog, CREATE PROCEDURE/DROP PROCEDURE, named notation (as
opposed to positional notation) for parameters at routine invocation
time, and a bit of prototyping. We were looking to finish this around
about now but we've been asked by Fujitsu to focus full time on Slony2,
for which we are working on prototypes.

As you've seen internally at Red Hat, the OUT parameter stuff is a real
show stopper for the JDBC guys. It would be good to see this in 8.1 but
I'm not sure when either Neil or I will find some time to look at it.

Thanks,

Gavin