CODEWITHSUNDEEP
postgresqlplpgsqldistributed-transactionsdblink
tdayi
tdayi

Reputation: 142

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

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

Upvotes: 1

Views: 815

Answers (1)

user554538
user554538

Reputation:

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.

Upvotes: 0

Related Questions