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
- worker_spi_procedure.patch (text/x-patch) patch
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