I was playing with PL/pgSQL functions that would start and commit
transactions and got very weird behavior.  First I just take the check
for TransactionStmt off, so I could execute them.  With this change I
can write a function that opens a subtransaction, and it works, sort of;
I can open a subtransaction, but I can't open another one because
CommitTransactionCommand is not called, so when the next
BeginTransactionBlock is called the transaction state is not valid.

To get a sane behavior I had to modify SPI so that whenever a
TransactionStmt is executed, it issues CommitTransactionCommand()
immediately followed by StartTransactionCommand().  Again it seems to
works ... sort of.

The current problem is that a function along the lines of

begin				-- plpgsql's begin
	start transaction;
	commit transaction;
end;				-- plpgsql's end

causes a SIGSEGV because the commit closes the outermost transaction,
because we were not in a transaction block when it started, only in a
transaction command.  At this point the SPI info is freed, and when the
function ends the SPI info is corrupted :-(

Of course, if I execute the function inside a transaction block it works
fine.

One way to fix this would be to automatically enclose calls to SPI in a
transaction block.  This may carry a nontrivial overhead, but only that
of starting a subtransaction (because the overhead of the main
transaction was already paid anyway).  Is this acceptable?  Other ideas?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Aprende a avergonzarte mï¿½s ante ti que ante los demï¿½s" (Demï¿½crito)


Tue, 1 Jun 2004 00:10:50 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
SPI issue with nested xacts