Re: "stored procedures"

On Thu, Sep 1, 2011 at 12:18 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 8/31/11 12:15 PM, Merlin Moncure wrote:
>> An out of process, autonomous transaction type implementation should
>> probably not sit under stored procedures for a number of reasons --
>> mainly that it's going to expose too many implementation details to
>> the user.  For example, does a SP heavy app have 2*N running
>> processes?  Or do we slot them into a defined number of backends for
>> that purpose? Yuck & yuck.  I like the AT feature, and kludge it
>> frequently via dblink, but it's a solution for a different set of
>> problems.
>
> I think that transaction control without parallelism would be the 80%
> solution.  That is, an SP has transaction control, but those
> transactions are strictly serial, and cannot be run in parallel.  For
> example, if you were writing an SP in PL/pgSQL, each "BEGIN ... END"
> block would be an explicit transaction, and standalone-only statements
> be allowed between BEGIN ... END blocks, or possibly in their own
> special block type (I prefer the latter).
>
> One issue we'd need to deal with is exception control around
> single-statement transactions and non-transactional statements (VACUUM,
> CREATE INDEX CONCURRENTLY, CHECKPOINT, etc.).  In some cases, the user
> is going to want to catch exceptions and abort the SP, and in other
> cases ignore them, so both need to be possible.

Totally agree -- was thinking about this very issue. One of the
things I'd really like to see SP be able to do is to abstract some of
the nasty details of MVCC away from the client -- setting isolation
mode, replaying errors on serialization, etc. This requires error
handling. Unfortunately, this (exception handling in non transaction
context) is probably going to add some complexity to the
implementation. Are we on the right track here (that is, maybe we
really *should* be looking at out of process execution)? How do
procedures fit in terms of execution from the tcop down?

merlin