Обсуждение: SPI Interface to Call Procedure with Transaction Control Statements?
Hi All,
I sent an email with the same problem in pgsql-general mailing but no one has responded, so I try to reach out by asking this question in the hacker list.
In PG-11, procedures were introduced. In the pg_partman PostgreSQL extension, a procedure named run_maintenance_proc was developed to replace run_maintenance function. I was trying to call this procedure in pg_partman with SPI_execute() interface and this is the command being executed:
CALL "partman".run_maintenance_proc(p_analyze := true, p_jobmon := true)
Detailed code please see: https://github.com/pgpartman/pg_partman/pull/242
I received the following error:
2019-01-02 20:13:04.951 PST [26446] ERROR: invalid transaction termination
2019-01-02 20:13:04.951 PST [26446] CONTEXT: PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean,boolean) line 45 at COMMITApparently, the transaction control command 'COMMIT' is not allowed in a procedure CALL function. But I can CALL this procedure in psql directly.
According to the documentation of CALL, "If
CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction." Therefore, it looks like that SPI_execute() is calling the procedure within a transaction block. So Is there any SPI interface that can be used in an extension library to call a procedure with transaction control commands? (I tried to use SPI_connect_ext(SPI_OPT_NONATOMIC) to establish a nonatomic connection but it doesn't help.)
Thanks,
Jiayi Liu
>>>>> "Jack" == Jack LIU <toliujiayi@gmail.com> writes: Jack> (I tried to use SPI_connect_ext(SPI_OPT_NONATOMIC) to establish a Jack> nonatomic connection but it doesn't help.) You need to be specific here about how it didn't help, because this is exactly what you're supposed to do, and it should at least change what error you got. -- Andrew (irc:RhodiumToad)
Hi Andrew,
This is my code to call the procedure with SPI_connect_ext(SPI_OPT_NONATOMIC).
if (run_proc) {
appendStringInfo(&buf, "CALL \"%s\".run_maintenance_proc(p_analyze := %s, p_jobmon := %s);", partman_schema, analyze, jobmon);
expected_ret = SPI_OK_UTILITY;
function_run = "run_maintenance_proc() procedure";
SPI_finish();
SPI_connect_ext(SPI_OPT_NONATOMIC);
pgstat_report_activity(STATE_RUNNING, buf.data);
ret = SPI_execute(buf.data, false, 0);
if (ret != expected_ret)
elog(FATAL, "Cannot call pg_partman %s: error code %d", function_run, ret);
}
It gave the same error:
2019-01-14 22:18:56.898 PST [16048] LOG: pg_partman dynamic background worker (dbname=postgres) dynamic background worker initialized with role ubuntu on database postgres
2019-01-14 22:18:56.918 PST [16048] ERROR: invalid transaction termination
2019-01-14 22:18:56.918 PST [16048] CONTEXT: PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean,boolean) line 45 at COMMIT
SQL statement "CALL "partman".run_maintenance_proc(p_analyze := true, p_jobmon := true);"
2019-01-14 22:18:56.923 PST [26352] LOG: background worker "pg_partman dynamic background worker (dbname=postgres)" (PID 16048) exited with exit code 1
Thanks,
Jack
On Sun, Jan 13, 2019 at 10:21 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Jack" == Jack LIU <toliujiayi@gmail.com> writes:
Jack> (I tried to use SPI_connect_ext(SPI_OPT_NONATOMIC) to establish a
Jack> nonatomic connection but it doesn't help.)
You need to be specific here about how it didn't help, because this is
exactly what you're supposed to do, and it should at least change what
error you got.
--
Andrew (irc:RhodiumToad)
>>>>> "Jack" == Jack LIU <toliujiayi@gmail.com> writes: Jack> Hi Andrew, Jack> This is my code to call the procedure with Jack> SPI_connect_ext(SPI_OPT_NONATOMIC). Ah. You need to take a look at exec_stmt_call in plpgsql, and do the same things it does with snapshot management (specifically, setting the no_snapshot flag on the plan that you're going to execute). SPI forces atomic mode if the normal snapshot management is in use, because otherwise a commit inside the procedure would warn about still having a snapshot open. -- Andrew (irc:RhodiumToad)
On 15/01/2019 11:49, Andrew Gierth wrote: >>>>>> "Jack" == Jack LIU <toliujiayi@gmail.com> writes: > > Jack> Hi Andrew, > Jack> This is my code to call the procedure with > Jack> SPI_connect_ext(SPI_OPT_NONATOMIC). > > Ah. You need to take a look at exec_stmt_call in plpgsql, and do the > same things it does with snapshot management (specifically, setting the > no_snapshot flag on the plan that you're going to execute). SPI forces > atomic mode if the normal snapshot management is in use, because > otherwise a commit inside the procedure would warn about still having a > snapshot open. Yeah, eventually we might want to add a new SPI function to do non-atomic calls, but right now you need to go the manual route. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services