Re: "stored procedures" - use cases?

On Mon, Apr 25, 2011 at 2:07 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On tor, 2011-04-21 at 18:24 +0300, Peter Eisentraut wrote:
>> So the topic of "real" "stored procedures" came up again.  Meaning a
>> function-like object that executes outside of a regular transaction,
>> with the ability to start and stop SQL transactions itself.
>>
>> I would like to collect some specs on this feature.  So does anyone have
>> links to documentation of existing implementations, or their own spec
>> writeup?  A lot of people appear to have a very clear idea of this
>> concept in their own head, so let's start collecting those.
>
> Another point, as there appear to be diverging camps about
> supertransactional stored procedures vs. autonomous transactions, what
> would be the actual use cases of any of these features?  Let's collect
> some, so we can think of ways to make them work.

My answer is this:
plpgsql with its first class SQL expressions, direct access to the
postgres type system, and other nifty features has proven for me to be
superior to all other languages in terms of defect rate, output
progress for input work, and other metrics one might apply by a
significant margin. By adding super-transactional (I prefer the
phrasing, 'explicit control of transaction state') features you can
eliminate all kinds of cases where you might otherwise be forced to
coding on the client side. Lots of people prefer not to do this (or
recoil in horror at the mere suggestion of doing so), and that's fine,
but I don't like being prohibited from being able to do so by
technical constraint. Explicit transaction controls remove those
constraints. Anyone who really 'gets' plpgsql programming knows
exactly what I'm talking about and has bumped into those constraints.

Autonomous transactions, basically a formalization of the dblink style
techniques of running SQL in a parallel transaction state, are also
useful, but for different reasons. You can extend them pretty far to
do most of things explicit transactions give you (like creating lots
of tables or running 'CLUSTER') although I find having to force users
to maintain separate transaction states just to do so to be a bit of a
kludge, and the outermost function still has to terminate within a
limited timeframe.

merlin