Re: "stored procedures"

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> ** question: if an SP is called by another SP, what is its
>> transaction context?

> Entering or leaving an SP should not start or end a transaction.

That all sounds mighty hand-wavy and at serious risk of tripping over
implementation details. Some things to think about:

1. Are you expecting the procedure definition to be fetched from a
system catalog? You're going to need to be inside a transaction
to do that.

2. Are you expecting the procedure to take any input parameters?
You're going to need to be inside a transaction to evaluate the
inputs, unless perhaps you restrict the feature to an extremely
lobotomized subset of possible arguments (no user-defined types,
no expressions, just for starters).

3. What sort of primitive operations do you expect the SP to be
able to execute "outside a transaction"? The plpgsql model where
all the primitive operations are really SQL ain't gonna work.

I think that we could finesse #1 and #2, along these lines:
The CALL command is ordinary SQL but not allowed inside a transaction
block, much like some existing commands like VACUUM. So we start a
transaction to parse and execute it. The CALL looks up the procedure
definition and evaluates any input arguments. It then copies this info to
some outside-the-transaction memory context, terminates its transaction,
and calls the procedure. On return it starts a new transaction, in
which it can call the output functions that are going to have to be
executed in order to pass anything back to the client. (This implies
that OUT argument values are collected up during SP execution and not
actually passed back to the client till later. People who were hoping
to stream vast amounts of data to the client will not be happy. But
I see no way around that unless you want to try to execute output
functions outside a transaction, which strikes me as a quagmire.)

I'm less sure what to do about #3. The most attractive approach would
probably be to make people use a non-SQL script interpreter --- perl,
python, or whatever floats your boat --- which would likely mean that
we have not just one SP implementation language but N of them. But
we've solved that problem before.

Calling another SP ... particularly one with a different implementation
language ... could be a bit tricky too. The above proposal assumes that
SPs are always entered outside a transaction, but do we want to make
that same restriction for the call-another-SP case? And if not, how's
it going to work? Again, you'll have to be inside a transaction at
least long enough to get the SP's definition out of the catalogs.

regards, tom lane