PostgreSQL Extensions — CREATE EXTENSION, Control Files, and Versioned Scripts

Contents:

• Theoretical Background
• Common DBMS Design
• PostgreSQL’s Approach
• Source Walkthrough
• Source verification (as of 2026-06-05)
• Beyond PostgreSQL — Comparative Designs & Research Frontiers
• Sources

Theoretical Background

A relational database is, at heart, a collection of typed objects — tables, functions, operators, types, casts, indexes — created by CREATE statements and tracked in system catalogs. The extension abstraction answers a packaging question that the bare CREATE machinery does not: how do you treat a group of related objects as one installable, versioned, droppable unit?

Without extensions, a feature like PostGIS or hstore is just a pile of loose catalog entries that some installation script happened to create. Nothing in the catalog records that those entries belong together, that they came from version 3.2 of a particular package, or that dropping one of them should be refused because the others depend on it. pg_dump cannot reproduce them as a unit — it must dump every individual object’s DDL, which is fragile across server versions and bloats the dump.

The extension concept, introduced in PostgreSQL 9.1, is the answer to three distinct sub-problems that recur in every extensible system:

1. Grouping / membership. Some catalog fact must record that object X is a member of package P. PostgreSQL records this as a pg_depend edge of type DEPENDENCY_EXTENSION (e) from each member object to the owning pg_extension row. Membership is what makes DROP EXTENSION cascade and what lets pg_dump emit a single CREATE EXTENSION line instead of hundreds of CREATE statements.

2. Versioning and upgrade paths. A package evolves. Installing version 1.0 today and version 1.2 tomorrow must produce the same catalog state as installing 1.2 directly. This is the classic schema-migration problem, and PostgreSQL solves it the way migration frameworks do: a directory of delta scripts (P--1.0--1.1.sql, P--1.1--1.2.sql) plus a path-finding step that composes the right sequence of deltas.

3. Provenance and reproducibility. A dump-and-restore (or pg_upgrade) must recreate the package by name and version, not by replaying its internal DDL. The receiving server may have a newer copy of the package’s scripts; the dump should say “install version 1.2 of hstore” and let the local script files do the work.

Database System Concepts frames a DBMS’s data-definition layer as the manager of the data dictionary (the catalogs): every schema object is a dictionary entry, and integrity constraints among entries (foreign keys, dependencies) are themselves dictionary facts. The POSTGRES project papers (Stonebraker & Rowe 1986, “The Design of POSTGRES”; Stonebraker & Kemnitz 1991, “The POSTGRES Next-Generation DBMS”) make extensibility — user-defined types, operators, access methods, and procedural languages — a first-class design goal rather than an afterthought. The modern extension mechanism is the packaging layer that finally let that extensibility ship as installable products: a C shared library plus an SQL glue script, bound together and versioned by the catalog.

The design space an extension implementer chooses within:

1. Where does membership live? In a dedicated “package” catalog with a foreign key from every object, or reusing the generic dependency graph? PostgreSQL reuses pg_depend, which means the extension code itself is tiny — most of DROP EXTENSION is handled by the existing dependency walker.

2. How are versions composed? A linear chain of migrations, or an arbitrary graph with shortest-path selection? PostgreSQL chose a graph: any from--to script is an edge, and the engine runs Dijkstra’s algorithm to find the cheapest install or upgrade route.

3. Who may install, and into which schema? A superuser-only model is simplest but blocks managed-cloud tenants. PostgreSQL layers two relaxations on top: relocatable extensions (the DBA chooses the schema) and trusted extensions (a non-superuser with database CREATE privilege may install a curated subset).

Common DBMS Design

Packaging, versioning, and dependency tracking of database objects converge on a recognizable set of engineering conventions across systems. Naming them first makes PostgreSQL’s specific symbols read as one set of choices within a shared playbook.

A package descriptor parsed before any object is created

Every package system reads a small manifest before it touches the catalog: it needs the package name, default version, and policy flags (who may install, into what schema) up front so it can reject the operation cheaply. The manifest is typically a key/value text file. The universal pattern is parse manifest -> validate policy -> plan work -> execute, with the manifest parse being side-effect-free.

Versioned delta scripts plus a path planner

Schema-migration tooling (Flyway, Rails migrations, Alembic) and database package managers all converge on the same shape: a directory of scripts named by the version transition they perform, and a planner that, given a “from” and “to” version, selects and orders the scripts to run. A linear naming convention (V1__, V2__) implies a chain; a from--to convention implies a graph and therefore a shortest-path search. The graph form is strictly more general — it lets a package author ship a direct 1.0--1.3 “fast-forward” script alongside the incremental steps.

Membership recorded in the dependency graph

Rather than invent a separate “what’s in this package” table, mature systems reuse the object-dependency graph that already exists to enforce DROP ... RESTRICT/CASCADE. A package becomes just another node; member objects get an edge to it. The drop logic, the dump-ordering logic, and the “cannot drop, others depend on it” error all fall out of the existing graph walker for free.

Privilege gating with a controlled escalation hatch

