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

Fabrízio de Royes Mello <fabriziomello@gmail.com>

From: Fabrízio de Royes Mello <fabriziomello@gmail.com>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Cc: Mats Kindahl <mats@timescale.com>
Date: 2021-09-13T12:48:45Z
Lists: pgsql-hackers

Attachments

Hi all,

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.568 -03 [23845] LOG:  worker_spi worker 2 initialized
with schema2.counted
2021-09-13 09:36:43.568 -03 [23846] LOG:  worker_spi worker 1 initialized
with schema1.counted
2021-09-13 09:36:43.571 -03 [23846] ERROR:  invalid transaction termination
2021-09-13 09:36:43.571 -03 [23846] CONTEXT:  PL/pgSQL function
schema1.counted_proc() line 1 at COMMIT
SQL statement "CALL "schema1"."counted_proc"()"
2021-09-13 09:36:43.571 -03 [23846] STATEMENT:  CALL
"schema1"."counted_proc"()
2021-09-13 09:36:43.571 -03 [23845] ERROR:  invalid transaction termination
2021-09-13 09:36:43.571 -03 [23845] CONTEXT:  PL/pgSQL function
schema2.counted_proc() line 1 at COMMIT
SQL statement "CALL "schema2"."counted_proc"()"
2021-09-13 09:36:43.571 -03 [23845] STATEMENT:  CALL
"schema2"."counted_proc"()
2021-09-13 09:36:43.571 -03 [23838] LOG:  background worker "worker_spi"
(PID 23845) exited with exit code 1
2021-09-13 09:36:43.571 -03 [23838] LOG:  background worker "worker_spi"
(PID 23846) exited with exit code 1

I changed the worker_spi example (attached) a bit to execute a simple
procedure. Even using SPI_connect_ext(SPI_OPT_NONATOMIC) I'm getting the
error "invalid transaction termination".

There are something wrong with the attached example or am I missing
something?

Regards,

-- 
Fabrízio de Royes Mello