5

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:

  1. if lets say S1 is successful but S2 fails, will S1 get committed?
  2. is my understanding that, all statements after BEGIN are executed as a single trasaction, correct?
  3. in the absence of an explicit COMMIT before END and all statements are successful, will the transaction be committed regardless of autocommit = on/off ?
  4. 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!

pranav3688
  • 694
  • 1
  • 11
  • 20
  • Possible duplicate of [Are PostgreSQL functions transactional?](https://stackoverflow.com/questions/12778209/are-postgresql-functions-transactional) – khoroshevj Feb 28 '18 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 Feb 28 '18 at 19:43

1 Answers1

5

Answers by number:

  1. No; if S2 fails, the whole transaction is aborted and can only be rolled back.

  2. 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.

  3. You cannot have COMMIT (or ROLLBACK) in a function.

  4. Yes. This is the same question as 1., only in the negative.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 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? – pranav3688 Feb 28 '18 at 20:04
  • That's what I mean. Even if you try to commit the transaction, it will be rolled back (because it is aborted). – Laurenz Albe Feb 28 '18 at 20:26