On Fri, Jan 6, 2023 at 11:47 AM Bharath Rupireddy
<bharath.rupireddyforpostg...@gmail.com> wrote:
>
> On Thu, Jan 5, 2023 at 6:51 PM Bharath Rupireddy
> <bharath.rupireddyforpostg...@gmail.com> wrote:
> >
> > > I'm also wondering if it would make sense to extend the test coverage of
> > > it (and pg_waldump) to "validate" that both
> > > extracted images are the same and matches the one modified right after
> > > the checkpoint.
> > >
> > > What do you think? (could be done later in another patch though).
> >
> > I think pageinspect can be used here. We can fetch the raw page from
> > the table after the checkpoint and raw FPI from the WAL record logged
> > as part of the update. I've tried to do so [1], but I see a slight
> > difference in the raw output. The expectation is that they both be the
> > same. It might be that the update operation logs the FPI with some
> > more info set (prune_xid). I'll try to see why it is so.
> >
> > I'm attaching the v2 patch for further review.
> >
> > [1]
> > SELECT * FROM page_header(:'page_from_table');
> > lsn | checksum | flags | lower | upper | special | pagesize |
> > version | prune_xid
> > -----------+----------+-------+-------+-------+---------+----------+---------+-----------
> > 0/1891D78 | 0 | 0 | 40 | 8064 | 8192 | 8192 |
> > 4 | 0
> > (1 row)
> >
> > SELECT * FROM page_header(:'page_from_wal');
> > lsn | checksum | flags | lower | upper | special | pagesize |
> > version | prune_xid
> > -----------+----------+-------+-------+-------+---------+----------+---------+-----------
> > 0/1891D78 | 0 | 0 | 44 | 8032 | 8192 | 8192 |
> > 4 | 735
> > (1 row)
>
> Ugh, v2 patch missed the new file added, I'm attaching v3 patch for
> further review. Sorry for the noise.
I took a stab at how and what gets logged as FPI in WAL records:
Option 1:
WAL record with FPI contains both the unmodified table page from the
disk after checkpoint and new tuple (not applied to the unmodified
page) and the recovery (redo) applies the new tuple to the unmodified
page as part of recovery. A bit more WAL is needed to store both
unmodified page and new tuple data in the WAL record and recovery can
get slower a bit too as it needs to stitch the modified page.
Option 2:
WAL record with FPI contains only the modified page (new tuple applied
to the unmodified page from the disk after checkpoint) and the
recovery (redo) just returns the applied block as BLK_RESTORED.
Recovery can get faster with this approach and less WAL is needed to
store just the modified page.
My earlier understanding was that postgres does option (1), however, I
was wrong, option (2) is what actually postgres has implemented for
the obvious advantages specified.
I now made the tests a bit stricter in checking the FPI contents
(tuple values) pulled from the WAL record with raw page contents
pulled from the table using the pageinspect extension. Please see the
attached v4 patch.
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
From 6b577f15aede723f5cb8ea675d41d0efe9b96727 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 6 Jan 2023 17:07:49 +0000
Subject: [PATCH v4] Add FPI extract function to pg_walinspect
---
contrib/pg_walinspect/Makefile | 3 +-
.../pg_walinspect/expected/pg_walinspect.out | 84 ++++++++++++-
contrib/pg_walinspect/meson.build | 1 +
.../pg_walinspect/pg_walinspect--1.0--1.1.sql | 24 ++++
contrib/pg_walinspect/pg_walinspect.c | 110 ++++++++++++++++++
contrib/pg_walinspect/pg_walinspect.control | 2 +-
contrib/pg_walinspect/sql/pg_walinspect.sql | 58 ++++++++-
doc/src/sgml/pgwalinspect.sgml | 49 ++++++++
8 files changed, 327 insertions(+), 4 deletions(-)
create mode 100644 contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
diff --git a/contrib/pg_walinspect/Makefile b/contrib/pg_walinspect/Makefile
index 960530eb6c..7412307ede 100644
--- a/contrib/pg_walinspect/Makefile
+++ b/contrib/pg_walinspect/Makefile
@@ -7,11 +7,12 @@ OBJS = \
PGFILEDESC = "pg_walinspect - functions to inspect contents of PostgreSQL Write-Ahead Log"
EXTENSION = pg_walinspect
-DATA = pg_walinspect--1.0.sql
+DATA = pg_walinspect--1.0.sql pg_walinspect--1.0--1.1.sql
REGRESS = pg_walinspect
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_walinspect/walinspect.conf
+EXTRA_INSTALL = contrib/pageinspect
# Disabled because these tests require "wal_level=replica", which
# some installcheck users do not have (e.g. buildfarm clients).
diff --git a/contrib/pg_walinspect/expected/pg_walinspect.out b/contrib/pg_walinspect/expected/pg_walinspect.out
index a1ee743457..b4ae466edb 100644
--- a/contrib/pg_walinspect/expected/pg_walinspect.out
+++ b/contrib/pg_walinspect/expected/pg_walinspect.out
@@ -1,4 +1,5 @@
CREATE EXTENSION pg_walinspect;
+CREATE EXTENSION pageinspect;
-- Make sure checkpoints don't interfere with the test.
SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false);
?column?
@@ -10,7 +11,7 @@ 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);
+INSERT INTO sample_tbl SELECT * FROM generate_series(3, 4);
-- ===================================================================
-- Tests for input validation
-- ===================================================================
@@ -73,6 +74,62 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'
t
(1 row)
+-- ===================================================================
+-- Tests to get full page image (FPI) from WAL record and compare it
+-- with raw page (post-update) from table
+-- ===================================================================
+SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
+-- Force FPI on the next update
+CHECKPOINT;
+-- Update table to generate an FPI. We intentionally update a single row
+-- here to ensure the predictability in verifying the FPI with raw page
+-- content from the table.
+UPDATE sample_tbl SET col1 = col1 * 100 WHERE col1 = 1;
+SELECT pg_current_wal_lsn() AS wal_lsn4 \gset
+-- Get FPI from WAL record
+SELECT fpi AS page_from_wal FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
+ WHERE relfile_number = :'sample_tbl_oid' \gset
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
+ WHERE relfile_number = :'sample_tbl_oid';
+ ok
+----
+ t
+(1 row)
+
+-- Get raw page from table
+SELECT get_raw_page('sample_tbl', 0) AS page_from_table \gset
+-- Compare FPI from WAL record and page from table, they must be same
+SELECT tuple_data_split('sample_tbl'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(:'page_from_table');
+ tuple_data_split
+----------------------
+ {"\\x01000000",NULL}
+ {"\\x02000000",NULL}
+ {"\\x03000000",NULL}
+ {"\\x04000000",NULL}
+ {"\\x64000000",NULL}
+(5 rows)
+
+SELECT tuple_data_split('sample_tbl'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(:'page_from_wal');
+ tuple_data_split
+----------------------
+ {"\\x01000000",NULL}
+ {"\\x02000000",NULL}
+ {"\\x03000000",NULL}
+ {"\\x04000000",NULL}
+ {"\\x64000000",NULL}
+(5 rows)
+
+SELECT tuple_data_split('sample_tbl'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(:'page_from_table')
+EXCEPT
+SELECT tuple_data_split('sample_tbl'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(:'page_from_wal');
+ tuple_data_split
+------------------
+(0 rows)
+
-- ===================================================================
-- Tests for permissions
-- ===================================================================
@@ -98,6 +155,13 @@ SELECT has_function_privilege('regress_pg_walinspect',
f
(1 row)
+SELECT has_function_privilege('regress_pg_walinspect',
+ 'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', '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',
@@ -121,6 +185,13 @@ SELECT has_function_privilege('regress_pg_walinspect',
t
(1 row)
+SELECT has_function_privilege('regress_pg_walinspect',
+ 'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', '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)
@@ -129,6 +200,8 @@ 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_fpi_info(pg_lsn, pg_lsn)
+ TO regress_pg_walinspect;
SELECT has_function_privilege('regress_pg_walinspect',
'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
has_function_privilege
@@ -150,12 +223,21 @@ SELECT has_function_privilege('regress_pg_walinspect',
t
(1 row)
+SELECT has_function_privilege('regress_pg_walinspect',
+ 'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', '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_fpi_info(pg_lsn, pg_lsn)
+ FROM regress_pg_walinspect;
-- ===================================================================
-- Clean up
-- ===================================================================
diff --git a/contrib/pg_walinspect/meson.build b/contrib/pg_walinspect/meson.build
index f6e912a7d8..bf7b79b1b7 100644
--- a/contrib/pg_walinspect/meson.build
+++ b/contrib/pg_walinspect/meson.build
@@ -19,6 +19,7 @@ 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,
)
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 0000000000..4ffbd91728
--- /dev/null
+++ b/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
@@ -0,0 +1,24 @@
+/* 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
+
+--
+-- pg_get_wal_fpi_info()
+--
+CREATE FUNCTION pg_get_wal_fpi_info(IN start_lsn pg_lsn,
+ IN end_lsn pg_lsn,
+ OUT lsn pg_lsn,
+ OUT tablespace_oid oid,
+ OUT database_oid oid,
+ OUT relfile_number oid,
+ OUT block_number int8,
+ OUT fork_name text,
+ OUT fpi bytea
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_wal_fpi_info'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+REVOKE EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn) TO pg_read_server_files;
diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c
index 08a4c2fb52..8f992960e8 100644
--- a/contrib/pg_walinspect/pg_walinspect.c
+++ b/contrib/pg_walinspect/pg_walinspect.c
@@ -35,6 +35,7 @@ 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);
+PG_FUNCTION_INFO_V1(pg_get_wal_fpi_info);
static bool IsFutureLSN(XLogRecPtr lsn, XLogRecPtr *curr_lsn);
static XLogReaderState *InitXLogReaderState(XLogRecPtr lsn);
@@ -55,6 +56,7 @@ static void FillXLogStatsRow(const char *name, uint64 n, uint64 total_count,
Datum *values, bool *nulls, uint32 ncols);
static void GetWalStats(FunctionCallInfo fcinfo, XLogRecPtr start_lsn,
XLogRecPtr end_lsn, bool stats_per_record);
+static void GetWALFPIInfo(FunctionCallInfo fcinfo, XLogReaderState *record);
/*
* Check if the given LSN is in future. Also, return the LSN up to which the
@@ -616,3 +618,111 @@ pg_get_wal_stats_till_end_of_wal(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+
+/*
+ * Get full page images and their info associated with a given WAL record.
+ * Decompression is applied to the blocks, if necessary.
+ */
+static void
+GetWALFPIInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
+{
+#define PG_GET_WAL_FPI_INFO_COLS 7
+ int block_id;
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ for (block_id = 0; block_id <= XLogRecMaxBlockId(record); block_id++)
+ {
+ PGAlignedBlock buf;
+ Page page;
+ bytea *raw_page;
+ BlockNumber blk;
+ RelFileLocator rnode;
+ ForkNumber fork;
+ Datum values[PG_GET_WAL_FPI_INFO_COLS] = {0};
+ bool nulls[PG_GET_WAL_FPI_INFO_COLS] = {0};
+ int i = 0;
+
+ if (!XLogRecHasBlockRef(record, block_id))
+ continue;
+
+ if (!XLogRecHasBlockImage(record, block_id))
+ continue;
+
+ page = (Page) buf.data;
+
+ if (!RestoreBlockImage(record, block_id, page))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg_internal("%s", record->errormsg_buf)));
+
+ /* Full page exists, so let's output its info and content. */
+ (void) XLogRecGetBlockTagExtended(record, block_id,
+ &rnode, &fork, &blk, NULL);
+
+ values[i++] = LSNGetDatum(record->ReadRecPtr);
+ values[i++] = ObjectIdGetDatum(rnode.spcOid);
+ values[i++] = ObjectIdGetDatum(rnode.dbOid);
+ values[i++] = ObjectIdGetDatum(rnode.relNumber);
+ values[i++] = Int64GetDatum((int64) blk);
+
+ if (fork >= 0 && fork <= MAX_FORKNUM)
+ values[i++] = CStringGetTextDatum(forkNames[fork]);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg_internal("invalid fork number: %u", fork)));
+
+ /* Initialize bytea buffer to copy the FPI to. */
+ raw_page = (bytea *) palloc(BLCKSZ + VARHDRSZ);
+ SET_VARSIZE(raw_page, BLCKSZ + VARHDRSZ);
+
+ /* Take a verbatim copy of the FPI. */
+ memcpy(VARDATA(raw_page), page, BLCKSZ);
+
+ values[i++] = PointerGetDatum(raw_page);
+
+ Assert(i == PG_GET_WAL_FPI_INFO_COLS);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+
+#undef PG_GET_WAL_FPI_INFO_COLS
+}
+
+/*
+ * Get full page images and their info of all WAL records between start LSN and
+ * end LSN. Decompression is applied to the blocks, if necessary.
+ *
+ * This function emits an error if a future start or end WAL LSN i.e. WAL LSN
+ * the database system doesn't know about is specified.
+ */
+Datum
+pg_get_wal_fpi_info(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr start_lsn;
+ XLogRecPtr end_lsn;
+ XLogReaderState *xlogreader;
+
+ start_lsn = PG_GETARG_LSN(0);
+ end_lsn = PG_GETARG_LSN(1);
+
+ end_lsn = ValidateInputLSNs(false, start_lsn, end_lsn);
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ xlogreader = InitXLogReaderState(start_lsn);
+
+ while (ReadNextXLogRecord(xlogreader) &&
+ xlogreader->EndRecPtr <= end_lsn)
+ {
+ GetWALFPIInfo(fcinfo, xlogreader);
+
+ CHECK_FOR_INTERRUPTS();
+ }
+
+ pfree(xlogreader->private_data);
+ XLogReaderFree(xlogreader);
+
+ PG_RETURN_VOID();
+}
diff --git a/contrib/pg_walinspect/pg_walinspect.control b/contrib/pg_walinspect/pg_walinspect.control
index 017e56a2bb..efa3cb2cfe 100644
--- a/contrib/pg_walinspect/pg_walinspect.control
+++ b/contrib/pg_walinspect/pg_walinspect.control
@@ -1,5 +1,5 @@
# pg_walinspect extension
comment = 'functions to inspect contents of PostgreSQL Write-Ahead Log'
-default_version = '1.0'
+default_version = '1.1'
module_pathname = '$libdir/pg_walinspect'
relocatable = true
diff --git a/contrib/pg_walinspect/sql/pg_walinspect.sql b/contrib/pg_walinspect/sql/pg_walinspect.sql
index 1b265ea7bc..6b4c3dd90a 100644
--- a/contrib/pg_walinspect/sql/pg_walinspect.sql
+++ b/contrib/pg_walinspect/sql/pg_walinspect.sql
@@ -1,5 +1,7 @@
CREATE EXTENSION pg_walinspect;
+CREATE EXTENSION pageinspect;
+
-- Make sure checkpoints don't interfere with the test.
SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false);
@@ -11,7 +13,7 @@ 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);
+INSERT INTO sample_tbl SELECT * FROM generate_series(3, 4);
-- ===================================================================
-- Tests for input validation
@@ -52,6 +54,45 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'
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 full page image (FPI) from WAL record and compare it
+-- with raw page (post-update) from table
+-- ===================================================================
+SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
+
+-- Force FPI on the next update
+CHECKPOINT;
+
+-- Update table to generate an FPI. We intentionally update a single row
+-- here to ensure the predictability in verifying the FPI with raw page
+-- content from the table.
+UPDATE sample_tbl SET col1 = col1 * 100 WHERE col1 = 1;
+
+SELECT pg_current_wal_lsn() AS wal_lsn4 \gset
+
+-- Get FPI from WAL record
+SELECT fpi AS page_from_wal FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
+ WHERE relfile_number = :'sample_tbl_oid' \gset
+
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
+ WHERE relfile_number = :'sample_tbl_oid';
+
+-- Get raw page from table
+SELECT get_raw_page('sample_tbl', 0) AS page_from_table \gset
+
+-- Compare FPI from WAL record and page from table, they must be same
+SELECT tuple_data_split('sample_tbl'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(:'page_from_table');
+
+SELECT tuple_data_split('sample_tbl'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(:'page_from_wal');
+
+SELECT tuple_data_split('sample_tbl'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(:'page_from_table')
+EXCEPT
+SELECT tuple_data_split('sample_tbl'::regclass, t_data, t_infomask, t_infomask2, t_bits)
+ FROM heap_page_items(:'page_from_wal');
+
-- ===================================================================
-- Tests for permissions
-- ===================================================================
@@ -66,6 +107,9 @@ SELECT has_function_privilege('regress_pg_walinspect',
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_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no
+
-- Functions accessible by users with role pg_read_server_files
GRANT pg_read_server_files TO regress_pg_walinspect;
@@ -79,6 +123,9 @@ SELECT has_function_privilege('regress_pg_walinspect',
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_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
+
REVOKE pg_read_server_files FROM regress_pg_walinspect;
-- Superuser can grant execute to other users
@@ -91,6 +138,9 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
TO regress_pg_walinspect;
+GRANT EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn)
+ TO regress_pg_walinspect;
+
SELECT has_function_privilege('regress_pg_walinspect',
'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
@@ -100,6 +150,9 @@ SELECT has_function_privilege('regress_pg_walinspect',
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_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
+
REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
FROM regress_pg_walinspect;
@@ -109,6 +162,9 @@ REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
FROM regress_pg_walinspect;
+REVOKE EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn)
+ FROM regress_pg_walinspect;
+
-- ===================================================================
-- Clean up
-- ===================================================================
diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml
index 1a1bee7d6a..ef5e5852a9 100644
--- a/doc/src/sgml/pgwalinspect.sgml
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -261,6 +261,55 @@ postgres=# select * from pg_get_wal_stats('0/14AFC30', '0/15011D7', true) where
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <function>
+ pg_get_wal_fpi_info(start_lsn pg_lsn,
+ end_lsn pg_lsn,
+ lsn OUT pg_lsn,
+ tablespace_oid OUT oid,
+ database_oid OUT oid,
+ relfile_number OUT oid,
+ block_number OUT int8,
+ fork_name OUT text,
+ fpi OUT bytea)
+ returns setof record
+ </function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets raw full page images and their information associated with all the
+ valid WAL records between <replaceable>start_lsn</replaceable> and
+ <replaceable>end_lsn</replaceable>. Returns one row per full page image.
+ Decompression is applied to the blocks, if necessary.
+ If <replaceable>start_lsn</replaceable> or
+ <replaceable>end_lsn</replaceable> are not yet available, the function
+ will raise an error. For example, usage of the function is as follows:
+<screen>
+postgres=# SELECT lsn, tablespace_oid, database_oid, relfile_number, block_number, fork_name, length(fpi) > 0 as fpi_ok FROM pg_get_wal_fpi_info('0/7418E60', '0/7518218');
+ lsn | tablespace_oid | database_oid | relfile_number | block_number | fork_name | fpi_ok
+-----------+----------------+--------------+----------------+--------------+-----------+--------
+ 0/7418F48 | 1663 | 5 | 16419 | 0 | main | t
+ 0/7419080 | 1663 | 5 | 16422 | 19 | main | t
+ 0/741B0B8 | 1663 | 5 | 16422 | 22 | main | t
+ 0/741BF48 | 1663 | 5 | 16422 | 20 | main | t
+ 0/74257B8 | 1663 | 5 | 16422 | 21 | main | t
+ 0/742FF30 | 1663 | 5 | 16425 | 42 | main | t
+ 0/7431F68 | 1663 | 5 | 16425 | 44 | main | t
+ 0/7439B78 | 1663 | 5 | 16425 | 43 | main | t
+ 0/74451E8 | 1663 | 5 | 16428 | 199 | main | t
+ 0/7447220 | 1663 | 5 | 16428 | 221 | main | t
+ 0/744E458 | 1663 | 5 | 16428 | 200 | main | t
+ 0/7457CB0 | 1663 | 5 | 16428 | 201 | main | t
+ 0/7461520 | 1663 | 5 | 16428 | 202 | main | t
+ 0/746AD90 | 1663 | 5 | 16428 | 203 | main | t
+(14 rows)
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
--
2.34.1
