pg_extension_base

Overview

Set shared_preload_libraries=pg_extension_base. It auto-loads dependent libraries declared through the pg_lake control-file marker and manages extension lifecycle background workers. Extension SQL/control version is 3.4; source and DEB/RPM package version is 3.4.0.

Version

Build

You can build the RPM / DEB packages for pg_lake using pig build:

pig build pkg pg_lake         # build RPM / DEB packages

Install

You can install pg_lake directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_lake;          # Install for current active PG version

pig ext install -y pg_lake -v 18  # PG 18
pig ext install -y pg_lake -v 17  # PG 17
pig ext install -y pg_lake -v 16  # PG 16

dnf install -y pg_lake_18       # PG 18
dnf install -y pg_lake_17       # PG 17
dnf install -y pg_lake_16       # PG 16

apt install -y postgresql-18-pg-lake   # PG 18
apt install -y postgresql-17-pg-lake   # PG 17
apt install -y postgresql-16-pg-lake   # PG 16

Preload:

shared_preload_libraries = 'pg_extension_base';

Create Extension:

CREATE EXTENSION pg_extension_base;

Usage

Sources:

• Official pg_extension_base README
• Version 3.4 control file
• SQL API definition
• pg_lake build and preload guide

pg_extension_base is Snowflake’s infrastructure extension for other PostgreSQL extensions. It provides control-file-driven library preloading, database-scoped lifecycle workers, dependency-aware updates, and short-lived attached workers. Application users normally install it as a dependency of pg_lake; extension developers use its C and SQL APIs directly.

Enable the Infrastructure

pg_extension_base must be preloaded before extensions can use its startup machinery:

shared_preload_libraries = 'pg_extension_base'

Restart PostgreSQL, then create it in a database that hosts dependent extensions:

CREATE EXTENSION pg_extension_base;

SELECT * FROM extension_base.list_preload_libraries();
SELECT * FROM extension_base.list_base_workers();

Extension-Developer Workflow

A dependent extension can request startup loading with a control-file directive:

requires = 'pg_extension_base'
module_pathname = '$libdir/my_extension'
#!shared_preload_libraries

To attach a database lifecycle worker, define an internal C function and register it during extension installation:

CREATE FUNCTION my_extension.main_worker(internal)
RETURNS internal
LANGUAGE C
AS 'MODULE_PATHNAME', 'my_extension_main_worker';

SELECT extension_base.register_worker(
    'my_extension_main',
    'my_extension.main_worker'
);

The base infrastructure starts the worker after server startup, CREATE EXTENSION, or creation of a database from a template, and attempts to stop it for DROP EXTENSION or DROP DATABASE.

SQL API Index

• extension_base.list_preload_libraries(): reports extension/library pairs discovered for startup loading.
• extension_base.register_worker(name, regproc) and deregister_worker(name): manage lifecycle-worker registrations; public execution is revoked.
• extension_base.list_base_workers(): reports database, extension, PID, and restart state for base workers.
• extension_base.list_database_starters(): reports per-database starter processes.
• extension_base.run_attached(command, dbname): runs a command in a short-lived worker, optionally in another database, and returns command IDs and tags.

Operational Boundaries

• Lifecycle worker functions run as superuser and outside a transaction. They must start their own transactions, check interrupts, and avoid trusting user-controlled SQL.
• Worker termination around failed DROP operations is best effort; worker code must tolerate the extension briefly disappearing or a stop being reversed.
• run_attached is for bounded work that may commit independently of the caller. It is not a detached job queue for long-running tasks.
• Version 3.4 changes no user-facing SQL objects relative to 3.3; its upgrade script is intentionally empty.