On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut <peter_e@gmx.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.
ya, that's an idea. if nothing else, it would certainly be faster,
and you still be able to control things properly.
Just thinking out loud here, but maybe you could make a cut down
version of StartTransaction() that does non-transactional set up like
memory, guc, etc but doesn't set the state (or set's it to something
else, like TRANS_PROCEDURE). We get here maybe by a new protocol
firstchar. One thing that's not clear is how you'd get there via a
simple query (sent via PQexec vs hypothetical PQcall). The protocol
and syntax portions are a whole separate issue anyways...
I poked around a bit in pl_exec.c and and pl_handler.c. My thinking
is that in strategic points, in particular in exec_stmt(), you check
if in procedure state and not already in a transaction, set one up
there, run the statement, and take it down afterwords. Maybe you do
this on every statement, or maybe as Peter suggest it's user
controlled, but i'm curious how this would turn out.
You'd also have to be in a transaction during the function call
setup/compilation, and the portions that handle the input arguments.
However the main execution loop which passes over the exec state istm
is fairly self contained and won't be problematic if run outside of
transaction. This is the key point -- the SPI routines when run would
always be in *a* transaction, just not always the same transaction.
:-)
What exactly SPI_connect does, and what the other SPI functions would
do if invoked from a different transaction is a mystery to me and
presumably a big problem. I'm quite aware this is all awfully light
on detail, and the million + 1 assumptions I'm making, but since your
getting basically injected directly into a function from the tiny
lizard brain of postgres in tcop, I wonder if it could be worked
out...
merlin