Do stored procedures run in database transaction in Postgres?
Clash Royale CLAN TAG#URR8PPP
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?
All covered in the manual. Including the fact that you don't technically have stored procedures in PostgreSQL
– Richard Huxton
Jan 31 '15 at 12:34
So do you have your answer?
– Erwin Brandstetter
Aug 10 at 17:05
1 Answer
1
Strictly speaking Postgres does not currently (up to and including version 10) have "stored procedures" as defined in the ANSI standard. Everything is done with "functions" instead, 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 are 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
or CREATE INDEX CONCURRENTLY
which cannot run within a transaction block are not allowed.
VACUUM
CREATE INDEX CONCURRENTLY
Per documentation 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.
EXCEPTION
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.
BEGIN
EXCEPTION
There are special exceptions, including but not limited to:
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.
serial
prepared statements
dblink calls (or similar)
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
possible duplicate of Execute postgreSQL stored procedure as one transaction
– Mitch Wheat
Jan 31 '15 at 10:05