public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bharath Rupireddy <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Add a new pg_walinspect function to extract FPIs from WAL records
Date: Tue, 27 Dec 2022 17:18:53 +0530
Message-ID: <CALj2ACVCcvzd7WiWvD=6_7NBvVB_r6G0EGSxL4F8vosAi6Se4g@mail.gmail.com> (raw)
Hi,
Here's a patch that implements the idea of extracting full page images
from WAL records [1] [2] with a function in pg_walinspect. This new
function accepts start and end lsn and returns full page image info
such as WAL record lsn, tablespace oid, database oid, relfile number,
block number, fork name and the raw full page (as bytea). I'll
register this in the next commitfest.
Thoughts?
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d497093cbecccf6df26365e06a5f8f861...
[2] https://postgr.es/m/CAOxo6XKjQb2bMSBRpePf3ZpzfNTwjQUc4Tafh21=jzjX6bX8CA@mail.gmail.com
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
[application/octet-stream] v1-0001-Add-FPI-extract-function-to-pg_walinspect.patch (16.5K, ../CALj2ACVCcvzd7WiWvD=6_7NBvVB_r6G0EGSxL4F8vosAi6Se4g@mail.gmail.com/2-v1-0001-Add-FPI-extract-function-to-pg_walinspect.patch)
download | inline diff:
From f80d957e5f8b95c434cb18f4ab9c0b7d31ac606a Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <[email protected]>
Date: Tue, 27 Dec 2022 11:16:49 +0000
Subject: [PATCH v1] Add FPI extract function to pg_walinspect
---
contrib/pg_walinspect/Makefile | 2 +-
.../pg_walinspect/expected/pg_walinspect.out | 46 ++++++++
contrib/pg_walinspect/meson.build | 1 +
.../pg_walinspect/pg_walinspect--1.0--1.1.sql | 24 ++++
contrib/pg_walinspect/pg_walinspect.c | 109 ++++++++++++++++++
contrib/pg_walinspect/pg_walinspect.control | 2 +-
contrib/pg_walinspect/sql/pg_walinspect.sql | 32 +++++
doc/src/sgml/pgwalinspect.sgml | 48 ++++++++
8 files changed, 262 insertions(+), 2 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..7033878a79 100644
--- a/contrib/pg_walinspect/Makefile
+++ b/contrib/pg_walinspect/Makefile
@@ -7,7 +7,7 @@ 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
diff --git a/contrib/pg_walinspect/expected/pg_walinspect.out b/contrib/pg_walinspect/expected/pg_walinspect.out
index a1ee743457..5cd28402ad 100644
--- a/contrib/pg_walinspect/expected/pg_walinspect.out
+++ b/contrib/pg_walinspect/expected/pg_walinspect.out
@@ -73,6 +73,27 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'
t
(1 row)
+-- ===================================================================
+-- Tests for getting full page images
+-- ===================================================================
+SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
+-- Force FPWs on the next writes.
+CHECKPOINT;
+UPDATE sample_tbl SET col1 = col1 + 1;
+SELECT 1 FROM pg_switch_wal();
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT pg_current_wal_lsn() AS wal_lsn4 \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)
+
-- ===================================================================
-- Tests for permissions
-- ===================================================================
@@ -98,6 +119,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 +149,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 +164,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 +187,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 7917e03557..6ab97d3672 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 beba4788c7..02fee9e2ab 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,110 @@ 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.
+ */
+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;
+
+ /* Full page exists, so let's output it. */
+ if (!RestoreBlockImage(record, block_id, page))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg_internal("%s", record->errormsg_buf)));
+
+ (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.
+ *
+ * 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..25f707de44 100644
--- a/contrib/pg_walinspect/sql/pg_walinspect.sql
+++ b/contrib/pg_walinspect/sql/pg_walinspect.sql
@@ -52,6 +52,23 @@ 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 for getting full page images
+-- ===================================================================
+SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
+
+-- Force FPWs on the next writes.
+CHECKPOINT;
+
+UPDATE sample_tbl SET col1 = col1 + 1;
+
+SELECT 1 FROM pg_switch_wal();
+
+SELECT pg_current_wal_lsn() AS wal_lsn4 \gset
+
+SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
+ WHERE relfile_number = :'sample_tbl_oid';
+
-- ===================================================================
-- Tests for permissions
-- ===================================================================
@@ -66,6 +83,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 +99,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 +114,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 +126,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 +138,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..98208c2deb 100644
--- a/doc/src/sgml/pgwalinspect.sgml
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -261,6 +261,54 @@ 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.
+ 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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Add a new pg_walinspect function to extract FPIs from WAL records
In-Reply-To: <CALj2ACVCcvzd7WiWvD=6_7NBvVB_r6G0EGSxL4F8vosAi6Se4g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox