Does a procedure run in a transaction in PostgreSQL?

Question

If a procedure fails in middle, are changes at that point from the beginning of SP rolled back implicitly or do we have to write any explicit code to make sure that SP runs in a transaction only?

Platonism

possible duplicate of Execute postgreSQL stored procedure as one transaction – Odel

All covered in the manual. Including the fact that you don't technically have stored procedures in PostgreSQL – Malcommalcontent

Answer 1

The term "stored procedure" often (incorrectly) refers to functions (CREATE FUNCTION), which provide much of the same functionality (and more) as other RDBMS provide with "stored procedures".

True stored procedures (CREATE PROCEDURE) as defined in the ISO/IEC standard were added in Postgres 11. The main difference being transaction handling. See:

• When to use stored procedure / user-defined function?
• What are the differences between “Stored Procedures” and “Stored Functions”?

Either of them always runs in a transaction context. The manual:

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it.

Function

... cannot COMMIT. It is atomic - succeeds or fails completely.

Well, there is error handling in PL/pgSQL. The manual:

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

Do not confuse PL/pgSQL BEGIN (start of a code block) with SQL BEGIN (start of a transaction).

Procedure

... allows COMMIT - which immediately starts a new transaction. There is fine print to this in the manual:

A new transaction starts out with default transaction characteristics such as transaction isolation level. In cases where transactions are committed in a loop, it might be desirable to start new transactions automatically with the same characteristics as the previous one. The commands COMMIT AND CHAIN and ROLLBACK AND CHAIN accomplish this.

DO (executing anonymous code blocks)

Starting with Postgres 11 (when procedures were introduced), transaction control statements are also allowed in anonymous code blocks executed with the DO command. See:

• Do PL/pgSQL functions autocommit on their own?
• COMMIT works in one plpgsql code block, but not in another?

Exceptions

Commands like VACUUM, CREATE DATABASE, or CREATE INDEX CONCURRENTLY do not run in a transaction context and are not allowed at all in a PL/pgSQL code block.

Some things can never be rolled back, including:

• data written to log files

• changes made to a sequence. The manual:

  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 (old sqlfiddle demo)

• dblink calls (or similar). See:

  • Does Postgres support nested or autonomous transactions?

Adscription

I saw the links in your answers but the doc doesn't clearly say that transaction is used in functions and procedures implicitly. So, I don't know where I can make sure your answer is true. – Noami

@SuperKai-KazuyaIto: It's not that a "transaction is used in functions and procedures implicitly". Rather, a function can only be executed within a transaction to begin with. There is a quote (with link to the manual) in my answer saying just that: "Functions and trigger procedures are always executed within a transaction established by an outer query ..." More basics here: postgresql.org/docs/current/tutorial-transactions.html – Adscription

The link postgresql.org/docs/current/tutorial-transactions.html no longer has "Functions and trigger procedures are always executed within a transaction established by an outer query ..." because it was updated. – Noami

@SuperKai: Well, it was the other link. But all the same, the manual has been reworked with the addition of procedures. I updated my answer now to reflect that. Thanks for pointing out! – Adscription

Answer 2

If you are using Postgres 14 procedure like below:

CREATE OR REPLACE PROCEDURE test_error(schema_name text)
LANGUAGE plpgsql
AS
$$
declare
<declare any vars that you need>
BEGIN
<do your thing>
END
$$;

For all practical purposes, code written in between the BEGIN and END block is executed in a single transaction. Hence, if any of the statements in the block fail, all the previous statements will be rolled back automatically. You do not need to explicitly write any roll back code.

However, there are special cases where one can have fine grained control over when to start/commit/rollback transactions. Refer to : https://www.postgresql.org/docs/current/plpgsql-transactions.html for details.

Hectorhecuba

Answer 3

From the official document of Postgresql:

In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/pgSQL.)

https://www.postgresql.org/docs/11/plpgsql-transactions.html

Abbasid

Answer 4

A procedure is run in a transaction so if there is error, the transaction is rollbacked.

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)

Noami