Re: "stored procedures"

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.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com