Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Do stored procedures run in database transaction in Postgres?

If a stored 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 database transaction only?

like image 377
Harshal Dhumal Avatar asked Jan 31 '15 10:01

Harshal Dhumal


People also ask

Do stored procedures run in a transaction?

This is how stored procedures work by default. The stored procedure isn't wrapped within a transaction automatically. If you want the stored procedure to stop when it hits the first error you'll want to put some TRY/CATCH login in there to return in the event of a problem with command 2 for example.

Does PostgreSQL use stored procedures?

It was after PostgreSQL 11 was released, that transactions were made possible through stored procedures. PostgreSQL Stored Procedure also supports various languages in tandem with standard SQL syntax.

How do I run a stored procedure in PostgreSQL?

To execute PROCEDURE in PostgreSQL, use the CALL statement instead of SELECT statement. This is one of the differences between PROCEDURE and FUNCTION. You can also specify parameter name in the CALL statement. This is another way to execute the PROCEDURE.

How transactions are managed in PostgreSQL?

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. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.


1 Answers

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.

  • What are the differences between “Stored Procedures” and “Stored Functions”?

True stored procedures were finally introduced with Postgres 11:

  • When to use stored procedure / user-defined function?

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)

    • Does Postgres support nested or autonomous transactions?
like image 66
Erwin Brandstetter Avatar answered Sep 20 '22 05:09

Erwin Brandstetter