From f4521468a030ba9efcdcd51988a81e6b569c3a28 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Fabr=C3=ADzio=20de=20Royes=20Mello?= Date: Mon, 15 Jun 2026 09:00:12 -0700 Subject: [PATCH v1] Add pg_stat_log contrib module MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit pg_stat_log collects cumulative statistics about server log messages using the custom cumulative statistics API. It hooks into emit_log_hook and counts emitted messages grouped by backend type, database, user, error severity level, and SQLSTATE code. The module must be loaded via shared_preload_libraries. Statistics are exposed through the pg_stat_log view and the pg_stat_log_data() function, with pg_stat_log_info() reporting capacity metadata and pg_stat_log_reset() clearing the counters. Read access is restricted to pg_read_all_stats; reset is superuser-only. Entries are kept in an index-based chaining hash table laid out inside the fixed-size stats block, so lookups, inserts, and drops stay O(1) expected even when the table is full, while the block remains safe to snapshot and persist verbatim. Behavior is controlled by three GUCs: pg_stat_log.enabled, pg_stat_log.min_error_level, and pg_stat_log.max_entries. Includes regression and TAP tests, SGML documentation, and both Make and Meson build integration. Signed-off-by: Fabrízio de Royes Mello --- contrib/Makefile | 1 + contrib/meson.build | 1 + contrib/pg_stat_log/.gitignore | 7 + contrib/pg_stat_log/Makefile | 40 ++ contrib/pg_stat_log/expected/pg_stat_log.out | 190 ++++++ contrib/pg_stat_log/generate-errcode-names.pl | 63 ++ contrib/pg_stat_log/meson.build | 56 ++ contrib/pg_stat_log/pg_stat_log--0.1.sql | 57 ++ contrib/pg_stat_log/pg_stat_log.c | 624 ++++++++++++++++++ contrib/pg_stat_log/pg_stat_log.conf | 1 + contrib/pg_stat_log/pg_stat_log.control | 4 + contrib/pg_stat_log/sql/pg_stat_log.sql | 119 ++++ contrib/pg_stat_log/t/001_pg_stat_log.pl | 250 +++++++ doc/src/sgml/contrib.sgml | 1 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/pgstatlog.sgml | 318 +++++++++ 16 files changed, 1733 insertions(+) create mode 100644 contrib/pg_stat_log/.gitignore create mode 100644 contrib/pg_stat_log/Makefile create mode 100644 contrib/pg_stat_log/expected/pg_stat_log.out create mode 100644 contrib/pg_stat_log/generate-errcode-names.pl create mode 100644 contrib/pg_stat_log/meson.build create mode 100644 contrib/pg_stat_log/pg_stat_log--0.1.sql create mode 100644 contrib/pg_stat_log/pg_stat_log.c create mode 100644 contrib/pg_stat_log/pg_stat_log.conf create mode 100644 contrib/pg_stat_log/pg_stat_log.control create mode 100644 contrib/pg_stat_log/sql/pg_stat_log.sql create mode 100644 contrib/pg_stat_log/t/001_pg_stat_log.pl create mode 100644 doc/src/sgml/pgstatlog.sgml diff --git a/contrib/Makefile b/contrib/Makefile index 7d91fe77db3..f962b3a860f 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -37,6 +37,7 @@ SUBDIRS = \ pg_plan_advice \ pg_prewarm \ pg_stash_advice \ + pg_stat_log \ pg_stat_statements \ pg_surgery \ pg_trgm \ diff --git a/contrib/meson.build b/contrib/meson.build index ebb7f83d8c5..e1e0cb1cdcd 100644 --- a/contrib/meson.build +++ b/contrib/meson.build @@ -52,6 +52,7 @@ subdir('pg_plan_advice') subdir('pg_prewarm') subdir('pgrowlocks') subdir('pg_stash_advice') +subdir('pg_stat_log') subdir('pg_stat_statements') subdir('pgstattuple') subdir('pg_surgery') diff --git a/contrib/pg_stat_log/.gitignore b/contrib/pg_stat_log/.gitignore new file mode 100644 index 00000000000..719d0725abe --- /dev/null +++ b/contrib/pg_stat_log/.gitignore @@ -0,0 +1,7 @@ +# Generated header +/pg_stat_log_errcodes.h + +# Generated subdirectories +/log/ +/results/ +/tmp_check/ diff --git a/contrib/pg_stat_log/Makefile b/contrib/pg_stat_log/Makefile new file mode 100644 index 00000000000..92c3a537c75 --- /dev/null +++ b/contrib/pg_stat_log/Makefile @@ -0,0 +1,40 @@ +# contrib/pg_stat_log/Makefile + +MODULE_big = pg_stat_log +OBJS = \ + $(WIN32RES) \ + pg_stat_log.o + +EXTENSION = pg_stat_log +DATA = pg_stat_log--0.1.sql +PGFILEDESC = "pg_stat_log - cumulative statistics about log messages" + +REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_log/pg_stat_log.conf +REGRESS = pg_stat_log + +# Disabled because these tests require "shared_preload_libraries=pg_stat_log", +# which typical installcheck users do not have (e.g. buildfarm clients). +NO_INSTALLCHECK = 1 + +TAP_TESTS = 1 + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/pg_stat_log +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif + +# Generate the errcode-name lookup header from the in-tree errcodes.txt. +ERRCODES_TXT = $(top_srcdir)/src/backend/utils/errcodes.txt + +pg_stat_log_errcodes.h: generate-errcode-names.pl $(ERRCODES_TXT) + $(PERL) $(srcdir)/generate-errcode-names.pl --outfile $@ $(ERRCODES_TXT) + +pg_stat_log.o: pg_stat_log_errcodes.h + +EXTRA_CLEAN = pg_stat_log_errcodes.h diff --git a/contrib/pg_stat_log/expected/pg_stat_log.out b/contrib/pg_stat_log/expected/pg_stat_log.out new file mode 100644 index 00000000000..94e3127f57d --- /dev/null +++ b/contrib/pg_stat_log/expected/pg_stat_log.out @@ -0,0 +1,190 @@ +-- +-- pg_stat_log regression tests +-- +CREATE EXTENSION pg_stat_log; +-- Start clean +SELECT pg_stat_log_reset(); + pg_stat_log_reset +------------------- + +(1 row) + +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +-- +-- Test 1: Warnings are counted +-- +DO $$ BEGIN RAISE WARNING 'test warning 1'; END $$; +WARNING: test warning 1 +DO $$ BEGIN RAISE WARNING 'test warning 2'; END $$; +WARNING: test warning 2 +DO $$ BEGIN RAISE WARNING 'test warning 3'; END $$; +WARNING: test warning 3 +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT count >= 3 AS warning_count_ok +FROM pg_stat_log_data() +WHERE elevel = 'WARNING' AND sqlerrcode = '01000'; + warning_count_ok +------------------ + t +(1 row) + +-- +-- Test 2: Errors are tracked +-- +SELECT 1/0; +ERROR: division by zero +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT count >= 1 AS division_by_zero_ok +FROM pg_stat_log_data() +WHERE elevel = 'ERROR' AND sqlerrcode = '22012'; + division_by_zero_ok +--------------------- + t +(1 row) + +-- +-- Test 3: pg_stat_log view works (returns rows with database/user names) +-- +SELECT count(*) > 0 AS view_has_rows FROM pg_stat_log WHERE count > 0; + view_has_rows +--------------- + t +(1 row) + +-- +-- Test 4: Disable via GUC stops counting +-- +SET pg_stat_log.enabled = off; +DO $$ BEGIN RAISE WARNING 'should not be counted'; END $$; +WARNING: should not be counted +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +-- The warning count should not have increased; we check by looking for the +-- specific message-related sqlerrcode that was already counted before. +SELECT count >= 3 AS still_same_warning_count +FROM pg_stat_log_data() +WHERE elevel = 'WARNING' AND sqlerrcode = '01000'; + still_same_warning_count +-------------------------- + t +(1 row) + +SET pg_stat_log.enabled = on; +-- +-- Test 5: min_error_level filtering +-- +SET pg_stat_log.min_error_level = 'error'; +-- Record warning count before +SELECT count AS cnt_before +FROM pg_stat_log_data() +WHERE elevel = 'WARNING' AND sqlerrcode = '01000' \gset +DO $$ BEGIN RAISE WARNING 'filtered out'; END $$; +WARNING: filtered out +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +-- Warning count should be unchanged +SELECT count = :cnt_before AS warning_filtered_ok +FROM pg_stat_log_data() +WHERE elevel = 'WARNING' AND sqlerrcode = '01000'; + warning_filtered_ok +--------------------- + t +(1 row) + +SET pg_stat_log.min_error_level = 'warning'; +-- +-- Test 6: Reset zeroes counters +-- +SELECT pg_stat_log_reset(); + pg_stat_log_reset +------------------- + +(1 row) + +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT COALESCE(sum(count), 0) = 0 AS reset_ok FROM pg_stat_log_data(); + reset_ok +---------- + t +(1 row) + +-- +-- Test 7: pg_stat_log_info() returns one row with expected columns +-- +SELECT count(*) = 1 AS info_one_row FROM pg_stat_log_info(); + info_one_row +-------------- + t +(1 row) + +-- +-- Test 8: max_entries matches GUC +-- +SELECT max_entries = current_setting('pg_stat_log.max_entries')::int AS max_matches_guc +FROM pg_stat_log_info(); + max_matches_guc +----------------- + t +(1 row) + +-- +-- Test 9: After reset, num_entries and n_dropped are zero +-- +SELECT pg_stat_log_reset(); + pg_stat_log_reset +------------------- + +(1 row) + +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT num_entries = 0 AS num_zero, n_dropped = 0 AS dropped_zero +FROM pg_stat_log_info(); + num_zero | dropped_zero +----------+-------------- + t | t +(1 row) + +-- +-- Test 10: pg_stat_log_reset() is restricted to superusers +-- +CREATE ROLE regress_pg_stat_log_user; +SET ROLE regress_pg_stat_log_user; +SELECT pg_stat_log_reset(); +ERROR: permission denied for function pg_stat_log_reset +RESET ROLE; +DROP ROLE regress_pg_stat_log_user; +-- Clean up +DROP EXTENSION pg_stat_log; diff --git a/contrib/pg_stat_log/generate-errcode-names.pl b/contrib/pg_stat_log/generate-errcode-names.pl new file mode 100644 index 00000000000..a1056d1d885 --- /dev/null +++ b/contrib/pg_stat_log/generate-errcode-names.pl @@ -0,0 +1,63 @@ +#!/usr/bin/perl +# +# Copyright (c) 2026, PostgreSQL Global Development Group +# +# Generate pg_stat_log_errcodes.h from errcodes.txt +# +# Produces a static lookup table mapping packed sqlerrcode values to their +# human-readable condition names (e.g. ERRCODE_DIVISION_BY_ZERO -> "division_by_zero"). +# + +use strict; +use warnings FATAL => 'all'; +use Getopt::Long; + +my $outfile = ''; + +GetOptions('outfile=s' => \$outfile) or die "$0: wrong arguments"; + +open my $errcodes, '<', $ARGV[0] + or die "$0: could not open input file '$ARGV[0]': $!\n"; + +my $outfh; +if ($outfile) +{ + open $outfh, '>', $outfile + or die "$0: could not open output file '$outfile': $!\n"; +} +else +{ + $outfh = *STDOUT; +} + +print $outfh "/* autogenerated from errcodes.txt, do not edit */\n\n"; +print $outfh "typedef struct PgStatLogErrCode\n"; +print $outfh "{\n"; +print $outfh "\tint\t\t\tsqlerrcode;\n"; +print $outfh "\tconst char *name;\n"; +print $outfh "} PgStatLogErrCode;\n\n"; +print $outfh "static const PgStatLogErrCode pg_stat_log_errcodes[] = {\n"; + +while (<$errcodes>) +{ + chomp; + next if /^#/; + next if /^\s*$/; + next if /^Section:/; + + # Parse: sqlstate E/W/S ERRCODE_MACRO [spec_name] + next unless /^([^\s]{5})\s+[EWS]\s+([^\s]+)(?:\s+([^\s]+))?/; + + my ($sqlstate, $errcode_macro, $spec_name) = ($1, $2, $3); + + # Skip entries without a spec_name + next unless defined $spec_name && $spec_name ne ''; + + print $outfh "\t{$errcode_macro, \"$spec_name\"},\n"; +} + +print $outfh "\t{0, NULL}\n"; +print $outfh "};\n"; + +close $errcodes; +close $outfh if ($outfile); diff --git a/contrib/pg_stat_log/meson.build b/contrib/pg_stat_log/meson.build new file mode 100644 index 00000000000..9661afaa0b6 --- /dev/null +++ b/contrib/pg_stat_log/meson.build @@ -0,0 +1,56 @@ +# Copyright (c) 2022-2026, PostgreSQL Global Development Group + +# Generate the errcode-name lookup header from the in-tree errcodes.txt. +pg_stat_log_errcodes = custom_target('pg_stat_log_errcodes', + input: '../../src/backend/utils/errcodes.txt', + output: 'pg_stat_log_errcodes.h', + command: [ + perl, files('generate-errcode-names.pl'), '--outfile', '@OUTPUT@', '@INPUT@', + ], +) +generated_sources += pg_stat_log_errcodes + +pg_stat_log_sources = files( + 'pg_stat_log.c', +) +pg_stat_log_sources += pg_stat_log_errcodes + +if host_system == 'windows' + pg_stat_log_sources += rc_lib_gen.process(win32ver_rc, extra_args: [ + '--NAME', 'pg_stat_log', + '--FILEDESC', 'pg_stat_log - cumulative statistics about log messages',]) +endif + +pg_stat_log = shared_module('pg_stat_log', + pg_stat_log_sources, + include_directories: include_directories('.'), + kwargs: contrib_mod_args, +) +contrib_targets += pg_stat_log + +install_data( + 'pg_stat_log.control', + 'pg_stat_log--0.1.sql', + kwargs: contrib_data_args, +) + +tests += { + 'name': 'pg_stat_log', + 'sd': meson.current_source_dir(), + 'bd': meson.current_build_dir(), + 'regress': { + 'sql': [ + 'pg_stat_log', + ], + 'regress_args': ['--temp-config', files('pg_stat_log.conf')], + # Disabled because these tests require + # "shared_preload_libraries=pg_stat_log", which typical runningcheck + # users do not have (e.g. buildfarm clients). + 'runningcheck': false, + }, + 'tap': { + 'tests': [ + 't/001_pg_stat_log.pl', + ], + }, +} diff --git a/contrib/pg_stat_log/pg_stat_log--0.1.sql b/contrib/pg_stat_log/pg_stat_log--0.1.sql new file mode 100644 index 00000000000..63ce35fd098 --- /dev/null +++ b/contrib/pg_stat_log/pg_stat_log--0.1.sql @@ -0,0 +1,57 @@ +/* pg_stat_log/pg_stat_log--0.1.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pg_stat_log" to load this file. \quit + +CREATE FUNCTION pg_stat_log_data( + OUT backend_type text, + OUT database_oid oid, + OUT user_oid oid, + OUT elevel text, + OUT sqlerrcode text, + OUT sqlerrcode_name text, + OUT count bigint +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_stat_log_data' +LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE FUNCTION pg_stat_log_reset() +RETURNS void +AS 'MODULE_PATHNAME', 'pg_stat_log_reset' +LANGUAGE C STRICT PARALLEL UNSAFE; + +CREATE VIEW pg_stat_log AS +SELECT s.backend_type, + s.database_oid, + d.datname AS database_name, + s.user_oid, + u.rolname AS user_name, + s.elevel, + s.sqlerrcode, + s.sqlerrcode_name, + s.count +FROM pg_stat_log_data() s +LEFT JOIN pg_database d ON d.oid = s.database_oid +LEFT JOIN pg_roles u ON u.oid = s.user_oid; + +REVOKE ALL ON FUNCTION pg_stat_log_reset() FROM PUBLIC; + +REVOKE ALL ON FUNCTION pg_stat_log_data() FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_stat_log_data() TO pg_read_all_stats; + +REVOKE ALL ON pg_stat_log FROM PUBLIC; +GRANT SELECT ON pg_stat_log TO pg_read_all_stats; + +CREATE FUNCTION pg_stat_log_info( + OUT max_entries int, + OUT num_entries int, + OUT n_dropped bigint, + OUT stats_reset timestamp with time zone +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_stat_log_info' +LANGUAGE C STRICT PARALLEL UNSAFE; + +REVOKE ALL ON FUNCTION pg_stat_log_info() FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_stat_log_info() TO pg_read_all_stats; diff --git a/contrib/pg_stat_log/pg_stat_log.c b/contrib/pg_stat_log/pg_stat_log.c new file mode 100644 index 00000000000..52707cadad7 --- /dev/null +++ b/contrib/pg_stat_log/pg_stat_log.c @@ -0,0 +1,624 @@ +/*-------------------------------------------------------------------------- + * + * pg_stat_log.c + * Cumulative statistics about log messages. + * + * Hooks into emit_log_hook to count log messages grouped by + * (elevel, sqlerrcode, database_oid, user_oid, backend_type). + * + * Uses the fixed-amount Custom Cumulative Stats API introduced in + * PostgreSQL 18, following the same pattern as test_custom_fixed_stats.c. + * + * Copyright (c) 2026, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/pg_stat_log/pg_stat_log.c + * + *-------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "funcapi.h" +#include "miscadmin.h" +#include "pgstat.h" +#include "common/hashfn.h" +#include "storage/proc.h" +#include "utils/builtins.h" +#include "utils/errcodes.h" +#include "utils/guc.h" +#include "utils/pgstat_internal.h" +#include "utils/timestamp.h" +#include "utils/tuplestore.h" + +#include "pg_stat_log_errcodes.h" + +#define PGSTAT_LOG_MODULE_NAME "pg_stat_log" +#define PGSTAT_LOG_TRANCHE_NAME PGSTAT_LOG_MODULE_NAME + +PG_MODULE_MAGIC_EXT(.name = PGSTAT_LOG_MODULE_NAME, .version = PG_VERSION); + +/* + * Custom stats kind ID — registered at + * https://wiki.postgresql.org/wiki/CustomCumulativeStats + */ +#define PGSTAT_KIND_LOG 28 + +/* GUC defaults and bounds */ +#define PGSTAT_LOG_MAX_DEFAULT 1024 +#define PGSTAT_LOG_MIN_ENTRIES 64 +#define PGSTAT_LOG_MAX_ENTRIES (PGSTAT_LOG_MAX_DEFAULT * PGSTAT_LOG_MAX_DEFAULT) + +/* + * Data structures + */ +typedef struct PgStatLogSlot +{ + int32 next; /* next slot index in bucket chain, or -1 */ + BackendType backend_type; + Oid dboid; + Oid userid; + int elevel; + int sqlerrcode; + PgStat_Counter count; +} PgStatLogSlot; + +/* + * Stats data block. Variable-length: a header followed by a payload laid out + * as (entries first to keep 8-byte alignment without padding): + * + * PgStatLogSlot entries[max_entries] insertion-ordered slots + * int32 heads[max_entries] bucket chain heads (-1 = empty) + * + * The slots form an index-based separate-chaining hash table: heads[bucket] + * points to the first slot of a chain and each slot's "next" links the rest. + * Slots are allocated sequentially (slot i is live iff i < num_entries) and + * never evicted, so lookups, inserts, and drops walk only one bucket chain + * (average length = load factor) and stay O(1) expected even at full load. + * Links are array indices (not pointers), so the block is snapshot- and + * persistence-safe. Use the accessors below to reach each sub-array. + * + * Why not a standard PostgreSQL hash table? The fixed-amount custom stats + * API snapshots this block with a raw memcpy and persists/restores it + * verbatim (fwrite/fread) with no serialize callbacks, so everything in the + * block must be position-independent and self-contained. dynahash chains + * entries with raw pointers, simplehash is process-local and grows by + * reallocation, and dshash would require switching to variable-amount stats + * (64-bit key limit, unbounded DSA growth, no extension-visible enumeration, + * and allocation inside emit_log_hook). + */ +typedef struct PgStatLog +{ + int max_entries; + int num_entries; + char data[FLEXIBLE_ARRAY_MEMBER]; +} PgStatLog; + +/* + * Shared memory wrapper. LWLock + changecount + metadata + data. Metadata + * fields (stat_reset_timestamp, n_dropped) live outside the copied stats + * block so they can be read directly under the LWLock without the + * changecount protocol. The data area holds one PgStatLog block. + */ +typedef struct PgStatLogShared +{ + LWLock lock; + uint32 changecount; + TimestampTz stat_reset_timestamp; + uint64 n_dropped; + char data[FLEXIBLE_ARRAY_MEMBER]; +} PgStatLogShared; + +/* + * GUC variables + */ +static bool pg_stat_log_enabled = true; +static int pg_stat_log_min_elevel = WARNING; +static int pg_stat_log_max = PGSTAT_LOG_MAX_DEFAULT; + +/* + * Computed sizes (set in _PG_init based on pg_stat_log.max_entries) + */ +static Size stats_block_size; /* one PgStatLog block */ + +/* + * Hook state + */ +static emit_log_hook_type prev_emit_log_hook = NULL; +static bool in_emit_log_hook = false; + +/* + * Accessor helpers + */ +static inline PgStatLog * +pg_stat_log_get_stats(PgStatLogShared *shmem) +{ + return (PgStatLog *) shmem->data; +} + +static inline PgStatLogSlot * +pg_stat_log_entries(PgStatLog *s) +{ + return (PgStatLogSlot *) s->data; +} + +static inline int32 * +pg_stat_log_heads(PgStatLog *s) +{ + return (int32 *) (s->data + sizeof(PgStatLogSlot) * (Size) s->max_entries); +} + +/* + * Errcode name lookup + */ +static const char * +pg_stat_log_errcode_name(int sqlerrcode) +{ + for (int i = 0; pg_stat_log_errcodes[i].name != NULL; i++) + { + if (pg_stat_log_errcodes[i].sqlerrcode == sqlerrcode) + return pg_stat_log_errcodes[i].name; + } + return NULL; +} + +/* + * Hash function for slot lookup — combines all key fields into a uint32 + * used to pick a chain bucket (hash % max_entries). + */ +static inline uint32 +pg_stat_log_hash_key(BackendType backend_type, Oid dboid, Oid userid, int elevel, int sqlerrcode) +{ + uint32 h; + + h = murmurhash32((uint32) backend_type); + h = hash_combine(h, murmurhash32((uint32) dboid)); + h = hash_combine(h, murmurhash32((uint32) userid)); + h = hash_combine(h, murmurhash32((uint32) elevel)); + h = hash_combine(h, murmurhash32((uint32) sqlerrcode)); + return h; +} + +/* + * PgStat_KindInfo — filled dynamically in _PG_init + */ +static void pg_stat_log_init_backend_cb(void); +static void pg_stat_log_init_shmem_cb(void *stats); +static void pg_stat_log_reset_all_cb(TimestampTz ts); +static void pg_stat_log_snapshot_cb(void); + +static PgStat_KindInfo log_stats_kind; + +/* + * init_backend_cb — per-backend initialization + * + * Runs in every backend after the stats file has been loaded by the + * startup process. Validates that persisted max_entries matches the + * current GUC. If pg_stat_log.max_entries was changed across a clean + * restart, the restored stats block has a stale layout — discard and + * reinitialize to prevent out-of-bounds access. + */ +static void +pg_stat_log_init_backend_cb(void) +{ + PgStatLogShared *shmem; + PgStatLog *s; + + shmem = (PgStatLogShared *) pgstat_get_custom_shmem_data(PGSTAT_KIND_LOG); + s = pg_stat_log_get_stats(shmem); + + if (s->max_entries != pg_stat_log_max) + { + elog(LOG, + "pg_stat_log: discarding persisted stats " + "(max_entries changed from %d to %d)", + s->max_entries, + pg_stat_log_max); + + LWLockAcquire(&shmem->lock, LW_EXCLUSIVE); + + pgstat_begin_changecount_write(&shmem->changecount); + s->max_entries = pg_stat_log_max; + s->num_entries = 0; + memset(pg_stat_log_heads(s), 0xFF, sizeof(int32) * (Size) s->max_entries); + pgstat_end_changecount_write(&shmem->changecount); + + shmem->stat_reset_timestamp = GetCurrentTimestamp(); + shmem->n_dropped = 0; + + LWLockRelease(&shmem->lock); + } +} + +/* + * init_shmem_cb — initialize shared memory + * + * Only runs in the postmaster (see StatsShmemInit), after the main LWLock + * array has been set up. This is the first place it is safe to call + * LWLockNewTrancheId(), which needs shared memory to exist. + */ +static void +pg_stat_log_init_shmem_cb(void *stats) +{ + PgStatLogShared *shmem = (PgStatLogShared *) stats; + PgStatLog *s; + + LWLockInitialize(&shmem->lock, LWLockNewTrancheId(PGSTAT_LOG_TRANCHE_NAME)); + shmem->stat_reset_timestamp = GetCurrentTimestamp(); + shmem->n_dropped = 0; + + s = pg_stat_log_get_stats(shmem); + s->max_entries = pg_stat_log_max; + s->num_entries = 0; + memset(pg_stat_log_heads(s), 0xFF, sizeof(int32) * (Size) s->max_entries); +} + +/* + * reset_all_cb — reset statistics + * + * Empty all bucket chains and reset num_entries so slots are reclaimed for + * reuse. Otherwise, once max_entries is reached, a reset would not free + * capacity for new distinct combinations. + */ +static void +pg_stat_log_reset_all_cb(TimestampTz ts) +{ + PgStatLogShared *shmem; + PgStatLog *s; + + shmem = (PgStatLogShared *) pgstat_get_custom_shmem_data(PGSTAT_KIND_LOG); + s = pg_stat_log_get_stats(shmem); + + LWLockAcquire(&shmem->lock, LW_EXCLUSIVE); + + pgstat_begin_changecount_write(&shmem->changecount); + s->num_entries = 0; + memset(pg_stat_log_heads(s), 0xFF, sizeof(int32) * (Size) s->max_entries); + pgstat_end_changecount_write(&shmem->changecount); + + shmem->stat_reset_timestamp = ts; + shmem->n_dropped = 0; + + LWLockRelease(&shmem->lock); +} + +/* + * snapshot_cb — build snapshot for reads + */ +static void +pg_stat_log_snapshot_cb(void) +{ + PgStatLogShared *shmem; + PgStatLog *snap; + + shmem = (PgStatLogShared *) pgstat_get_custom_shmem_data(PGSTAT_KIND_LOG); + snap = (PgStatLog *) pgstat_get_custom_snapshot_data(PGSTAT_KIND_LOG); + + /* Copy current stats via changecount protocol */ + pgstat_copy_changecounted_stats(snap, + pg_stat_log_get_stats(shmem), + stats_block_size, + &shmem->changecount); +} + +/* + * pg_stat_log_count_message — record one log message in shared memory + * + * Acquires the LWLock, walks the hash bucket chain to find or create the + * entry, increments the counter, and releases the lock. The chain walk is + * O(1) expected (average chain length = load factor) for all of lookup, + * insert, and drop, even when the table is full. + */ +static void +pg_stat_log_count_message(ErrorData *edata) +{ + PgStatLogShared *shmem; + PgStatLog *s; + PgStatLogSlot *entries; + int32 *heads; + Oid dboid; + Oid userid; + int sec_context; + uint32 hash; + uint32 bucket; + int32 idx; + bool found; + + shmem = (PgStatLogShared *) pgstat_get_custom_shmem_data(PGSTAT_KIND_LOG); + + dboid = MyDatabaseId; + GetUserIdAndSecContext(&userid, &sec_context); + + LWLockAcquire(&shmem->lock, LW_EXCLUSIVE); + + s = pg_stat_log_get_stats(shmem); + entries = pg_stat_log_entries(s); + heads = pg_stat_log_heads(s); + + hash = pg_stat_log_hash_key(MyBackendType, dboid, userid, edata->elevel, edata->sqlerrcode); + bucket = hash % s->max_entries; + + found = false; + for (idx = heads[bucket]; idx != -1; idx = entries[idx].next) + { + PgStatLogSlot *slot = &entries[idx]; + + if (slot->backend_type == MyBackendType && slot->dboid == dboid && slot->userid == userid && + slot->elevel == edata->elevel && slot->sqlerrcode == edata->sqlerrcode) + { + pgstat_begin_changecount_write(&shmem->changecount); + slot->count++; + pgstat_end_changecount_write(&shmem->changecount); + found = true; + break; + } + } + + if (!found) + { + if (s->num_entries < s->max_entries) + { + int32 newidx = s->num_entries; + PgStatLogSlot *slot = &entries[newidx]; + + pgstat_begin_changecount_write(&shmem->changecount); + slot->backend_type = MyBackendType; + slot->dboid = dboid; + slot->userid = userid; + slot->elevel = edata->elevel; + slot->sqlerrcode = edata->sqlerrcode; + slot->count = 1; + slot->next = heads[bucket]; + heads[bucket] = newidx; + s->num_entries++; + pgstat_end_changecount_write(&shmem->changecount); + } + else + { + shmem->n_dropped++; + } + } + + LWLockRelease(&shmem->lock); +} + +/* + * emit_log_hook — intercept log messages for counting + * + * Always forwards to the previous hook in the chain. Only counts the + * message if pg_stat_log is enabled and the severity meets the threshold. + */ +static void +pg_stat_log_emit_hook(ErrorData *edata) +{ + if (in_emit_log_hook) + return; + + /* + * pgstat shared memory might not be set up yet during early startup or + * in auxiliary processes before attachment. + */ + if ((!IsUnderPostmaster && IsPostmasterEnvironment) || !MyProc) + return; + + in_emit_log_hook = true; + PG_TRY(); + { + if (prev_emit_log_hook) + prev_emit_log_hook(edata); + + if (pg_stat_log_enabled && edata->elevel >= pg_stat_log_min_elevel) + pg_stat_log_count_message(edata); + } + PG_FINALLY(); + { + in_emit_log_hook = false; + } + PG_END_TRY(); +} + +/* + * Module initialization + */ +void +_PG_init(void) +{ + Size shared_size; + + if (!process_shared_preload_libraries_in_progress) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("pg_stat_log must be loaded via " + "shared_preload_libraries"))); + + /* Define GUCs before computing sizes */ + DefineCustomBoolVariable("pg_stat_log.enabled", + "Enable collection of log statistics.", + NULL, + &pg_stat_log_enabled, + true, + PGC_SUSET, + 0, + NULL, + NULL, + NULL); + + DefineCustomEnumVariable("pg_stat_log.min_error_level", + "Minimum error level to track.", + NULL, + &pg_stat_log_min_elevel, + WARNING, + server_message_level_options, + PGC_SUSET, + 0, + NULL, + NULL, + NULL); + + DefineCustomIntVariable("pg_stat_log.max_entries", + "Maximum number of distinct log entry " + "combinations to track.", + NULL, + &pg_stat_log_max, + PGSTAT_LOG_MAX_DEFAULT, + PGSTAT_LOG_MIN_ENTRIES, + PGSTAT_LOG_MAX_ENTRIES, + PGC_POSTMASTER, + 0, + NULL, + NULL, + NULL); + + MarkGUCPrefixReserved("pg_stat_log"); + + /* Compute sizes based on pg_stat_log.max_entries */ + stats_block_size = offsetof(PgStatLog, data) + + sizeof(PgStatLogSlot) * (Size) pg_stat_log_max + + sizeof(int32) * (Size) pg_stat_log_max; + + shared_size = offsetof(PgStatLogShared, data) + stats_block_size; + + /* Fill in the KindInfo struct — use memcpy because .name is const */ + { + PgStat_KindInfo tmp = { + .name = "pg_stat_log", + .fixed_amount = true, + .write_to_file = true, + .shared_size = shared_size, + .shared_data_off = offsetof(PgStatLogShared, data), + .shared_data_len = stats_block_size, + .init_backend_cb = pg_stat_log_init_backend_cb, + .init_shmem_cb = pg_stat_log_init_shmem_cb, + .reset_all_cb = pg_stat_log_reset_all_cb, + .snapshot_cb = pg_stat_log_snapshot_cb, + }; + + memcpy(&log_stats_kind, &tmp, sizeof(PgStat_KindInfo)); + } + + pgstat_register_kind(PGSTAT_KIND_LOG, &log_stats_kind); + + /* Install emit_log_hook */ + prev_emit_log_hook = emit_log_hook; + emit_log_hook = pg_stat_log_emit_hook; +} + +/* + * SQL-callable functions + */ +PG_FUNCTION_INFO_V1(pg_stat_log_data); + +/* + * pg_stat_log_data() + * Return all tracked log statistics as a set of rows. + */ +Datum +pg_stat_log_data(PG_FUNCTION_ARGS) +{ + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + PgStatLog *snap; + PgStatLogSlot *entries; + int i; + + pgstat_snapshot_fixed(PGSTAT_KIND_LOG); + snap = (PgStatLog *) pgstat_get_custom_snapshot_data(PGSTAT_KIND_LOG); + + InitMaterializedSRF(fcinfo, 0); + + entries = pg_stat_log_entries(snap); + + for (i = 0; i < snap->num_entries; i++) + { + Datum values[7]; + bool nulls[7] = {0}; + PgStatLogSlot *slot = &entries[i]; + const char *errname; + + if (slot->count <= 0) + continue; + + values[0] = CStringGetTextDatum(GetBackendTypeDesc(slot->backend_type)); + + if (OidIsValid(slot->dboid)) + values[1] = ObjectIdGetDatum(slot->dboid); + else + nulls[1] = true; + + if (OidIsValid(slot->userid)) + values[2] = ObjectIdGetDatum(slot->userid); + else + nulls[2] = true; + + values[3] = CStringGetTextDatum(error_severity(slot->elevel)); + values[4] = CStringGetTextDatum(unpack_sql_state(slot->sqlerrcode)); + + errname = pg_stat_log_errcode_name(slot->sqlerrcode); + if (errname) + values[5] = CStringGetTextDatum(errname); + else + nulls[5] = true; + + values[6] = Int64GetDatum(slot->count); + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); + } + + return (Datum) 0; +} + +PG_FUNCTION_INFO_V1(pg_stat_log_reset); + +/* + * pg_stat_log_reset() + * Reset all tracked log statistics. + */ +Datum +pg_stat_log_reset(PG_FUNCTION_ARGS) +{ + pgstat_reset_of_kind(PGSTAT_KIND_LOG); + + PG_RETURN_VOID(); +} + +PG_FUNCTION_INFO_V1(pg_stat_log_info); + +/* + * pg_stat_log_info() + * Return metadata about the pg_stat_log shared memory area: + * max_entries capacity, current num_entries, number of dropped + * messages due to capacity, and the last reset timestamp. + */ +Datum +pg_stat_log_info(PG_FUNCTION_ARGS) +{ + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + PgStatLogShared *shmem; + PgStatLog *s; + Datum values[4]; + bool nulls[4] = {0}; + int max_entries; + int num_entries; + uint64 n_dropped; + TimestampTz stat_reset_timestamp; + + InitMaterializedSRF(fcinfo, 0); + + shmem = (PgStatLogShared *) pgstat_get_custom_shmem_data(PGSTAT_KIND_LOG); + s = pg_stat_log_get_stats(shmem); + + LWLockAcquire(&shmem->lock, LW_SHARED); + max_entries = s->max_entries; + num_entries = s->num_entries; + n_dropped = shmem->n_dropped; + stat_reset_timestamp = shmem->stat_reset_timestamp; + LWLockRelease(&shmem->lock); + + values[0] = Int32GetDatum(max_entries); + values[1] = Int32GetDatum(num_entries); + values[2] = Int64GetDatum((int64) n_dropped); + values[3] = TimestampTzGetDatum(stat_reset_timestamp); + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); + + return (Datum) 0; +} diff --git a/contrib/pg_stat_log/pg_stat_log.conf b/contrib/pg_stat_log/pg_stat_log.conf new file mode 100644 index 00000000000..65581a3efb9 --- /dev/null +++ b/contrib/pg_stat_log/pg_stat_log.conf @@ -0,0 +1 @@ +shared_preload_libraries = 'pg_stat_log' diff --git a/contrib/pg_stat_log/pg_stat_log.control b/contrib/pg_stat_log/pg_stat_log.control new file mode 100644 index 00000000000..204c113f04b --- /dev/null +++ b/contrib/pg_stat_log/pg_stat_log.control @@ -0,0 +1,4 @@ +comment = 'cumulative statistics about log messages' +default_version = '0.1' +module_pathname = '$libdir/pg_stat_log' +relocatable = true diff --git a/contrib/pg_stat_log/sql/pg_stat_log.sql b/contrib/pg_stat_log/sql/pg_stat_log.sql new file mode 100644 index 00000000000..64bb1813454 --- /dev/null +++ b/contrib/pg_stat_log/sql/pg_stat_log.sql @@ -0,0 +1,119 @@ +-- +-- pg_stat_log regression tests +-- + +CREATE EXTENSION pg_stat_log; + +-- Start clean +SELECT pg_stat_log_reset(); +SELECT pg_stat_force_next_flush(); + +-- +-- Test 1: Warnings are counted +-- +DO $$ BEGIN RAISE WARNING 'test warning 1'; END $$; +DO $$ BEGIN RAISE WARNING 'test warning 2'; END $$; +DO $$ BEGIN RAISE WARNING 'test warning 3'; END $$; + +SELECT pg_stat_force_next_flush(); + +SELECT count >= 3 AS warning_count_ok +FROM pg_stat_log_data() +WHERE elevel = 'WARNING' AND sqlerrcode = '01000'; + +-- +-- Test 2: Errors are tracked +-- +SELECT 1/0; + +SELECT pg_stat_force_next_flush(); + +SELECT count >= 1 AS division_by_zero_ok +FROM pg_stat_log_data() +WHERE elevel = 'ERROR' AND sqlerrcode = '22012'; + +-- +-- Test 3: pg_stat_log view works (returns rows with database/user names) +-- +SELECT count(*) > 0 AS view_has_rows FROM pg_stat_log WHERE count > 0; + +-- +-- Test 4: Disable via GUC stops counting +-- +SET pg_stat_log.enabled = off; + +DO $$ BEGIN RAISE WARNING 'should not be counted'; END $$; + +SELECT pg_stat_force_next_flush(); + +-- The warning count should not have increased; we check by looking for the +-- specific message-related sqlerrcode that was already counted before. +SELECT count >= 3 AS still_same_warning_count +FROM pg_stat_log_data() +WHERE elevel = 'WARNING' AND sqlerrcode = '01000'; + +SET pg_stat_log.enabled = on; + +-- +-- Test 5: min_error_level filtering +-- +SET pg_stat_log.min_error_level = 'error'; + +-- Record warning count before +SELECT count AS cnt_before +FROM pg_stat_log_data() +WHERE elevel = 'WARNING' AND sqlerrcode = '01000' \gset + +DO $$ BEGIN RAISE WARNING 'filtered out'; END $$; + +SELECT pg_stat_force_next_flush(); + +-- Warning count should be unchanged +SELECT count = :cnt_before AS warning_filtered_ok +FROM pg_stat_log_data() +WHERE elevel = 'WARNING' AND sqlerrcode = '01000'; + +SET pg_stat_log.min_error_level = 'warning'; + +-- +-- Test 6: Reset zeroes counters +-- +SELECT pg_stat_log_reset(); + +SELECT pg_stat_force_next_flush(); + +SELECT COALESCE(sum(count), 0) = 0 AS reset_ok FROM pg_stat_log_data(); + + +-- +-- Test 7: pg_stat_log_info() returns one row with expected columns +-- +SELECT count(*) = 1 AS info_one_row FROM pg_stat_log_info(); + +-- +-- Test 8: max_entries matches GUC +-- +SELECT max_entries = current_setting('pg_stat_log.max_entries')::int AS max_matches_guc +FROM pg_stat_log_info(); + +-- +-- Test 9: After reset, num_entries and n_dropped are zero +-- +SELECT pg_stat_log_reset(); +SELECT pg_stat_force_next_flush(); + +SELECT num_entries = 0 AS num_zero, n_dropped = 0 AS dropped_zero +FROM pg_stat_log_info(); + + +-- +-- Test 10: pg_stat_log_reset() is restricted to superusers +-- +CREATE ROLE regress_pg_stat_log_user; +SET ROLE regress_pg_stat_log_user; +SELECT pg_stat_log_reset(); +RESET ROLE; +DROP ROLE regress_pg_stat_log_user; + +-- Clean up +DROP EXTENSION pg_stat_log; diff --git a/contrib/pg_stat_log/t/001_pg_stat_log.pl b/contrib/pg_stat_log/t/001_pg_stat_log.pl new file mode 100644 index 00000000000..895e36e3995 --- /dev/null +++ b/contrib/pg_stat_log/t/001_pg_stat_log.pl @@ -0,0 +1,250 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Test pg_stat_log persistence behavior +# +# These tests require server restart/crash and cannot be covered by +# regular regression tests. +# +# Verifies: +# - Stats persist across clean restart +# - Stats are lost after crash recovery + +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $node = PostgreSQL::Test::Cluster->new('main'); +$node->init; +$node->append_conf('postgresql.conf', + "shared_preload_libraries = 'pg_stat_log'"); +$node->append_conf('postgresql.conf', + "pg_stat_log.min_error_level = 'warning'"); +$node->start; + +$node->safe_psql('postgres', q(CREATE EXTENSION pg_stat_log)); + +# Generate some data to persist +$node->safe_psql('postgres', q( + DO $$ BEGIN RAISE WARNING 'persist test'; END $$; +)); +$node->psql('postgres', q(SELECT 1/0)); + +$node->safe_psql('postgres', q(SELECT pg_stat_force_next_flush())); + +my $result = $node->safe_psql('postgres', q( + SELECT count FROM pg_stat_log_data() + WHERE elevel = 'ERROR' AND sqlerrcode = '22012' +)); +my $error_count_pre_restart = $result; + +# --------------------------------------------------------------- +# Test 1: Stats persist across clean restart +# --------------------------------------------------------------- + +$node->stop; +$node->start; + +$result = $node->safe_psql('postgres', q( + SELECT count FROM pg_stat_log_data() + WHERE elevel = 'ERROR' AND sqlerrcode = '22012' +)); +is($result, $error_count_pre_restart, + "error count persists after clean restart"); + + +# --------------------------------------------------------------- +# Test 1b: n_dropped and stats_reset do not persist across restart +# --------------------------------------------------------------- + +my $n_dropped = $node->safe_psql('postgres', q( + SELECT n_dropped FROM pg_stat_log_info() +)); +is($n_dropped, "0", + "n_dropped resets to 0 after clean restart"); + +my $reset_ts = $node->safe_psql('postgres', q( + SELECT stats_reset FROM pg_stat_log_info() +)); +ok(defined $reset_ts && $reset_ts ne '', + "stats_reset is set to startup timestamp after clean restart"); + +# --------------------------------------------------------------- +# Test 2: Stats lost after crash recovery +# --------------------------------------------------------------- + +$node->stop('immediate'); +$node->start; + +$result = $node->safe_psql('postgres', q( + SELECT COALESCE(sum(count), 0) FROM pg_stat_log_data() +)); +is($result, "0", "all counts are zero after crash recovery"); + + +# --------------------------------------------------------------- +# Test 3: pg_stat_log_info() basics +# --------------------------------------------------------------- + +my $info_rows = $node->safe_psql('postgres', q( + SELECT count(*) FROM pg_stat_log_info() +)); +is($info_rows, "1", "pg_stat_log_info() returns one row"); + +my $max_entries = $node->safe_psql('postgres', q( + SELECT max_entries FROM pg_stat_log_info() +)); +my $guc_max = $node->safe_psql('postgres', + q(SHOW pg_stat_log.max_entries)); +is($max_entries, $guc_max, + "pg_stat_log_info.max_entries matches GUC pg_stat_log.max_entries"); + +# --------------------------------------------------------------- +# Test 4: stats_reset advances on reset +# --------------------------------------------------------------- + +my $reset_before = $node->safe_psql('postgres', q( + SELECT extract(epoch FROM stats_reset)::numeric FROM pg_stat_log_info() +)); +$node->safe_psql('postgres', + q(SELECT pg_sleep(0.1); SELECT pg_stat_log_reset();)); +my $reset_after = $node->safe_psql('postgres', q( + SELECT extract(epoch FROM stats_reset)::numeric FROM pg_stat_log_info() +)); +ok($reset_after > $reset_before, + "stats_reset timestamp advances after pg_stat_log_reset()"); + +# --------------------------------------------------------------- +# Test 5: n_dropped increments and reset reclaims slots +# --------------------------------------------------------------- + +$node->stop('immediate'); +$node->append_conf('postgresql.conf', "pg_stat_log.max_entries = 64"); +$node->start; + +$max_entries = $node->safe_psql('postgres', q( + SELECT max_entries FROM pg_stat_log_info() +)); +is($max_entries, "64", "max_entries reflects restart-scoped GUC"); + +# Generate 100 distinct SQLSTATE codes to overflow the 64-slot capacity +$node->safe_psql('postgres', q{ + DO $$ + DECLARE + i int; + code text; + BEGIN + FOR i IN 1..100 LOOP + code := 'Z' || lpad(i::text, 4, '0'); + BEGIN + RAISE WARNING 'overflow test %', i USING ERRCODE = code; + EXCEPTION WHEN OTHERS THEN + NULL; + END; + END LOOP; + END $$; +}); +$node->safe_psql('postgres', q(SELECT pg_stat_force_next_flush())); + +my $num_entries = $node->safe_psql('postgres', q( + SELECT num_entries FROM pg_stat_log_info() +)); +is($num_entries, "64", "num_entries saturates at max_entries"); + +$n_dropped = $node->safe_psql('postgres', q( + SELECT n_dropped FROM pg_stat_log_info() +)); +ok($n_dropped > 0, "n_dropped > 0 after overflowing max_entries"); + +# An already-tracked signature must keep counting even when the table is full +my $tracked_code = $node->safe_psql('postgres', q( + SELECT sqlerrcode FROM pg_stat_log_data() LIMIT 1 +)); +my $count_before = $node->safe_psql('postgres', + "SELECT count FROM pg_stat_log_data() WHERE sqlerrcode = '$tracked_code'"); +$node->safe_psql('postgres', + "DO \$\$ BEGIN RAISE WARNING 'still counts' USING ERRCODE = '$tracked_code'; END \$\$;"); +$node->safe_psql('postgres', q(SELECT pg_stat_force_next_flush())); +my $count_after = $node->safe_psql('postgres', + "SELECT count FROM pg_stat_log_data() WHERE sqlerrcode = '$tracked_code'"); +cmp_ok($count_after, '>', $count_before, + "tracked signature still counts when table is full"); + +# Reset should reclaim slots +$node->safe_psql('postgres', q(SELECT pg_stat_log_reset())); + +$num_entries = $node->safe_psql('postgres', q( + SELECT num_entries FROM pg_stat_log_info() +)); +is($num_entries, "0", "num_entries is 0 after reset when saturated"); + +$n_dropped = $node->safe_psql('postgres', q( + SELECT n_dropped FROM pg_stat_log_info() +)); +is($n_dropped, "0", "n_dropped is 0 after reset"); + +# Generate a NEW distinct error and verify it is tracked (slot reclaimed) +$node->safe_psql('postgres', q{ + DO $$ + BEGIN + RAISE WARNING 'post-reset' USING ERRCODE = 'Z9999'; + EXCEPTION WHEN OTHERS THEN + NULL; + END $$; +}); +$node->safe_psql('postgres', q(SELECT pg_stat_force_next_flush())); + +my $post_reset = $node->safe_psql('postgres', q( + SELECT count FROM pg_stat_log_data() WHERE sqlerrcode = 'Z9999' +)); +is($post_reset, "1", + "new distinct error is tracked after reset (slots reclaimed)"); + + +# --------------------------------------------------------------- +# Test 6: Stats discarded when max_entries changes across restart +# --------------------------------------------------------------- + +# Generate some stats with the current max_entries=64 +$node->safe_psql('postgres', q(SELECT pg_stat_log_reset())); +$node->safe_psql('postgres', q{ + DO $$ BEGIN RAISE WARNING 'before resize'; END $$; +}); +$node->safe_psql('postgres', q(SELECT pg_stat_force_next_flush())); + +$num_entries = $node->safe_psql('postgres', q( + SELECT num_entries FROM pg_stat_log_info() +)); +ok($num_entries > 0, "have entries before max_entries change"); + +# Clean restart with a different max_entries +$node->stop; +$node->append_conf('postgresql.conf', "pg_stat_log.max_entries = 128"); +$node->start; + +# Stats should have been discarded due to capacity mismatch +$max_entries = $node->safe_psql('postgres', q( + SELECT max_entries FROM pg_stat_log_info() +)); +is($max_entries, "128", "max_entries reflects new GUC after restart"); + +$num_entries = $node->safe_psql('postgres', q( + SELECT num_entries FROM pg_stat_log_info() +)); +is($num_entries, "0", + "persisted stats discarded after max_entries change"); + +# Verify new entries can be tracked with the new capacity +$node->safe_psql('postgres', q{ + DO $$ BEGIN RAISE WARNING 'after resize'; END $$; +}); +$node->safe_psql('postgres', q(SELECT pg_stat_force_next_flush())); + +$num_entries = $node->safe_psql('postgres', q( + SELECT num_entries FROM pg_stat_log_info() +)); +ok($num_entries > 0, "new entries tracked after max_entries change"); + +done_testing(); diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index b9b03654aad..d037c584960 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -160,6 +160,7 @@ CREATE EXTENSION extension_name; &pgprewarm; &pgrowlocks; &pgstashadvice; + &pgstatlog; &pgstatstatements; &pgstattuple; &pgsurgery; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 0797dcf96da..90aa2de837b 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -153,6 +153,7 @@ + diff --git a/doc/src/sgml/pgstatlog.sgml b/doc/src/sgml/pgstatlog.sgml new file mode 100644 index 00000000000..e190ebbb3e7 --- /dev/null +++ b/doc/src/sgml/pgstatlog.sgml @@ -0,0 +1,318 @@ + + + + pg_stat_log — cumulative statistics about log messages + + + pg_stat_log + + + + The pg_stat_log module provides a means for tracking + cumulative statistics about server log messages. It hooks into the server's + logging system and counts emitted messages, grouped by backend type, + database, user, error severity level, and SQLSTATE code. + + + + The module must be loaded by adding pg_stat_log to + in + postgresql.conf, because it requires additional shared + memory. This means that a server restart is needed to add or remove the + module. + + + + When pg_stat_log is loaded, it tracks statistics across + all databases of the cluster. To access and manipulate these statistics, + the module provides the views pg_stat_log and a set + of functions. These are not available globally but can be enabled for a + specific database with CREATE EXTENSION pg_stat_log. + + + + The <structname>pg_stat_log</structname> View + + + The statistics gathered by the module are made available via a view named + pg_stat_log. This view contains one row for each + distinct combination of backend type, database, user, error level, and + SQLSTATE code observed since the statistics were last reset. The columns of + the view are shown in . + + + + <structname>pg_stat_log</structname> Columns + + + + + Column Type + + + Description + + + + + + + + backend_type text + + + Type of the backend that emitted the message (for example, + client backend, autovacuum worker, + checkpointer). + + + + + + database_oid oid + + + OID of the database the message was emitted in, or NULL + for shared objects or messages emitted before a database was selected. + + + + + + database_name text + + + Name of the database, resolved from pg_database. + NULL if the database OID is unset or the database has + been dropped. + + + + + + user_oid oid + + + OID of the role active when the message was emitted, or + NULL for background processes. + + + + + + user_name text + + + Name of the role, resolved from pg_roles. + NULL if the role OID is unset or the role has been + dropped. + + + + + + elevel text + + + Error severity level of the message (for example, WARNING, + ERROR, FATAL, PANIC). + + + + + + sqlerrcode text + + + Five-character SQLSTATE code associated with the message. + + + + + + sqlerrcode_name text + + + Human-readable condition name for the SQLSTATE code (for example, + division_by_zero), or NULL if the + code has no associated name. + + + + + + count bigint + + + Cumulative number of messages observed for this combination. + + + + +
+ + + For security reasons, only superusers and roles with privileges of the + pg_read_all_stats role are allowed to read the + pg_stat_log view. + + + + + Functions + + + + + pg_stat_log_data() returns setof record + + pg_stat_log_data + + + + + + The underlying function backing the pg_stat_log + view. It returns one row per tracked combination, exposing the raw + database_oid and user_oid + values without resolving them to names. Access is restricted to + superusers and members of pg_read_all_stats. + + + + + + + pg_stat_log_info() returns record + + pg_stat_log_info + + + + + + Returns a single row of metadata about the tracking state: + max_entries (the configured capacity), + num_entries (combinations currently tracked), + n_dropped (messages that could not be tracked + because pg_stat_log.max_entries was reached), and + stats_reset (the time of the last reset, or of + shared-memory initialization). Access is restricted to superusers and + members of pg_read_all_stats. + + + + + + + pg_stat_log_reset() returns void + + pg_stat_log_reset + + + + + + Discards all statistics gathered so far and reclaims the tracking slots. + By default this function can only be executed by superusers. + + + + + + + + Configuration Parameters + + + + + pg_stat_log.enabled (boolean) + + pg_stat_log.enabled configuration parameter + + + + + Controls whether the module counts log messages. The default is + on. Only superusers can change this setting. + + + + + + + pg_stat_log.min_error_level (enum) + + pg_stat_log.min_error_level configuration parameter + + + + + Minimum message severity level that is counted. The default is + warning. Note that this is additionally bounded by + , which determines what reaches the + server log in the first place. Only superusers can change this setting. + + + + + + + pg_stat_log.max_entries (integer) + + pg_stat_log.max_entries configuration parameter + + + + + Maximum number of distinct combinations that can be tracked + simultaneously. The default is 1024. Once this limit + is reached, new combinations are not tracked until + pg_stat_log_reset() is called; such drops are + counted in n_dropped. Each tracked combination + consumes shared memory, so this value must be chosen with the overall + shared-memory budget in mind. This parameter can only be set at server + start. + + + + + + + + Notes + + + Because the module hooks into the logging system, it only ever sees messages + that actually reach the server log. Messages filtered out by + , and exceptions caught within + PL/pgSQL BEGIN ... EXCEPTION + blocks, are never counted. + + + + Statistics are kept in shared memory and persist across a clean server + restart, but are discarded after a crash, following the standard cumulative + statistics semantics. The n_dropped and + stats_reset values reported by + pg_stat_log_info() are reinitialized at server startup. + + + + The library remains active for as long as it is listed in + shared_preload_libraries; dropping the SQL extension with + DROP EXTENSION removes the views and functions but does + not stop the underlying collection. + + + + + Author + + + Fabrízio de Royes Mello fabriziomello@gmail.com + + + + -- 2.55.0