Are PostgreSQL functions transactional?

by

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.

More Related Contents:

  1. How do I do large non-blocking updates in PostgreSQL?
  2. Sequences not affected by transactions?
  3. now() default values are all showing same timestamp
  4. Why the “Group BY” by month didn’t work? [closed]
  5. What to do with null values when modeling and normalizing?
  6. Postgres: INSERT if does not exist already
  7. postgres default timezone
  8. How to get the count of current month Sunday’s in psql?
  9. quick random row selection in Postgres
  10. Restore a postgres backup file using the command line?
  11. How to close idle connections in PostgreSQL automatically?
  12. postgresql – replace all instances of a string within text field
  13. Is it possible to have autoincrement number without it being an ID?
  14. PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
  15. How do I login and authenticate to Postgresql after a fresh install?
  16. pgadmin4 : postgresql application server could not be contacted.
  17. PostgreSQL error ‘Could not connect to server: No such file or directory’
  18. Insert if not exists, else return id in postgresql
  19. function returns multiple columns as a single column instead of multiple columns
  20. Postgres: check if array field contains value?
  21. Dynamic pivot query using PostgreSQL 9.3
  22. PostgreSQL ERROR: canceling statement due to conflict with recovery
  23. Find Parent Recursively using Query
  24. Is there a way to disable updates/deletes but still allow triggers to perform them?
  25. Terminate hung query (idle in transaction)
  26. Reset PostgreSQL primary key to 1
  27. Setting application_name on Postgres/SQLAlchemy
  28. How to fix pg_dump version mismatch errors?
  29. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  30. PostgreSQL composite primary key

Leave a Comment