Unable to Create COMMIT inside PostgreSQL 11.5 Procedure

I'm trying to learn PostgreSQL stored procedures. Specifically creating the procedure below in PSQL.

CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor) 
    LANGUAGE PLPGSQL 
    AS $
    BEGIN
        BEGIN; -- I've tried removing this but the behaviour is the same
        cresults:= 'cur';
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;  
        COMMIT;
        OPEN cresults FOR SELECT * FROM procsampledata;  
    END;
$;

Then I execute it like so, but receive an error:

postgres=# call build_and_populate(null);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT

I've tried with AUTOCOMMIT set to both on and off.

This is my Postgres version

PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

Can anyone see what I'm doing wrong? Thank you!

The

BEGIN;

is wrong and will cause an error. You cannot start a transaction inside a procedure, because there is already an active transaction.

You can end a transaction, which implies that a new transaction is started immediately.

Without the BEGIN;, your procedure works just fine.

The problem must be with the way you are calling it. As the documentation says:


Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between.

There is probably a SELECT in your call stack.

Another possibility is that you explicitly started a transaction with BEGIN before calling the stored procedure.

This does not work either, which is an undocumented implementation restriction that may be fixed in the future. See this thread for reference.

StackExchange Database Administrators Q#249446, answer score: 8