Why should you care?

PostgresVerse end-of-day settlement runs procedure that posts batches and commits per chunk without one giant transaction.

See it live — copy this example

Run in pgAdmin or psql.

CREATE OR REPLACE PROCEDURE settle_pending_orders(batch_size int)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE orders SET status = 'settled'
  WHERE order_id IN (
    SELECT order_id FROM orders
    WHERE status = 'confirmed'
    LIMIT batch_size
  );
  COMMIT;
END;
$$;

CALL settle_pending_orders(100);

What happened?

• Procedure updates up to 100 confirmed orders to settled then COMMIT inside body.
• CALL executes it — unlike SELECT function().

Practice next

1. Create procedure in PostgresVerse.
2. Seed confirmed orders and CALL with small batch.
3. Verify status changes and transaction committed.
4. Add second COMMIT loop with FOR batch in plpgsql.
5. Wrap CALL in outer transaction and observe interaction.

Remember

Procedures use CALL, not SELECT. Can commit/rollback sub-steps. Good for ETL chunks and maintenance jobs.

PostgresVerse settlement job

Cron CALL settle_pending_orders(500) every minute during reconciliation window.

Outcome: Long lock held on entire orders table avoided.