A package install runs arbitrary DDL, so the naive rule is “superuser only.” But multi-tenant deployments need non-superusers to install vetted packages. The common refinement is a per-package “safe to run as a privileged role on behalf of an unprivileged caller” flag, combined with a transient, automatically-reverted privilege switch for the duration of the script.

flowchart TD
  A["CREATE EXTENSION foo"] --> B["read control file<br/>foo.control (manifest)"]
  B --> C["choose version<br/>(explicit or default_version)"]
  C --> D{"direct install<br/>script exists?"}
  D -->|yes| E["run foo--ver.sql"]
  D -->|no| F["build version graph<br/>+ Dijkstra shortest path"]
  F --> G["run install script then<br/>each upgrade delta in order"]
  E --> H["insert pg_extension row"]
  G --> H
  H --> I["scripts create objects with<br/>creating_extension = true"]
  I --> J["each object auto-records<br/>DEPENDENCY_EXTENSION edge in pg_depend"]

Figure 1 — The shared package-install pipeline, instantiated with PostgreSQL’s symbol names. The manifest is the .control file; the path planner is Dijkstra over the from--to script graph; membership is the DEPENDENCY_EXTENSION edge.

PostgreSQL’s Approach

PostgreSQL implements the entire mechanism in one file, src/backend/commands/extension.c, and one catalog, pg_extension. The opening comment states the minimalist design intent:

// extension.c header comment — src/backend/commands/extension.c
// All we need internally to manage an extension is an OID so that the
// dependent objects can be associated with it.  An extension is created by
// populating the pg_extension catalog from a "control" file.
// The extension control file is parsed with the same parser we use for
// postgresql.conf.  An extension also has an installation script file,
// containing SQL commands to create the extension's objects.

That is the whole idea: an extension is an OID plus a catalog row, and everything else (membership, drop cascade, dump) is delegated to the generic dependency machinery described in postgres-dependency-tracking.md and the DDL executor in postgres-ddl-execution.md.

The pg_extension catalog row

The catalog is deliberately thin — six fixed columns plus two variable-length arrays:

// FormData_pg_extension — src/include/catalog/pg_extension.h
CATALOG(pg_extension,3079,ExtensionRelationId)
{
    Oid         oid;
    NameData    extname;        /* extension name */
    Oid         extowner BKI_LOOKUP(pg_authid);
    Oid         extnamespace BKI_LOOKUP(pg_namespace); /* namespace of
                                                        * contained objects */
    bool        extrelocatable; /* if true, allow ALTER EXTENSION SET SCHEMA */
#ifdef CATALOG_VARLEN
    text        extversion BKI_FORCE_NOT_NULL;  /* extension version name */
    Oid         extconfig[1] BKI_LOOKUP(pg_class);   /* dumpable config tables */
    text        extcondition[1];                     /* WHERE clauses for them */
#endif
} FormData_pg_extension;

Note what is not here: there is no list of member objects. Membership lives entirely in pg_depend. The extversion column is the single source of truth for “what version is installed”; extconfig/extcondition support the pg_extension_config_dump() feature (config tables whose data, filtered by a WHERE clause, should be dumped even though the table itself belongs to the extension). Two unique indexes back the OID and name syscaches (EXTENSIONOID, EXTENSIONNAME).

Control file: the manifest

The control file extname.control is parsed by ParseConfigFp — the same GUC/postgresql.conf parser — into an ExtensionControlFile struct. The recognized keys map one-to-one onto struct fields:

// ExtensionControlFile — src/backend/commands/extension.c
typedef struct ExtensionControlFile
{
    char       *name;
    char       *directory;          /* directory for script files */
    char       *default_version;    /* default install target version */
    char       *module_pathname;    /* substituted for MODULE_PATHNAME */
    char       *comment;
    char       *schema;             /* target schema (allowed if !relocatable) */
    bool        relocatable;        /* is ALTER EXTENSION SET SCHEMA supported? */
    bool        superuser;          /* must be superuser to install? */
    bool        trusted;            /* allow becoming superuser on the fly? */
    int         encoding;           /* encoding of the script file, or -1 */
    List       *requires;           /* names of prerequisite extensions */
    List       *no_relocate;        /* prerequisites that must not be relocated */
    /* ... control_dir, basedir elided ... */
} ExtensionControlFile;

new_ExtensionControlFile sets the defaults that apply when a key is absent — these defaults are part of the contract extension authors rely on:

// new_ExtensionControlFile — src/backend/commands/extension.c
control->name = pstrdup(extname);
control->relocatable = false;
control->superuser = true;      /* default: superuser-only */
control->trusted = false;       /* default: not trusted */
control->encoding = -1;         /* default: database encoding */

A primary control file (foo.control) may set any key. A secondary or auxiliary control file (foo--1.2.control) carries per-version overrides but is forbidden from setting directory or default_version, because those are global to the extension, not per-version. parse_extension_control_file enforces this and also enforces the relocatable/schema mutual exclusion:

