postgres functions and transactions with BEGIN

Question

I have some questions about how postgres functions and transactions work.

Currently my function looks like this:

CREATE OR REPLACE FUNCTION test_function(some_id character varying)
    RETURNS character varying AS
$BODY$
BEGIN
    S1;
    S2;
    S3;
    .
    .
    Sn;
RETURN some_id;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

The statements can be INSERT, UPDATE or plain SELECT queries based on some_id. As I understand from postgre documentation, all statements in this function are executed as a single transaction and committed at the END.

My questions are:

if lets say S1 is successful but S2 fails, will S1 get committed?
is my understanding that, all statements after BEGIN are executed as a single trasaction, correct?
in the absence of an explicit COMMIT before END and all statements are successful, will the transaction be committed regardless of autocommit = on/off ?
lets say S1, S2, S3 are all INSERT statements. S1 and S2 succeed but S3 fails, will the inserts in S1, S2 be reversed in the absence of an explicit ROLLBACK statement?

Thank you!

Possible duplicate of Are PostgreSQL functions transactional? — khoroshevj
– khoroshevj, Commented Feb 28, 2018 at 19:41
BEGIN and END here are used just for grouping statements in the function, they do not start or end a transaction — khoroshevj
– khoroshevj, Commented Feb 28, 2018 at 19:43

Laurenz Albe · Accepted Answer · 2018-02-28 19:53:38Z

6

Answers by number:

No; if S2 fails, the whole transaction is aborted and can only be rolled back.
There is probably a misunderstanding. The SQL statement BEGIN that starts a transaction is something quite different from the BEGIN that starts a PL/pgSQL block. The latter does not start a transaction.

If there was no explicit SQL command BEGIN, every statement runs in its own transaction (“autocommit”).

All statements in a function are executed in a single transaction.
You cannot have COMMIT (or ROLLBACK) in a function.
Yes. This is the same question as 1., only in the negative.

answered Feb 28, 2018 at 19:53

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

2 Comments

pan8863 Over a year ago

just to be clear about your answer for 4: 'YES' the inserted rows by S1, S2 will be reversed in the absence of explicit ROLLBACK. Right?

Laurenz Albe Over a year ago

That's what I mean. Even if you try to commit the transaction, it will be rolled back (because it is aborted).

Collectives™ on Stack Overflow

postgres functions and transactions with BEGIN

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related