Are PostgreSQL functions transactional?

Question

Is a PostgreSQL function such as an automatic transaction?

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 a function, will any changes be undone? If this is not the default behavior, how can I make the function transactional?

Answer 1

PostgreSQL 12 Update : Limited top-level PROCEDURE support that can manage transactions . You still cannot manage transactions in regular SQL functions that are called, so the following remains true, unless you use new top-level procedures.

---

Functions are part of the transaction from which they are called. Their effects roll back if the transaction rolls back. Their work is committed if the transaction is committed. Any BEGIN... EXCEPT blocks within a function work similarly (and covertly used) to savepoints, such as the SQL SAVEPOINT and ROLLBACK TO SAVEPOINT statements.

The function either succeeds or completes with an error entirely, with the exception of BEGIN... EXCEPT error handling. If an error occurs in the function that is not being processed, the transaction that calls the function is aborted. Aborted transactions cannot commit, and if they try to commit, COMMIT treated as a ROLLBACK , just like for any other transaction with an error. Note:

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

See how a transaction that is in a state of error due to zero division rolls back to COMMIT ?

If you call a function without an explicit surrounding 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 SELECT raises an error).

PostgreSQL (for now) does not support stand-alone transactions in functions where a procedure / function can commit / roll back regardless of the calling transaction. This can be modeled using a new session via dblink .

BUT , things that are not transactional or imperfectly transactional exist in PostgreSQL. If it has non-transactional behavior in normal BEGIN; do stuff; COMMIT; mode BEGIN; do stuff; COMMIT; BEGIN; do stuff; COMMIT; BEGIN; do stuff; COMMIT; block, it also has non-transactional behavior in the function. For example, nextval and setval , TRUNCATE , etc.

Answer 2

Since my knowledge of PostgreSQL is less profound than that of Craig Ringer, I will try to give a shorter answer: Yes.

If you execute the function with an error, none of the steps will affect the database.

Also, if you execute a request in PgAdmin the same thing happens.

For example, if you execute a query:

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

The update in the line id = 20 your_table will not be stored in the database.

UPDATE September - 2018

To clarify the concept, I made a small example with the nontransactional function nextval.

First, let's create a sequence:

create sequence test_sequence start 100;

Then let's do:

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 request and execute

select nextval('test_sequence');

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

Answer 3

At the function level, it is not transnational. In other words, each statement in a function belongs to one transaction, which is the default db auto-commit value. Auto-default is true. But in any case, you need to call the function using

select schemaName.functionName()

The above statement "select schemaName.functionName ()" is a single transaction, let it name transaction T1, and therefore all statements in the function belong to transaction T1. Thus, the function is in one transaction.

Answer 4

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 using the so-called SQL-commands for transaction management. PL / pgSQL BEGIN / END are for grouping only; they do not start or end a transaction. Functions and trigger procedures are always executed in a transaction set by an external request - they cannot start or commit this transaction, since there will be no context for them. However, the block containing the EXCEPTION clause actually generates a subtransaction that can be rolled back without affecting the external transaction. See Section 39.6.6 for more on this.