postgresql two phase commit prepare transaction error: transactions can not be started in PL / pgSQL

Question

I would like to do a two phase commit transaction with prepare transaction for PostgreSQL.

Could you help with the error?

I can not understand how to connect to the remote database via dblick with prepare transaction?

create or replace function insert_into_table_a() returns void as $$
    declare 
        trnxprepare text;
        trnxcommit text;
        trnxrollback text;
        trnxid varchar;
begin

    select uuid_generate_v4() into trnxid;
    select 'prepare transaction ' || ' ''' || trnxid || ' ''' into trnxprepare;
    select 'commit prepared     ' || ' ''' || trnxid || ' ''' into trnxcommit;
    select 'rollback prepared   ' || ' ''' || trnxid || ' ''' into trnxrollback;

    insert into table_a values ('test');
    perform dblink_connect('cn','dbname=test2 user=test2user password=123456');
    perform dblink_exec('cn','insert into table_b values (''test 2'');');
    perform dblink_disconnect('cn');

    execute trnxprepare;
    execute trnxcommit;

    exception 
        when others then
            execute trnxrollback;
            perform dblink_disconnect('cn');
            raise notice '% %', sqlerrm, sqlstate;
end;
$$ language plpgsql;




select insert_into_table_a();

ERROR: ERROR: transactions can not be started in PL / pgSQL

HINT: Use the BEGIN block with the EXCEPTION clause instead.

CONTEXT: insert_into_table_a () PL / pgSQL function, line 24, in EXECUTE

SQL state: 0A000

What questions remain open after reading the error message and the hint? You just cannot manage transactions inside PL/pgSQL. PostgreSQL v11 will allow some transaction handling inside PL/pgSQL. — Laurenz Albe

Unknown Unknown · Accepted Answer · 2018-09-14T06:13:44

So, in Postgres, you can't control transactions from inside functions for the most part. You can raise errors to abort them indirectly, if they aren't caught, but you can't begin or end them directly like this.

To manage transactions, you'd either need a worker process as a loadable module, or to control the transaction from a client through a connection.

postgresql two phase commit prepare transaction error: transactions can not be started in PL / pgSQL

1 Answers