=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziome...@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