// parse_extension_control_file — src/backend/commands/extension.c
if (control->relocatable && control->schema != NULL)
    ereport(ERROR,
            (errcode(ERRCODE_SYNTAX_ERROR),
             errmsg("parameter \"schema\" cannot be specified when \"relocatable\" is true")));

File layout and the version graph

Script files live next to the control file (or under its directory=) and are named by the version transition they perform. PostgreSQL discovers the version graph by listing the directory and parsing filenames — there is no index file. get_extension_script_filename shows the two naming forms:

// get_extension_script_filename — src/backend/commands/extension.c
if (from_version)
    snprintf(result, MAXPGPATH, "%s/%s--%s--%s.sql",
             scriptdir, control->name, from_version, version);   /* upgrade delta */
else
    snprintf(result, MAXPGPATH, "%s/%s--%s.sql",
             scriptdir, control->name, version);                 /* install script */

So hstore--1.4.sql is an install script (a graph vertex marked installable) and hstore--1.3--1.4.sql is an upgrade edge from 1.3 to 1.4. This double-dash convention is exactly why extension and version names are forbidden from containing -- (see check_valid_extension_name / check_valid_version_name) — it would make the filename grammar ambiguous.

flowchart LR
  subgraph installs["installable versions"]
    v10["1.0"]
    v12["1.2"]
  end
  v10 -->|"foo--1.0--1.1.sql"| v11["1.1"]
  v11 -->|"foo--1.1--1.2.sql"| v12
  v12 -->|"foo--1.2--1.3.sql"| v13["1.3"]
  v10 -->|"foo--1.0--1.3.sql<br/>(fast-forward edge)"| v13
  classDef tgt fill:#d5f5d5,stroke:#2a2;
  class v13 tgt;

Figure 2 — A version graph. get_ext_ver_list builds this from the directory listing; find_install_path runs Dijkstra from every installable vertex to the target (1.3), choosing the cheapest route. If the fast-forward 1.0--1.3 edge exists, the two-hop direct path beats the three-hop incremental chain.

CREATE EXTENSION: plan then execute

CreateExtension is a thin wrapper: it checks the name, rejects a duplicate (or honors IF NOT EXISTS), forbids nesting (only one extension may be under construction at a time, tracked by the global creating_extension flag), deconstructs the SCHEMA/VERSION/CASCADE options, and hands off to CreateExtensionInternal. The internal worker is where the planning happens. It first reads the primary control file and resolves the target version:

// CreateExtensionInternal — src/backend/commands/extension.c
pcontrol = read_extension_control_file(extensionName);

if (versionName == NULL)
{
    if (pcontrol->default_version)
        versionName = pcontrol->default_version;
    else
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                 errmsg("version to install must be specified")));
}
check_valid_version_name(versionName);

It then asks: is there a direct install script for the target version? If stat() finds foo--<target>.sql, no path-finding is needed. Otherwise it builds the version graph and runs find_install_path to compute a starting installable version plus a sequence of upgrade deltas:

// CreateExtensionInternal — src/backend/commands/extension.c
filename = get_extension_script_filename(pcontrol, NULL, versionName);
if (stat(filename, &fst) == 0)
{
    updateVersions = NIL;           /* easy: one install script */
}
else
{
    evi_list = get_ext_ver_list(pcontrol);
    evi_target = get_ext_ver_info(versionName, &evi_list);
    evi_start = find_install_path(evi_list, evi_target, &updateVersions);
    if (evi_start == NULL)
        ereport(ERROR, ... "has no installation script nor update path" ...);
    versionName = evi_start->name;  /* install this first, then upgrade */
}

Only after the plan is fixed does it touch the catalog: it resolves the target schema, recursively installs any requires prerequisites (via get_required_extension, which honors CASCADE and detects cyclic dependencies), inserts the pg_extension row with InsertExtensionTuple, runs the base install script, and finally replays the upgrade deltas via ApplyExtensionUpdates. The ordering matters: the catalog row must exist before the script runs, because the script’s CREATE statements need a valid CurrentExtensionObject OID to point their dependency edges at.

Executing a script: the dependency-recording switch

execute_extension_script is where the membership magic happens. Before running the SQL it sets two global variables:

// execute_extension_script — src/backend/commands/extension.c
creating_extension = true;
CurrentExtensionObject = extensionOid;
PG_TRY();
{
    char *c_sql = read_extension_script_file(control, filename);
    /* ... @extschema@ / @extowner@ / MODULE_PATHNAME substitution ... */
    execute_sql_string(c_sql, filename);
}
PG_FINALLY();
{
    creating_extension = false;
    CurrentExtensionObject = InvalidOid;
}
PG_END_TRY();

While creating_extension is true, every object the script creates calls recordDependencyOnCurrentExtension (from the DDL layer), which adds a DEPENDENCY_EXTENSION ('e') edge from the new object to CurrentExtensionObject. This is the single mechanism that makes an object a member of the extension — the extension code never enumerates its members; the members enumerate themselves at creation time. This is covered in depth in postgres-dependency-tracking.md.

