pgelog

pgelog : Extended logging via pseudo-autonomous transactions

Overview

Packages

Source

pig build pkg pgelog;		# 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 pgelog;		# install via package name, for the active PG version

pig install pgelog -v 18;   # install for PG 18
pig install pgelog -v 17;   # install for PG 17
pig install pgelog -v 16;   # install for PG 16
pig install pgelog -v 15;   # install for PG 15
pig install pgelog -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pgelog CASCADE; -- requires dblink, pg_variables

Usage

Source: README, control file, extension SQL 1.0.2, Tag v1.0.2

pgelog writes rollback-resistant log rows through pseudo-autonomous transactions implemented with dblink. It caches the extra connection in pg_variables so repeated logging in the same session is cheaper.

Requirements and install

• PostgreSQL 11+
• dblink
• pg_variables
• passwordless local dblink access, typically via peer

CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS pg_variables;
CREATE EXTENSION pgelog;

Main objects and functions

SELECT pgelog_to_log('SQL', 'standalone', 'Test of logging by pgelog', '1');

SELECT pgelog_get_param('pgelog_ttl_minutes');
SELECT pgelog_set_param('pgelog_ttl_minutes', '2880');

• pgelog_logs: base log table.
• pgelog_vw_logs: log view with timing deltas.
• pgelog_params: configuration table.
• pgelog_to_log(...): write a log row that survives rollback.
• pgelog_get_param(...), pgelog_set_param(...), pgelog_delete_param(...): manage extension settings.

Typical use

The official README shows pgelog_to_log(...) in PL/pgSQL exception handlers: collect diagnostics with GET STACKED DIAGNOSTICS, write a FAIL log row, then re-raise the error.

Caveats

• Each session can open one additional dblink connection, so max_connections should account for that.
• Upstream v1.0.2 still ships extension SQL under the same user-visible object family; Pigsty’s note about runtime dblink plus pg_variables requirements is confirmed by the control file and README.