Are PostgreSQL functions transactional?

Question

Is a PostgreSQL function such as the following automatically transactional?

CREATE OR REPLACE FUNCTION refresh_materialized_view(name)
  RETURNS integer AS
$BODY$
 DECLARE
     _table_name ALIAS FOR $1;
     _entry materialized_views%ROWTYPE;
     _result INT;
 BEGIN          

     EXECUTE 'TRUNCATE TABLE ' || _table_name;

     UPDATE materialized_views
     SET    last_refresh = CURRENT_TIMESTAMP
     WHERE  table_name = _table_name;

     RETURN 1;
END
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

In other words, if an error occurs during the execution of the function, will any changes be rolled back? If this isn't the default behavior, how can I make the function transactional?

Franconian

@FrankHeikens my question is "will changes be automtically rolled-back after an error", rather than "will changes be rolled-back if I execute ROLLBACK" – Esse

@Don Be aware that TRUNCATE has, or used to have, some somewhat funky transactional behaviour. I don't remember the specifics; search the pgsql-general archives. – Elexa

Yes, functions are transactional, even if written in LANGUAGE SQL. – Construction

@CraigRinger As far as I can tell TRUNCATE ignores all savepoints and just destroys your table data outright – Wamble

Answer 1

PostgreSQL 12 update: there is limited support for top-level PROCEDUREs that can do transaction control. You still cannot manage transactions in regular SQL-callable functions, so the below remains true except when using the new top-level procedures.

---

Functions are part of the transaction they're called from. Their effects are rolled back if the transaction rolls back. Their work commits if the transaction commits. Any BEGIN ... EXCEPT blocks within the function operate like (and under the hood use) savepoints like the SAVEPOINT and ROLLBACK TO SAVEPOINT SQL statements.

The function either succeeds in its entirety or fails in its entirety, barring BEGIN ... EXCEPT error handling. If an error is raised within the function and not handled, the transaction calling the function is aborted. Aborted transactions cannot commit, and if they try to commit the COMMIT is treated as ROLLBACK, same as for any other transaction in error. Observe:

regress=# BEGIN;
BEGIN
regress=# SELECT 1/0;
ERROR:  division by zero
regress=# COMMIT;
ROLLBACK

See how the transaction, which is in the error state due to the zero division, rolls back on COMMIT?

If you call a function without an explicit surounding transaction the rules are exactly the same as for any other Pg statement:

BEGIN;
SELECT refresh_materialized_view(name);
COMMIT;

(where COMMIT will fail if the SELECT raised an error).

PostgreSQL does not (yet) support autonomous transactions in functions, where the procedure/function could commit/rollback independently of the calling transaction. This can be simulated using a new session via dblink.

BUT, things that aren't transactional or are imperfectly transactional exist in PostgreSQL. If it has non-transactional behaviour in a normal BEGIN; do stuff; COMMIT; block, it has non-transactional behaviour in a function too. For example, nextval and setval, TRUNCATE, etc.

Elexa

Very clear explanation. Special thanks for illustrating your answer with short and to the point examples. – Absurdity

I believe you can now perform an actual commit inside a procedure, since PG11. blog.dbi-services.com/… – Shuntwound

(and more info here in official docs: postgresql.org/docs/12/plpgsql-transactions.html ) – Shuntwound

@craig-ringer What did you mean by "limited" support of procedures? Why is it limited? – Partlow

Answer 2

As my knowledge of PostgreSQL isn't as deep as Craig Ringer´s I will try to give a shorter answer: Yes.

If you execute a function that has an error in it, none of the steps will impact in the database.

Also, if you execute a query in PgAdmin the same happen.

For example, if you execute in a query:

update your_table yt set column1 = 10 where yt.id=20;

select anything_that_do_not_exists;

The update in the row, id = 20 of your_table will not be saved in the database.

UPDATE Sep - 2018

To clarify the concept I have made a little example with non-transactional function nextval.

First, let´s create a sequence:

create sequence test_sequence start 100;

Then, let´s execute:

