Writing Idempotent CREATE EXTENSION Guards

How to write extension-install statements that are safe to run any number of times — using CREATE EXTENSION IF NOT EXISTS, version-aware guards, and schema pinning — so a retried migration converges instead of erroring with extension already exists.

Up: Idempotent Migration Orchestration — this page is the concrete guard syntax for that orchestration, under the Automated Execution & Rollback Workflows area of the site.

Context & When This Applies

Every automated pipeline eventually re-runs a migration — a retried job, a re-applied desired-state manifest, a re-triggered deploy. A bare CREATE EXTENSION postgis errors on the second run with extension "postgis" already exists, turning a benign retry into a failed pipeline. An idempotent install statement makes the second (and hundredth) run a clean no-op. This page applies to PostgreSQL 12–17 and covers the three facets that make an install truly idempotent: existence, version, and schema. It is the smallest, most reused building block of the convergence loop in Idempotent Migration Orchestration.

The subtlety is that IF NOT EXISTS alone guarantees existence idempotency but not version or schema idempotency — a common trap that leaves a pipeline believing it converged when the installed version or schema is wrong. Getting all three right is what makes the guard trustworthy under repeated execution.

The three facets of an idempotent CREATE EXTENSION guard A truly idempotent extension install needs three facets. Existence idempotency comes from CREATE EXTENSION IF NOT EXISTS, which makes a re-run a no-op instead of a duplicate-object error. Version idempotency comes from checking the installed version against the desired version and issuing an ALTER EXTENSION UPDATE only when they differ. Schema idempotency comes from an explicit SCHEMA clause so the extension always lands in the intended schema. Only when all three hold does re-running the install converge to the same state every time. Existence IF NOT EXISTS Version check, then UPDATE if differs Schema explicit SCHEMA clause All three → converges Re-run = no-op same state always

Concept: Existence, Version, and Schema Are Three Separate Guards

CREATE EXTENSION IF NOT EXISTS handles exactly one thing: it skips the create when the extension already exists, so a re-run does not raise duplicate_object. But it says nothing about which version is installed — if the desired version moved, IF NOT EXISTS happily leaves the old one in place and the pipeline wrongly believes it converged. Version idempotency is a separate guard: read pg_extension.extversion, and issue ALTER EXTENSION ... UPDATE TO only when it differs from the target. Likewise, IF NOT EXISTS does not correct a schema: an extension already installed in the wrong schema stays there. Schema idempotency comes from an explicit SCHEMA clause at install time (and accepting that relocatable extensions can be moved later, non-relocatable ones cannot).

The reason all three matter is that the orchestrator’s convergence check asks “is the observed state equal to the desired state?” — and desired state is a tuple of existence, version, and schema. A guard that satisfies only existence lets the loop terminate on a partial match, shipping the wrong version or schema. Composing the three guards is what makes the install a faithful building block of the convergence loop, and it pairs with the pinned desired state from pinning extension versions in deployment manifests.

Runnable Implementation

The function composes all three guards: it installs into the pinned schema if absent, updates the version if it differs, and is a clean no-op when the full tuple already matches.

#!/usr/bin/env python3
"""An idempotent extension install: existence + version + schema."""
import psycopg2


def ensure_extension(dsn: str, name: str, version: str, schema: str) -> dict:
    conn = psycopg2.connect(dsn)
    conn.autocommit = True                      # some updates disallow txn blocks
    try:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT e.extversion, n.nspname "
                "FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace "
                "WHERE e.extname = %s", (name,))
            row = cur.fetchone()

            if row is None:
                # Existence + schema guard: create in the pinned schema.
                cur.execute(
                    f'CREATE EXTENSION IF NOT EXISTS "{name}" '
                    f'WITH SCHEMA "{schema}" VERSION %s', (version,))
                return {"action": "installed", "to": version, "schema": schema}

            installed_version, installed_schema = row
            actions = []
            # Version guard: update only if the installed version differs.
            if installed_version != version:
                cur.execute(f'ALTER EXTENSION "{name}" UPDATE TO %s', (version,))
                actions.append(f"updated {installed_version}->{version}")
            # Schema guard: relocate only if relocatable and misplaced.
            if installed_schema != schema:
                cur.execute(f'ALTER EXTENSION "{name}" SET SCHEMA "{schema}"')
                actions.append(f"moved {installed_schema}->{schema}")

            return {"action": actions or "noop",
                    "version": version, "schema": schema}
    finally:
        conn.close()


if __name__ == "__main__":
    import json
    print(json.dumps(ensure_extension("dbname=appdb", "pg_trgm", "1.6", "ext"),
                     indent=2))

Because the whole function is guarded on observed state, calling it repeatedly converges — the property the orchestrator relies on to make retries safe. Keep a restore point via Snapshot & Point-in-Time Recovery before the first run in case a version update has a non-transactional step.

Expected Output & Verification

A first run installs; a second, identical run is a no-op:

{"action": "installed", "to": "1.6", "schema": "ext"}
{"action": "noop", "version": "1.6", "schema": "ext"}

Verify true convergence by asserting the full tuple, not just existence:

-- Existence, version, AND schema must all match the desired state.
SELECT e.extname, e.extversion, n.nspname AS schema
FROM   pg_extension e
JOIN   pg_namespace n ON n.oid = e.extnamespace
WHERE  e.extname = 'pg_trgm';

All three columns must equal the desired values. A matching name with a stale extversion is exactly the partial-convergence trap an existence-only guard leaves behind — this check catches it. Record the converged tuple the way version control and branching records deploy state.

Edge Cases & Gotchas

IF NOT EXISTS silently ignores a version mismatch. This is the headline trap: CREATE EXTENSION IF NOT EXISTS postgis VERSION '3.4.2' on a node already running 3.4.1 does nothing and raises no error — it does not upgrade. The version guard (ALTER EXTENSION ... UPDATE) is mandatory; never assume the VERSION clause on IF NOT EXISTS enforces the version.

SET SCHEMA fails on a non-relocatable extension. The schema guard’s ALTER EXTENSION ... SET SCHEMA raises 0A000 on an extension whose control file sets relocatable = false:

ERROR:  extension "postgis" does not support SET SCHEMA

For non-relocatable extensions, get the schema right at install time and treat a schema mismatch as a rebuild decision per resolving 0A000 feature-not-supported errors, not a runtime move.

A concurrent runner can race an unguarded create. Two pipelines both seeing “not installed” can both attempt the create; IF NOT EXISTS makes the loser a no-op rather than an error, but the version/schema ALTERs are not similarly guarded. Serialize with the advisory lock from idempotent migration orchestration so the two steps do not interleave.

A version update may still be non-transactional. The version guard issues a real ALTER EXTENSION UPDATE, which for extensions like TimescaleDB has immediate-commit steps. Idempotency makes the guard safe to re-run, but it cannot undo such a step — pair it with a snapshot, exactly as the orchestration guidance requires.