Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc31cf1c

Browse files
committed
pg_walinspect: Add pg_get_wal_fpi_info()
This function is able to extract the full page images from a range ofrecords, specified as of input arguments start_lsn and end_lsn. Likethe other functions of this module, an error is returned if using LSNsthat do not reflect real system values. All the FPIs stored in a singlerecord are extracted.The module's version is bumped to 1.1.Author: Bharath RupireddyReviewed-by: Bertrand DrouvotDiscussion:https://postgr.es/m/CALj2ACVCcvzd7WiWvD=6_7NBvVB_r6G0EGSxL4F8vosAi6Se4g@mail.gmail.com
1 parent16fd03e commitc31cf1c

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

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+
CREATEFUNCTIONpg_get_wal_fpi_info(IN start_lsn pg_lsn,
10+
IN end_lsn pg_lsn,
11+
OUT lsn pg_lsn,
12+
OUT reltablespaceoid,
13+
OUT reldatabaseoid,
14+
OUT relfilenodeoid,
15+
OUT relblocknumber int8,
16+
OUT forknametext,
17+
OUT fpibytea
18+
)
19+
RETURNS SETOF record
20+
AS'MODULE_PATHNAME','pg_get_wal_fpi_info'
21+
LANGUAGE C STRICT PARALLEL SAFE;
22+
23+
REVOKE EXECUTEON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn)FROM PUBLIC;
24+
GRANT EXECUTEON 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,uint32ncols);
5657
staticvoidGetWalStats(FunctionCallInfofcinfo,XLogRecPtrstart_lsn,
5758
XLogRecPtrend_lsn,boolstats_per_record);
59+
staticvoidGetWALFPIInfo(FunctionCallInfofcinfo,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+
staticvoid
227+
GetWALFPIInfo(FunctionCallInfofcinfo,XLogReaderState*record)
228+
{
229+
#definePG_GET_WAL_FPI_INFO_COLS 7
230+
intblock_id;
231+
ReturnSetInfo*rsinfo= (ReturnSetInfo*)fcinfo->resultinfo;
232+
233+
for (block_id=0;block_id <=XLogRecMaxBlockId(record);block_id++)
234+
{
235+
PGAlignedBlockbuf;
236+
Pagepage;
237+
bytea*raw_page;
238+
BlockNumberblk;
239+
RelFileLocatorrnode;
240+
ForkNumberfork;
241+
Datumvalues[PG_GET_WAL_FPI_INFO_COLS]= {0};
242+
boolnulls[PG_GET_WAL_FPI_INFO_COLS]= {0};
243+
inti=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+
XLogRecPtrstart_lsn;
305+
XLogRecPtrend_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
*
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_tblSELECT*FROM generate_series(1,2);
14+
INSERT INTO sample_tblSELECT*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
SELECTCOUNT(*)>=1AS okFROM 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_tblSET col1= col1*100WHERE col1=1;
65+
SELECT pg_current_wal_lsn()AS wal_lsn4 \gset
66+
67+
-- Check if we get FPI from WAL record.
68+
SELECTCOUNT(*)>=1AS okFROM 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_filesFROM 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 EXECUTEON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn,boolean)
92114
TO regress_pg_walinspect;
93115

116+
GRANT EXECUTEON 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 EXECUTEON 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 EXECUTEON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn,boolean)
110138
FROM regress_pg_walinspect;
111139

140+
REVOKE EXECUTEON 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)
[8]ページ先頭
©2009-2025 Movatter.jp