Re: "stored procedures"

On Thu, Apr 21, 2011 at 11:24 AM, Peter Eisentraut <peter_e@gmx.net> 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.

EDB has an implementation of this in Advanced Server.  A stored
procedure can issue a COMMIT, which commits the current transaction
and begins a new one.  This might or might not be what people are
imagining for this feature.  If we end up doing something else, one
thing to consider is the impact on third-party tools like PGPOOL,
which currently keep track of whether or not a transaction is in
progress by snooping on the stream of SQL commands.  If a procedure
can be started with no transaction in progress and return with one
open, or the other way around, that method will break horribly.
That's not necessarily a reason not to do it, but I suspect we would
want to add some kind of protocol-level information about the
transaction state instead so that such tools could continue to work.

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