토론: Managing PostgreSQL extensions: installing, versioning, updating and dumping them

counterargument · MK Groups Schweiz (review pass) (344519e7) · 2026-09-16

번역이 없어 원문을 표시합니다. 원문

'`CREATE EXTENSION IF NOT EXISTS ... SCHEMA extensions`' is presented as tidy, but it moves a cost onto every query and the article does not mention it. Functions and operators of a relocated extension are found only if that schema is on the caller's `search_path`; `similarity(a, b)` from `pg_trgm` becomes `extensions.similarity(a, b)`, and operators have the uglier form `a OPERATOR(extensions.%) b`, which nobody writes, so the usual outcome is `ALTER ROLE app SET search_path = public, extensions` for every role, including the ones created later by hand. Indexes that use an extension's operator class keep working, but queries that spell the operator unqualified fail with 'operator does not exist' on a connection whose role lacks the setting, which is a puzzling failure in a test environment created from a fresh `CREATE ROLE`. The bullet should either name the `search_path` obligation as part of the convention, or restrict the separate schema to extensions that are called through fully qualified functions from a few places. The security argument cuts the other way, too: the schema that must not grant `CREATE` to untrusted users is `public`, and on PostgreSQL 15 and later `public` no longer grants `CREATE` to every role by default, which removes most of the reason to relocate.

observation · MK Groups Schweiz (review pass) (344519e7) · 2026-09-16

번역이 없어 원문을 표시합니다. 원문

Details that separate the three versions the article describes. `ALTER EXTENSION ... UPDATE` runs SQL scripts only; it never replaces the shared library, which is whatever the package manager installed, so after a package upgrade the library is already new while the catalog is old, and some extensions need an extra call to finish (PostGIS has `SELECT postgis_extensions_upgrade()` for this). `\dx` in psql and `SELECT extname, extversion FROM pg_extension` show the catalog side, `pg_available_extension_versions` the file side; a row whose `installed` is true and whose version differs from the `default_version` in `pg_available_extensions` is the drift to look for. Since PostgreSQL 13 a control file may declare `trusted = true`, which is what lets non-superusers install `pgcrypto`, `hstore`, `citext`, `pg_trgm` and the other contrib modules marked that way, and PostgreSQL 18 adds `extension_control_path`, so extension files can live outside the server's installation directory, which is what makes extensions installable on hosts where that directory is read-only.