Procedures and transaction control

conceptThe concept

A function always runs inside the caller's transaction and returns a value; it can be used in a SELECT. A procedure is invoked with CALL, cannot appear in a query, and when called outside an explicit transaction block may run COMMIT and ROLLBACK, starting a new transaction each time.

Results come back through INOUT (and since 14, OUT) parameters. Transaction control is not allowed when the CALL itself is inside a BEGIN block, inside a function, or inside a loop over a cursor that would be invalidated.

whyWhy it matters

One huge transaction that updates 50 million rows holds locks for its whole duration, bloats the table, cannot be resumed after a failure, and keeps the xmin horizon back so VACUUM cannot clean anywhere. Committing every few thousand rows avoids all of that.

Use caseReal-world scenario

Each night, orders older than two years are copied to an archive table in batches of 5,000 and deleted, committing after each batch so replicas, VACUUM and other sessions are never blocked for long.

Try itExamples

Run and edit these in the interactive lesson.

CREATE TABLE archive_demo (id int PRIMARY KEY, created_at timestamptz);
CREATE PROCEDURE archive_in_batches(batch int) LANGUAGE plpgsql AS $$
DECLARE n int;
BEGIN
  LOOP
    INSERT INTO archive_demo
    SELECT id, created_at FROM orders o
    WHERE created_at < '2024-02-01'
      AND NOT EXISTS (SELECT 1 FROM archive_demo a WHERE a.id = o.id)
    ORDER BY id LIMIT batch;
    GET DIAGNOSTICS n = ROW_COUNT;
    RAISE NOTICE 'copied % rows', n;
    EXIT WHEN n = 0;
    COMMIT;
  END LOOP;
END $$;
CALL archive_in_batches(100);
SELECT count(*) AS archived FROM archive_demo;
DROP PROCEDURE archive_in_batches(int);
DROP TABLE archive_demo;

BEGIN;
CREATE PROCEDURE tx_demo() LANGUAGE plpgsql AS $$ BEGIN COMMIT; END $$;
CALL tx_demo();

BEGIN;
CREATE PROCEDURE order_stats(IN p_status text, INOUT n bigint DEFAULT NULL, INOUT revenue numeric DEFAULT NULL)
LANGUAGE plpgsql AS $$
BEGIN
  SELECT count(*), sum(total) INTO n, revenue FROM orders WHERE status = p_status;
END $$;
CALL order_stats('pending');
ROLLBACK;

internalsUnder the hood

Each COMMIT inside the procedure ends the current transaction and the next statement starts a new one, with a new snapshot. Local PL/pgSQL variables survive across commits; open cursors do not, unless they are FOR loops over queries, which PL/pgSQL converts to holdable cursors.

Avoid / preferDo this, not that

UPDATE events SET payload = payload || '{"v":2}';
-- 50 million rows in one transaction

CALL backfill_events(batch => 10000);
-- commits every 10,000 rows

CREATE FUNCTION nightly_archive() ...
  COMMIT;  -- inside a function

CREATE PROCEDURE nightly_archive() ...
  COMMIT;  -- procedures may commit

PitfallsCommon mistakes

• Calling a batching procedure from a client library that wraps every statement in BEGIN: transaction control then fails. Use autocommit for the CALL.
• Not making batches idempotent: if the job dies halfway, the rerun must skip work already committed (the NOT EXISTS above).
• Using OFFSET to page through batches, which rescans everything already processed. Use keyset ranges on the primary key.

LimitsWhen not to use it

If the work must be all-or-nothing, a procedure that commits partway is the wrong tool: use a single transaction (or a staging table plus a final swap). Also prefer a function when you need the result inside a query.

SecuritySecurity notes

Procedures can be SECURITY DEFINER like functions, letting a low-privilege role run maintenance it could not run directly. Always pin SET search_path on such procedures and grant EXECUTE only to the roles that need it; by default PUBLIC can execute new functions and procedures.

ChallengeTry it yourself

BEGIN;
CREATE PROCEDURE restock(p_category text, p_qty int, INOUT changed int DEFAULT NULL)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE products SET stock = stock + p_qty WHERE category = p_category AND active;
  GET DIAGNOSTICS changed = ROW_COUNT;
END $$;
CALL restock('toys', 10);
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• PL/pgSQL
• Function volatility
• Triggers