SPI Interface to Call Procedure with Transaction Control Statements

Поиск
Список
Период
Сортировка
От Jack LIU
Тема SPI Interface to Call Procedure with Transaction Control Statements
Дата
Msg-id CABZ0cG3ZhKRJGHNOgpO_Ae73N-K=Wj++MOGmvB5QzXZHxS0kfQ@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
Hi All,

In PG-11, procedures were introduced. In the pg_partman tool, 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)

 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 COMMIT
Apparently, 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 my question is that is there any SPI interface that can 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Chuck Martin
Дата:
Сообщение: Re: getting pg_basebackup to use remote destination
Следующее
От: Zexuan Luo
Дата:
Сообщение: Function `set_config` doesn't work in with query?