The function also sets up a safe execution environment: it forces client_min_messages/log_min_messages to at least WARNING, disables check_function_bodies, and constructs a search_path with the target schema first, then required extensions’ schemas, then pg_temp last — deliberately keeping pg_temp last so a malicious temp object cannot shadow a real one.

Macro substitution: @extschema@, @extowner@, MODULE_PATHNAME

Before the script text is executed it is run through a series of replace_text substitutions. A non-relocatable extension can hard-code its schema via @extschema@; required extensions’ schemas are reachable via @extschema:other_ext@; the owner via @extowner@; and the C library path via MODULE_PATHNAME (so the same script works regardless of where the .so lives). Each substituted value is filtered through quote_identifier, and substitution is rejected if the value contains any quoting-relevant character, to close a SQL-injection hole:

// execute_extension_script — src/backend/commands/extension.c
const char *quoting_relevant_chars = "\"$'\\";
/* ... */
if (!control->relocatable)
{
    const char *qSchemaName = quote_identifier(schemaName);
    t_sql = DirectFunctionCall3Coll(replace_text, C_COLLATION_OID, t_sql,
                                    CStringGetTextDatum("@extschema@"),
                                    CStringGetTextDatum(qSchemaName));
    if (t_sql != old && strpbrk(schemaName, quoting_relevant_chars))
        ereport(ERROR, ... "invalid character in extension \"%s\" schema" ...);
}

Relocatable extensions and ALTER EXTENSION SET SCHEMA

A relocatable extension promises that none of its objects hard-code their schema, so the whole set can be moved to a different schema after install. AlterExtensionNamespace implements ALTER EXTENSION ... SET SCHEMA: it checks ownership, verifies extrelocatable, then scans pg_depend for every object that depends directly on the extension and moves each one’s namespace, finally updating extnamespace on the pg_extension row:

// AlterExtensionNamespace — src/backend/commands/extension.c
/* Check extension is supposed to be relocatable */
if (!extForm->extrelocatable)
    ereport(ERROR,
            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
             errmsg("extension \"%s\" does not support SET SCHEMA",
                    NameStr(extForm->extname))));

The no_relocate control key lets an extension forbid relocation of one of its prerequisites: during the namespace scan, if a dependent extension lists this one in no_relocate, SET SCHEMA is refused. This matters when extension A captures B’s schema name at install time (e.g. via a hard-coded reference) and would break if B later moved.

Trusted extensions: controlled privilege escalation

By default superuser = true, so installing an extension requires superuser. A trusted extension (trusted = true in the control file) relaxes this: a non-superuser who holds CREATE on the current database may install it, and the script runs as the bootstrap superuser for its duration. The policy gate is one small function:

// extension_is_trusted — src/backend/commands/extension.c
static bool
extension_is_trusted(ExtensionControlFile *control)
{
    AclResult   aclresult;
    /* Never trust unless extension's control file says it's okay */
    if (!control->trusted)
        return false;
    /* Allow if user has CREATE privilege on current database */
    aclresult = object_aclcheck(DatabaseRelationId, MyDatabaseId,
                                GetUserId(), ACL_CREATE);
    return (aclresult == ACLCHECK_OK);
}

When the gate passes for a non-superuser, execute_extension_script transiently switches identity to BOOTSTRAP_SUPERUSERID using SetUserIdAndSecContext with SECURITY_LOCAL_USERID_CHANGE. The switch is automatically undone if the transaction aborts, and the GUC changes are rolled back by guc.c. This is the controlled-escalation hatch from the “Common DBMS Design” section, made concrete. The set of in-core trusted extensions (the trusted = true flag in their control files) is curated by the project — anything that could let a tenant escape their privileges is deliberately not marked trusted.

flowchart TD
  A["execute_extension_script"] --> B{"control->superuser<br/>&& !superuser()?"}
  B -->|no| F["run script as caller"]
  B -->|yes| C{"extension_is_trusted?<br/>(trusted flag + DB CREATE priv)"}
  C -->|no| D["ERROR: permission denied"]
  C -->|yes| E["SetUserIdAndSecContext<br/>BOOTSTRAP_SUPERUSERID"]
  E --> G["run script as bootstrap superuser"]
  G --> H["identity + GUCs auto-reverted<br/>at txn end / on abort"]
  F --> H

Figure 3 — The superuser / trusted decision in execute_extension_script. The escalation is local, scoped to the script, and self-reverting.

ALTER EXTENSION UPDATE and DROP EXTENSION

ExecAlterExtensionStmt handles ALTER EXTENSION ... UPDATE. It reads the installed version from extversion, the target from the option list or default_version, then calls identify_update_path to get the delta sequence and ApplyExtensionUpdates to replay it. Each delta is applied as if it were a standalone ALTER EXTENSION UPDATE: the pg_extension row is rewritten with the new version and relocatability, prerequisite dependencies are deleted and recreated, and the from--to script runs. Doing it one delta at a time ensures an old upgrade script never sees control parameters from a newer version.

DROP EXTENSION needs almost no extension-specific code. By the time RemoveExtensionById is reached, the generic dependency walker has already collected every DEPENDENCY_EXTENSION member for cascade deletion; this function only deletes the pg_extension row itself, after guarding against dropping an extension that is currently being built:

