Wrapping ALTER EXTENSION UPDATE in Transactional Savepoints

How to bracket the transactional portion of an ALTER EXTENSION UPDATE in a savepoint so a mid-update failure rolls back to a clean point without aborting the whole surrounding transaction — and how to know which parts a savepoint cannot protect.

Up: ALTER EXTENSION Automation — this page is the savepoint-scoping technique behind that automation’s transactional boundary, under the Automated Execution & Rollback Workflows area of the site.

Context & When This Applies

A savepoint is a named marker inside a transaction you can roll back to without discarding everything before it. For extension automation, savepoints let a pipeline attempt an update, catch a failure, and ROLLBACK TO SAVEPOINT to retry a variant or fall through cleanly — all without losing the pre-update work already staged in the same transaction. This applies to PostgreSQL 12–17 for the transactional part of an update: ordinary catalog DDL, function redefinitions, type changes. It does not extend the transaction to cover non-transactional steps (background-worker registration, shared-memory allocation), and knowing that boundary is the whole point.

Use savepoints when your automation runs several related statements in one transaction and wants fine-grained recovery — for example staging a dependent-object migration and the extension update together, where a failure in the update should undo just the update and let the automation decide what to do next, rather than forcing a full transaction abort.

A savepoint bracketing the transactional part of an extension update Inside one transaction, the automation stages prior work, then sets a savepoint before ALTER EXTENSION UPDATE. If the update's transactional DDL fails, ROLLBACK TO SAVEPOINT undoes just the update and preserves the staged work, letting the automation retry or fall through. If it succeeds, RELEASE SAVEPOINT keeps the change. A non-transactional step inside the update — a background-worker registration or shared-memory allocation — commits immediately and escapes the savepoint, so it cannot be undone by ROLLBACK TO SAVEPOINT and needs a snapshot instead. BEGIN stage prior work SAVEPOINT sp mark clean point ALTER EXTENSION transactional DDL RELEASE sp keep the change ROLLBACK TO sp undo just the update non-txn escapes needs a snapshot

Concept: Savepoints Cover DDL, Not Immediate-Commit Side Effects

A savepoint scopes rollback within a transaction, and it protects exactly what the transaction protects: ordinary catalog DDL. When ALTER EXTENSION UPDATE runs functions, redefines types, or rewrites pg_proc/pg_class rows, those changes are transactional, so a ROLLBACK TO SAVEPOINT set just before the update cleanly undoes them and leaves everything staged earlier in the transaction intact. That is what makes savepoints useful for automation: the update becomes a retryable sub-step, not an all-or-nothing gamble on the whole transaction.

The critical limit is that some update steps commit immediately regardless of the surrounding transaction — registering a background worker, allocating shared memory, touching cluster-global state. These are not part of your transaction, so no savepoint (and no ROLLBACK) can undo them. If an update performs such a step and then a later statement fails, ROLLBACK TO SAVEPOINT reverses the DDL but the immediate-commit side effect persists, leaving the catalog and shared state disagreeing. This is exactly the class async upgrade simulation surfaces, and it is why a savepoint is a complement to — never a replacement for — the snapshot from Snapshot & Point-in-Time Recovery.

Runnable Implementation

The function stages prior work, sets a savepoint, attempts the update, and rolls back just the update on a transactional failure — keeping the staged work and letting the caller decide next steps.

#!/usr/bin/env python3
"""Bracket the transactional part of an extension update in a savepoint."""
import psycopg2
from psycopg2 import errors


def update_with_savepoint(dsn: str, name: str, target: str,
                          stage_sql: str) -> dict:
    conn = psycopg2.connect(dsn)
    conn.autocommit = False                     # we manage the transaction
    try:
        with conn.cursor() as cur:
            cur.execute(stage_sql)              # prior work in this transaction
            cur.execute("SAVEPOINT before_update;")
            try:
                cur.execute(f'ALTER EXTENSION "{name}" UPDATE TO %s', (target,))
                cur.execute("RELEASE SAVEPOINT before_update;")
                conn.commit()
                return {"status": "updated", "to": target}
            except errors.InvalidFunctionDefinition as exc:
                # Transactional failure (e.g. 42P13): undo ONLY the update,
                # keep the staged work, and let the caller re-plan.
                cur.execute("ROLLBACK TO SAVEPOINT before_update;")
                conn.commit()                   # commit the staged work alone
                return {"status": "update_rolled_back",
                        "staged_kept": True, "error": str(exc).strip()}
    finally:
        conn.close()


if __name__ == "__main__":
    import json
    print(json.dumps(update_with_savepoint(
        "dbname=appdb", "hstore", "1.8",
        stage_sql="CREATE TABLE IF NOT EXISTS migration_audit(id serial);"),
        indent=2))

Only use this pattern for extensions whose update is fully transactional; for one with immediate-commit steps, take a snapshot first and treat the savepoint as covering the DDL portion only, per ALTER EXTENSION Automation.

Expected Output & Verification

A transactional failure rolls back the update while preserving the staged work:

{
  "status": "update_rolled_back",
  "staged_kept": true,
  "error": "cannot change return type of existing function"
}

Verify that the rollback was scoped: the extension version is unchanged, but the staged object still exists.

-- The update was undone...
SELECT extversion FROM pg_extension WHERE extname = 'hstore';
-- ...but the staged work committed independently.
SELECT to_regclass('public.migration_audit') IS NOT NULL AS staged_present;

staged_present should be true while the version is the pre-update value — proof the savepoint scoped the rollback to the update alone. Record the outcome for the error categorization framework so a rolled-back transactional failure is triaged, not retried blindly.

Edge Cases & Gotchas

A failed statement invalidates the transaction until you roll back to the savepoint. After any error, the transaction enters an aborted state and rejects further statements with:

ERROR:  current transaction is aborted, commands ignored until end of transaction block

You must ROLLBACK TO SAVEPOINT (or ROLLBACK) before issuing anything else. The pattern above does this immediately in the except block; omitting it strands the connection.

A non-transactional update step defeats the savepoint silently. If the update registered a background worker before failing, ROLLBACK TO SAVEPOINT undoes the DDL but not the worker. There is no error — the mismatch is silent. Confirm the extension is fully transactional in async upgrade simulation before relying on savepoint rollback, and keep a snapshot for the ones that are not.

Savepoints do not survive a COMMIT. Releasing or committing ends the savepoint’s scope; you cannot roll back to it afterward. Savepoints are an intra-transaction tool. For post-commit recovery, the mechanism is PITR restore vs schema-level rollback, not a savepoint.

Deeply nested savepoints add overhead and confusion. Each savepoint holds resources until released; a long chain of them in one transaction bloats the subtransaction stack. Keep the bracket tight — one savepoint around the update — rather than nesting a savepoint per statement.