Re: "stored procedures"

On Thu, Apr 21, 2011 at 2:37 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Apr 21, 2011 at 2:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "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 we could handle a lot of these details cleanly if we had
> autonomous transactions as a system primitive.  When you enter a
> stored procedure at the outermost level, you begin a transaction,
> which will remain open until the outermost stored procedure exits.

If you do it that (base it on AT) way, then you can't:
1) call any utility command (vacuum, etc)
2) run for an arbitrary amount of time
3) discard any locks (except advisory)
4) deal with serialization isolation/mvcc snapshot issues that plague functions.

Points 2 & (especially) 4 for me are painful.

#4 explained:
If you are trying to tuck all the gory mvcc details into server side
functions, there is no real effective way to prevent serialization
errors because the snapshot is already made when you enter the
function. Even if you LOCK something on function line#1, it's already
too late. No transaction procedures don't have this problem and allow
encapsulating all that nastiness in the server.

merlin