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)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
