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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general