On Wed, Jan 11, 2023 at 10:07 AM Michael Paquier <mich...@paquier.xyz> wrote:
>
> +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');
>
> This query in the docs is too long IMO. Could you split that across
> multiple lines for readability?
Done.
> + 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)
> I am a bit surprised by this format, used to define the functions part
> of the module in the docs, while we have examples that actually show
> what's printed out. I understand that this comes from the original
> commit of the module, but the rendered docs are really hard to parse
> as well, no? FWIW, I think that this had better be fixed as well in
> the docs of v15.. Showing a full set of attributes for the returned
> record is fine by me, still if these are too long we could just use
> \x.
Thanks. I'll work on that separately.
> For this one, I think that there is little point in showing 14
> records, so I would stick with a style similar to pageinspect.
I've done it that way for pg_get_wal_fpi_info. If this format looks
okay, I can propose to do the same for other functions (for
backpatching too) in a separate thread though.
> +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,
> Slight indentation issue here.
Done.
> Using "relfile_number" would be a first, for what is defined in the
> code and the docs as a filenode.
Yes, I've changed the column names to be consistent (like in pg_buffercache).
> +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
> I would be tempted to keep the checks run here minimal with only a
> basic set of checks on the LSN, without the dependencies to
> pageinspect (tuple_data_split and get_raw_page), which would be fine
> enough to check the execution of the function.
I understand the concern here that creating dependency between
extensions just for testing isn't good.
I'm okay to just read the LSN (lsn1) from raw FPI (bytea stream) and
the WAL record's LSN (lsn2) and compare them to be lsn2 > lsn1. I'm
looking for a way to convert the first 8 bytes from bytea stream to
pg_lsn type, on a quick look I couldn't find direct conversion
functions, however, I'll try to figure out a way.
> FWIW, I am surprised by the design choice behind ValidateInputLSNs()
> to allow data to be gathered until the end of WAL in some cases, but
> to not allow it in others. It is likely too late to come back to this
> choice for the existing functions in v15 (quoique?), but couldn't it
Separate functions for users passing end_lsn by themselves and users
letting functions decide the end_lsn (current flush LSN or replay LSN)
were chosen for better and easier usability and easier validation of
user-entered input lsns.
We deliberated to have something like below:
pg_get_wal_stats(start_lsn, end_lsn, till_end_of_wal default false);
pg_get_wal_records_info(start_lsn, end_lsn, till_end_of_wal default false);
We wanted to have better validation of the start_lsn and end_lsn, that
is, start_lsn < end_lsn and end_lsn mustn't fall into the future when
users specify it by themselves (otherwise, one can easily trick the
server by passing in the extreme end of the LSN - 0xFFFFFFFFFFFFFFFF).
And, we couldn't find a better way to deal with when till_end_of_wal
is passed as true (in the above version of the functions).
Another idea was to have something like below:
pg_get_wal_stats(start_lsn, end_lsn default '0/0');
pg_get_wal_records_info(start_lsn, end_lsn default '0/0');
When end_lsn is not entered or entered as invalid lsn, then return the
stats/info till end of the WAL. Again, we wanted to have some
validation of the user-entered end_lsn.
Instead of cooking multiple behaviours into a single function we opted
for till_end_of_wal versions. I still feel this is better unless
there's a strong reason against till_end_of_wal versions.
> be useful to make this new FPI function work at least with an insanely
> high LSN value to make sure that we fetch all the FPIs from a given
> start position, up to the end of WAL? That looks like a pretty good
> default behavior to me, rather than issuing an error when a LSN is
> defined as in the future.. I am really wondering why we have
> ValidateInputLSNs(till_end_of_wal=false) to begin with, while we could
> just allow any LSN value in the future automatically, as we can know
> the current insert or replay LSNs (depending on the recovery state).
Hm. How about having pg_get_wal_fpi_info_till_end_of_wal() then?
With some of the review comments addressed, I'm attaching v5 patch
herewith. I would like to hear thoughts on the above open points
before writing the v6 patch.
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
From a611efa28d2caf86c3b228ec538d5a9c22931bb1 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Wed, 11 Jan 2023 12:09:46 +0000
Subject: [PATCH v5] 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 | 56 +++++++++
8 files changed, 334 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..b422e450e2 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 relfilenode = :'sample_tbl_oid' \gset
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
+ WHERE relfilenode = :'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..1e9e1e6115
--- /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 reltablespace oid,
+ OUT reldatabase oid,
+ OUT relfilenode oid,
+ OUT relblocknumber int8,
+ OUT forkname 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..3cd1beea35 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 relfilenode = :'sample_tbl_oid' \gset
+
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
+ WHERE relfilenode = :'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 c4c3efe932..d74432a39f 100644
--- a/doc/src/sgml/pgwalinspect.sgml
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -261,6 +261,62 @@ 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) 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=# \x
+postgres=# SELECT lsn, reltablespace, reldatabase, relfilenode, relblocknumber,
+ forkname, length(fpi) > 0 as fpi_ok
+ FROM pg_get_wal_fpi_info('0/1590ED0', '0/15BCC58');
+-[ RECORD 1 ]--+----------
+lsn | 0/1590F08
+reltablespace | 1663
+reldatabase | 5
+relfilenode | 16403
+relblocknumber | 4
+forkname | main
+fpi_ok | t
+-[ RECORD 2 ]--+----------
+lsn | 0/159DF90
+reltablespace | 1663
+reldatabase | 5
+relfilenode | 16403
+relblocknumber | 6
+forkname | main
+fpi_ok | t
+-[ RECORD 3 ]--+----------
+lsn | 0/15B9C20
+reltablespace | 1663
+reldatabase | 5
+relfilenode | 16406
+relblocknumber | 0
+forkname | main
+fpi_ok | t
+-[ RECORD 4 ]--+----------
+lsn | 0/15BBC58
+reltablespace | 1663
+reldatabase | 5
+relfilenode | 16406
+relblocknumber | 4
+forkname | main
+fpi_ok | t
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
--
2.34.1
