Re: "stored procedures"

On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On fre, 2011-04-22 at 08:37 -0500, Merlin Moncure wrote:
>> It wouldn't bother me in the lest that if in plpgsql procedures if you
>> had to set up and tear down a transaction on every line.
>
> It would probably be more reasonable and feasible to have a setup where
> you can end a transaction in plpgsql but a new one would start right
> away.

I've been poking around to see how this might be done, and yes there
are a lot of issue. The good news is that at least, from what I can
tell so far, that there are relatively few problems inside plpgsql
itself in terms of making it span transactions (there is a small
assumption with the simple eval code but that can probably easily
fixed). The problems are further up in that plpgsql relies on various
structures that are tucked into the transaction memory context.

The very first thing that I think has to be figured out to implement
supertransactional behaviors is under which memory context the various
structures plpgsql depends on will live, especially the execution
state. I'm thinking it should rely in the message context, with some
participation at the portal level, possibly via a new routine
(PortalRunProcedure) that is special in that it has to communicate to
plpgsql that it is a procedure and what to do when doing transactional
management. For example, it is currently managing the
ExecutorQueryDesc and should probably continue doing so. One way to
do this is to inject a callback somewhere (in the queryDesc?) which
could be accessible at the lower levels (ideally even in SPI if we
want to be able to get to this from other PLs).

The callback implementation would kill the snapshot, reset the
transaction etc. Most other transaction management is not happening
here, but in postgres.c, so I'm not sure if this is the right place.
I'd also like to defer the snapshot creation as long as possible after
flushing the current transaction so that it's possible to sneak a lock
into the procedure body to deal with serialization problems. If that
can't be worked out, maybe a textual implementation or something based
on autonomous approach is better.

merlin