Re: "stored procedures"

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.
Any transactions that the stored procedure begins, commits, or rolls
back are in fact autonomous subtransactions under the hood. Possibly
conditions like IF (1/0) THEN ... END IF that throw run time errors
get evaluated in the outer transaction context, so any errors stops
execution at that point - and we also avoid beginning and ending a
gabazillion transactions.

Possibly I am still waving my hands.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company