Metadata Desc

Each extension comes with several metadata attributes. Below are the descriptions of these attributes:

• id

  Extension identifier, an unique integer assigned to each extension for internal sorting.

• name

  Extension name, the name of the extension in the PostgreSQL system catalog, used in CREATE EXTENSION.

  Extensions typically come with files like <name>.control, <name>*.so, and <name>*.sql.

• alias

  Extension alias, a normalized name assigned by Pigsty to each extension, usually matching the extension name name. However, there are exceptions. For example, installing an RPM package that introduces multiple extensions will share a common alias, such as postgis.

• version

  Default version of the extension, usually the latest version. In some special cases, the available versions in RPM and Debian may slightly differ.

• category

  Extension category, used to distinguish the type of functionality provided by the extension, such as: gis, time, rag, fts, olap, feat, lang, type, func, admin, stat, sec, fdw, sim, etl

• tags

  Tags describing the features of the extension.

• repo

  The source repository of the extension, CONTRIB means it’s a PostgreSQL built-in extension, PGDG denotes a PGDG first-party extension, and PIGSTY indicates a Pigsty third-party extension.

• lang

  The programming language used by the extension, usually C, but there are some written in C++ or Rust. There are also extensions purely composed of SQL and data.

• need_load

  Marked with Load, meaning the extension uses PostgreSQL hooks, requiring dynamic loading and a PostgreSQL restart to take effect. Only a few extensions need dynamic loading, most are statically loaded.

• need_ddl

  Marked with DDL, meaning the extension requires executing DDL statements: CREATE EXTENSION.

  Most extensions need the CREATE EXTENSION DDL statement for creation, but there are exceptions like pg_stat_statements and wal2json.

• trusted

  Does installing this extension require superuser privileges? Or is the extension “trusted” — only providing functions internally within the database.

  A few extensions only provide functions internally within the database and thus do not require superuser privileges to install (trusted). Any user with CREATE privileges can install trusted extensions.

• relocatable

  Can the extension be relocated? That is, can it be installed into other schemas? Most extensions are relocatable, but there are exceptions where extensions specify their schema explicitly.

• schemas

  If the extension is relocatable, it can be installed into a specified schema. This attribute specifies the default schema for the extension. PostgreSQL typically allows extensions to use only one schema, but some extensions do not follow this rule, such as citus and timescaledb.

• pg_ver

  The PostgreSQL versions supported by the extension, typically only considering versions within the support lifecycle, i.e., 12 - 16.

• requires

  Other extensions this extension depends on, if any. An extension may depend on multiple other extensions, and these dependencies are usually declared in the requires field of the extension’s control file.

  When installing an extension, dependencies can be automatically installed with the CREATE EXTENSION xxx CASCADE statement.

• pkg

  Extension package (RPM/DEB) name, using $v to replace the specific major PostgreSQL version number.

• pkg_ver

  The version number of the extension package (RPM/DEB), usually consistent with the extension’s version (versionobtained from system views). However, there are rare exceptions where the package version and the extension version are inconsistent or independently managed.

• pkg_deps

  The dependencies of the extension package (RPM/DEB), different from the extension’s dependencies (requires), here referring to the specific dependencies of the RPM/DEB package.

• url

  The official website or source code repository of the extension.

• license

  The open-source license used by the extension, typically PostgreSQL, MIT, Apache, GPL, etc.

• en_desc

  The English description of the extension, describing its functions and uses.

• zh_desc

  The Chinese description of the extension, describing its functions and uses.

• comment

  Additional comments describing the features or considerations of the extension.

Database Schema

CREATE TABLE IF NOT EXISTS ext.extension
(
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    pkg         TEXT NOT NULL,
    alias       TEXT,
    category    TEXT,
    state       TEXT,
    url         TEXT,
    license     TEXT,
    tags        TEXT[],
    version     TEXT,
    repo        TEXT,
    lang        TEXT,
    contrib     BOOLEAN,
    lead        BOOLEAN,
    has_bin     BOOLEAN,
    has_lib     BOOLEAN,
    need_ddl    BOOLEAN,
    need_load   BOOLEAN,
    trusted     BOOLEAN,
    relocatable BOOLEAN,
    schemas     TEXT[],
    pg_ver      TEXT[],
    requires    TEXT[],
    rpm_ver     TEXT,
    rpm_repo    TEXT,
    rpm_pkg     TEXT,
    rpm_pg      TEXT[],
    rpm_deps    TEXT[],
    deb_ver     TEXT,
    deb_repo    TEXT,
    deb_pkg     TEXT,
    deb_deps    TEXT[],
    deb_pg      TEXT[],
    bad_case    TEXT[],
    extra       JSONB,
    ctime       DATE DEFAULT CURRENT_DATE,
    mtime       DATE DEFAULT CURRENT_DATE,
    en_desc     TEXT,
    zh_desc     TEXT,
    comment     TEXT
);
COMMENT ON TABLE ext.extension IS 'PostgreSQL Extension Table';
COMMENT ON COLUMN ext.extension.id IS 'Extension Identifier (integer)';
COMMENT ON COLUMN ext.extension.name IS 'Extension Name (in system catalog)';
COMMENT ON COLUMN ext.extension.pkg IS 'Normalized extension package name';
COMMENT ON COLUMN ext.extension.alias IS 'Download pkg group alias';
COMMENT ON COLUMN ext.extension.category IS 'Category of this extension';
COMMENT ON COLUMN ext.extension.state IS 'Extension State (available, deprecated, removed, not-ready)';
COMMENT ON COLUMN ext.extension.url IS 'Extension Repo URL';
COMMENT ON COLUMN ext.extension.license IS 'Extension License';
COMMENT ON COLUMN ext.extension.tags IS 'Extra tags';
COMMENT ON COLUMN ext.extension.version IS 'the latest available version of this extension';
COMMENT ON COLUMN ext.extension.lang IS 'Programming Language of this extension';
COMMENT ON COLUMN ext.extension.lead IS 'Mark the primary extension among one multi-ext package';
COMMENT ON COLUMN ext.extension.has_bin IS 'does this extension has binary utils';
COMMENT ON COLUMN ext.extension.has_lib IS 'Does the extension have shared library?';
COMMENT ON COLUMN ext.extension.need_ddl IS 'Extension need `CREATE EXTENSION` to work?';
COMMENT ON COLUMN ext.extension.need_load IS 'Require LOAD & shared_preload_libraries to work?';
COMMENT ON COLUMN ext.extension.trusted IS 'A Trusted extension does not require superuser to work';
COMMENT ON COLUMN ext.extension.relocatable IS 'Can this extension be relocated?';
COMMENT ON COLUMN ext.extension.schemas IS 'Installed Schema, if not relocatable';
COMMENT ON COLUMN ext.extension.pg_ver IS 'Supported PostgreSQL major versions';
COMMENT ON COLUMN ext.extension.requires IS 'Dependencies of this extension';
COMMENT ON COLUMN ext.extension.rpm_pkg IS 'RPM package name, major version is replace with $v';
COMMENT ON COLUMN ext.extension.deb_pkg IS 'DEB package name, major version is replace with $v';
COMMENT ON COLUMN ext.extension.en_desc IS 'English description';
COMMENT ON COLUMN ext.extension.zh_desc IS 'Chinese description';
COMMENT ON COLUMN ext.extension.comment IS 'Extra information';