Re: SQL-Invoked Procedures for 8.1

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
>> 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.

> Is that really needed? What if the procedure starts in a transaction normally
> but is just allowed to commit it and start another transaction?

In fact it would more or less have to start in a transaction; keep in
mind that *we cannot do any database access* outside a transaction,
and therefore we could not have looked up the procedure in the system
catalogs in the first place without starting a transaction. We could
however commit that and let the procedure launch its own transactions
(compare to VACUUM, db-wide CLUSTER, etc) once we have read the
procedure body from the catalogs and done any pre-parsing we want to do.

Another point is that we are not really equipped to deal with errors
that occur outside a transaction --- the backend manages not to crash
but it's not really the way things ought to happen. So every action
that the procedure takes will need to be wrapped, explicitly or
implicitly, inside a transaction. This is fairly close to our semantics
for interactive SQL commands, so maybe we could essentially treat the
procedure as a mechanism for pushing commands into the SQL engine.

regards, tom lane