// RemoveExtensionById — src/backend/commands/extension.c
if (extId == CurrentExtensionObject)
    ereport(ERROR,
            (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
             errmsg("cannot drop extension \"%s\" because it is being modified",
                    get_extension_name(extId))));

The comment above it states the design directly: “All we need do here is remove the pg_extension tuple itself. Everything else is taken care of by the dependency infrastructure.” That sentence is the whole architectural bet of the extension subsystem.

Where control files are found: extension_control_path

In PostgreSQL 18, control files are located by searching a path rather than only the fixed SHAREDIR/extension directory. The extension_control_path GUC (a PGC_SUSET setting) lists directories; find_in_paths walks them looking for foo.control. An empty GUC falls back to the system SHAREDIR/extension. This lets a packager install an extension’s control/script files outside the core installation tree — useful for immutable system images and per-tenant overlays. Every path component must be absolute:

// find_in_paths — src/backend/commands/extension.c
if (!is_absolute_path(path))
    ereport(ERROR,
            errcode(ERRCODE_INVALID_NAME),
            errmsg("component in parameter \"%s\" is not an absolute path",
                   "extension_control_path"));

Source Walkthrough

Symbols grouped by concern. Files are under /data/hgryoo/references/postgres/. The canonical anchor is the symbol name; line numbers are in the position-hint table at the end of this section and are scoped to the updated: date.

Catalog and lookups (pg_extension.h, extension.c)

• FormData_pg_extension / Form_pg_extension — the catalog row: extname, extowner, extnamespace, extrelocatable, extversion, extconfig[], extcondition[]. No member list — membership is in pg_depend.
• ExtensionRelationId (3079) — the catalog OID; ExtensionOidIndexId / ExtensionNameIndexId back the syscaches.
• EXTENSIONOID / EXTENSIONNAME — MAKE_SYSCACHE declarations used by the lookup helpers.
• get_extension_oid / get_extension_name — name<->OID via syscache.
• get_extension_schema — read extnamespace for an extension OID.
• get_function_sibling_type — given a C function’s OID, find a type in the same extension by name; used by relocatable extensions that cannot assume a fixed schema. Cached in ExtensionSiblingCache, invalidated by ext_sibling_callback.

Control-file parsing (extension.c)

• ExtensionControlFile (struct) — parsed manifest; one field per recognized key plus control_dir / basedir working state.
• new_ExtensionControlFile — allocate and set defaults (superuser = true, relocatable = false, trusted = false, encoding = -1).
• parse_extension_control_file — ParseConfigFp the file, map keys to fields, enforce primary-only keys and the relocatable/schema exclusion.
• read_extension_control_file — parse the primary foo.control.
• read_extension_aux_control_file — flat-copy then overlay a per-version foo--ver.control.
• get_extension_control_directories / find_extension_control_filename / find_in_paths — resolve foo.control across extension_control_path.
• Extension_control_path — the GUC variable (string).

Filename and version-name grammar (extension.c)

• is_extension_control_filename / is_extension_script_filename — suffix tests for .control / .sql.
• check_valid_extension_name / check_valid_version_name — reject empty, --, leading/trailing -, and directory separators.
• get_extension_script_directory — resolve where scripts live (control dir, absolute directory=, or basedir/directory).
• get_extension_script_filename — build name--ver.sql (install) or name--from--to.sql (upgrade).
• get_extension_aux_control_filename — build name--ver.control.

Version graph and path finding (extension.c)

• ExtensionVersionInfo (struct) — a graph vertex: name, reachable edges, installable flag, plus Dijkstra working state (distance, distance_known, previous).
• get_ext_ver_list — ReadDir the script directory, parse each name--...sql filename into vertices and edges.
• get_ext_ver_info — find-or-create a vertex by version name.
• get_nearest_unprocessed_vertex — O(N^2) min-distance pick for Dijkstra.
• find_update_path — Dijkstra shortest path between two versions; deterministic tie-break by strcmp.
• identify_update_path — wrapper that errors if no path exists.
• find_install_path — consider all installable vertices as start points, return the cheapest route to a non-installable target.

CREATE / ALTER / DROP drivers (extension.c)

• CreateExtension — parse statement options, dedupe, forbid nesting, dispatch to the internal worker.
• CreateExtensionInternal — the planner+executor: resolve version, plan scripts, resolve schema, install requires, insert row, run scripts. Recurses for CASCADE.
• get_required_extension — resolve/auto-install a prerequisite; detect cyclic dependency via the parents list.
• InsertExtensionTuple — build and insert the pg_extension row, record owner/schema/prerequisite dependencies, fire the post-create hook. Exported for pg_upgrade.
• ExecAlterExtensionStmt — ALTER EXTENSION UPDATE: read installed version, compute path, call ApplyExtensionUpdates.
• ApplyExtensionUpdates — replay each delta: rewrite the row, refresh prerequisite deps, run the from--to script.
• AlterExtensionNamespace — SET SCHEMA: verify relocatable, move each member object, honor no_relocate, update extnamespace.
• ExecAlterExtensionContentsStmt / ExecAlterExtensionContentsRecurse — ALTER EXTENSION ADD/DROP member.
• RemoveExtensionById — delete the pg_extension row (the dependency machinery handles the rest).
• extension_config_remove — drop an OID from the extconfig array (pg_extension_config_dump bookkeeping).

