Search code examples
plpgsql

PL/pgSQL transaction behavior when function calls another

I have function A calling function B.

Does the transaction support of A, include B? Meaning B succeeds, but after that A fails.

CREATE OR REPLACE FUNCTION A(<params>) 
RETURNS bool as 
$$
DECLARE 
  <>

BEGIN
  <..>
  PERFORM "B"(<params>);
  <..> -- error occurs here
END;
$$ LANGUAGE plpgsql;

I don't want B to be committed. Is this implicitly guaranteed or do I need to explicitly take care of things (if so, how should I?)

Solution

  • That is implicitly guaranteed. In PostgreSQL, almost all SQL statements run in a single transaction, and INSERT, UPDATE, DELETE and SELECT always do. A function always gets called from one of these statements, so that function and all the functions it calls again run in the same single transaction as the statement that called the function. If there is a ROLLBACK the whole statement and the effects of all the functions it called directly or indirectly get rolled back.