CODEWITHSUNDEEP
postgresql
yellowandy
yellowandy

Reputation: 89

PostgreSQL function is always called inside a transaction

I've been trying to research if postgres functions are always run as a single transaction and have found hints in various answers but no links back to the official documentation. Can somebody cite a source that states postgres functions are always run inside a single transaction?

Have read through the documentation here but no luck: https://www.postgresql.org/docs/12/plpgsql-transactions.html

The functions are just being called via a select call and no transaction BEGIN/COMMIT calls are used:


select ccdb.fn_automation_for_updation()

In short I am going through a code base where a user is trying to solve concurrency issues inside a function but think a race condition is present.

Upvotes: 0

Views: 1739

Answers (1)

harmic
harmic

Reputation: 30577

In the documentation here it says:

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.

In other words, even a single SELECT happens inside a transaction even if no specific BEGIN command was issued.

Functions that are called from a query will execute in the context of the transaction established by the query.

Procedures executed using CALL or DO do not automatically execute in a transaction; procedures should manage their own transactions. See: plpgsql transactions.

Upvotes: 3

Related Questions