Script execution and policy (extension.c)

• execute_extension_script — set creating_extension / CurrentExtensionObject, enforce superuser/trusted policy, set up search_path and GUCs, substitute macros, run SQL, reset globals.
• execute_sql_string — parse and execute the (post-substitution) script text statement by statement.
• extension_is_trusted — policy gate: trusted flag plus database CREATE privilege.
• read_extension_script_file / read_whole_file — slurp the .sql file, validate and convert encoding.
• creating_extension / CurrentExtensionObject — the two globals the dependency layer reads to attribute new objects to the extension.

Position hints (as of 2026-06-05, REL_18 273fe94):

Symbol	File	Line
FormData_pg_extension	src/include/catalog/pg_extension.h	28
Extension_control_path (GUC var)	src/backend/commands/extension.c	76
creating_extension / CurrentExtensionObject	src/backend/commands/extension.c	79
ExtensionControlFile (struct)	src/backend/commands/extension.c	85
ExtensionVersionInfo (struct)	src/backend/commands/extension.c	109
get_extension_oid	src/backend/commands/extension.c	187
get_extension_schema	src/backend/commands/extension.c	232
get_function_sibling_type	src/backend/commands/extension.c	272
check_valid_extension_name	src/backend/commands/extension.c	360
check_valid_version_name	src/backend/commands/extension.c	407
is_extension_control_filename	src/backend/commands/extension.c	454
is_extension_script_filename	src/backend/commands/extension.c	462
get_extension_control_directories	src/backend/commands/extension.c	473
find_extension_control_filename	src/backend/commands/extension.c	541
get_extension_script_directory	src/backend/commands/extension.c	567
get_extension_aux_control_filename	src/backend/commands/extension.c	586
get_extension_script_filename	src/backend/commands/extension.c	604
parse_extension_control_file	src/backend/commands/extension.c	641
read_extension_control_file	src/backend/commands/extension.c	829
read_extension_aux_control_file	src/backend/commands/extension.c	848
read_extension_script_file	src/backend/commands/extension.c	871
execute_sql_string	src/backend/commands/extension.c	1046
extension_is_trusted	src/backend/commands/extension.c	1174
execute_extension_script	src/backend/commands/extension.c	1196
get_ext_ver_info	src/backend/commands/extension.c	1469
get_nearest_unprocessed_vertex	src/backend/commands/extension.c	1502
get_ext_ver_list	src/backend/commands/extension.c	1530
identify_update_path	src/backend/commands/extension.c	1593
find_update_path	src/backend/commands/extension.c	1636
find_install_path	src/backend/commands/extension.c	1729
CreateExtensionInternal	src/backend/commands/extension.c	1784
get_required_extension	src/backend/commands/extension.c	2022
CreateExtension	src/backend/commands/extension.c	2094
InsertExtensionTuple	src/backend/commands/extension.c	2192
RemoveExtensionById	src/backend/commands/extension.c	2280
extension_config_remove	src/backend/commands/extension.c	3028
AlterExtensionNamespace	src/backend/commands/extension.c	3193
ExecAlterExtensionStmt	src/backend/commands/extension.c	3408
ApplyExtensionUpdates	src/backend/commands/extension.c	3555
ExecAlterExtensionContentsStmt	src/backend/commands/extension.c	3713
new_ExtensionControlFile	src/backend/commands/extension.c	4003
find_in_paths	src/backend/commands/extension.c	4028
extension_control_path (GUC def)	src/backend/utils/misc/guc_tables.c	4402

Source verification (as of 2026-06-05)

Verified facts

• An extension is internally just an OID plus one pg_extension row; membership is not stored on the extension. Read from src/include/catalog/pg_extension.h on 2026-06-05: FormData_pg_extension carries extname, extowner, extnamespace, extrelocatable, extversion, extconfig[], extcondition[] and no member list. The header comment at the top of extension.c states the design directly: “All we need internally to manage an extension is an OID so that the dependent objects can be associated with it.” Membership is the DEPENDENCY_EXTENSION ('e') edge recorded in pg_depend.

• The control file is parsed by the postgresql.conf parser, and the primary control file is the only one allowed to set directory / default_version. Verified in parse_extension_control_file, which calls ParseConfigFp (the GUC parser) and, when version != NULL (an auxiliary name--ver.control), rejects directory and default_version with “parameter “%s” cannot be set in a secondary extension control file”. The same function enforces the relocatable/schema exclusion — the error “parameter “schema” cannot be specified when “relocatable” is true” fires when both are set (confirmed at the errmsg site in extension.c).

