Skip to content

pg_extension_base

Extension development kit by Snowflake

pg_lake : Extension development kit by Snowflake

Overview

ID Extension Package Version Category License Language
2561 pg_extension_base pg_lake 3.7 OLAP Apache-2.0 C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--sLd-- No Yes Yes Yes no no
Preload.

Packages

Type Repo Version PG Major Compatibility Package Pattern Dependencies
EXT PIGSTY 3.7 19 18 17 16 15 pg_lake -
RPM PIGSTY 3.7.0 19 18 17 16 15 pg_lake_$v -
DEB PIGSTY 3.7.0 19 18 17 16 15 postgresql-$v-pg-lake -
Linux / PG PG19 PG18 PG17 PG16 PG15
el8.x86_64 N/A N/A N/A N/A N/A
el8.aarch64 N/A N/A N/A N/A N/A
el9.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el9.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el10.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
el10.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d12.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d12.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d13.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
d13.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u22.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u22.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u24.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u24.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u26.x86_64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A
u26.aarch64 N/A PIGSTY 3.6.0 PIGSTY 3.6.0 PIGSTY 3.6.0 N/A

Source

github.com/Snowflake-Labs/pg_lake/tree/main/pg_extension_base

pig build pkg pg_lake;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_lake;		# install via package name, for the active PG version
pig install pg_extension_base;		# install by extension name, for the current active PG version

pig install pg_extension_base -v 19;   # install for PG 19
pig install pg_extension_base -v 18;   # install for PG 18
pig install pg_extension_base -v 17;   # install for PG 17
pig install pg_extension_base -v 16;   # install for PG 16

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_extension_base';

Create this extension with:

CREATE EXTENSION pg_extension_base;

Usage

Sources:

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.

The reviewed upstream release is 3.6.0, with SQL extension version 3.6 and PostgreSQL 16–19 support. Existing packaged versions are tracked separately. Keep libraries and the query server from the same release.

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.5 adds failure_count to extension_base.list_base_workers() so worker restart backoff can be observed. Its upgrade drops and recreates that function; review dependent database objects and explicit grants before migration.

Upstream 3.6.0

Upstream 3.6.0 adds PostgreSQL 19 support and uses DuckDB 1.5.6. Deploy matching libraries and pgduck_server, keep pg_extension_base preloaded, restart loaded processes and apply the supplied SQL updates to version 3.6. This component’s 3.5-to-3.6 SQL migration is empty; the matching runtime and its dependencies still need the coordinated release upgrade.

Upstream 3.7.0

The current reviewed release is 3.7.0 and this component’s control version is 3.7. Earlier version sections describe historical behavior and package examples. Upgrade the complete family with matching libraries and pgduck_server, restart processes holding old code, and apply the supplied extension updates to SQL version 3.7. This does not establish package availability.

Table reader