Обсуждение: SPI returns error for SAVEPOINT

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.

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

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

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

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