On Thu, Mar 3, 2022 at 10:05 PM Robert Haas <robertmh...@gmail.com> wrote:
>
> On Fri, Feb 25, 2022 at 6:03 AM Bharath Rupireddy
> <bharath.rupireddyforpostg...@gmail.com> wrote:
> > Added a new function that returns the first and last valid WAL record
> > LSN of a given WAL file.
>
> Sounds like fuzzy thinking to me. WAL records can cross file
> boundaries, and forgetting about that leads to all sorts of problems.
> Just give people one function that decodes a range of LSNs and call it
> good. Why do you need anything else? If people want to get the first
> record that begins in a segment or the first record any portion of
> which is in a particular segment or the last record that begins in a
> segment or the last record that ends in a segment or any other such
> thing, they can use a WHERE clause for that... and if you think they
> can't, then that should be good cause to rethink the return value of
> the one-and-only SRF that I think you need here.
Thanks Robert.
Thanks to others for your review comments.
Here's the v7 patch set. These patches are based on the motive that
"keep it simple and short yet effective and useful". With that in
mind, I have not implemented the wait mode for any of the functions
(as it doesn't look an effective use-case and requires adding a new
page_read callback, instead throw error if future LSN is specified),
also these functions will give WAL information on the current server's
timeline. Having said that, I'm open to adding new functions in future
once this initial version gets in, if there's a requirement and users
ask for the new functions.
Please review the v7 patch set and provide your thoughts.
Regards,
Bharath Rupireddy.
From df9e5b5f9a76a2002d9ef8d9b0db88003a07a5b5 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 4 Mar 2022 08:12:20 +0000
Subject: [PATCH v7] pg_walinspect
---
contrib/Makefile | 1 +
contrib/pg_walinspect/.gitignore | 4 +
contrib/pg_walinspect/Makefile | 26 +
contrib/pg_walinspect/pg_walinspect--1.0.sql | 91 ++
contrib/pg_walinspect/pg_walinspect.c | 949 +++++++++++++++++++
contrib/pg_walinspect/pg_walinspect.control | 5 +
src/backend/access/transam/xlogreader.c | 14 +-
src/bin/pg_waldump/pg_waldump.c | 5 +
src/common/relpath.c | 18 +
src/include/access/xlog.h | 2 +-
src/include/access/xlog_internal.h | 2 +-
src/include/access/xlogreader.h | 2 -
src/include/common/relpath.h | 1 +
13 files changed, 1109 insertions(+), 11 deletions(-)
create mode 100644 contrib/pg_walinspect/.gitignore
create mode 100644 contrib/pg_walinspect/Makefile
create mode 100644 contrib/pg_walinspect/pg_walinspect--1.0.sql
create mode 100644 contrib/pg_walinspect/pg_walinspect.c
create mode 100644 contrib/pg_walinspect/pg_walinspect.control
diff --git a/contrib/Makefile b/contrib/Makefile
index e3e221308b..705c6fc36b 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgrowlocks \
pgstattuple \
pg_visibility \
+ pg_walinspect \
postgres_fdw \
seg \
spi \
diff --git a/contrib/pg_walinspect/.gitignore b/contrib/pg_walinspect/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /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 0000000000..c92a97447f
--- /dev/null
+++ b/contrib/pg_walinspect/Makefile
@@ -0,0 +1,26 @@
+# 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"
+
+PG_CPPFLAGS = -I$(libpq_srcdir)
+SHLIB_LINK_INTERNAL = $(libpq)
+
+EXTENSION = pg_walinspect
+DATA = pg_walinspect--1.0.sql
+
+REGRESS = pg_walinspect
+
+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/pg_walinspect--1.0.sql b/contrib/pg_walinspect/pg_walinspect--1.0.sql
new file mode 100644
index 0000000000..619b1d1d4a
--- /dev/null
+++ b/contrib/pg_walinspect/pg_walinspect--1.0.sql
@@ -0,0 +1,91 @@
+/* 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_raw_wal_record()
+--
+CREATE FUNCTION pg_get_raw_wal_record(IN in_lsn pg_lsn,
+ OUT lsn pg_lsn,
+ OUT record bytea
+)
+AS 'MODULE_PATHNAME', 'pg_get_raw_wal_record'
+LANGUAGE C CALLED ON NULL INPUT PARALLEL SAFE;
+
+GRANT EXECUTE ON FUNCTION pg_get_raw_wal_record(pg_lsn) TO pg_monitor;
+
+--
+-- pg_get_first_valid_wal_record_lsn()
+--
+CREATE FUNCTION pg_get_first_valid_wal_record_lsn(IN in_lsn pg_lsn,
+ OUT lsn pg_lsn,
+ OUT prev_lsn pg_lsn
+)
+AS 'MODULE_PATHNAME', 'pg_get_first_valid_wal_record_lsn'
+LANGUAGE C CALLED ON NULL INPUT PARALLEL SAFE;
+
+GRANT EXECUTE ON FUNCTION pg_get_first_valid_wal_record_lsn(pg_lsn) TO pg_monitor;
+
+--
+-- pg_get_wal_record_info()
+--
+CREATE FUNCTION pg_get_wal_record_info(IN in_lsn pg_lsn,
+ OUT lsn pg_lsn,
+ OUT prev_lsn pg_lsn,
+ OUT xid xid,
+ OUT resource_manager text,
+ OUT length int4,
+ OUT total_length int4,
+ OUT description text,
+ OUT block_ref text,
+ OUT data bytea,
+ OUT data_len int4
+)
+AS 'MODULE_PATHNAME', 'pg_get_wal_record_info'
+LANGUAGE C CALLED ON NULL INPUT PARALLEL SAFE;
+
+GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) TO pg_monitor;
+
+--
+-- pg_get_wal_records_info()
+--
+CREATE FUNCTION pg_get_wal_records_info(IN start_lsn pg_lsn,
+ IN end_lsn pg_lsn DEFAULT NULL,
+ OUT lsn pg_lsn,
+ OUT prev_lsn pg_lsn,
+ OUT xid xid,
+ OUT resource_manager text,
+ OUT length int4,
+ OUT total_length int4,
+ OUT description text,
+ OUT block_ref text,
+ OUT data bytea,
+ OUT data_len int4
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_wal_records_info'
+LANGUAGE C CALLED ON NULL INPUT PARALLEL SAFE;
+
+GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) TO pg_monitor;
+
+--
+-- pg_get_wal_stats()
+--
+CREATE FUNCTION pg_get_wal_stats(IN start_lsn pg_lsn,
+ IN end_lsn pg_lsn DEFAULT NULL,
+ OUT resource_manager text,
+ OUT count int8,
+ OUT count_percentage float4,
+ OUT record_size int8,
+ OUT record_size_percentage float4,
+ OUT fpi_size int8,
+ OUT fpi_size_percentage float4,
+ OUT combined_size int8,
+ OUT combined_size_percentage float4
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_wal_stats'
+LANGUAGE C CALLED ON NULL INPUT PARALLEL SAFE;
+
+GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn) TO pg_monitor;
diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c
new file mode 100644
index 0000000000..99f2f43c42
--- /dev/null
+++ b/contrib/pg_walinspect/pg_walinspect.c
@@ -0,0 +1,949 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_walinspect.c
+ * Functions to inspect contents of PostgreSQL Write-Ahead Log
+ *
+ * Copyright (c) 2022, 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/xlogutils.h"
+#include "funcapi.h"
+#include "miscadmin.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;
+
+#define MAX_XLINFO_TYPES 16
+
+typedef struct Stats
+{
+ uint64 count;
+ uint64 rec_len;
+ uint64 fpi_len;
+} Stats;
+
+typedef struct XLogRecStats
+{
+ uint64 count;
+ Stats rmgr_stats[RM_NEXT_ID];
+ Stats record_stats[RM_NEXT_ID][MAX_XLINFO_TYPES];
+} XLogRecStats;
+
+extern void _PG_init(void);
+extern void _PG_fini(void);
+
+PG_FUNCTION_INFO_V1(pg_get_raw_wal_record);
+PG_FUNCTION_INFO_V1(pg_get_first_valid_wal_record_lsn);
+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_stats);
+
+static XLogRecPtr ValidateInputLSN(XLogRecPtr lsn);
+static XLogRecPtr ValidateStartAndEndLSNs(XLogRecPtr start_lsn,
+ XLogRecPtr end_lsn);
+static XLogReaderState *InitXLogReaderState(XLogRecPtr lsn,
+ XLogRecPtr *first_record);
+static XLogRecord *ReadNextXLogRecord(XLogReaderState *xlogreader,
+ XLogRecPtr first_record);
+static void GetXLogRecordInfo(XLogReaderState *record, XLogRecPtr lsn,
+ Datum *values, bool *nulls, uint32 ncols);
+static void StoreXLogRecordStats(XLogRecStats * stats,
+ XLogReaderState *record);
+static void GetXLogSummaryStats(XLogRecStats * stats,
+ Tuplestorestate *tupstore, TupleDesc tupdesc,
+ Datum *values, bool *nulls, uint32 ncols);
+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 GetWalStatsInternal(FunctionCallInfo fcinfo, XLogRecPtr start_lsn,
+ XLogRecPtr end_lsn);
+static void GetWALRecordsInfoInternal(FunctionCallInfo fcinfo,
+ XLogRecPtr start_lsn,
+ XLogRecPtr end_lsn);
+
+/*
+ * Module load callback.
+ */
+void
+_PG_init(void)
+{
+ /* Define custom GUCs and install hooks here, if any. */
+
+ /*
+ * Have EmitWarningsOnPlaceholders("pg_walinspect"); if custom GUCs are
+ * defined.
+ */
+}
+
+/*
+ * Module unload callback.
+ */
+void
+_PG_fini(void)
+{
+ /* Uninstall hooks, if any. */
+}
+
+/*
+ * Validate given LSN and return the LSN up to which the server has WAL.
+ */
+static XLogRecPtr
+ValidateInputLSN(XLogRecPtr lsn)
+{
+ XLogRecPtr curr_lsn;
+
+ /* Validate input WAL LSN. */
+ if (XLogRecPtrIsInvalid(lsn))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid WAL LSN")));
+
+ if (!RecoveryInProgress())
+ curr_lsn = GetFlushRecPtr(NULL);
+ else
+ curr_lsn = GetXLogReplayRecPtr(NULL);
+
+ Assert(!XLogRecPtrIsInvalid(curr_lsn));
+
+ if (lsn >= curr_lsn)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot get information of WAL at future LSN"),
+ errdetail("Last known WAL LSN on the database system is %X/%X.",
+ LSN_FORMAT_ARGS(curr_lsn))));
+
+ return curr_lsn;
+}
+
+/*
+ * Validate given start LSN and end LSN, return the new end LSN in case user
+ * hasn't specified one.
+ */
+static XLogRecPtr
+ValidateStartAndEndLSNs(XLogRecPtr start_lsn, XLogRecPtr end_lsn)
+{
+ XLogRecPtr curr_lsn;
+
+ /* Validate WAL start LSN. */
+ if (XLogRecPtrIsInvalid(start_lsn))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid WAL start LSN")));
+
+ if (!RecoveryInProgress())
+ curr_lsn = GetFlushRecPtr(NULL);
+ else
+ curr_lsn = GetXLogReplayRecPtr(NULL);
+
+ if (start_lsn >= curr_lsn)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("WAL start LSN cannot be a future WAL LSN"),
+ errdetail("Last known WAL LSN on the database system is %X/%X.",
+ LSN_FORMAT_ARGS(curr_lsn))));
+
+ /*
+ * If end_lsn is specified, let's ensure that it's not a future LSN i.e.
+ * something the database system doesn't know about.
+ */
+ if (!XLogRecPtrIsInvalid(end_lsn) &&
+ (end_lsn >= curr_lsn))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("WAL end LSN cannot be a future WAL LSN"),
+ errdetail("Last known WAL LSN on the database system is %X/%X.",
+ LSN_FORMAT_ARGS(curr_lsn))));
+
+ /*
+ * When end_lsn is not specified let's read up to the last WAL position
+ * known to be on the server.
+ */
+ if (XLogRecPtrIsInvalid(end_lsn))
+ end_lsn = curr_lsn;
+
+ Assert(!XLogRecPtrIsInvalid(end_lsn));
+
+ if (start_lsn >= end_lsn)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("WAL start LSN must be less than end LSN")));
+
+ return end_lsn;
+}
+
+/*
+ * Intialize WAL reader and identify first valid LSN.
+ */
+static XLogReaderState *
+InitXLogReaderState(XLogRecPtr lsn, XLogRecPtr *first_record)
+{
+ XLogReaderState *xlogreader;
+
+ /*
+ * Reading WAL below the first page of the first sgements 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))));
+
+ xlogreader = XLogReaderAllocate(wal_segment_size, NULL,
+ XL_ROUTINE(.page_read = &read_local_xlog_page,
+ .segment_open = &wal_segment_open,
+ .segment_close = &wal_segment_close),
+ NULL);
+
+ 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_record = XLogFindNextRecord(xlogreader, lsn);
+
+ if (XLogRecPtrIsInvalid(*first_record))
+ ereport(ERROR,
+ (errmsg("could not find a valid record after %X/%X",
+ LSN_FORMAT_ARGS(lsn))));
+
+ /*
+ * Display a message that we're skipping data if the given LSN wasn't a
+ * pointer to the start of a record and also wasn't a pointer to the
+ * beginning of a segment (e.g. we were used in file mode).
+ */
+ if (*first_record != lsn && XLogSegmentOffset(lsn, wal_segment_size) != 0)
+ ereport(WARNING,
+ (errmsg_plural("first record is after %X/%X, at %X/%X, skipping over %u byte",
+ "first record is after %X/%X, at %X/%X, skipping over %u bytes",
+ (*first_record - lsn),
+ LSN_FORMAT_ARGS(lsn),
+ LSN_FORMAT_ARGS(*first_record),
+ (uint32) (*first_record - lsn))));
+
+ return xlogreader;
+}
+
+/*
+ * Read next WAL record.
+ */
+static XLogRecord *
+ReadNextXLogRecord(XLogReaderState *xlogreader, XLogRecPtr first_record)
+{
+ XLogRecord *record;
+ char *errormsg;
+
+ record = XLogReadRecord(xlogreader, &errormsg);
+
+ if (record == NULL)
+ {
+ if (errormsg)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not read WAL at %X/%X: %s",
+ LSN_FORMAT_ARGS(first_record), errormsg)));
+ else
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not read WAL at %X/%X",
+ LSN_FORMAT_ARGS(first_record))));
+ }
+
+ return record;
+}
+
+/*
+ * Get raw WAL record.
+ *
+ * This function emits an error if a future WAL LSN i.e. WAL LSN the database
+ * system doesn't know about is specified.
+ */
+Datum
+pg_get_raw_wal_record(PG_FUNCTION_ARGS)
+{
+#define PG_GET_RAW_WAL_RECORD_COLS 2
+ XLogRecPtr lsn;
+ XLogRecord *record;
+ XLogRecPtr first_record;
+ XLogReaderState *xlogreader;
+ bytea *raw_record;
+ uint32 rec_len;
+ char *raw_record_data;
+ TupleDesc tupdesc;
+ Datum result;
+ HeapTuple tuple;
+ Datum values[PG_GET_RAW_WAL_RECORD_COLS];
+ bool nulls[PG_GET_RAW_WAL_RECORD_COLS];
+ int i = 0;
+
+ lsn = PG_GETARG_LSN(0);
+
+ (void) ValidateInputLSN(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, &first_record);
+
+ Assert(xlogreader);
+
+ record = ReadNextXLogRecord(xlogreader, first_record);
+
+ rec_len = XLogRecGetTotalLen(xlogreader);
+
+ Assert(rec_len > 0);
+
+ raw_record = (bytea *) palloc(rec_len + VARHDRSZ);
+ SET_VARSIZE(raw_record, rec_len + VARHDRSZ);
+ raw_record_data = VARDATA(raw_record);
+
+ memcpy(raw_record_data, record, rec_len);
+
+ XLogReaderFree(xlogreader);
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ values[i++] = LSNGetDatum(first_record);
+ values[i++] = PointerGetDatum(raw_record);
+
+ Assert(i == PG_GET_RAW_WAL_RECORD_COLS);
+
+ tuple = heap_form_tuple(tupdesc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+
+ PG_RETURN_DATUM(result);
+#undef PG_GET_RAW_WAL_RECORD_COLS
+}
+
+/*
+ * Get first valid raw WAL record LSN following the given LSN.
+ *
+ * This function emits an error if a future WAL LSN i.e. WAL LSN the database
+ * system doesn't know about is specified.
+ */
+Datum
+pg_get_first_valid_wal_record_lsn(PG_FUNCTION_ARGS)
+{
+#define PG_GET_FIRST_VALID_WAL_RECORD_LSN_COLS 2
+ XLogRecPtr lsn;
+ XLogRecPtr first_record;
+ XLogRecPtr prev_record;
+ XLogReaderState *xlogreader;
+ TupleDesc tupdesc;
+ Datum result;
+ HeapTuple tuple;
+ Datum values[PG_GET_FIRST_VALID_WAL_RECORD_LSN_COLS];
+ bool nulls[PG_GET_FIRST_VALID_WAL_RECORD_LSN_COLS];
+ int i = 0;
+
+ lsn = PG_GETARG_LSN(0);
+
+ (void) ValidateInputLSN(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, &first_record);
+
+ Assert(xlogreader);
+
+ (void) ReadNextXLogRecord(xlogreader, first_record);
+
+ prev_record = XLogRecGetPrev(xlogreader);
+
+ XLogReaderFree(xlogreader);
+
+ /*
+ * Previous valid WAL record must be at an LSN lower than next valid WAL
+ * record LSN. Otherwise, it is an indication of something wrong, so error
+ * out.
+ */
+ if (prev_record >= first_record)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("record with incorrect prev-link %X/%X at %X/%X",
+ LSN_FORMAT_ARGS(prev_record),
+ LSN_FORMAT_ARGS(first_record))));
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ values[i++] = LSNGetDatum(first_record);
+ values[i++] = LSNGetDatum(prev_record);
+
+ Assert(i == PG_GET_FIRST_VALID_WAL_RECORD_LSN_COLS);
+
+ tuple = heap_form_tuple(tupdesc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+
+ PG_RETURN_DATUM(result);
+#undef PG_GET_FIRST_VALID_WAL_RECORD_LSN_COLS
+}
+
+/*
+ * Calculate size of a record, split into !FPI and FPI parts.
+ */
+static void
+GetXLogRecordLen(XLogReaderState *record, uint32 *rec_len, uint32 *fpi_len)
+{
+ int block_id;
+
+ /*
+ * Calculate the amount of FPI data in the record.
+ *
+ * XXX: We peek into xlogreader's private decoded backup blocks for the
+ * bimg_len indicating the length of FPI data. It doesn't seem worth it to
+ * add an accessor macro for this.
+ */
+ *fpi_len = 0;
+ for (block_id = 0; block_id <= record->max_block_id; block_id++)
+ {
+ if (XLogRecHasBlockImage(record, block_id))
+ *fpi_len += record->blocks[block_id].bimg_len;
+ }
+
+ /*
+ * Calculate the length of the record as the total length - the length of
+ * all the block images.
+ */
+ *rec_len = XLogRecGetTotalLen(record) - *fpi_len;
+}
+
+/*
+ * Get WAL record info.
+ */
+static void
+GetXLogRecordInfo(XLogReaderState *record, XLogRecPtr lsn,
+ Datum *values, bool *nulls, uint32 ncols)
+{
+ const char *id;
+ const RmgrData *desc;
+ uint32 rec_len;
+ uint32 fpi_len;
+ RelFileNode rnode;
+ ForkNumber forknum;
+ BlockNumber blk;
+ int block_id;
+ StringInfoData rec_desc;
+ StringInfoData rec_blk_ref;
+ StringInfoData temp;
+ bytea *data;
+ char *main_data;
+ uint32 main_data_len;
+ int i = 0;
+
+ desc = &RmgrTable[XLogRecGetRmid(record)];
+
+ GetXLogRecordLen(record, &rec_len, &fpi_len);
+
+ values[i++] = LSNGetDatum(lsn);
+ values[i++] = LSNGetDatum(XLogRecGetPrev(record));
+ values[i++] = TransactionIdGetDatum(XLogRecGetXid(record));
+ values[i++] = CStringGetTextDatum(desc->rm_name);
+ values[i++] = UInt32GetDatum(rec_len);
+ values[i++] = UInt32GetDatum(XLogRecGetTotalLen(record));
+
+ initStringInfo(&rec_desc);
+
+ id = desc->rm_identify(XLogRecGetInfo(record));
+
+ if (id == NULL)
+ appendStringInfo(&rec_desc, "UNKNOWN (%x) ", XLogRecGetInfo(record) & ~XLR_INFO_MASK);
+ else
+ appendStringInfo(&rec_desc, "%s ", id);
+
+ initStringInfo(&temp);
+
+ desc->rm_desc(&temp, record);
+ appendStringInfo(&rec_desc, "%s", temp.data);
+
+ values[i++] = CStringGetTextDatum(rec_desc.data);
+
+ pfree(temp.data);
+
+ initStringInfo(&rec_blk_ref);
+
+ /* Block references (detailed format). */
+ for (block_id = 0; block_id <= record->max_block_id; block_id++)
+ {
+ if (!XLogRecHasBlockRef(record, block_id))
+ continue;
+
+ XLogRecGetBlockTag(record, block_id, &rnode, &forknum, &blk);
+
+ if (forknum != MAIN_FORKNUM)
+ {
+ appendStringInfo(&rec_blk_ref,
+ "blkref #%u: rel %u/%u/%u fork %s blk %u",
+ block_id, rnode.spcNode, rnode.dbNode,
+ rnode.relNode, get_forkname(forknum), blk);
+ }
+ else
+ {
+ appendStringInfo(&rec_blk_ref,
+ "blkref #%u: rel %u/%u/%u blk %u",
+ block_id, rnode.spcNode, rnode.dbNode,
+ rnode.relNode, blk);
+ }
+
+ if (XLogRecHasBlockImage(record, block_id))
+ {
+ uint8 bimg_info = record->blocks[block_id].bimg_info;
+
+ if (BKPIMAGE_COMPRESSED(bimg_info))
+ {
+ const char *method;
+
+ if ((bimg_info & BKPIMAGE_COMPRESS_PGLZ) != 0)
+ method = "pglz";
+ else if ((bimg_info & BKPIMAGE_COMPRESS_LZ4) != 0)
+ method = "lz4";
+ else
+ method = "unknown";
+
+ appendStringInfo(&rec_blk_ref, " (FPW%s); hole: offset: %u, length: %u, "
+ "compression saved: %u, method: %s",
+ XLogRecBlockImageApply(record, block_id) ?
+ "" : " for WAL verification",
+ record->blocks[block_id].hole_offset,
+ record->blocks[block_id].hole_length,
+ BLCKSZ -
+ record->blocks[block_id].hole_length -
+ record->blocks[block_id].bimg_len,
+ method);
+ }
+ else
+ {
+ appendStringInfo(&rec_blk_ref, " (FPW%s); hole: offset: %u, length: %u",
+ XLogRecBlockImageApply(record, block_id) ?
+ "" : " for WAL verification",
+ record->blocks[block_id].hole_offset,
+ record->blocks[block_id].hole_length);
+ }
+ }
+ }
+
+ values[i++] = CStringGetTextDatum(rec_blk_ref.data);
+
+ main_data_len = XLogRecGetDataLen(record);
+
+ data = (bytea *) palloc(main_data_len + VARHDRSZ);
+ SET_VARSIZE(data, main_data_len + VARHDRSZ);
+ main_data = VARDATA(data);
+
+ memcpy(main_data, XLogRecGetData(record), main_data_len);
+
+ values[i++] = PointerGetDatum(data);
+ values[i++] = UInt32GetDatum(main_data_len);
+
+ Assert(i == ncols);
+}
+
+/*
+ * Get WAL record info and data.
+ *
+ * This function emits an error if a future WAL LSN i.e. WAL LSN the database
+ * system doesn't know about is specified.
+ */
+Datum
+pg_get_wal_record_info(PG_FUNCTION_ARGS)
+{
+#define PG_GET_WAL_RECORD_INFO_COLS 10
+ XLogRecPtr lsn;
+ XLogRecPtr first_record;
+ XLogReaderState *xlogreader;
+ TupleDesc tupdesc;
+ Datum result;
+ HeapTuple tuple;
+ Datum values[PG_GET_WAL_RECORD_INFO_COLS];
+ bool nulls[PG_GET_WAL_RECORD_INFO_COLS];
+
+ lsn = PG_GETARG_LSN(0);
+
+ (void) ValidateInputLSN(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, &first_record);
+
+ Assert(xlogreader);
+
+ (void) ReadNextXLogRecord(xlogreader, first_record);
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ GetXLogRecordInfo(xlogreader, first_record, values, nulls,
+ PG_GET_WAL_RECORD_INFO_COLS);
+
+ XLogReaderFree(xlogreader);
+
+ tuple = heap_form_tuple(tupdesc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+
+ PG_RETURN_DATUM(result);
+#undef PG_GET_WAL_RECORD_INFO_COLS
+}
+
+/*
+ * Get info and data of all WAL records between start LSN and end LSN.
+ */
+static void
+GetWALRecordsInfoInternal(FunctionCallInfo fcinfo, XLogRecPtr start_lsn,
+ XLogRecPtr end_lsn)
+{
+#define PG_GET_WAL_RECORDS_INFO_COLS 10
+ XLogRecPtr first_record;
+ XLogReaderState *xlogreader;
+ ReturnSetInfo *rsinfo;
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+ Datum values[PG_GET_WAL_RECORDS_INFO_COLS];
+ bool nulls[PG_GET_WAL_RECORDS_INFO_COLS];
+
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ /* Check to see if caller supports us returning a tuplestore. */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not allowed in this context")));
+
+ /* 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");
+
+ /* Build tuplestore to hold the result rows. */
+ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ tupstore = tuplestore_begin_heap(true, false, work_mem);
+ rsinfo->returnMode = SFRM_Materialize;
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+
+ MemoryContextSwitchTo(oldcontext);
+
+ xlogreader = InitXLogReaderState(start_lsn, &first_record);
+
+ Assert(xlogreader);
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ for (;;)
+ {
+ /* Exit loop if read up to end_lsn. */
+ if (!XLogRecPtrIsInvalid(end_lsn) &&
+ xlogreader->EndRecPtr >= end_lsn)
+ break;
+
+ (void) ReadNextXLogRecord(xlogreader, first_record);
+
+ GetXLogRecordInfo(xlogreader, xlogreader->currRecPtr, values, nulls,
+ PG_GET_WAL_RECORDS_INFO_COLS);
+
+ tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+
+ CHECK_FOR_INTERRUPTS();
+ }
+
+ XLogReaderFree(xlogreader);
+
+ /* Clean up and return the tuplestore. */
+ tuplestore_donestoring(tupstore);
+#undef PG_GET_WAL_RECORDS_INFO_COLS
+}
+
+/*
+ * Get info and data of all WAL records between start LSN and end LSN.
+ *
+ * This function emits an error if a future end WAL LSN i.e. WAL LSN the
+ * database system doesn't know about is specified.
+ *
+ * This function will figure out the end LSN if it's not specified.
+ */
+Datum
+pg_get_wal_records_info(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr start_lsn;
+ XLogRecPtr end_lsn;
+
+ start_lsn = PG_GETARG_LSN(0);
+ end_lsn = PG_GETARG_LSN(1);
+
+ end_lsn = ValidateStartAndEndLSNs(start_lsn, end_lsn);
+
+ GetWALRecordsInfoInternal(fcinfo, start_lsn, end_lsn);
+
+ PG_RETURN_VOID();
+}
+
+/*
+ * Store per-rmgr and per-record statistics for a given record.
+ */
+static void
+StoreXLogRecordStats(XLogRecStats * stats, XLogReaderState *record)
+{
+ RmgrId rmid;
+ uint8 recid;
+ uint32 rec_len;
+ uint32 fpi_len;
+
+ stats->count++;
+
+ rmid = XLogRecGetRmid(record);
+
+ GetXLogRecordLen(record, &rec_len, &fpi_len);
+
+ /* Update per-rmgr statistics. */
+ stats->rmgr_stats[rmid].count++;
+ stats->rmgr_stats[rmid].rec_len += rec_len;
+ stats->rmgr_stats[rmid].fpi_len += fpi_len;
+
+ /*
+ * Update per-record statistics, where the record is identified by a
+ * combination of the RmgrId and the four bits of the xl_info field that
+ * are the rmgr's domain (resulting in sixteen possible entries per
+ * RmgrId).
+ */
+ recid = XLogRecGetInfo(record) >> 4;
+
+ /*
+ * XACT records need to be handled differently. Those records use the
+ * first bit of those four bits for an optional flag variable and the
+ * following three bits for the opcode. We filter opcode out of xl_info
+ * and use it as the identifier of the record.
+ */
+ if (rmid == RM_XACT_ID)
+ recid &= 0x07;
+
+ stats->record_stats[rmid][recid].count++;
+ stats->record_stats[rmid][recid].rec_len += rec_len;
+ stats->record_stats[rmid][recid].fpi_len += fpi_len;
+}
+
+/*
+ * 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;
+ double rec_len_pct;
+ double fpi_len_pct;
+ double 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++] = Float4GetDatum(n_pct);
+ values[i++] = Int64GetDatum(rec_len);
+ values[i++] = Float4GetDatum(rec_len_pct);
+ values[i++] = Int64GetDatum(fpi_len);
+ values[i++] = Float4GetDatum(fpi_len_pct);
+ values[i++] = Int64GetDatum(tot_len);
+ values[i++] = Float4GetDatum(tot_len_pct);
+
+ Assert(i == ncols);
+}
+
+/*
+ * Get summary statistics about the records seen so far.
+ */
+static void
+GetXLogSummaryStats(XLogRecStats * stats, Tuplestorestate *tupstore,
+ TupleDesc tupdesc, Datum *values, bool *nulls,
+ uint32 ncols)
+{
+ 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_NEXT_ID; ri++)
+ {
+ 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;
+
+ for (ri = 0; ri < RM_NEXT_ID; ri++)
+ {
+ uint64 count;
+ uint64 rec_len;
+ uint64 fpi_len;
+ uint64 tot_len;
+ const RmgrData *desc = &RmgrTable[ri];
+
+ 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;
+
+ 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(tupstore, tupdesc, values, nulls);
+ }
+}
+
+/*
+ * Get WAL stats between start LSN and end LSN.
+ */
+static void
+GetWalStatsInternal(FunctionCallInfo fcinfo, XLogRecPtr start_lsn,
+ XLogRecPtr end_lsn)
+{
+#define PG_GET_WAL_STATS_COLS 9
+ XLogRecPtr first_record;
+ XLogReaderState *xlogreader;
+ XLogRecStats stats;
+ ReturnSetInfo *rsinfo;
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+ Datum values[PG_GET_WAL_STATS_COLS];
+ bool nulls[PG_GET_WAL_STATS_COLS];
+
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ /* Check to see if caller supports us returning a tuplestore. */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not allowed in this context")));
+
+ /* 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");
+
+ /* Build tuplestore to hold the result rows. */
+ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ tupstore = tuplestore_begin_heap(true, false, work_mem);
+ rsinfo->returnMode = SFRM_Materialize;
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+
+ MemoryContextSwitchTo(oldcontext);
+
+ xlogreader = InitXLogReaderState(start_lsn, &first_record);
+
+ MemSet(&stats, 0, sizeof(stats));
+
+ for (;;)
+ {
+ /* Exit loop if read up to end_lsn. */
+ if (!XLogRecPtrIsInvalid(end_lsn) &&
+ xlogreader->EndRecPtr >= end_lsn)
+ break;
+
+ (void) ReadNextXLogRecord(xlogreader, first_record);
+
+ StoreXLogRecordStats(&stats, xlogreader);
+
+ CHECK_FOR_INTERRUPTS();
+ }
+
+ XLogReaderFree(xlogreader);
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ GetXLogSummaryStats(&stats, tupstore, tupdesc, values, nulls,
+ PG_GET_WAL_STATS_COLS);
+
+ /* Clean up and return the tuplestore. */
+ tuplestore_donestoring(tupstore);
+#undef PG_GET_WAL_STATS_COLS
+}
+
+/*
+ * Get WAL stats between start LSN and end LSN.
+ *
+ * This function emits an error if a future end WAL LSN i.e. WAL LSN the
+ * database system doesn't know about is specified.
+ *
+ * This function will figure out the end LSN if it's not specified.
+ */
+Datum
+pg_get_wal_stats(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr start_lsn;
+ XLogRecPtr end_lsn;
+
+ start_lsn = PG_GETARG_LSN(0);
+ end_lsn = PG_GETARG_LSN(1);
+
+ end_lsn = ValidateStartAndEndLSNs(start_lsn, end_lsn);
+
+ GetWalStatsInternal(fcinfo, start_lsn, end_lsn);
+
+ PG_RETURN_VOID();
+}
diff --git a/contrib/pg_walinspect/pg_walinspect.control b/contrib/pg_walinspect/pg_walinspect.control
new file mode 100644
index 0000000000..017e56a2bb
--- /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.0'
+module_pathname = '$libdir/pg_walinspect'
+relocatable = true
diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c
index 35029cf97d..4d217cabbe 100644
--- a/src/backend/access/transam/xlogreader.c
+++ b/src/backend/access/transam/xlogreader.c
@@ -956,13 +956,6 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
return true;
}
-#ifdef FRONTEND
-/*
- * Functions that are currently not needed in the backend, but are better
- * implemented inside xlogreader.c because of the internal facilities available
- * here.
- */
-
/*
* Find the first record with an lsn >= RecPtr.
*
@@ -1080,6 +1073,13 @@ err:
return InvalidXLogRecPtr;
}
+#ifdef FRONTEND
+/*
+ * Functions that are currently not needed in the backend, but are better
+ * implemented inside xlogreader.c because of the internal facilities available
+ * here.
+ */
+
#endif /* FRONTEND */
/*
diff --git a/src/bin/pg_waldump/pg_waldump.c b/src/bin/pg_waldump/pg_waldump.c
index 2340dc247b..60940d30c2 100644
--- a/src/bin/pg_waldump/pg_waldump.c
+++ b/src/bin/pg_waldump/pg_waldump.c
@@ -26,6 +26,11 @@
#include "getopt_long.h"
#include "rmgrdesc.h"
+/*
+ * NOTE: For any code change or issue fix here, it is highly recommended to
+ * give a thought about doing the same in pg_walinspect contrib module as well.
+ */
+
static const char *progname;
static int WalSegSz;
diff --git a/src/common/relpath.c b/src/common/relpath.c
index 636c96efd3..e8e3c44eae 100644
--- a/src/common/relpath.c
+++ b/src/common/relpath.c
@@ -40,6 +40,24 @@ const char *const forkNames[] = {
StaticAssertDecl(lengthof(forkNames) == (MAX_FORKNUM + 1),
"array length mismatch");
+/*
+ * get_forkname - return fork name given fork number
+ *
+ * This function is defined with "extern PGDLLIMPORT ..." in the core here so
+ * that the loadable modules can access it.
+ */
+const char *const
+get_forkname(ForkNumber num)
+{
+ /*
+ * As this function gets called by external modules, let's ensure that the
+ * fork number passed in is valid.
+ */
+ Assert(num > InvalidForkNumber && num <= MAX_FORKNUM);
+
+ return forkNames[num];
+}
+
/*
* forkname_to_number - look up fork number by name
*
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 4b45ac64db..f34f228563 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -31,7 +31,7 @@ extern XLogRecPtr XactLastRecEnd;
extern PGDLLIMPORT XLogRecPtr XactLastCommitEnd;
/* these variables are GUC parameters related to XLOG */
-extern int wal_segment_size;
+extern PGDLLIMPORT int wal_segment_size;
extern int min_wal_size_mb;
extern int max_wal_size_mb;
extern int wal_keep_size_mb;
diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h
index 849954a8e5..38fd51bafa 100644
--- a/src/include/access/xlog_internal.h
+++ b/src/include/access/xlog_internal.h
@@ -319,7 +319,7 @@ typedef struct RmgrData
struct XLogRecordBuffer *buf);
} RmgrData;
-extern const RmgrData RmgrTable[];
+extern PGDLLIMPORT const RmgrData RmgrTable[];
/*
* Exported to support xlog switching from checkpointer
diff --git a/src/include/access/xlogreader.h b/src/include/access/xlogreader.h
index 477f0efe26..d62d6ce7f8 100644
--- a/src/include/access/xlogreader.h
+++ b/src/include/access/xlogreader.h
@@ -276,9 +276,7 @@ extern void XLogReaderFree(XLogReaderState *state);
/* Position the XLogReader to given record */
extern void XLogBeginRead(XLogReaderState *state, XLogRecPtr RecPtr);
-#ifdef FRONTEND
extern XLogRecPtr XLogFindNextRecord(XLogReaderState *state, XLogRecPtr RecPtr);
-#endif /* FRONTEND */
/* Read the next XLog record. Returns NULL on end-of-WAL or failure */
extern struct XLogRecord *XLogReadRecord(XLogReaderState *state,
diff --git a/src/include/common/relpath.h b/src/include/common/relpath.h
index a4b5dc853b..3743f2e505 100644
--- a/src/include/common/relpath.h
+++ b/src/include/common/relpath.h
@@ -57,6 +57,7 @@ typedef enum ForkNumber
#define FORKNAMECHARS 4 /* max chars for a fork name */
extern const char *const forkNames[];
+extern PGDLLIMPORT const char *const get_forkname(ForkNumber num);
extern ForkNumber forkname_to_number(const char *forkName);
extern int forkname_chars(const char *str, ForkNumber *fork);
--
2.25.1
From fc6c124efa87ff3c9840bcf444697f762a4cf1fe Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 4 Mar 2022 08:29:35 +0000
Subject: [PATCH v7] pg_walinspect tests
---
.../pg_walinspect/expected/pg_walinspect.out | 69 +++++++++++++++++++
contrib/pg_walinspect/sql/pg_walinspect.sql | 58 ++++++++++++++++
2 files changed, 127 insertions(+)
create mode 100644 contrib/pg_walinspect/expected/pg_walinspect.out
create mode 100644 contrib/pg_walinspect/sql/pg_walinspect.sql
diff --git a/contrib/pg_walinspect/expected/pg_walinspect.out b/contrib/pg_walinspect/expected/pg_walinspect.out
new file mode 100644
index 0000000000..ad295976dd
--- /dev/null
+++ b/contrib/pg_walinspect/expected/pg_walinspect.out
@@ -0,0 +1,69 @@
+CREATE EXTENSION pg_walinspect;
+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);
+SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
+INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
+-- ===================================================================
+-- tests for input validation
+-- ===================================================================
+SELECT pg_get_raw_wal_record('0/0'); -- ERROR
+ERROR: invalid WAL LSN
+SELECT pg_get_first_valid_wal_record_lsn('0/0'); -- ERROR
+ERROR: invalid WAL LSN
+SELECT pg_get_wal_record_info('0/0'); -- ERROR
+ERROR: invalid WAL LSN
+SELECT pg_get_wal_records_info('0/0', '0/0'); -- ERROR
+ERROR: invalid WAL start LSN
+SELECT pg_get_wal_stats('0/0', '0/0'); -- ERROR
+ERROR: invalid WAL start LSN
+-- ===================================================================
+-- tests for all function executions
+-- ===================================================================
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_first_valid_wal_record_lsn(:'wal_lsn1');
+ ok
+----
+ t
+(1 row)
+
+SELECT lsn AS valid_wal_lsn1 FROM pg_get_first_valid_wal_record_lsn(:'wal_lsn1') \gset
+SELECT lsn AS valid_wal_lsn2 FROM pg_get_first_valid_wal_record_lsn(:'wal_lsn2') \gset
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_raw_wal_record(:'valid_wal_lsn1');
+ ok
+----
+ t
+(1 row)
+
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info(:'valid_wal_lsn1');
+ ok
+----
+ t
+(1 row)
+
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'valid_wal_lsn1', :'valid_wal_lsn2');
+ ok
+----
+ t
+(1 row)
+
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'valid_wal_lsn1', :'valid_wal_lsn2');
+ ok
+----
+ t
+(1 row)
+
+-- ===================================================================
+-- tests 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(:'valid_wal_lsn1', :'valid_wal_lsn2')
+ WHERE block_ref LIKE concat('%', :'sample_tbl_oid', '%') AND resource_manager = 'Heap';
+ ok
+----
+ t
+(1 row)
+
+-- ===================================================================
+-- clean up
+-- ===================================================================
+DROP TABLE sample_tbl;
diff --git a/contrib/pg_walinspect/sql/pg_walinspect.sql b/contrib/pg_walinspect/sql/pg_walinspect.sql
new file mode 100644
index 0000000000..013b285bdb
--- /dev/null
+++ b/contrib/pg_walinspect/sql/pg_walinspect.sql
@@ -0,0 +1,58 @@
+CREATE EXTENSION pg_walinspect;
+
+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);
+
+SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
+
+INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
+
+-- ===================================================================
+-- tests for input validation
+-- ===================================================================
+
+SELECT pg_get_raw_wal_record('0/0'); -- ERROR
+
+SELECT pg_get_first_valid_wal_record_lsn('0/0'); -- ERROR
+
+SELECT pg_get_wal_record_info('0/0'); -- ERROR
+
+SELECT pg_get_wal_records_info('0/0', '0/0'); -- ERROR
+
+SELECT pg_get_wal_stats('0/0', '0/0'); -- ERROR
+
+-- ===================================================================
+-- tests for all function executions
+-- ===================================================================
+
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_first_valid_wal_record_lsn(:'wal_lsn1');
+
+SELECT lsn AS valid_wal_lsn1 FROM pg_get_first_valid_wal_record_lsn(:'wal_lsn1') \gset
+
+SELECT lsn AS valid_wal_lsn2 FROM pg_get_first_valid_wal_record_lsn(:'wal_lsn2') \gset
+
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_raw_wal_record(:'valid_wal_lsn1');
+
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info(:'valid_wal_lsn1');
+
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'valid_wal_lsn1', :'valid_wal_lsn2');
+
+SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'valid_wal_lsn1', :'valid_wal_lsn2');
+
+-- ===================================================================
+-- tests 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(:'valid_wal_lsn1', :'valid_wal_lsn2')
+ WHERE block_ref LIKE concat('%', :'sample_tbl_oid', '%') AND resource_manager = 'Heap';
+
+-- ===================================================================
+-- clean up
+-- ===================================================================
+
+DROP TABLE sample_tbl;
--
2.25.1
From 1efcb8fc07cf5f5b1b2987339321dd803c96275a Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 4 Mar 2022 09:15:21 +0000
Subject: [PATCH v7] pg_walinspect docs
---
doc/src/sgml/contrib.sgml | 1 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/pgwalinspect.sgml | 128 +++++++++++++++++++++++++++++++++
3 files changed, 130 insertions(+)
create mode 100644 doc/src/sgml/pgwalinspect.sgml
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index be9711c6f2..19614a42e1 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -130,6 +130,7 @@ CREATE EXTENSION <replaceable>module_name</replaceable>;
&pgsurgery;
&pgtrgm;
&pgvisibility;
+ &pgwalinspect;
&postgres-fdw;
&seg;
&sepgsql;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 328cd1f378..a2e8fd4a08 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -146,6 +146,7 @@
<!ENTITY pgsurgery SYSTEM "pgsurgery.sgml">
<!ENTITY pgtrgm SYSTEM "pgtrgm.sgml">
<!ENTITY pgvisibility SYSTEM "pgvisibility.sgml">
+<!ENTITY pgwalinspect SYSTEM "pgwalinspect.sgml">
<!ENTITY postgres-fdw SYSTEM "postgres-fdw.sgml">
<!ENTITY seg SYSTEM "seg.sgml">
<!ENTITY contrib-spi SYSTEM "contrib-spi.sgml">
diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml
new file mode 100644
index 0000000000..5bc79c80ae
--- /dev/null
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -0,0 +1,128 @@
+<!-- doc/src/sgml/pgwalinspect.sgml -->
+
+<sect1 id="pgwalinspect" xreflabel="pg_walinspect">
+ <title>pg_walinspect</title>
+
+ <indexterm zone="pgwalinspect">
+ <primary>pg_walinspect</primary>
+ </indexterm>
+
+ <para>
+ The <filename>pg_walinspect</filename> module provides functions that allow
+ you to inspect the contents of write-ahead log of <productname>PostgreSQL</productname>
+ database cluster at a low level, which is useful for debugging or analytical
+ or reporting or educational purposes.
+ </para>
+
+ <para>
+ By default, use of these functions is restricted to superusers and members of
+ the <literal>pg_monitor</literal> role. Access may be granted to others using
+ <command>GRANT</command>.
+ </para>
+
+ <para>
+ All the functions of this module will provide the WAL information using the
+ current server's timeline ID.
+ </para>
+
+ <sect2>
+ <title>General Functions</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <function>pg_get_raw_wal_record(in_lsn pg_lsn, lsn OUT pg_lsn, record OUT bytea)</function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets raw WAL record data of a given LSN. Issues a warning if the given
+ LSN wasn't a pointer to the start of a record and also wasn't a pointer
+ to the beginning of a WAL segment file. This function emits an error if
+ a future (the LSN database system doesn't know about)
+ <replaceable>in_lsn</replaceable> is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_get_first_valid_wal_record_lsn(in_lsn pg_lsn, lsn OUT pg_lsn, prev_lsn OUT pg_lsn)</function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets first and previous valid WAL record LSNs of the given LSN. Issues
+ a warning if the given LSN wasn't a pointer to the start of a record and
+ also wasn't a pointer to the beginning of a WAL segment file. This
+ function emits an error if a future (the LSN database system doesn't know
+ about) <replaceable>in_lsn</replaceable> is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_get_wal_record_info(in_lsn pg_lsn, lsn OUT pg_lsn, prev_lsn OUT pg_lsn, xid OUT xid, resource_manager OUT text, length OUT int4, total_length OUT int4, description OUT text, block_ref OUT text, data OUT bytea, data_len OUT int4)</function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets WAL record information of the given LSN. Issues a warning if the
+ given LSN wasn't a pointer to the start of a record and also wasn't a
+ pointer to the beginning of a WAL segment file. This function emits an
+ error if a future (the LSN database system doesn't know about)
+ <replaceable>in_lsn</replaceable> is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_get_wal_records_info(start_lsn pg_lsn, end_lsn pg_lsn DEFAULT NULL, lsn OUT pg_lsn, prev_lsn OUT pg_lsn, xid OUT xid, resource_manager OUT text, length OUT int4, total_length OUT int4, description OUT text, block_ref OUT text, data OUT bytea, data_len OUT int4)</function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets information of all the valid WAL records between
+ <replaceable>start_lsn</replaceable> and <replaceable>end_lsn</replaceable>.
+ Returns one row per each valid WAL record. Issues a warning if the given
+ <replaceable>start_lsn</replaceable> wasn't a pointer to the start of a
+ record and also wasn't a pointer to the beginning of a WAL segment file.
+ This function figures out the <replaceable>end_lsn</replaceable> if it's
+ not specified, that means, it returns information up to the end of WAL.
+ Default value of <replaceable>end_lsn</replaceable> is <literal>NULL</literal>.
+ This function emits an error if a future (the LSN database system doesn't
+ know about) <replaceable>start_lsn</replaceable> or <replaceable>end_lsn</replaceable>
+ is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>pg_get_wal_stats(start_lsn pg_lsn, end_lsn pg_lsn, wait_for_wal boolean DEFAULT false, resource_manager OUT text, count OUT int8, count_percentage OUT float4, record_size OUT int8, record_size_percentage OUT float4, fpi_size OUT int8, fpi_size_percentage OUT float4, combined_size OUT int8, combined_size_percentage OUT float4)</function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets statistics of all the valid WAL records between
+ <replaceable>start_lsn</replaceable> and <replaceable>end_lsn</replaceable>.
+ Returns one row per each <replaceable>resource_manager</replaceable>
+ type. Issues a warning if the given <replaceable>start_lsn</replaceable>
+ wasn't a pointer to the start of a record and also wasn't a pointer to
+ the beginning of a WAL segment file. This function figures out the
+ <replaceable>end_lsn</replaceable> if it's not specified, that means, it
+ returns information up to the end of WAL. Default value of
+ <replaceable>end_lsn</replaceable> is <literal>NULL</literal>. This
+ function emits an error if a future (the LSN database system doesn't know
+ about) <replaceable>start_lsn</replaceable> or <replaceable>end_lsn</replaceable>
+ is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
+
+</sect1>
--
2.25.1
