Do stored procedures run in database transaction in Postgres?

by

Strictly speaking, Postgres did not have stored procedures as defined in the ISO/IEC standard before version 11. The term is often used incorrectly to refer to functions, which provide much of the same functionality (and more) as other RDBMS provide with “stored procedures”. The main difference being transaction handling.

True stored procedures were finally introduced with Postgres 11:

Functions are atomic in Postgres and automatically run inside their own transaction unless called within an outer transaction. They always run inside a single transaction and succeed or fail completely. Consequently, one cannot begin or commit transactions within the function. And commands like VACUUM, CREATE DATABASE, or CREATE INDEX CONCURRENTLY which do not run in a transaction context are not allowed.

The manual on PL/pgSQL:

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.

Error handling:

By default, any error occurring in a PL/pgSQL function aborts
execution of the function, and indeed of the surrounding transaction
as well. You can trap errors and recover from them by using a BEGIN
block with an EXCEPTION clause.

There are exceptions, including but not limited to:

  • data written to log files

  • changes made to a sequence

    Important: Some PostgreSQL data types and functions have special rules
    regarding transactional behavior. In particular, changes made to a
    sequence (and therefore the counter of a column declared using serial)
    are immediately visible to all other transactions and are not rolled
    back if the transaction that made the changes aborts.

  • prepared statements
    SQL Fiddle demo

  • dblink calls (or similar)

More Related Contents:

  1. Store query result in a variable using in PL/pgSQL
  2. PostgreSQL Database Log Transaction
  3. What is the minimal proof that a database relation is not in BCNF?
  4. Keep PostgreSQL from sometimes choosing a bad query plan
  5. Copying PostgreSQL database to another server
  6. What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  7. When and why are database joins expensive?
  8. NoSql vs Relational database
  9. What is a database transaction?
  10. Relational table naming convention [closed]
  11. When I should use one to one relationship?
  12. Kill a postgresql session/connection
  13. PostgreSQL: Drop PostgreSQL database through command line [closed]
  14. Storing time-series data, relational or non?
  15. Still Confused About Identifying vs. Non-Identifying Relationships
  16. Difference between a theta join, equijoin and natural join
  17. Continuing a transaction after primary key violation error
  18. How can I find MAX with relational algebra?
  19. How can I solve Postgresql SCRAM authentication problem?
  20. Convert SQLITE SQL dump file to POSTGRESQL
  21. Minimum no of tables that exists after decomposing relation R into 1NF?
  22. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  23. NULL permitted in Primary Key – why and in which DBMS?
  24. Connect to a heroku database with pgadmin
  25. How do I automatically update a timestamp in PostgreSQL
  26. Starting and populating a Postgres container in Docker
  27. Adding a new value to an existing ENUM Type
  28. How can I import a .sql file into my Heroku postgres database?
  29. Knex Transaction with Promises
  30. Pros/cons of document-based databases vs. relational databases

Leave a Comment