Re: SQL-Invoked Procedures for 8.1

On Thu, Sep 23, 2004 at 12:40:41PM -0400, Tom Lane wrote:

> What I'd like to see is a "procedure" capability which is somehow
> outside the transaction system and can therefore invoke BEGIN, COMMIT,
> SAVEPOINT, etc. I have no immediate ideas about how to do this, but
> I think that's what people are really after when they ask for
> server-side procedures. They want to be able, for example, to have
> a procedure encapsulate an abort-and-retry loop around a serializable
> transaction. (It'd be great if we could do that in a function, but
> I haven't thought of a way to make it work.)

I don't think we can do that in a standard function, at least not
without a lot of work. If we think of procedures as different from
functions, however, it seems doable.

What's needed for this is to isolate the transaction-initiating code
from the main query-processing loop. So for CALL statements it wouldn't
be invoked, and the procedure would be able to use its own explicit
transaction blocks and savepoints.

This part is not hard to do at all. It can be handled from the parser,
I think.

What's harder is handling the execution code. If the procedure uses
SPI, we need a mechanism to keep its SPI state, outside the normal
transaction-bound SPI stack.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"And as an added bonus, now my computer goes to the toilet for me, leaving me
free to spend time on more useful activities! yay slug codefests!" (C. Parker)