Skip to content

Commit c31cf1c

Browse files
michaelpqmichaelpq
committed
pg_walinspect: Add pg_get_wal_fpi_info()
This function is able to extract the full page images from a range of records, specified as of input arguments start_lsn and end_lsn. Like the other functions of this module, an error is returned if using LSNs that do not reflect real system values. All the FPIs stored in a single record are extracted. The module's version is bumped to 1.1. Author: Bharath Rupireddy Reviewed-by: Bertrand Drouvot Discussion: https://postgr.es/m/CALj2ACVCcvzd7WiWvD=6_7NBvVB_r6G0EGSxL4F8vosAi6Se4g@mail.gmail.com
1 parent 16fd03e commit c31cf1c

File tree

8 files changed

+245
-4
lines changed

8 files changed

+245
-4
lines changed

‎contrib/pg_walinspect/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@ OBJS = \
77
PGFILEDESC = "pg_walinspect - functions to inspect contents of PostgreSQL Write-Ahead Log"
88

99
EXTENSION = pg_walinspect
10-
DATA = pg_walinspect--1.0.sql
10+
DATA = pg_walinspect--1.0.sql pg_walinspect--1.0--1.1.sql
1111

1212
REGRESS = pg_walinspect
1313

‎contrib/pg_walinspect/expected/pg_walinspect.out

Lines changed: 43 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@ CREATE TABLE sample_tbl(col1 int, col2 int);
1010
SELECT pg_current_wal_lsn() AS wal_lsn1 \gset
1111
INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
1212
SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
13-
INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
13+
INSERT INTO sample_tbl SELECT * FROM generate_series(3, 4);
1414
-- ===================================================================
1515
-- Tests for input validation
1616
-- ===================================================================
@@ -73,6 +73,23 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'
7373
t
7474
(1 row)
7575

