Does Postgres support nested or autonomous transactions?

by

Postgres does support nested transactions, but they differ from the conventional SQL, more like transactions with nested partial points.

On the top level you always have your typical BEGIN/COMMIT/ROLLBACK, and on nested levels you have to use the following commands:

  • SAVEPOINT name – creates a new savepoint, with name unique for the transaction
  • RELEASE SAVEPOINT name – commits the savepoint, though it will only persist if the containing transaction commits
  • ROLLBACK TO SAVEPOINT name – rolls back the savepoint

You would also have to make sure that:

  • The names used for each SAVEPOINT are unique;
  • Failure in one SAVEPOINT is propagated upwards to the top level.

The last bit is a bit tricky, unless you use a library that can do that for you automatically.

When I wrote pg-promise, I made sure that those two provisions are guaranteed:

  • It generates save-point names automatically, as level_1, level_2, and so on, based on the transaction level;
  • It executes containing ROLLBACK TO SAVEPOINT name, plus the top-level ROLLBACK in case a child transaction fails – all built on the standard promise-chaining logic.

See also the limitations of the PostgreSQL nested transactions explained…

More Related Contents:

  1. Continuing a transaction after primary key violation error
  2. Refactor a PL/pgSQL function to return the output of various SELECT queries
  3. Any downsides of using data type “text” for storing strings?
  4. Query for array elements inside JSON type
  5. Calculating Cumulative Sum in PostgreSQL
  6. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  7. Is it possible to make a recursive SQL query?
  8. Change PostgreSQL columns used in views
  9. Way to try multiple SELECTs till a result is available?
  10. How to set auto increment primary key in PostgreSQL?
  11. PostgreSQL IF statement
  12. Split given string and prepare case statement
  13. Column ‘mary’ does not exist
  14. Get top row(s) with highest value, with ties
  15. Delete parent if it’s not referenced by any other child
  16. PostgreSQL wildcard LIKE for any of a list of words
  17. Query that ignore the spaces
  18. SQL query to get most recent row for each instance of a given key
  19. Postgres constraint for unique datetime range
  20. Postgres function returning table not returning data in columns
  21. COPY with dynamic file name
  22. Table or column name cannot start with numeric?
  23. PostgreSQL column ‘foo’ does not exist
  24. PostgreSQL 9.3: Dynamic pivot table
  25. How to detect query which holds the lock in Postgres?
  26. PostgreSQL how to create a copy of a database or schema?
  27. PostgreSQL: fill NULL values in timeserie query with previous value
  28. Postgres: convert single row to multiple rows (unpivot)
  29. Get most common value for each value of another column in SQL
  30. PostgreSQL: top n entries per item in same table

Leave a Comment