Re: SPI returns error for SAVEPOINT

On Tue, May 24, 2016 at 9:55 PM, dandl <david@andl.org> wrote:
> A plandl (language handler for Andl) function is called as follows:
>
>       BEGIN;
>       SELECT plandl_compile($1); // argument is Andl code
>       COMMIT;
>
> Inside:
>
>>SPI_exec("BEGIN",...) returns error SPI_ERROR_TRANSACTION. As expected.
>>SPI_exec("SAVEPOINT xyz",...) returns error SPI_ERROR_TRANSACTION. Not
> expected.

The docs say that this is expected:
https://www.postgresql.org/docs/devel/static/spi-spi-execute.html
SPI_ERROR_TRANSACTION
if a transaction manipulation command was attempted (BEGIN, COMMIT,
ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK
PREPARED, or any variant thereof)

> Is it not possible to set a SAVEPOINT inside a function? Or am I missing
> something?

A function using a given procedural language is itself running a
transaction, so you cannot use DefineSavepoint directly. Have a look
at src/backend/access/transam/README, particularly Subtransaction
Handling", instead for more details regarding the routines that can be
used for sub-transaction handling in prodedural languages. What I
think you are looking for is BeginInternalSubTransaction(),
ReleaseCurrentSubTransaction() and
RollbackAndReleaseCurrentSubTransaction() to be able to handle
exceptions.
--
Michael