Re: SPI Interface to Call Procedure with Transaction Control Statements?

[Jack LIU]

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 <and...@tao11.riddles.org.uk>
wrote:

> >>>>> "Jack" == Jack LIU <toliuji...@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)
>