• Control-file defaults are superuser-only, non-relocatable, untrusted. Verified in new_ExtensionControlFile: relocatable = false, superuser = true, trusted = false, encoding = -1. These are the defaults extension authors rely on when a key is omitted.

• The version graph is discovered by listing the script directory and parsing filenames; the install/upgrade plan is a Dijkstra shortest path. Verified in get_ext_ver_list (a ReadDir loop over the script directory that turns each name--...sql into vertices/edges), find_update_path (the Dijkstra core with get_nearest_unprocessed_vertex doing the O(N^2) min-distance pick and a strcmp tie-break), and find_install_path (which seeds the search from every installable vertex). The double-dash filename grammar is enforced by check_valid_extension_name / check_valid_version_name rejecting -- in names.

• CreateExtensionInternal plans before it touches the catalog: it tries a direct install script first and only path-finds if that file is absent. Verified in CreateExtensionInternal: it stat()s get_extension_script_filename(pcontrol, NULL, versionName); on success updateVersions = NIL, otherwise it builds the graph and calls find_install_path, resetting versionName to the chosen installable start. The pg_extension row is inserted by InsertExtensionTuple before the script runs, because the script’s CREATE statements need a valid CurrentExtensionObject to attach their dependency edges to.

• execute_extension_script is the single point that arms the dependency-recording globals and reverts them on every exit path. Verified in execute_extension_script: it sets creating_extension = true and CurrentExtensionObject = extensionOid (lines 1319-1320 on 2026-06-05) inside a PG_TRY, and resets both to false / InvalidOid in the matching PG_FINALLY (lines 1443-1444). While the flag is set, each object the script creates calls recordDependencyOnCurrentExtension from the DDL layer.

• Trusted-extension escalation is a local, self-reverting identity switch to the bootstrap superuser. Verified in execute_extension_script: when extension_is_trusted(control) returns true for a non-superuser it calls SetUserIdAndSecContext(BOOTSTRAP_SUPERUSERID, save_sec_context | SECURITY_LOCAL_USERID_CHANGE). extension_is_trusted itself returns false unless control->trusted is set, then requires ACL_CREATE on MyDatabaseId via object_aclcheck. The SECURITY_LOCAL_USERID_CHANGE context guarantees the identity is restored at transaction end / abort.

• ALTER EXTENSION ... SET SCHEMA is gated on extrelocatable and honors a prerequisite’s no_relocate veto. Verified in AlterExtensionNamespace: it errors “extension “%s” does not support SET SCHEMA” unless extForm->extrelocatable, then during its pg_depend scan it consults each dependent extension’s no_relocate list (the dcontrol->no_relocate foreach near line 3324) and refuses the move if this extension is named.

• DROP EXTENSION carries almost no extension-specific logic. Verified in RemoveExtensionById: the function only deletes the pg_extension tuple (after refusing to drop the extension currently being built, extId == CurrentExtensionObject). The header comment states “Everything else is taken care of by the dependency infrastructure” — the generic dependency walker has already collected every DEPENDENCY_EXTENSION member for cascade before this point.

• PG 18 locates control files via the extension_control_path GUC, not just SHAREDIR/extension. Verified: extension_control_path is a PGC_SUSET string GUC defined in guc_tables.c; find_in_paths walks its components and rejects any non-absolute component with “component in parameter “%s” is not an absolute path”. An empty value falls back to the system SHAREDIR/extension. (This is a PG 18 feature; it is correct to assert it for REL_18.)

Open questions

1. Worst-case find_install_path cost on a dense version graph. The Dijkstra implementation is O(V^2) via get_nearest_unprocessed_vertex, and find_install_path re-runs the search conceptually from every installable vertex. For an extension with many fast-forward edges the constant factor is unmeasured here. Investigation path: count get_ext_ver_info / find_update_path invocations against a synthetic control directory with O(100) versions and dense from--to edges.

2. The exact failure mode when a requires prerequisite is itself mid-install under CASCADE. get_required_extension detects cycles via the parents list, but the interaction between recursive CASCADE installs and the single-extension-at-a-time creating_extension flag (only one extension may be under construction) deserves a traced example. Investigation path: build a three-extension requires chain with a cycle and observe which guard fires first.

3. How extconfig/extcondition config-table dumping interacts with a relocated extension. pg_extension_config_dump() records dumpable config tables by OID; after ALTER EXTENSION SET SCHEMA the table moves but the OID is stable. Whether any WHERE-clause text in extcondition can become stale relative to the new schema is not exercised here. Investigation path: relocate an extension that registered a config table with a schema- qualified condition and inspect the pg_dump output.

Beyond PostgreSQL — Comparative Designs & Research Frontiers

• POSTGRES’s original extensibility thesis. Stonebraker & Rowe 1986 (“The Design of POSTGRES”) and Stonebraker & Kemnitz 1991 (“The POSTGRES Next-Generation DBMS”) made user-defined types, operators, access methods, and procedural languages first-class. The modern extension mechanism is the packaging layer that finally let that extensibility ship as installable, versioned products: the catalog row plus the dependency edge are what turn a loose pile of CREATE TYPE/CREATE OPERATOR statements into one droppable unit. A note mapping each 1986 extensibility axis to the CREATE EXTENSION object it now ships as would close the loop. (Bibliography: dbms-papers/goes-around.md and the design-of-POSTGRES entries in .omc/plans/postgres-paper-bibliography.md.)

