diff options
Diffstat (limited to 'contrib/pg_walinspect')
| -rw-r--r-- | contrib/pg_walinspect/.gitignore | 4 | ||||
| -rw-r--r-- | contrib/pg_walinspect/Makefile | 29 | ||||
| -rw-r--r-- | contrib/pg_walinspect/expected/oldextversions.out | 69 | ||||
| -rw-r--r-- | contrib/pg_walinspect/expected/pg_walinspect.out | 262 | ||||
| -rw-r--r-- | contrib/pg_walinspect/meson.build | 40 | ||||
| -rw-r--r-- | contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql | 42 | ||||
| -rw-r--r-- | contrib/pg_walinspect/pg_walinspect--1.0.sql | 118 | ||||
| -rw-r--r-- | contrib/pg_walinspect/pg_walinspect.c | 851 | ||||
| -rw-r--r-- | contrib/pg_walinspect/pg_walinspect.control | 5 | ||||
| -rw-r--r-- | contrib/pg_walinspect/sql/oldextversions.sql | 39 | ||||
| -rw-r--r-- | contrib/pg_walinspect/sql/pg_walinspect.sql | 157 | ||||
| -rw-r--r-- | contrib/pg_walinspect/walinspect.conf | 2 |
12 files changed, 1618 insertions, 0 deletions
diff --git a/contrib/pg_walinspect/.gitignore b/contrib/pg_walinspect/.gitignore new file mode 100644 index 0000000..5dcb3ff --- /dev/null +++ b/contrib/pg_walinspect/.gitignore @@ -0,0 +1,4 @@ +# Generated subdirectories +/log/ +/results/ +/tmp_check/ diff --git a/contrib/pg_walinspect/Makefile b/contrib/pg_walinspect/Makefile new file mode 100644 index 0000000..22090f7 --- /dev/null +++ b/contrib/pg_walinspect/Makefile @@ -0,0 +1,29 @@ +# contrib/pg_walinspect/Makefile + +MODULE_big = pg_walinspect +OBJS = \ + $(WIN32RES) \ + pg_walinspect.o +PGFILEDESC = "pg_walinspect - functions to inspect contents of PostgreSQL Write-Ahead Log" + +EXTENSION = pg_walinspect +DATA = pg_walinspect--1.0.sql pg_walinspect--1.0--1.1.sql + +REGRESS = pg_walinspect oldextversions + +REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_walinspect/walinspect.conf + +# Disabled because these tests require "wal_level=replica", which +# some installcheck users do not have (e.g. buildfarm clients). +NO_INSTALLCHECK = 1 + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/pg_walinspect +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/pg_walinspect/expected/oldextversions.out b/contrib/pg_walinspect/expected/oldextversions.out new file mode 100644 index 0000000..89953af --- /dev/null +++ b/contrib/pg_walinspect/expected/oldextversions.out @@ -0,0 +1,69 @@ +-- Test old extension version entry points. +CREATE EXTENSION pg_walinspect WITH VERSION '1.0'; +-- Mask DETAIL messages as these could refer to current LSN positions. +\set VERBOSITY terse +-- List what version 1.0 contains, using a locale-independent sorting. +SELECT pg_describe_object(classid, objid, 0) AS obj + FROM pg_depend + WHERE refclassid = 'pg_extension'::regclass AND + refobjid = (SELECT oid FROM pg_extension + WHERE extname = 'pg_walinspect') AND deptype = 'e' + ORDER BY pg_describe_object(classid, objid, 0) COLLATE "C"; + obj +----------------------------------------------------------- + function pg_get_wal_record_info(pg_lsn) + function pg_get_wal_records_info(pg_lsn,pg_lsn) + function pg_get_wal_records_info_till_end_of_wal(pg_lsn) + function pg_get_wal_stats(pg_lsn,pg_lsn,boolean) + function pg_get_wal_stats_till_end_of_wal(pg_lsn,boolean) +(5 rows) + +-- Make sure checkpoints don't interfere with the test. +SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false); + ?column? +---------- + init +(1 row) + +CREATE TABLE sample_tbl(col1 int, col2 int); +SELECT pg_current_wal_lsn() AS wal_lsn1 \gset +INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2); +-- Tests for the past functions. +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info_till_end_of_wal(:'wal_lsn1'); + ok +---- + t +(1 row) + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats_till_end_of_wal(:'wal_lsn1'); + ok +---- + t +(1 row) + +-- Failures with start LSNs. +SELECT * FROM pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF'); +ERROR: WAL start LSN must be less than current LSN +SELECT * FROM pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF'); +ERROR: WAL start LSN must be less than current LSN +-- Move to new version 1.1. +ALTER EXTENSION pg_walinspect UPDATE TO '1.1'; +-- List what version 1.1 contains. +\dx+ pg_walinspect + Objects in extension "pg_walinspect" + Object description +------------------------------------------------------- + function pg_get_wal_block_info(pg_lsn,pg_lsn,boolean) + function pg_get_wal_record_info(pg_lsn) + function pg_get_wal_records_info(pg_lsn,pg_lsn) + function pg_get_wal_stats(pg_lsn,pg_lsn,boolean) +(4 rows) + +SELECT pg_drop_replication_slot('regress_pg_walinspect_slot'); + pg_drop_replication_slot +-------------------------- + +(1 row) + +DROP TABLE sample_tbl; +DROP EXTENSION pg_walinspect; diff --git a/contrib/pg_walinspect/expected/pg_walinspect.out b/contrib/pg_walinspect/expected/pg_walinspect.out new file mode 100644 index 0000000..a8f4c91 --- /dev/null +++ b/contrib/pg_walinspect/expected/pg_walinspect.out @@ -0,0 +1,262 @@ +CREATE EXTENSION pg_walinspect; +-- Mask DETAIL messages as these could refer to current LSN positions. +\set VERBOSITY terse +-- Make sure checkpoints don't interfere with the test. +SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false); + ?column? +---------- + init +(1 row) + +CREATE TABLE sample_tbl(col1 int, col2 int); +-- Save some LSNs for comparisons. +SELECT pg_current_wal_lsn() AS wal_lsn1 \gset +INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2); +SELECT pg_current_wal_lsn() AS wal_lsn2 \gset +INSERT INTO sample_tbl SELECT * FROM generate_series(3, 4); +-- =================================================================== +-- Tests for input validation +-- =================================================================== +-- Invalid input LSN. +SELECT * FROM pg_get_wal_record_info('0/0'); +ERROR: could not read WAL at LSN 0/0 +-- Invalid start LSN. +SELECT * FROM pg_get_wal_records_info('0/0', :'wal_lsn1'); +ERROR: could not read WAL at LSN 0/0 +SELECT * FROM pg_get_wal_stats('0/0', :'wal_lsn1'); +ERROR: could not read WAL at LSN 0/0 +SELECT * FROM pg_get_wal_block_info('0/0', :'wal_lsn1'); +ERROR: could not read WAL at LSN 0/0 +-- Start LSN > End LSN. +SELECT * FROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1'); +ERROR: WAL start LSN must be less than end LSN +SELECT * FROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1'); +ERROR: WAL start LSN must be less than end LSN +SELECT * FROM pg_get_wal_block_info(:'wal_lsn2', :'wal_lsn1'); +ERROR: WAL start LSN must be less than end LSN +-- LSNs with the highest value possible. +SELECT * FROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF'); +ERROR: WAL input LSN must be less than current LSN +-- Success with end LSNs. +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF'); + ok +---- + t +(1 row) + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF'); + ok +---- + t +(1 row) + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF'); + ok +---- + t +(1 row) + +-- Failures with start LSNs. +SELECT * FROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF'); +ERROR: WAL start LSN must be less than current LSN +SELECT * FROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF'); +ERROR: WAL start LSN must be less than current LSN +SELECT * FROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF'); +ERROR: WAL start LSN must be less than current LSN +-- =================================================================== +-- Tests for all function executions +-- =================================================================== +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_record_info(:'wal_lsn1'); + ok +---- + t +(1 row) + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'); + ok +---- + t +(1 row) + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2'); + ok +---- + t +(1 row) + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2'); + ok +---- + t +(1 row) + +-- =================================================================== +-- Test for filtering out WAL records of a particular table +-- =================================================================== +SELECT oid AS sample_tbl_oid FROM pg_class WHERE relname = 'sample_tbl' \gset +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2') + WHERE block_ref LIKE concat('%', :'sample_tbl_oid', '%') AND resource_manager = 'Heap'; + ok +---- + t +(1 row) + +-- =================================================================== +-- Test for filtering out WAL records based on resource_manager and +-- record_type +-- =================================================================== +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2') + WHERE resource_manager = 'Heap' AND record_type = 'INSERT'; + ok +---- + t +(1 row) + +-- =================================================================== +-- Tests to get block information from WAL record +-- =================================================================== +-- Update table to generate some block data. +SELECT pg_current_wal_lsn() AS wal_lsn3 \gset +UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 1; +SELECT pg_current_wal_lsn() AS wal_lsn4 \gset +-- Check if we get block data from WAL record. +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn3', :'wal_lsn4') + WHERE relfilenode = :'sample_tbl_oid' AND block_data IS NOT NULL; + ok +---- + t +(1 row) + +-- Force full-page image on the next update. +SELECT pg_current_wal_lsn() AS wal_lsn5 \gset +CHECKPOINT; +UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 2; +SELECT pg_current_wal_lsn() AS wal_lsn6 \gset +-- Check if we get FPI from WAL record. +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn5', :'wal_lsn6') + WHERE relfilenode = :'sample_tbl_oid' AND block_fpi_data IS NOT NULL; + ok +---- + t +(1 row) + +-- =================================================================== +-- Tests for permissions +-- =================================================================== +CREATE ROLE regress_pg_walinspect; +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + +-- Functions accessible by users with role pg_read_server_files. +GRANT pg_read_server_files TO regress_pg_walinspect; +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +REVOKE pg_read_server_files FROM regress_pg_walinspect; +-- Superuser can grant execute to other users. +GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) + TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) + TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) + TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) + TO regress_pg_walinspect; +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) + FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) + FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) + FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) + FROM regress_pg_walinspect; +-- =================================================================== +-- Clean up +-- =================================================================== +DROP ROLE regress_pg_walinspect; +SELECT pg_drop_replication_slot('regress_pg_walinspect_slot'); + pg_drop_replication_slot +-------------------------- + +(1 row) + +DROP TABLE sample_tbl; +DROP EXTENSION pg_walinspect; diff --git a/contrib/pg_walinspect/meson.build b/contrib/pg_walinspect/meson.build new file mode 100644 index 0000000..80059f6 --- /dev/null +++ b/contrib/pg_walinspect/meson.build @@ -0,0 +1,40 @@ +# Copyright (c) 2022-2023, PostgreSQL Global Development Group + +pg_walinspect_sources = files('pg_walinspect.c') + +if host_system == 'windows' + pg_walinspect_sources += rc_lib_gen.process(win32ver_rc, extra_args: [ + '--NAME', 'pg_walinspect', + '--FILEDESC', 'pg_walinspect - functions to inspect contents of PostgreSQL Write-Ahead Log',]) +endif + +pg_walinspect = shared_module('pg_walinspect', + pg_walinspect_sources, + kwargs: contrib_mod_args + { + 'dependencies': contrib_mod_args['dependencies'], + }, +) +contrib_targets += pg_walinspect + +install_data( + 'pg_walinspect.control', + 'pg_walinspect--1.0.sql', + 'pg_walinspect--1.0--1.1.sql', + kwargs: contrib_data_args, +) + +tests += { + 'name': 'pg_walinspect', + 'sd': meson.current_source_dir(), + 'bd': meson.current_build_dir(), + 'regress': { + 'sql': [ + 'pg_walinspect', + 'oldextversions', + ], + # Disabled because these tests require "wal_level=replica", which + # some runningcheck users do not have (e.g. buildfarm clients). + 'regress_args': ['--temp-config', files('walinspect.conf')], + 'runningcheck': false, + }, +} diff --git a/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql b/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql new file mode 100644 index 0000000..a4d50d3 --- /dev/null +++ b/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql @@ -0,0 +1,42 @@ +/* contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pg_walinspect UPDATE TO '1.1'" to load this file. \quit + +-- Unsupported functions after 1.1. +DROP FUNCTION pg_get_wal_records_info_till_end_of_wal(pg_lsn); +DROP FUNCTION pg_get_wal_stats_till_end_of_wal(pg_lsn, boolean); + +-- +-- pg_get_wal_block_info() +-- +CREATE FUNCTION pg_get_wal_block_info(IN start_lsn pg_lsn, + IN end_lsn pg_lsn, + IN show_data boolean DEFAULT true, + OUT start_lsn pg_lsn, + OUT end_lsn pg_lsn, + OUT prev_lsn pg_lsn, + OUT block_id int2, + OUT reltablespace oid, + OUT reldatabase oid, + OUT relfilenode oid, + OUT relforknumber int2, + OUT relblocknumber int8, + OUT xid xid, + OUT resource_manager text, + OUT record_type text, + OUT record_length int4, + OUT main_data_length int4, + OUT block_data_length int4, + OUT block_fpi_length int4, + OUT block_fpi_info text[], + OUT description text, + OUT block_data bytea, + OUT block_fpi_data bytea +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_get_wal_block_info' +LANGUAGE C STRICT PARALLEL SAFE; + +REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) TO pg_read_server_files; diff --git a/contrib/pg_walinspect/pg_walinspect--1.0.sql b/contrib/pg_walinspect/pg_walinspect--1.0.sql new file mode 100644 index 0000000..08b3dd5 --- /dev/null +++ b/contrib/pg_walinspect/pg_walinspect--1.0.sql @@ -0,0 +1,118 @@ +/* contrib/pg_walinspect/pg_walinspect--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pg_walinspect" to load this file. \quit + +-- +-- pg_get_wal_record_info() +-- +CREATE FUNCTION pg_get_wal_record_info(IN in_lsn pg_lsn, + OUT start_lsn pg_lsn, + OUT end_lsn pg_lsn, + OUT prev_lsn pg_lsn, + OUT xid xid, + OUT resource_manager text, + OUT record_type text, + OUT record_length int4, + OUT main_data_length int4, + OUT fpi_length int4, + OUT description text, + OUT block_ref text +) +AS 'MODULE_PATHNAME', 'pg_get_wal_record_info' +LANGUAGE C STRICT PARALLEL SAFE; + +REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) TO pg_read_server_files; + +-- +-- pg_get_wal_records_info() +-- +CREATE FUNCTION pg_get_wal_records_info(IN start_lsn pg_lsn, + IN end_lsn pg_lsn, + OUT start_lsn pg_lsn, + OUT end_lsn pg_lsn, + OUT prev_lsn pg_lsn, + OUT xid xid, + OUT resource_manager text, + OUT record_type text, + OUT record_length int4, + OUT main_data_length int4, + OUT fpi_length int4, + OUT description text, + OUT block_ref text +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_get_wal_records_info' +LANGUAGE C STRICT PARALLEL SAFE; + +REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) TO pg_read_server_files; + +-- +-- pg_get_wal_records_info_till_end_of_wal() +-- +CREATE FUNCTION pg_get_wal_records_info_till_end_of_wal(IN start_lsn pg_lsn, + OUT start_lsn pg_lsn, + OUT end_lsn pg_lsn, + OUT prev_lsn pg_lsn, + OUT xid xid, + OUT resource_manager text, + OUT record_type text, + OUT record_length int4, + OUT main_data_length int4, + OUT fpi_length int4, + OUT description text, + OUT block_ref text +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_get_wal_records_info_till_end_of_wal' +LANGUAGE C STRICT PARALLEL SAFE; + +REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info_till_end_of_wal(pg_lsn) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_get_wal_records_info_till_end_of_wal(pg_lsn) TO pg_read_server_files; + +-- +-- pg_get_wal_stats() +-- +CREATE FUNCTION pg_get_wal_stats(IN start_lsn pg_lsn, + IN end_lsn pg_lsn, + IN per_record boolean DEFAULT false, + OUT "resource_manager/record_type" text, + OUT count int8, + OUT count_percentage float8, + OUT record_size int8, + OUT record_size_percentage float8, + OUT fpi_size int8, + OUT fpi_size_percentage float8, + OUT combined_size int8, + OUT combined_size_percentage float8 +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_get_wal_stats' +LANGUAGE C STRICT PARALLEL SAFE; + +REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) TO pg_read_server_files; + +-- +-- pg_get_wal_stats_till_end_of_wal() +-- +CREATE FUNCTION pg_get_wal_stats_till_end_of_wal(IN start_lsn pg_lsn, + IN per_record boolean DEFAULT false, + OUT "resource_manager/record_type" text, + OUT count int8, + OUT count_percentage float8, + OUT record_size int8, + OUT record_size_percentage float8, + OUT fpi_size int8, + OUT fpi_size_percentage float8, + OUT combined_size int8, + OUT combined_size_percentage float8 +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_get_wal_stats_till_end_of_wal' +LANGUAGE C STRICT PARALLEL SAFE; + +REVOKE EXECUTE ON FUNCTION pg_get_wal_stats_till_end_of_wal(pg_lsn, boolean) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_get_wal_stats_till_end_of_wal(pg_lsn, boolean) TO pg_read_server_files; diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c new file mode 100644 index 0000000..796a74f --- /dev/null +++ b/contrib/pg_walinspect/pg_walinspect.c @@ -0,0 +1,851 @@ +/*------------------------------------------------------------------------- + * + * pg_walinspect.c + * Functions to inspect contents of PostgreSQL Write-Ahead Log + * + * Copyright (c) 2022-2023, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/pg_walinspect/pg_walinspect.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/xlog.h" +#include "access/xlog_internal.h" +#include "access/xlogreader.h" +#include "access/xlogrecovery.h" +#include "access/xlogstats.h" +#include "access/xlogutils.h" +#include "funcapi.h" +#include "miscadmin.h" +#include "utils/array.h" +#include "utils/builtins.h" +#include "utils/pg_lsn.h" + +/* + * NOTE: For any code change or issue fix here, it is highly recommended to + * give a thought about doing the same in pg_waldump tool as well. + */ + +PG_MODULE_MAGIC; + +PG_FUNCTION_INFO_V1(pg_get_wal_block_info); +PG_FUNCTION_INFO_V1(pg_get_wal_record_info); +PG_FUNCTION_INFO_V1(pg_get_wal_records_info); +PG_FUNCTION_INFO_V1(pg_get_wal_records_info_till_end_of_wal); +PG_FUNCTION_INFO_V1(pg_get_wal_stats); +PG_FUNCTION_INFO_V1(pg_get_wal_stats_till_end_of_wal); + +static void ValidateInputLSNs(XLogRecPtr start_lsn, XLogRecPtr *end_lsn); +static XLogRecPtr GetCurrentLSN(void); +static XLogReaderState *InitXLogReaderState(XLogRecPtr lsn); +static XLogRecord *ReadNextXLogRecord(XLogReaderState *xlogreader); +static void GetWALRecordInfo(XLogReaderState *record, Datum *values, + bool *nulls, uint32 ncols); +static void GetWALRecordsInfo(FunctionCallInfo fcinfo, + XLogRecPtr start_lsn, + XLogRecPtr end_lsn); +static void GetXLogSummaryStats(XLogStats *stats, ReturnSetInfo *rsinfo, + Datum *values, bool *nulls, uint32 ncols, + bool stats_per_record); +static void FillXLogStatsRow(const char *name, uint64 n, uint64 total_count, + uint64 rec_len, uint64 total_rec_len, + uint64 fpi_len, uint64 total_fpi_len, + uint64 tot_len, uint64 total_len, + Datum *values, bool *nulls, uint32 ncols); +static void GetWalStats(FunctionCallInfo fcinfo, + XLogRecPtr start_lsn, + XLogRecPtr end_lsn, + bool stats_per_record); +static void GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record, + bool show_data); + +/* + * Return the LSN up to which the server has WAL. + */ +static XLogRecPtr +GetCurrentLSN(void) +{ + XLogRecPtr curr_lsn; + + /* + * We determine the current LSN of the server similar to how page_read + * callback read_local_xlog_page_no_wait does. + */ + if (!RecoveryInProgress()) + curr_lsn = GetFlushRecPtr(NULL); + else + curr_lsn = GetXLogReplayRecPtr(NULL); + + Assert(!XLogRecPtrIsInvalid(curr_lsn)); + + return curr_lsn; +} + +/* + * Initialize WAL reader and identify first valid LSN. + */ +static XLogReaderState * +InitXLogReaderState(XLogRecPtr lsn) +{ + XLogReaderState *xlogreader; + ReadLocalXLogPageNoWaitPrivate *private_data; + XLogRecPtr first_valid_record; + + /* + * Reading WAL below the first page of the first segments isn't allowed. + * This is a bootstrap WAL page and the page_read callback fails to read + * it. + */ + if (lsn < XLOG_BLCKSZ) + ereport(ERROR, + (errmsg("could not read WAL at LSN %X/%X", + LSN_FORMAT_ARGS(lsn)))); + + private_data = (ReadLocalXLogPageNoWaitPrivate *) + palloc0(sizeof(ReadLocalXLogPageNoWaitPrivate)); + + xlogreader = XLogReaderAllocate(wal_segment_size, NULL, + XL_ROUTINE(.page_read = &read_local_xlog_page_no_wait, + .segment_open = &wal_segment_open, + .segment_close = &wal_segment_close), + private_data); + + if (xlogreader == NULL) + ereport(ERROR, + (errcode(ERRCODE_OUT_OF_MEMORY), + errmsg("out of memory"), + errdetail("Failed while allocating a WAL reading processor."))); + + /* first find a valid recptr to start from */ + first_valid_record = XLogFindNextRecord(xlogreader, lsn); + + if (XLogRecPtrIsInvalid(first_valid_record)) + ereport(ERROR, + (errmsg("could not find a valid record after %X/%X", + LSN_FORMAT_ARGS(lsn)))); + + return xlogreader; +} + +/* + * Read next WAL record. + * + * By design, to be less intrusive in a running system, no slot is allocated + * to reserve the WAL we're about to read. Therefore this function can + * encounter read errors for historical WAL. + * + * We guard against ordinary errors trying to read WAL that hasn't been + * written yet by limiting end_lsn to the flushed WAL, but that can also + * encounter errors if the flush pointer falls in the middle of a record. In + * that case we'll return NULL. + */ +static XLogRecord * +ReadNextXLogRecord(XLogReaderState *xlogreader) +{ + XLogRecord *record; + char *errormsg; + + record = XLogReadRecord(xlogreader, &errormsg); + + if (record == NULL) + { + ReadLocalXLogPageNoWaitPrivate *private_data; + + /* return NULL, if end of WAL is reached */ + private_data = (ReadLocalXLogPageNoWaitPrivate *) + xlogreader->private_data; + + if (private_data->end_of_wal) + return NULL; + + if (errormsg) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not read WAL at %X/%X: %s", + LSN_FORMAT_ARGS(xlogreader->EndRecPtr), errormsg))); + else + ereport(ERROR, + (errcode_for_file_access(), + errmsg("could not read WAL at %X/%X", + LSN_FORMAT_ARGS(xlogreader->EndRecPtr)))); + } + + return record; +} + +/* + * Output values that make up a row describing caller's WAL record. + * + * This function leaks memory. Caller may need to use its own custom memory + * context. + * + * Keep this in sync with GetWALBlockInfo. + */ +static void +GetWALRecordInfo(XLogReaderState *record, Datum *values, + bool *nulls, uint32 ncols) +{ + const char *record_type; + RmgrData desc; + uint32 fpi_len = 0; + StringInfoData rec_desc; + StringInfoData rec_blk_ref; + int i = 0; + + desc = GetRmgr(XLogRecGetRmid(record)); + record_type = desc.rm_identify(XLogRecGetInfo(record)); + + if (record_type == NULL) + record_type = psprintf("UNKNOWN (%x)", XLogRecGetInfo(record) & ~XLR_INFO_MASK); + + initStringInfo(&rec_desc); + desc.rm_desc(&rec_desc, record); + + if (XLogRecHasAnyBlockRefs(record)) + { + initStringInfo(&rec_blk_ref); + XLogRecGetBlockRefInfo(record, false, true, &rec_blk_ref, &fpi_len); + } + + values[i++] = LSNGetDatum(record->ReadRecPtr); + values[i++] = LSNGetDatum(record->EndRecPtr); + values[i++] = LSNGetDatum(XLogRecGetPrev(record)); + values[i++] = TransactionIdGetDatum(XLogRecGetXid(record)); + values[i++] = CStringGetTextDatum(desc.rm_name); + values[i++] = CStringGetTextDatum(record_type); + values[i++] = UInt32GetDatum(XLogRecGetTotalLen(record)); + values[i++] = UInt32GetDatum(XLogRecGetDataLen(record)); + values[i++] = UInt32GetDatum(fpi_len); + + if (rec_desc.len > 0) + values[i++] = CStringGetTextDatum(rec_desc.data); + else + nulls[i++] = true; + + if (XLogRecHasAnyBlockRefs(record)) + values[i++] = CStringGetTextDatum(rec_blk_ref.data); + else + nulls[i++] = true; + + Assert(i == ncols); +} + + +/* + * Output one or more rows in rsinfo tuple store, each describing a single + * block reference from caller's WAL record. (Should only be called with + * records that have block references.) + * + * This function leaks memory. Caller may need to use its own custom memory + * context. + * + * Keep this in sync with GetWALRecordInfo. + */ +static void +GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record, + bool show_data) +{ +#define PG_GET_WAL_BLOCK_INFO_COLS 20 + int block_id; + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + RmgrData desc; + const char *record_type; + StringInfoData rec_desc; + + Assert(XLogRecHasAnyBlockRefs(record)); + + desc = GetRmgr(XLogRecGetRmid(record)); + record_type = desc.rm_identify(XLogRecGetInfo(record)); + + if (record_type == NULL) + record_type = psprintf("UNKNOWN (%x)", + XLogRecGetInfo(record) & ~XLR_INFO_MASK); + + initStringInfo(&rec_desc); + desc.rm_desc(&rec_desc, record); + + for (block_id = 0; block_id <= XLogRecMaxBlockId(record); block_id++) + { + DecodedBkpBlock *blk; + BlockNumber blkno; + RelFileLocator rnode; + ForkNumber forknum; + Datum values[PG_GET_WAL_BLOCK_INFO_COLS] = {0}; + bool nulls[PG_GET_WAL_BLOCK_INFO_COLS] = {0}; + uint32 block_data_len = 0, + block_fpi_len = 0; + ArrayType *block_fpi_info = NULL; + int i = 0; + + if (!XLogRecHasBlockRef(record, block_id)) + continue; + + blk = XLogRecGetBlock(record, block_id); + + (void) XLogRecGetBlockTagExtended(record, block_id, + &rnode, &forknum, &blkno, NULL); + + /* Save block_data_len */ + if (blk->has_data) + block_data_len = blk->data_len; + + if (blk->has_image) + { + /* Block reference has an FPI, so prepare relevant output */ + int bitcnt; + int cnt = 0; + Datum *flags; + + /* Save block_fpi_len */ + block_fpi_len = blk->bimg_len; + + /* Construct and save block_fpi_info */ + bitcnt = pg_popcount((const char *) &blk->bimg_info, + sizeof(uint8)); + flags = (Datum *) palloc0(sizeof(Datum) * bitcnt); + if ((blk->bimg_info & BKPIMAGE_HAS_HOLE) != 0) + flags[cnt++] = CStringGetTextDatum("HAS_HOLE"); + if (blk->apply_image) + flags[cnt++] = CStringGetTextDatum("APPLY"); + if ((blk->bimg_info & BKPIMAGE_COMPRESS_PGLZ) != 0) + flags[cnt++] = CStringGetTextDatum("COMPRESS_PGLZ"); + if ((blk->bimg_info & BKPIMAGE_COMPRESS_LZ4) != 0) + flags[cnt++] = CStringGetTextDatum("COMPRESS_LZ4"); + if ((blk->bimg_info & BKPIMAGE_COMPRESS_ZSTD) != 0) + flags[cnt++] = CStringGetTextDatum("COMPRESS_ZSTD"); + + Assert(cnt <= bitcnt); + block_fpi_info = construct_array_builtin(flags, cnt, TEXTOID); + } + + /* start_lsn, end_lsn, prev_lsn, and blockid outputs */ + values[i++] = LSNGetDatum(record->ReadRecPtr); + values[i++] = LSNGetDatum(record->EndRecPtr); + values[i++] = LSNGetDatum(XLogRecGetPrev(record)); + values[i++] = Int16GetDatum(block_id); + + /* relfile and block related outputs */ + values[i++] = ObjectIdGetDatum(blk->rlocator.spcOid); + values[i++] = ObjectIdGetDatum(blk->rlocator.dbOid); + values[i++] = ObjectIdGetDatum(blk->rlocator.relNumber); + values[i++] = Int16GetDatum(forknum); + values[i++] = Int64GetDatum((int64) blkno); + + /* xid, resource_manager, and record_type outputs */ + values[i++] = TransactionIdGetDatum(XLogRecGetXid(record)); + values[i++] = CStringGetTextDatum(desc.rm_name); + values[i++] = CStringGetTextDatum(record_type); + + /* + * record_length, main_data_length, block_data_len, and + * block_fpi_length outputs + */ + values[i++] = UInt32GetDatum(XLogRecGetTotalLen(record)); + values[i++] = UInt32GetDatum(XLogRecGetDataLen(record)); + values[i++] = UInt32GetDatum(block_data_len); + values[i++] = UInt32GetDatum(block_fpi_len); + + /* block_fpi_info (text array) output */ + if (block_fpi_info) + values[i++] = PointerGetDatum(block_fpi_info); + else + nulls[i++] = true; + + /* description output (describes WAL record) */ + if (rec_desc.len > 0) + values[i++] = CStringGetTextDatum(rec_desc.data); + else + nulls[i++] = true; + + /* block_data output */ + if (blk->has_data && show_data) + { + bytea *block_data; + + block_data = (bytea *) palloc(block_data_len + VARHDRSZ); + SET_VARSIZE(block_data, block_data_len + VARHDRSZ); + memcpy(VARDATA(block_data), blk->data, block_data_len); + values[i++] = PointerGetDatum(block_data); + } + else + nulls[i++] = true; + + /* block_fpi_data output */ + if (blk->has_image && show_data) + { + PGAlignedBlock buf; + Page page; + bytea *block_fpi_data; + + page = (Page) buf.data; + if (!RestoreBlockImage(record, block_id, page)) + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg_internal("%s", record->errormsg_buf))); + + block_fpi_data = (bytea *) palloc(BLCKSZ + VARHDRSZ); + SET_VARSIZE(block_fpi_data, BLCKSZ + VARHDRSZ); + memcpy(VARDATA(block_fpi_data), page, BLCKSZ); + values[i++] = PointerGetDatum(block_fpi_data); + } + else + nulls[i++] = true; + + Assert(i == PG_GET_WAL_BLOCK_INFO_COLS); + + /* Store a tuple for this block reference */ + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, + values, nulls); + } + +#undef PG_GET_WAL_BLOCK_INFO_COLS +} + +/* + * Get WAL record info, unnested by block reference + */ +Datum +pg_get_wal_block_info(PG_FUNCTION_ARGS) +{ + XLogRecPtr start_lsn = PG_GETARG_LSN(0); + XLogRecPtr end_lsn = PG_GETARG_LSN(1); + bool show_data = PG_GETARG_BOOL(2); + XLogReaderState *xlogreader; + MemoryContext old_cxt; + MemoryContext tmp_cxt; + + ValidateInputLSNs(start_lsn, &end_lsn); + + InitMaterializedSRF(fcinfo, 0); + + xlogreader = InitXLogReaderState(start_lsn); + + tmp_cxt = AllocSetContextCreate(CurrentMemoryContext, + "pg_get_wal_block_info temporary cxt", + ALLOCSET_DEFAULT_SIZES); + + while (ReadNextXLogRecord(xlogreader) && + xlogreader->EndRecPtr <= end_lsn) + { + CHECK_FOR_INTERRUPTS(); + + if (!XLogRecHasAnyBlockRefs(xlogreader)) + continue; + + /* Use the tmp context so we can clean up after each tuple is done */ + old_cxt = MemoryContextSwitchTo(tmp_cxt); + + GetWALBlockInfo(fcinfo, xlogreader, show_data); + + /* clean up and switch back */ + MemoryContextSwitchTo(old_cxt); + MemoryContextReset(tmp_cxt); + } + + MemoryContextDelete(tmp_cxt); + pfree(xlogreader->private_data); + XLogReaderFree(xlogreader); + + PG_RETURN_VOID(); +} + +/* + * Get WAL record info. + */ +Datum +pg_get_wal_record_info(PG_FUNCTION_ARGS) +{ +#define PG_GET_WAL_RECORD_INFO_COLS 11 + Datum result; + Datum values[PG_GET_WAL_RECORD_INFO_COLS] = {0}; + bool nulls[PG_GET_WAL_RECORD_INFO_COLS] = {0}; + XLogRecPtr lsn; + XLogRecPtr curr_lsn; + XLogReaderState *xlogreader; + TupleDesc tupdesc; + HeapTuple tuple; + + lsn = PG_GETARG_LSN(0); + curr_lsn = GetCurrentLSN(); + + if (lsn > curr_lsn) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("WAL input LSN must be less than current LSN"), + errdetail("Current WAL LSN on the database system is at %X/%X.", + LSN_FORMAT_ARGS(curr_lsn)))); + + /* Build a tuple descriptor for our result type. */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + xlogreader = InitXLogReaderState(lsn); + + if (!ReadNextXLogRecord(xlogreader)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not read WAL at %X/%X", + LSN_FORMAT_ARGS(xlogreader->EndRecPtr)))); + + GetWALRecordInfo(xlogreader, values, nulls, PG_GET_WAL_RECORD_INFO_COLS); + + pfree(xlogreader->private_data); + XLogReaderFree(xlogreader); + + tuple = heap_form_tuple(tupdesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +#undef PG_GET_WAL_RECORD_INFO_COLS +} + +/* + * Validate start and end LSNs coming from the function inputs. + * + * If end_lsn is found to be higher than the current LSN reported by the + * cluster, use the current LSN as the upper bound. + */ +static void +ValidateInputLSNs(XLogRecPtr start_lsn, XLogRecPtr *end_lsn) +{ + XLogRecPtr curr_lsn = GetCurrentLSN(); + + if (start_lsn > curr_lsn) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("WAL start LSN must be less than current LSN"), + errdetail("Current WAL LSN on the database system is at %X/%X.", + LSN_FORMAT_ARGS(curr_lsn)))); + + if (start_lsn > *end_lsn) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("WAL start LSN must be less than end LSN"))); + + if (*end_lsn > curr_lsn) + *end_lsn = curr_lsn; +} + +/* + * Get info of all WAL records between start LSN and end LSN. + */ +static void +GetWALRecordsInfo(FunctionCallInfo fcinfo, XLogRecPtr start_lsn, + XLogRecPtr end_lsn) +{ +#define PG_GET_WAL_RECORDS_INFO_COLS 11 + XLogReaderState *xlogreader; + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + MemoryContext old_cxt; + MemoryContext tmp_cxt; + + Assert(start_lsn <= end_lsn); + + InitMaterializedSRF(fcinfo, 0); + + xlogreader = InitXLogReaderState(start_lsn); + + tmp_cxt = AllocSetContextCreate(CurrentMemoryContext, + "GetWALRecordsInfo temporary cxt", + ALLOCSET_DEFAULT_SIZES); + + while (ReadNextXLogRecord(xlogreader) && + xlogreader->EndRecPtr <= end_lsn) + { + Datum values[PG_GET_WAL_RECORDS_INFO_COLS] = {0}; + bool nulls[PG_GET_WAL_RECORDS_INFO_COLS] = {0}; + + /* Use the tmp context so we can clean up after each tuple is done */ + old_cxt = MemoryContextSwitchTo(tmp_cxt); + + GetWALRecordInfo(xlogreader, values, nulls, + PG_GET_WAL_RECORDS_INFO_COLS); + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, + values, nulls); + + /* clean up and switch back */ + MemoryContextSwitchTo(old_cxt); + MemoryContextReset(tmp_cxt); + + CHECK_FOR_INTERRUPTS(); + } + + MemoryContextDelete(tmp_cxt); + pfree(xlogreader->private_data); + XLogReaderFree(xlogreader); + +#undef PG_GET_WAL_RECORDS_INFO_COLS +} + +/* + * Get info of all WAL records between start LSN and end LSN. + */ +Datum +pg_get_wal_records_info(PG_FUNCTION_ARGS) +{ + XLogRecPtr start_lsn = PG_GETARG_LSN(0); + XLogRecPtr end_lsn = PG_GETARG_LSN(1); + + ValidateInputLSNs(start_lsn, &end_lsn); + GetWALRecordsInfo(fcinfo, start_lsn, end_lsn); + + PG_RETURN_VOID(); +} + +/* + * Fill single row of record counts and sizes for an rmgr or record. + */ +static void +FillXLogStatsRow(const char *name, + uint64 n, uint64 total_count, + uint64 rec_len, uint64 total_rec_len, + uint64 fpi_len, uint64 total_fpi_len, + uint64 tot_len, uint64 total_len, + Datum *values, bool *nulls, uint32 ncols) +{ + double n_pct, + rec_len_pct, + fpi_len_pct, + tot_len_pct; + int i = 0; + + n_pct = 0; + if (total_count != 0) + n_pct = 100 * (double) n / total_count; + + rec_len_pct = 0; + if (total_rec_len != 0) + rec_len_pct = 100 * (double) rec_len / total_rec_len; + + fpi_len_pct = 0; + if (total_fpi_len != 0) + fpi_len_pct = 100 * (double) fpi_len / total_fpi_len; + + tot_len_pct = 0; + if (total_len != 0) + tot_len_pct = 100 * (double) tot_len / total_len; + + values[i++] = CStringGetTextDatum(name); + values[i++] = Int64GetDatum(n); + values[i++] = Float8GetDatum(n_pct); + values[i++] = Int64GetDatum(rec_len); + values[i++] = Float8GetDatum(rec_len_pct); + values[i++] = Int64GetDatum(fpi_len); + values[i++] = Float8GetDatum(fpi_len_pct); + values[i++] = Int64GetDatum(tot_len); + values[i++] = Float8GetDatum(tot_len_pct); + + Assert(i == ncols); +} + +/* + * Get summary statistics about the records seen so far. + */ +static void +GetXLogSummaryStats(XLogStats *stats, ReturnSetInfo *rsinfo, + Datum *values, bool *nulls, uint32 ncols, + bool stats_per_record) +{ + MemoryContext old_cxt; + MemoryContext tmp_cxt; + uint64 total_count = 0; + uint64 total_rec_len = 0; + uint64 total_fpi_len = 0; + uint64 total_len = 0; + int ri; + + /* + * Each row shows its percentages of the total, so make a first pass to + * calculate column totals. + */ + for (ri = 0; ri <= RM_MAX_ID; ri++) + { + if (!RmgrIdIsValid(ri)) + continue; + + total_count += stats->rmgr_stats[ri].count; + total_rec_len += stats->rmgr_stats[ri].rec_len; + total_fpi_len += stats->rmgr_stats[ri].fpi_len; + } + total_len = total_rec_len + total_fpi_len; + + tmp_cxt = AllocSetContextCreate(CurrentMemoryContext, + "GetXLogSummaryStats temporary cxt", + ALLOCSET_DEFAULT_SIZES); + + for (ri = 0; ri <= RM_MAX_ID; ri++) + { + uint64 count; + uint64 rec_len; + uint64 fpi_len; + uint64 tot_len; + RmgrData desc; + + if (!RmgrIdIsValid(ri)) + continue; + + if (!RmgrIdExists(ri)) + continue; + + desc = GetRmgr(ri); + + if (stats_per_record) + { + int rj; + + for (rj = 0; rj < MAX_XLINFO_TYPES; rj++) + { + const char *id; + + count = stats->record_stats[ri][rj].count; + rec_len = stats->record_stats[ri][rj].rec_len; + fpi_len = stats->record_stats[ri][rj].fpi_len; + tot_len = rec_len + fpi_len; + + /* Skip undefined combinations and ones that didn't occur */ + if (count == 0) + continue; + + old_cxt = MemoryContextSwitchTo(tmp_cxt); + + /* the upper four bits in xl_info are the rmgr's */ + id = desc.rm_identify(rj << 4); + if (id == NULL) + id = psprintf("UNKNOWN (%x)", rj << 4); + + FillXLogStatsRow(psprintf("%s/%s", desc.rm_name, id), count, + total_count, rec_len, total_rec_len, fpi_len, + total_fpi_len, tot_len, total_len, + values, nulls, ncols); + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, + values, nulls); + + /* clean up and switch back */ + MemoryContextSwitchTo(old_cxt); + MemoryContextReset(tmp_cxt); + } + } + else + { + count = stats->rmgr_stats[ri].count; + rec_len = stats->rmgr_stats[ri].rec_len; + fpi_len = stats->rmgr_stats[ri].fpi_len; + tot_len = rec_len + fpi_len; + + old_cxt = MemoryContextSwitchTo(tmp_cxt); + + FillXLogStatsRow(desc.rm_name, count, total_count, rec_len, + total_rec_len, fpi_len, total_fpi_len, tot_len, + total_len, values, nulls, ncols); + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, + values, nulls); + + /* clean up and switch back */ + MemoryContextSwitchTo(old_cxt); + MemoryContextReset(tmp_cxt); + } + } + + MemoryContextDelete(tmp_cxt); +} + +/* + * Get WAL stats between start LSN and end LSN. + */ +static void +GetWalStats(FunctionCallInfo fcinfo, XLogRecPtr start_lsn, XLogRecPtr end_lsn, + bool stats_per_record) +{ +#define PG_GET_WAL_STATS_COLS 9 + XLogReaderState *xlogreader; + XLogStats stats = {0}; + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + Datum values[PG_GET_WAL_STATS_COLS] = {0}; + bool nulls[PG_GET_WAL_STATS_COLS] = {0}; + + Assert(start_lsn <= end_lsn); + + InitMaterializedSRF(fcinfo, 0); + + xlogreader = InitXLogReaderState(start_lsn); + + while (ReadNextXLogRecord(xlogreader) && + xlogreader->EndRecPtr <= end_lsn) + { + XLogRecStoreStats(&stats, xlogreader); + + CHECK_FOR_INTERRUPTS(); + } + + pfree(xlogreader->private_data); + XLogReaderFree(xlogreader); + + GetXLogSummaryStats(&stats, rsinfo, values, nulls, + PG_GET_WAL_STATS_COLS, + stats_per_record); + +#undef PG_GET_WAL_STATS_COLS +} + +/* + * Get stats of all WAL records between start LSN and end LSN. + */ +Datum +pg_get_wal_stats(PG_FUNCTION_ARGS) +{ + XLogRecPtr start_lsn = PG_GETARG_LSN(0); + XLogRecPtr end_lsn = PG_GETARG_LSN(1); + bool stats_per_record = PG_GETARG_BOOL(2); + + ValidateInputLSNs(start_lsn, &end_lsn); + GetWalStats(fcinfo, start_lsn, end_lsn, stats_per_record); + + PG_RETURN_VOID(); +} + +/* + * The following functions have been removed in newer versions in 1.1, but + * they are kept around for compatibility. + */ +Datum +pg_get_wal_records_info_till_end_of_wal(PG_FUNCTION_ARGS) +{ + XLogRecPtr start_lsn = PG_GETARG_LSN(0); + XLogRecPtr end_lsn = GetCurrentLSN(); + + if (start_lsn > end_lsn) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("WAL start LSN must be less than current LSN"), + errdetail("Current WAL LSN on the database system is at %X/%X.", + LSN_FORMAT_ARGS(end_lsn)))); + + GetWALRecordsInfo(fcinfo, start_lsn, end_lsn); + + PG_RETURN_VOID(); +} + +Datum +pg_get_wal_stats_till_end_of_wal(PG_FUNCTION_ARGS) +{ + XLogRecPtr start_lsn = PG_GETARG_LSN(0); + XLogRecPtr end_lsn = GetCurrentLSN(); + bool stats_per_record = PG_GETARG_BOOL(1); + + if (start_lsn > end_lsn) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("WAL start LSN must be less than current LSN"), + errdetail("Current WAL LSN on the database system is at %X/%X.", + LSN_FORMAT_ARGS(end_lsn)))); + + GetWalStats(fcinfo, start_lsn, end_lsn, stats_per_record); + + PG_RETURN_VOID(); +} diff --git a/contrib/pg_walinspect/pg_walinspect.control b/contrib/pg_walinspect/pg_walinspect.control new file mode 100644 index 0000000..efa3cb2 --- /dev/null +++ b/contrib/pg_walinspect/pg_walinspect.control @@ -0,0 +1,5 @@ +# pg_walinspect extension +comment = 'functions to inspect contents of PostgreSQL Write-Ahead Log' +default_version = '1.1' +module_pathname = '$libdir/pg_walinspect' +relocatable = true diff --git a/contrib/pg_walinspect/sql/oldextversions.sql b/contrib/pg_walinspect/sql/oldextversions.sql new file mode 100644 index 0000000..e35c4f3 --- /dev/null +++ b/contrib/pg_walinspect/sql/oldextversions.sql @@ -0,0 +1,39 @@ +-- Test old extension version entry points. + +CREATE EXTENSION pg_walinspect WITH VERSION '1.0'; + +-- Mask DETAIL messages as these could refer to current LSN positions. +\set VERBOSITY terse + +-- List what version 1.0 contains, using a locale-independent sorting. +SELECT pg_describe_object(classid, objid, 0) AS obj + FROM pg_depend + WHERE refclassid = 'pg_extension'::regclass AND + refobjid = (SELECT oid FROM pg_extension + WHERE extname = 'pg_walinspect') AND deptype = 'e' + ORDER BY pg_describe_object(classid, objid, 0) COLLATE "C"; + +-- Make sure checkpoints don't interfere with the test. +SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false); + +CREATE TABLE sample_tbl(col1 int, col2 int); +SELECT pg_current_wal_lsn() AS wal_lsn1 \gset +INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2); + +-- Tests for the past functions. +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info_till_end_of_wal(:'wal_lsn1'); +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats_till_end_of_wal(:'wal_lsn1'); +-- Failures with start LSNs. +SELECT * FROM pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF'); +SELECT * FROM pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF'); + +-- Move to new version 1.1. +ALTER EXTENSION pg_walinspect UPDATE TO '1.1'; + +-- List what version 1.1 contains. +\dx+ pg_walinspect + +SELECT pg_drop_replication_slot('regress_pg_walinspect_slot'); + +DROP TABLE sample_tbl; +DROP EXTENSION pg_walinspect; diff --git a/contrib/pg_walinspect/sql/pg_walinspect.sql b/contrib/pg_walinspect/sql/pg_walinspect.sql new file mode 100644 index 0000000..f987ca3 --- /dev/null +++ b/contrib/pg_walinspect/sql/pg_walinspect.sql @@ -0,0 +1,157 @@ +CREATE EXTENSION pg_walinspect; + +-- Mask DETAIL messages as these could refer to current LSN positions. +\set VERBOSITY terse + +-- Make sure checkpoints don't interfere with the test. +SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false); + +CREATE TABLE sample_tbl(col1 int, col2 int); + +-- Save some LSNs for comparisons. +SELECT pg_current_wal_lsn() AS wal_lsn1 \gset +INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2); +SELECT pg_current_wal_lsn() AS wal_lsn2 \gset +INSERT INTO sample_tbl SELECT * FROM generate_series(3, 4); + +-- =================================================================== +-- Tests for input validation +-- =================================================================== + +-- Invalid input LSN. +SELECT * FROM pg_get_wal_record_info('0/0'); + +-- Invalid start LSN. +SELECT * FROM pg_get_wal_records_info('0/0', :'wal_lsn1'); +SELECT * FROM pg_get_wal_stats('0/0', :'wal_lsn1'); +SELECT * FROM pg_get_wal_block_info('0/0', :'wal_lsn1'); + +-- Start LSN > End LSN. +SELECT * FROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1'); +SELECT * FROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1'); +SELECT * FROM pg_get_wal_block_info(:'wal_lsn2', :'wal_lsn1'); + +-- LSNs with the highest value possible. +SELECT * FROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF'); +-- Success with end LSNs. +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF'); +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF'); +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF'); +-- Failures with start LSNs. +SELECT * FROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF'); +SELECT * FROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF'); +SELECT * FROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF'); + +-- =================================================================== +-- Tests for all function executions +-- =================================================================== + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_record_info(:'wal_lsn1'); +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'); +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2'); +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2'); + +-- =================================================================== +-- Test for filtering out WAL records of a particular table +-- =================================================================== + +SELECT oid AS sample_tbl_oid FROM pg_class WHERE relname = 'sample_tbl' \gset + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2') + WHERE block_ref LIKE concat('%', :'sample_tbl_oid', '%') AND resource_manager = 'Heap'; + +-- =================================================================== +-- Test for filtering out WAL records based on resource_manager and +-- record_type +-- =================================================================== + +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2') + WHERE resource_manager = 'Heap' AND record_type = 'INSERT'; + +-- =================================================================== +-- Tests to get block information from WAL record +-- =================================================================== + +-- Update table to generate some block data. +SELECT pg_current_wal_lsn() AS wal_lsn3 \gset +UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 1; +SELECT pg_current_wal_lsn() AS wal_lsn4 \gset +-- Check if we get block data from WAL record. +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn3', :'wal_lsn4') + WHERE relfilenode = :'sample_tbl_oid' AND block_data IS NOT NULL; + +-- Force full-page image on the next update. +SELECT pg_current_wal_lsn() AS wal_lsn5 \gset +CHECKPOINT; +UPDATE sample_tbl SET col1 = col1 + 1 WHERE col1 = 2; +SELECT pg_current_wal_lsn() AS wal_lsn6 \gset +-- Check if we get FPI from WAL record. +SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn5', :'wal_lsn6') + WHERE relfilenode = :'sample_tbl_oid' AND block_fpi_data IS NOT NULL; + +-- =================================================================== +-- Tests for permissions +-- =================================================================== +CREATE ROLE regress_pg_walinspect; + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- no +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no + +-- Functions accessible by users with role pg_read_server_files. +GRANT pg_read_server_files TO regress_pg_walinspect; + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes + +REVOKE pg_read_server_files FROM regress_pg_walinspect; + +-- Superuser can grant execute to other users. +GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) + TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) + TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) + TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) + TO regress_pg_walinspect; + +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes + +REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) + FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) + FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) + FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) + FROM regress_pg_walinspect; + +-- =================================================================== +-- Clean up +-- =================================================================== + +DROP ROLE regress_pg_walinspect; + +SELECT pg_drop_replication_slot('regress_pg_walinspect_slot'); + +DROP TABLE sample_tbl; +DROP EXTENSION pg_walinspect; diff --git a/contrib/pg_walinspect/walinspect.conf b/contrib/pg_walinspect/walinspect.conf new file mode 100644 index 0000000..67ceb2b --- /dev/null +++ b/contrib/pg_walinspect/walinspect.conf @@ -0,0 +1,2 @@ +wal_level = replica +max_replication_slots = 4 |