Re: SQL-Invoked Procedures for 8.1

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> 3) Procedures can be run in the same savepoint level as the caller when
> OLD SAVEPOINT LEVEL is specified at creation time. According to SQL2003,
> functions must be run on a new savepoint level. From my understanding, we
> do not do this currently.

It's irrelevant since we don't allow functions to call SAVEPOINT/RELEASE/
ROLLBACK TO explicitly, and probably won't do so anytime soon. The only
thing we can really manage for a function is constrained use of
subtransactions a la plpgsql exceptions. This doesn't require the
savepoints to be named at all, so savepoint levels need not enter into it.

> This makes the difference between procedures and functions quite
> superficial: procedures are functions which return void and have parameter
> modes.

If you implement it that way I think it'll be very largely a waste of
effort :-(. What you're talking about seems mere syntactic sugar and
not a fundamental advance in capability.

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 concur with Grant Finnemore's objection as well: people expect
procedures to be able to return resultsets, ie SETOF something,
not only scalar values. Whether this is what SQL2003 says is not
really the issue -- we have to look at what's out there in competing
products.

regards, tom lane