update your_table yt set column1 = 10 where yt.id=20; select nextval('test_sequence'); select anything_that_do_not_exists;

Now, if we open another query and execute

select nextval('test_sequence');

We will get 101 because the first value (100) was used in the latter query (that is because the sequences are not transactional) although the update was not committed.

Spinks

Thank you for the clear crisp answer! After reading Craig's answer I was left feeling unsure. – Anticlockwise

much better answer than Craig's – Framing

Beware of non-transactional things like nextval though; if they're not transactional or not fully transactional as normal queries, they aren't in functions either. – Elexa

Answer 3

https://www.postgresql.org/docs/current/static/plpgsql-structure.html

It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the similarly-named SQL commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. For more about that see Section 39.6.6.

Kraken

Answer 4

In the function level, it is not transnational. In other words, each statement in the function belongs to a single transaction, which is the default db auto commit value. Auto commit is true by default. But anyway, you have to call the function using

select schemaName.functionName()

The above statement 'select schemaName.functionName()' is a single transaction, let's name the transaction T1, and so the all the statements in the function belong to the transaction T1. In this way, the function is in a single transaction.

Demit

Thats not true at all. Functions are always atomic. – Protrude

Answer 5

Postgres 14 update: All statements written in between the BEGIN and END block of a Procedure/Function is executed in a single transaction. Thus, any errors arising while execution of this block will cause automatic roll back of the transaction.

Choriamb

For PostgreSQL 15: "By default, any error occurring in a PL/pgSQL function aborts execution of the function and the surrounding transaction." Cfr. 43.6.8. Trapping Errors – Ebersole

BEGIN ... END; is not transaction. BEGIN ... END; is just the statement to have multiple statements. BEGIN; ... COMMIT; is actually transaction. – Chloromycetin

Answer 6

Yes, a function and procedure are transactional(atomic) in PostgreSQL. A function and procedure are run in a transaction so if there is error, the transaction is rollbacked.

For example, you create my_func() function which sets 5 to my.var then can cause division by zero error as shown below:

CREATE FUNCTION my_func(value INTEGER) RETURNS INTEGER AS $$
BEGIN
SET my.var = 5; -- Here
RETURN 1/value;
END;
$$ LANGUAGE plpgsql;

First, you set 2 to my.var, then call my_func(1) then 5 is set to my.var successfully as shown below:

postgres=# SET my.var = 2;
SET
postgres=# SELECT my_func(1);
 my_func
---------
       1
(1 row)
postgres=# SELECT current_setting('my.var');
 current_setting
-----------------
 5
(1 row)

Now, you set 2 to my.var, then call my_func(0) then division by zero error occurs, then my.var is still 2 as shown below because my_func() function is rollbacked:

postgres=# SET my.var = 2;
SET
postgres=# SELECT my_func(0);
ERROR:  division by zero
...
postgres=# SELECT current_setting('my.var');
 current_setting
-----------------
 2
(1 row)

And for example, you create my_proc() procedure which sets 5 to my.var then can cause division by zero error as shown below:

CREATE PROCEDURE my_proc(INOUT value INTEGER) AS $$
BEGIN
SET my.var = 5; -- Here
SELECT 1/value INTO value;
END;
$$ LANGUAGE plpgsql;

First, you set 2 to my.var, then call my_proc(1) then 5 is set to my.var successfully as shown below:

postgres=# SET my.var = 2;
SET
postgres=# CALL my_proc(1);
 my_func
---------
       1
(1 row)
postgres=# SELECT current_setting('my.var');
 current_setting
-----------------
 5
(1 row)

Now, you set 2 to my.var, then call my_proc(0) then division by zero error occurs, then my.var is still 2 as shown below because my_proc() procedure is rollbacked:

postgres=# SET my.var = 2;
SET
postgres=# CALL my_proc(0);
ERROR:  division by zero
...
postgres=# SELECT current_setting('my.var');
 current_setting
-----------------
 2
(1 row)

Chloromycetin

Answer 7

Additionally, the ATOMIC Transaction including triggers as well.

Fishy