commit and rollback inside the postgres function

Question

How can i implement commit and rollback in my postgres function , code is as below. rollback here is used in exceptional block

    CREATE OR REPLACE FUNCTION test () RETURNS VOID AS $body$
DECLARE

test_var smallint;

BEGIN
FOR abc IN
(SELECT DISTINCT ID
    FROM plantab abc
    JOIN xxx_upl_att xxx_val ON zzz_val.idf_tec = abc.ID
)
LOOP
    select * from abc_pk_attribut(abc.ID) into test_var;
    select * from abc_pk_champ(abc.ID) into test_var;
    select * from abc_pk_valeur(abc.ID) into test_var;
    select * from abc_pk_m_att(abc.ID) into test_var;
    PERFORM abc_pk_maj_avalorise(abc.ID);
    PERFORM abc_pk_maj_ab_attr(abc.ID);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise notice 'program_error';
END;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

Pavel Stehule · Accepted Answer · 2018-11-13 07:45:46Z

2

You cannot to do it. PostgreSQL functions is running inside outer transaction, and this outer transaction should be committed or rollback outside. When you run function from SELECT statement, then this statement is executed under implicit transaction (in autocommit mode) or explicit transaction (when autocommit is off). Who started this transaction, then he has to finish transaction.

What you can:

you can raise a exception - when this exception is not handled, then outer statement has to run rollback.
you can use clause EXCEPTION WHEN. Then protected block is implicitly joined with savepoint. When any exception is handled, then engine use ROLLBACK TO savepoint implicitly.

Anyway, this system is different than you can know from MS-SQL or Oracle, and you cannot to use some patterns that you know from Oracle. On second hand - Postgres design is much simple.

New PostgreSQL 11 has procedures, and there you can use explicit COMMIT and ROLLBACK.

answered Nov 13, 2018 at 7:45

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sanjum Over a year ago

Can you modify the above code and show me how can i implement it ? am using postgresql 9.5 as per business requirement

Sanjum Over a year ago

i don't need autocommit as this is bank transaction project, do i have to add autocommit off in my function at first then execute the later part ?

Pavel Stehule Over a year ago

@Sanjum - autocommit is client side functionality on PostgreSQL. You cannot to operate with this from server side.

Collectives™ on Stack Overflow

commit and rollback inside the postgres function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related