76+
-- ===================================================================
77+
-- Tests to get full page image (FPI) from WAL record
78+
-- ===================================================================
79+
SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
80+
-- Force FPI on the next update.
81+
CHECKPOINT;
82+
-- Update table to generate an FPI.
83+
UPDATE sample_tbl SET col1 = col1 * 100 WHERE col1 = 1;
84+
SELECT pg_current_wal_lsn() AS wal_lsn4 \gset
85+
-- Check if we get FPI from WAL record.
86+
SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
87+
WHERE relfilenode = :'sample_tbl_oid';
88+
ok
89+
----
90+
t
91+
(1 row)
92+
7693
-- ===================================================================
7794
-- Tests for permissions
7895
-- ===================================================================
@@ -98,6 +115,13 @@ SELECT has_function_privilege('regress_pg_walinspect',
98115
f
99116
(1 row)
100117

118+
SELECT has_function_privilege('regress_pg_walinspect',
119+
'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no
120+
has_function_privilege
121+
------------------------
122+
f
123+
(1 row)
124+
101125
-- Functions accessible by users with role pg_read_server_files
102126
GRANT pg_read_server_files TO regress_pg_walinspect;
103127
SELECT has_function_privilege('regress_pg_walinspect',
@@ -121,6 +145,13 @@ SELECT has_function_privilege('regress_pg_walinspect',
121145
t
122146
(1 row)
123147

148+
SELECT has_function_privilege('regress_pg_walinspect',
149+
'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
150+
has_function_privilege
151+
------------------------
152+
t
153+
(1 row)
154+
124155
REVOKE pg_read_server_files FROM regress_pg_walinspect;
125156
-- Superuser can grant execute to other users
126157
GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
@@ -129,6 +160,8 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
129160
TO regress_pg_walinspect;
130161
GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
131162
TO regress_pg_walinspect;
163+
GRANT EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn)
164+
TO regress_pg_walinspect;
132165
SELECT has_function_privilege('regress_pg_walinspect',
133166
'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
134167
has_function_privilege
@@ -150,12 +183,21 @@ SELECT has_function_privilege('regress_pg_walinspect',
150183
t
151184
(1 row)
152185

186+
SELECT has_function_privilege('regress_pg_walinspect',
187+
'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
188+
has_function_privilege
189+
------------------------
190+
t
191+
(1 row)
192+
153193
REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
154194
FROM regress_pg_walinspect;
155195
REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
156196
FROM regress_pg_walinspect;
157197
REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
158198
FROM regress_pg_walinspect;
199+
REVOKE EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn)
200+
FROM regress_pg_walinspect;
159201
-- ===================================================================
160202
-- Clean up
161203
-- ===================================================================

‎contrib/pg_walinspect/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@ contrib_targets += pg_walinspect
1919
install_data(
2020
'pg_walinspect.control',
2121
'pg_walinspect--1.0.sql',
22+
'pg_walinspect--1.0--1.1.sql',
2223
kwargs: contrib_data_args,
2324
)
2425

‎contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
/* contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql */
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use "ALTER EXTENSION pg_walinspect UPDATE TO '1.1'" to load this file. \quit
5+
6+
--
7+
-- pg_get_wal_fpi_info()
8+
--
9+
CREATE FUNCTION pg_get_wal_fpi_info(IN start_lsn pg_lsn,
10+
IN end_lsn pg_lsn,
11+
OUT lsn pg_lsn,
12+
OUT reltablespace oid,
13+
OUT reldatabase oid,
14+
OUT relfilenode oid,
15+
OUT relblocknumber int8,
16+
OUT forkname text,
17+
OUT fpi bytea
18+
)
19+
RETURNS SETOF record
20+
AS 'MODULE_PATHNAME', 'pg_get_wal_fpi_info'
21+
LANGUAGE C STRICT PARALLEL SAFE;
22+
23+
REVOKE EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn) FROM PUBLIC;
24+
GRANT EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn) TO pg_read_server_files;

‎contrib/pg_walinspect/pg_walinspect.c

Lines changed: 111 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030

3131
PG_MODULE_MAGIC;
3232

33+
PG_FUNCTION_INFO_V1(pg_get_wal_fpi_info);
3334
PG_FUNCTION_INFO_V1(pg_get_wal_record_info);
3435
PG_FUNCTION_INFO_V1(pg_get_wal_records_info);
3536
PG_FUNCTION_INFO_V1(pg_get_wal_records_info_till_end_of_wal);
@@ -55,6 +56,7 @@ static void FillXLogStatsRow(const char *name, uint64 n, uint64 total_count,
5556
Datum *values, bool *nulls, uint32 ncols);
5657
static void GetWalStats(FunctionCallInfo fcinfo, XLogRecPtr start_lsn,
5758
XLogRecPtr end_lsn, bool stats_per_record);
59+
static void GetWALFPIInfo(FunctionCallInfo fcinfo, XLogReaderState *record);
5860

5961
/*
6062
* Check if the given LSN is in future. Also, return the LSN up to which the
@@ -217,6 +219,115 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
217219
Assert(i == ncols);
218220
}
219221

222+
223+
/*
224+
* Store a set of full page images from a single record.
225+
*/
226+
static void
227+
GetWALFPIInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
228+
{
229+
#define PG_GET_WAL_FPI_INFO_COLS 7
230+
int block_id;
231+
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
232+
233+
for (block_id = 0; block_id <= XLogRecMaxBlockId(record); block_id++)
234+
{
235+
PGAlignedBlock buf;
236+
Page page;
237+
bytea *raw_page;
238+
BlockNumber blk;
239+
RelFileLocator rnode;
240+
ForkNumber fork;
241+
Datum values[PG_GET_WAL_FPI_INFO_COLS] = {0};
242+
bool nulls[PG_GET_WAL_FPI_INFO_COLS] = {0};
243+
int i = 0;
244+
245+
if (!XLogRecHasBlockRef(record, block_id))
246+
continue;
247+
248+
if (!XLogRecHasBlockImage(record, block_id))
249+
continue;
250+
251+
page = (Page) buf.data;
252+
253+
if (!RestoreBlockImage(record, block_id, page))
254+
ereport(ERROR,
255+
(errcode(ERRCODE_INTERNAL_ERROR),
256+
errmsg_internal("%s", record->errormsg_buf)));
257+
258+
/* Full page exists, so let's save it. */
259+
(void) XLogRecGetBlockTagExtended(record, block_id,
260+
&rnode, &fork, &blk, NULL);
261+
262+
values[i++] = LSNGetDatum(record->ReadRecPtr);
263+
values[i++] = ObjectIdGetDatum(rnode.spcOid);
264+
values[i++] = ObjectIdGetDatum(rnode.dbOid);
265+
values[i++] = ObjectIdGetDatum(rnode.relNumber);
266+
values[i++] = Int64GetDatum((int64) blk);
267+
268+
if (fork >= 0 && fork <= MAX_FORKNUM)
269+
values[i++] = CStringGetTextDatum(forkNames[fork]);
270+
else
271+
ereport(ERROR,
272+
(errcode(ERRCODE_INTERNAL_ERROR),
273+
errmsg_internal("invalid fork number: %u", fork)));
274+
275+
/* Initialize bytea buffer to copy the FPI to. */
276+
raw_page = (bytea *) palloc(BLCKSZ + VARHDRSZ);
277+
SET_VARSIZE(raw_page, BLCKSZ + VARHDRSZ);
278+
279+
/* Take a verbatim copy of the FPI. */
280+
memcpy(VARDATA(raw_page), page, BLCKSZ);
281+
282+
values[i++] = PointerGetDatum(raw_page);
283+
284+
Assert(i == PG_GET_WAL_FPI_INFO_COLS);
285+
286+
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
287+
values, nulls);
288+
}
289+
290+
#undef PG_GET_WAL_FPI_INFO_COLS
291+
}
292+
293+
/*
294+
* Get full page images with their relation information for all the WAL
295+
* records between start and end LSNs. Decompression is applied to the
296+
* blocks, if necessary.
297+
*
298+
* This function emits an error if a future start or end WAL LSN i.e. WAL LSN
299+
* the database system doesn't know about is specified.
300+
*/
301+
Datum
302+
pg_get_wal_fpi_info(PG_FUNCTION_ARGS)
303+
{
304+
XLogRecPtr start_lsn;
305+
XLogRecPtr end_lsn;
306+
XLogReaderState *xlogreader;
307+
308+
start_lsn = PG_GETARG_LSN(0);
309+
end_lsn = PG_GETARG_LSN(1);
310+
311+
end_lsn = ValidateInputLSNs(false, start_lsn, end_lsn);
312+
313+
InitMaterializedSRF(fcinfo, 0);
314+
315+
xlogreader = InitXLogReaderState(start_lsn);
316+
317+
while (ReadNextXLogRecord(xlogreader) &&
318+
xlogreader->EndRecPtr <= end_lsn)
319+
{
320+
GetWALFPIInfo(fcinfo, xlogreader);
321+
322+
CHECK_FOR_INTERRUPTS();
323+
}
324+
325+
pfree(xlogreader->private_data);
326+
XLogReaderFree(xlogreader);
327+
328+
PG_RETURN_VOID();
329+
}
330+
220331
/*
221332
* Get WAL record info.
222333
*

‎contrib/pg_walinspect/pg_walinspect.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_walinspect extension
22
comment = 'functions to inspect contents of PostgreSQL Write-Ahead Log'
3-
default_version = '1.0'
3+
default_version = '1.1'
44
module_pathname = '$libdir/pg_walinspect'
55
relocatable = true

‎contrib/pg_walinspect/sql/pg_walinspect.sql

Lines changed: 32 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
1111

1212
SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
1313

14-
INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
14+
INSERT INTO sample_tbl SELECT * FROM generate_series(3, 4);
1515

1616
-- ===================================================================
1717
-- Tests for input validation
@@ -52,6 +52,22 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2'
5252
SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2')
5353
WHERE resource_manager = 'Heap' AND record_type = 'INSERT';
5454

55+
-- ===================================================================
56+
-- Tests to get full page image (FPI) from WAL record
57+
-- ===================================================================
58+
SELECT pg_current_wal_lsn() AS wal_lsn3 \gset
59+
60+
-- Force FPI on the next update.
61+
CHECKPOINT;
62+
63+
-- Update table to generate an FPI.
64+
UPDATE sample_tbl SET col1 = col1 * 100 WHERE col1 = 1;
65+
SELECT pg_current_wal_lsn() AS wal_lsn4 \gset
66+
67+
-- Check if we get FPI from WAL record.
68+
SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_fpi_info(:'wal_lsn3', :'wal_lsn4')
69+
WHERE relfilenode = :'sample_tbl_oid';
70+
5571
-- ===================================================================
5672
-- Tests for permissions
5773
-- ===================================================================
@@ -66,6 +82,9 @@ SELECT has_function_privilege('regress_pg_walinspect',
6682
SELECT has_function_privilege('regress_pg_walinspect',
6783
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no
6884

85+
SELECT has_function_privilege('regress_pg_walinspect',
86+
'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no
87+
6988
-- Functions accessible by users with role pg_read_server_files
7089

7190
GRANT pg_read_server_files TO regress_pg_walinspect;
@@ -79,6 +98,9 @@ SELECT has_function_privilege('regress_pg_walinspect',
7998
SELECT has_function_privilege('regress_pg_walinspect',
8099
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
81100

101+
SELECT has_function_privilege('regress_pg_walinspect',
102+
'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
103+
82104
REVOKE pg_read_server_files FROM regress_pg_walinspect;
83105

84106
-- Superuser can grant execute to other users
@@ -91,6 +113,9 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
91113
GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
92114
TO regress_pg_walinspect;
93115

116+
GRANT EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn)
117+
TO regress_pg_walinspect;
118+
94119
SELECT has_function_privilege('regress_pg_walinspect',
95120
'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
96121

@@ -100,6 +125,9 @@ SELECT has_function_privilege('regress_pg_walinspect',
100125
SELECT has_function_privilege('regress_pg_walinspect',
101126
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
102127

128+
SELECT has_function_privilege('regress_pg_walinspect',
129+
'pg_get_wal_fpi_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
130+
103131
REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
104132
FROM regress_pg_walinspect;
105133

@@ -109,6 +137,9 @@ REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
109137
REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
110138
FROM regress_pg_walinspect;
111139

140+
REVOKE EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn)
141+
FROM regress_pg_walinspect;
142+
112143
-- ===================================================================
113144
-- Clean up
114145
-- ===================================================================

‎doc/src/sgml/pgwalinspect.sgml

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,38 @@ combined_size_percentage | 2.8634072910530795
188188
</listitem>
189189
</varlistentry>
190190

191+
<varlistentry>
192+
<term>
193+
<function>pg_get_wal_fpi_info(start_lsn pg_lsn, end_lsn pg_lsn) returns setof record</function>
194+
</term>
195+
196+
<listitem>
197+
<para>
198+
Gets a copy of full page images as <type>bytea</type> values (after
199+
applying decompression when necessary) and their information associated
200+
with all the valid WAL records between
201+
<replaceable>start_lsn</replaceable> and
202+
<replaceable>end_lsn</replaceable>. Returns one row per full page image.
203+
If <replaceable>start_lsn</replaceable> or
204+
<replaceable>end_lsn</replaceable> are not yet available, the function
205+
will raise an error. For example:
206+
<screen>
207+
postgres=# SELECT lsn, reltablespace, reldatabase, relfilenode, relblocknumber,
208+
forkname, substring(fpi for 24) as fpi_trimmed
209+
FROM pg_get_wal_fpi_info('0/1801690', '0/1825C60');
210+
-[ RECORD 1 ]--+---------------------------------------------------
211+
lsn | 0/1807E20
212+
reltablespace | 1663
213+
reldatabase | 5
214+
relfilenode | 16396
215+
relblocknumber | 43
216+
forkname | main
217+
fpi_trimmed | \x00000000b89e660100000000a003c0030020042000000000
218+
</screen>
219+
</para>
220+
</listitem>
221+
</varlistentry>
222+
191223
</variablelist>
192224
</sect2>
193225

0 commit comments

Comments
 (0)