COMMIT inside a procedure in SQL

Only procedures may commit mid-run, releasing work batch by batch.

-- Only procedures may COMMIT mid-run, releasing work batch by batch.
CREATE PROCEDURE archive_canceled()
LANGUAGE plpgsql
AS $$
DECLARE
    doomed RECORD;
BEGIN
    FOR doomed IN
        SELECT id
        FROM orders
        WHERE status = 'canceled'
    LOOP
        UPDATE orders
        SET status = 'archived'
        WHERE id = doomed.id;
        COMMIT;
    END LOOP;
END;
$$;

CALL archive_canceled();

SELECT id, status
FROM orders
WHERE status = 'archived';

How it works

1. The FOR ... IN SELECT loop visits each canceled order.
2. Each pass archives one order and COMMITs it before the next begins.
3. A crash mid-run keeps every batch already committed — the point of the pattern.

Keywords and builtins used here

• AS
• BEGIN
• CALL
• COMMIT
• CREATE
• DECLARE
• END
• FOR
• FROM
• IN
• LANGUAGE
• PROCEDURE
• SELECT
• SET
• UPDATE
• WHERE

The run, in numbers

Lines

25

Characters to type

395

Tokens

67

Three-star pace

65 tpm

At the three-star pace of 65 tokens a minute, this run takes about 62 seconds.

Type this snippet

Step 3 of 4 in Procedures & transactions, step 11 of 23 in PostgreSQL Stored Procedures.

← Previous Next →