PostgreSQL 11 Administration Cookbook

There's more…

You can use both COMMIT and ROLLBACK in a procedure. Each new transaction will see the changes from prior transactions and any other concurrent commits that have occurred.

What happens if your Procedure is interrupted? Since we are using multiple transactions to complete the task, we wouldn't expect the whole task to be atomic. If the execution is interrupted, we would need to re-run the parts that didn’t execute successfully. What happens if we accidentally re-run parts that have already been executed? We would give some people a double pay rise, but not everyone.

To cope, let's invent a simple job restart mechanism. This uses a persistent table to track changes as they are made, accessed by a simple API:

CREATE TABLE job_status ...