Thread: Is SPI + Procedures (with COMMIT) inside a bgworker broken?

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello@gmail.com> writes:
> I'm trying to execute a PROCEDURE (with COMMIT inside) called from a
> background worker using SPI but I'm always getting the error below:
> 2021-09-13 09:36:43.571 -03 [23846] ERROR:  invalid transaction termination

The direct cause of that is that SPI_execute() doesn't permit the called
query to perform COMMIT/ROLLBACK, which is because most callers would fail
to cope with that.  You can instruct SPI to allow that by replacing the
SPI_execute() call with something like

        SPIExecuteOptions options;

        ...
        memset(&options, 0, sizeof(options));
        options.allow_nonatomic = true;

        ret = SPI_execute_extended(buf.data, &options);


However, that's not enough to make this example work :-(.
I find that it still fails inside the procedure's COMMIT,
with

2021-09-13 15:14:54.775 EDT worker_spi[476310] ERROR:  portal snapshots (0) did not account for all active snapshots
(1)
2021-09-13 15:14:54.775 EDT worker_spi[476310] CONTEXT:  PL/pgSQL function schema4.counted_proc() line 1 at COMMIT
        SQL statement "CALL "schema4"."counted_proc"()"

I think what this indicates is that worker_spi_main's cavalier
management of the active snapshot isn't up to snuff for this
use-case.  The error is coming from ForgetPortalSnapshots, which
is expecting that all active snapshots are attached to Portals;
but that one isn't.

Probably the most appropriate fix is to make worker_spi_main
set up a Portal to run the query inside of.  There are other
bits of code that are not happy if they're not inside a Portal,
so if you're hoping to run arbitrary SQL this way, sooner or
later you're going to have to cross that bridge.

(I remain of the opinion that replication/logical/worker.c
is going to have to do that eventually, too...)

            regards, tom lane