Re: SPI returns error for SAVEPOINT

> >>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)

OK, I missed it. Thanks.

> > 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.

Thanks for the reminder. IIRC I've seen those in one of the other PL handlers.

But I haven't seen that README. Thanks again.
[First look: complicated! Hopefully a subset will be enough for what I need.]

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org