Safely Dropping an Extension with Dependent Objects

A focused procedure for dropping a PostgreSQL extension when views, indexes, or typed columns still depend on it — reassigning or migrating each dependent first so the final DROP carries no surprise CASCADE.

Up: Extension Deprecation & Removal — this page is the concrete drop-with-dependents recipe for that removal pipeline, under the PostgreSQL Extension Architecture & Lifecycle Fundamentals area of the site.

Context & When This Applies

This applies the moment DROP EXTENSION refuses to run because real objects depend on it — the common, healthy case for any extension that has been in production long enough to be used. You have a small number of dependents (a reporting view calling an extension function, an index on an extension operator class, a column typed as an extension type) and you need to remove the extension without either the blunt CASCADE that destroys them or the manual guesswork that misses one. It applies to PostgreSQL 12–17 and is the hands-on complement to the planning discipline in Extension Deprecation & Removal.

The technique is to detach each dependent from the extension — rewrite it to not use the extension, or drop it deliberately — until zero external dependents remain, then drop the extension plainly. A plain DROP EXTENSION that succeeds is proof the blast radius is empty; that proof is the whole goal.

Detaching each dependent kind before a plain DROP EXTENSION Three kinds of dependent are detached from an extension before removal. A view that calls an extension function is recreated to not use it, or dropped. An index built on an extension operator class is dropped or rebuilt on a core operator class. A column typed as an extension type is migrated to a core type with ALTER TABLE. Once all three kinds are detached, no external dependent remains and a plain DROP EXTENSION, without CASCADE, succeeds — which itself proves the blast radius is empty. View → extension function recreate without it, or drop Index → extension opclass rebuild on core opclass Column → extension type migrate to a core type Detach all 0 external deps DROP EXTENSION no CASCADE, succeeds

Concept: Detach Until the Plain Drop Succeeds

DROP EXTENSION without CASCADE is a test as much as a command: it succeeds only when no external object depends on the extension, and otherwise fails naming the first blocker. That property makes it the perfect terminal check for a detach loop. Each external dependent falls into one of three kinds, and each has a specific detach action. A view or function that calls an extension routine is recreated to use a core equivalent, or dropped if obsolete. An index built on an extension operator class is dropped or rebuilt on a core opclass. A column typed as an extension type is migrated with ALTER TABLE ... ALTER COLUMN ... TYPE to a core type, casting the data across.

The reason to detach rather than CASCADE is intentionality: after a detach loop, every object that was going to be affected has been individually decided and, where needed, preserved in a new form. A CASCADE makes those same removals but without the decisions, so an object you needed is gone before you knew it mattered. Detaching in dependency order — dependents before the objects they reference — reuses the ordering from Dependency Tree Analysis, and the whole loop should sit behind a restore point per PITR restore vs schema-level rollback.

Runnable Implementation

The script detaches dependents by kind, then attempts the plain drop; if the drop still fails, it surfaces the remaining blocker rather than escalating to CASCADE.

#!/usr/bin/env python3
"""Detach an extension's dependents by kind, then drop it without CASCADE."""
import psycopg2
from psycopg2 import errors

# Per-dependent detach SQL, decided during classification. Ordered dependents
# first so nothing references an object still to be handled.
DETACH_STEPS = [
    # kind: view calling an extension function -> recreate without it
    "CREATE OR REPLACE VIEW legacy_report AS SELECT id, name FROM events;",
    # kind: column typed as an extension type -> migrate to a core type
    "ALTER TABLE events ALTER COLUMN tags TYPE text USING tags::text;",
    # kind: index on an extension opclass -> rebuild on a core opclass
    "DROP INDEX IF EXISTS idx_events_tags_gin;",
]


def drop_with_dependents(dsn: str, extname: str) -> dict:
    conn = psycopg2.connect(dsn)
    conn.autocommit = False
    try:
        with conn.cursor() as cur:
            for stmt in DETACH_STEPS:
                cur.execute(stmt)
            # Plain drop: succeeds ONLY if zero external dependents remain.
            cur.execute(f'DROP EXTENSION "{extname}";')
        conn.commit()
        return {"status": "REMOVED", "extension": extname}
    except errors.DependentObjectsStillExist as exc:
        conn.rollback()
        # A dependent slipped past classification — surface it, do NOT CASCADE.
        return {"status": "BLOCKED", "extension": extname,
                "remaining_dependent": str(exc).strip().splitlines()[0]}
    except Exception as exc:                       # noqa: BLE001 — CI boundary
        conn.rollback()
        return {"status": "ERROR", "error": str(exc).strip()}
    finally:
        conn.close()


if __name__ == "__main__":
    import json
    print(json.dumps(drop_with_dependents("dbname=appdb", "deprecated_ext"), indent=2))

The transaction wrapping means a blocked drop rolls back every detach too, so a partial removal never persists — the transactional-safety guarantee owned by ALTER EXTENSION automation. Take a snapshot before running it via Snapshot & Point-in-Time Recovery.

Expected Output & Verification

A complete detach yields a clean removal:

{"status": "REMOVED", "extension": "deprecated_ext"}

Verify the extension is truly gone and left no orphaned objects behind:

-- The extension must no longer exist...
SELECT count(*) AS still_present FROM pg_extension WHERE extname = 'deprecated_ext';

-- ...and nothing should still claim a dependency on it.
SELECT count(*) AS dangling
FROM   pg_depend d
JOIN   pg_extension e ON e.oid = d.refobjid
WHERE  e.extname = 'deprecated_ext';

Both counts must be zero. If the script returns BLOCKED, the remaining_dependent names exactly what to add to the detach steps — add it, re-classify per Extension Deprecation & Removal, and re-run. Record the final detach set under Version Control & Branching.

Edge Cases & Gotchas

A migrated column type rewrites the whole table. ALTER COLUMN ... TYPE from an extension type to a core type rewrites every row and holds an AccessExclusiveLock for the duration:

ALTER TABLE events ALTER COLUMN tags TYPE text USING tags::text;
-- on a large table this can hold the lock for minutes

Size that lock window like any rewrite; the migration, not the drop, is the expensive step. If there is no valid cast from the extension type to a core type, stage an explicit USING expression or a two-column swap rather than forcing it.

An index on an extension opclass has no core equivalent by default. Dropping a GIN/GiST index that used an extension operator class removes the acceleration; if the query pattern still needs it, rebuild on a core opclass before dropping the extension, or accept the sequential scans. Never leave an INVALID index behind from an interrupted rebuild.

A dependent in another database is invisible here. The enumeration is per-database, so an extension used in two databases must be detached and dropped in each. A plain drop succeeding in one database says nothing about the others — sweep them all, per Extension Registry Mapping.

Never “fix” a BLOCKED result by switching to CASCADE. The BLOCKED status is the safety mechanism working — it caught a dependent the plan missed. Escalating to CASCADE throws away that catch and destroys the unhandled object. Add the named dependent to the detach steps instead, which is the entire point of the loop.