• The data dictionary as the unit of packaging. Database System Concepts frames the catalogs as the data dictionary and inter-object dependencies as dictionary facts. PostgreSQL’s bet — that membership is just another dependency edge — is the maximal expression of that framing: there is no bespoke “package contents” table, only pg_depend. Comparing this against systems that do keep a dedicated package/manifest catalog (Oracle PL/SQL packages, SQL Server assemblies) would sharpen the tradeoff: a generic dependency graph keeps the extension code tiny but makes “what is in this package?” a graph query rather than a table scan.

• Schema migration frameworks vs. the version graph. Flyway, Rails migrations, and Alembic almost all model migrations as a linear chain (V1 -> V2 -> V3). PostgreSQL’s from--to script convention is strictly more general: it is a directed graph with shortest-path selection, so an author can ship a 1.0--1.3 fast-forward alongside the incremental steps and let find_install_path pick the cheaper route. A comparison of the operational consequences (rollback story, branching releases, the cost of a missing intermediate script) against the linear-chain tools would be a practitioner-grade companion.

• Trusted extensions vs. cloud-managed allowlists. The trusted flag plus the transient BOOTSTRAP_SUPERUSERID switch is PostgreSQL’s in-core answer to “let a non-superuser install vetted packages.” Managed clouds (RDS, Cloud SQL, Aurora) layer their own allowlists on top, because the in-core trusted set is curated for correctness, not for any one provider’s threat model. Documenting where the in-core gate (extension_is_trusted) ends and the provider allowlist begins would clarify a frequent operator confusion.

• extension_control_path and immutable system images. PG 18’s searchable control path (find_in_paths) is aimed squarely at immutable OS images and per-tenant overlays where SHAREDIR/extension is read-only. This is adjacent to, but distinct from, the dynamic_library_path mechanism that locates the .so; a note tracing how a single extension’s .control, its .sql scripts, and its shared library can each live on a different search path would map the full “where do the bytes come from” picture.

Sources

In-tree source files (REL_18_STABLE, commit 273fe94)

• src/backend/commands/extension.c — the entire mechanism: control-file parsing (parse_extension_control_file, read_extension_control_file, new_ExtensionControlFile), filename/version grammar (check_valid_extension_name, get_extension_script_filename), the version graph and path finding (get_ext_ver_list, find_update_path, find_install_path), the CREATE/ALTER/DROP drivers (CreateExtension, CreateExtensionInternal, ExecAlterExtensionStmt, ApplyExtensionUpdates, AlterExtensionNamespace, RemoveExtensionById), script execution and policy (execute_extension_script, execute_sql_string, extension_is_trusted), and the control-path resolver (find_in_paths).
• src/include/catalog/pg_extension.h — FormData_pg_extension / Form_pg_extension, ExtensionRelationId (3079), the OID/name index ids.
• src/include/commands/extension.h — the creating_extension / CurrentExtensionObject extern declarations and the CreateExtension / InsertExtensionTuple / RemoveExtensionById prototypes.
• src/backend/utils/misc/guc_tables.c — the extension_control_path PGC_SUSET GUC definition.

Papers and textbook chapters

• Stonebraker, M. & Rowe, L. (1986). “The Design of POSTGRES.” SIGMOD. The extensibility-as-design-goal thesis the extension packaging layer serves. (.omc/plans/postgres-paper-bibliography.md.)
• Stonebraker, M. & Kemnitz, G. (1991). “The POSTGRES Next-Generation DBMS.” CACM 34(10). Access-method and procedural-language extensibility.
• Stonebraker, M. (2005). “What Goes Around Comes Around.” Type-system and data-model history that frames why extensibility mattered (knowledge/research/dbms-papers/goes-around.md).
• Database System Concepts (Silberschatz, Korth, Sudarshan, 7e) — the data-dictionary framing of the catalogs and inter-object dependencies (knowledge/research/dbms-general/).

Sibling docs (cross-references — mechanism owned there, not duplicated here)

• postgres-dependency-tracking.md — the pg_depend graph, DEPENDENCY_EXTENSION edges, recordDependencyOnCurrentExtension, and the cascade walker that makes DROP EXTENSION and pg_dump ordering work. The membership mechanism this doc relies on lives there.
• postgres-ddl-execution.md — the DDL executor and ProcessUtility path that execute_sql_string drives; how an individual CREATE inside a script becomes a catalog object.
• postgres-guc-parameters.md — the ParseConfigFp GUC parser reused to read .control files, and the extension_control_path setting.
• postgres-system-catalogs.md — pg_extension as one row in the broader catalog set; the syscache (EXTENSIONOID / EXTENSIONNAME) infrastructure.
• postgres-architecture-overview.md — the extensibility axis where the extension subsystem sits alongside FDWs, custom access methods, and hooks.