Skip to content

Commit df4f3ab

Browse files
petergeogheganpetergeoghegan
committed
Add show_data option to pg_get_wal_block_info.
Allow users to opt out of returning FPI data and block data from pg_get_wal_block_info as an optimization. Testing has shown that this can make function execution over twice as fast in some cases. When pg_get_wal_block_info is called with "show_data := false", it always returns NULL values for its block_data and block_fpi_data bytea output parameters. Nothing else changes. In particular, the function will still return the usual per-block summary of block data/FPI space overhead. Use of "show_data := false" is therefore feasible with all queries that don't specifically require these raw binary strings. Follow-up to recent work in commit 122376f. There still hasn't been a stable release with the pg_get_wal_block_info function, so no bump in the pg_walinspect extension version. Per suggestion from Melanie Plageman. Author: Peter Geoghegan <pg@bowt.ie> Discussion: https://postgr.es/m/CAAKRu_bJvbcYBRj2cN6G2xV7B7-Ja+pjTO1nEnEhRR8OXYiABA@mail.gmail.com Discussion: https://postgr.es/m/CAH2-Wzm9shOkEDM10_+qOZkRSQhKVxwBFiehH6EHWQQRd_rDPw@mail.gmail.com
1 parent 6ee3020 commit df4f3ab

File tree

6 files changed

+39
-24
lines changed

6 files changed

+39
-24
lines changed

‎contrib/pg_walinspect/expected/oldextversions.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -50,10 +50,10 @@ ERROR: WAL start LSN must be less than current LSN
5050
ALTER EXTENSION pg_walinspect UPDATE TO '1.1';
5151
-- List what version 1.1 contains.
5252
\dx+ pg_walinspect
53-
Objects in extension "pg_walinspect"
54-
Object description
55-
--------------------------------------------------
56-
function pg_get_wal_block_info(pg_lsn,pg_lsn)
53+
Objects in extension "pg_walinspect"
54+
Object description
55+
-------------------------------------------------------
56+
function pg_get_wal_block_info(pg_lsn,pg_lsn,boolean)
5757
function pg_get_wal_record_info(pg_lsn)
5858
function pg_get_wal_records_info(pg_lsn,pg_lsn)
5959
function pg_get_wal_stats(pg_lsn,pg_lsn,boolean)

‎contrib/pg_walinspect/expected/pg_walinspect.out

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -166,7 +166,7 @@ SELECT has_function_privilege('regress_pg_walinspect',
166166
(1 row)
167167

168168
SELECT has_function_privilege('regress_pg_walinspect',
169-
'pg_get_wal_block_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no
169+
'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no
170170
has_function_privilege
171171
------------------------
172172
f
@@ -196,7 +196,7 @@ SELECT has_function_privilege('regress_pg_walinspect',
196196
(1 row)
197197

198198
SELECT has_function_privilege('regress_pg_walinspect',
199-
'pg_get_wal_block_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
199+
'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
200200
has_function_privilege
201201
------------------------
202202
t
@@ -210,7 +210,7 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
210210
TO regress_pg_walinspect;
211211
GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
212212
TO regress_pg_walinspect;
213-
GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)
213+
GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean)
214214
TO regress_pg_walinspect;
215215
SELECT has_function_privilege('regress_pg_walinspect',
216216
'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
@@ -234,7 +234,7 @@ SELECT has_function_privilege('regress_pg_walinspect',
234234
(1 row)
235235

236236
SELECT has_function_privilege('regress_pg_walinspect',
237-
'pg_get_wal_block_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
237+
'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
238238
has_function_privilege
239239
------------------------
240240
t
@@ -246,7 +246,7 @@ REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
246246
FROM regress_pg_walinspect;
247247
REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
248248
FROM regress_pg_walinspect;
249-
REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)
249+
REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean)
250250
FROM regress_pg_walinspect;
251251
-- ===================================================================
252252
-- Clean up

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@ DROP FUNCTION pg_get_wal_stats_till_end_of_wal(pg_lsn, boolean);
1212
--
1313
CREATE FUNCTION pg_get_wal_block_info(IN start_lsn pg_lsn,
1414
IN end_lsn pg_lsn,
15+
IN show_data boolean DEFAULT true,
1516
OUT start_lsn pg_lsn,
1617
OUT end_lsn pg_lsn,
1718
OUT prev_lsn pg_lsn,
@@ -37,5 +38,5 @@ RETURNS SETOF record
3738
AS 'MODULE_PATHNAME', 'pg_get_wal_block_info'
3839
LANGUAGE C STRICT PARALLEL SAFE;
3940

40-
REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn) FROM PUBLIC;
41-
GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn) TO pg_read_server_files;
41+
REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) FROM PUBLIC;
42+
GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) TO pg_read_server_files;

‎contrib/pg_walinspect/pg_walinspect.c

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,8 @@ static void GetWalStats(FunctionCallInfo fcinfo,
5959
XLogRecPtr start_lsn,
6060
XLogRecPtr end_lsn,
6161
bool stats_per_record);
62-
static void GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record);
62+
static void GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record,
63+
bool show_data);
6364

6465
/*
6566
* Return the LSN up to which the server has WAL.
@@ -244,7 +245,8 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
244245
* Keep this in sync with GetWALRecordInfo.
245246
*/
246247
static void
247-
GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
248+
GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record,
249+
bool show_data)
248250
{
249251
#define PG_GET_WAL_BLOCK_INFO_COLS 20
250252
int block_id;
@@ -359,7 +361,7 @@ GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
359361
nulls[i++] = true;
360362

361363
/* block_data output */
362-
if (blk->has_data)
364+
if (blk->has_data && show_data)
363365
{
364366
bytea *block_data;
365367

@@ -372,7 +374,7 @@ GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
372374
nulls[i++] = true;
373375

374376
/* block_fpi_data output */
375-
if (blk->has_image)
377+
if (blk->has_image && show_data)
376378
{
377379
PGAlignedBlock buf;
378380
Page page;
@@ -410,6 +412,7 @@ pg_get_wal_block_info(PG_FUNCTION_ARGS)
410412
{
411413
XLogRecPtr start_lsn = PG_GETARG_LSN(0);
412414
XLogRecPtr end_lsn = PG_GETARG_LSN(1);
415+
bool show_data = PG_GETARG_BOOL(2);
413416
XLogReaderState *xlogreader;
414417
MemoryContext old_cxt;
415418
MemoryContext tmp_cxt;
@@ -435,7 +438,7 @@ pg_get_wal_block_info(PG_FUNCTION_ARGS)
435438
/* Use the tmp context so we can clean up after each tuple is done */
436439
old_cxt = MemoryContextSwitchTo(tmp_cxt);
437440

438-
GetWALBlockInfo(fcinfo, xlogreader);
441+
GetWALBlockInfo(fcinfo, xlogreader, show_data);
439442

440443
/* clean up and switch back */
441444
MemoryContextSwitchTo(old_cxt);

‎contrib/pg_walinspect/sql/pg_walinspect.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -101,7 +101,7 @@ SELECT has_function_privilege('regress_pg_walinspect',
101101
SELECT has_function_privilege('regress_pg_walinspect',
102102
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no
103103
SELECT has_function_privilege('regress_pg_walinspect',
104-
'pg_get_wal_block_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no
104+
'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no
105105

106106
-- Functions accessible by users with role pg_read_server_files.
107107
GRANT pg_read_server_files TO regress_pg_walinspect;
@@ -113,7 +113,7 @@ SELECT has_function_privilege('regress_pg_walinspect',
113113
SELECT has_function_privilege('regress_pg_walinspect',
114114
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
115115
SELECT has_function_privilege('regress_pg_walinspect',
116-
'pg_get_wal_block_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
116+
'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
117117

118118
REVOKE pg_read_server_files FROM regress_pg_walinspect;
119119

@@ -124,7 +124,7 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
124124
TO regress_pg_walinspect;
125125
GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
126126
TO regress_pg_walinspect;
127-
GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)
127+
GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean)
128128
TO regress_pg_walinspect;
129129

130130
SELECT has_function_privilege('regress_pg_walinspect',
@@ -134,15 +134,15 @@ SELECT has_function_privilege('regress_pg_walinspect',
134134
SELECT has_function_privilege('regress_pg_walinspect',
135135
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
136136
SELECT has_function_privilege('regress_pg_walinspect',
137-
'pg_get_wal_block_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
137+
'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
138138

139139
REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
140140
FROM regress_pg_walinspect;
141141
REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
142142
FROM regress_pg_walinspect;
143143
REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
144144
FROM regress_pg_walinspect;
145-
REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)
145+
REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean)
146146
FROM regress_pg_walinspect;
147147

148148
-- ===================================================================

‎doc/src/sgml/pgwalinspect.sgml

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -133,7 +133,7 @@ block_ref |
133133

134134
<varlistentry>
135135
<term>
136-
<function>pg_get_wal_block_info(start_lsn pg_lsn, end_lsn pg_lsn) returns setof record</function>
136+
<function>pg_get_wal_block_info(start_lsn pg_lsn, end_lsn pg_lsn, show_data boolean DEFAULT true) returns setof record</function>
137137
</term>
138138

139139
<listitem>
@@ -209,10 +209,21 @@ block_fpi_data |
209209
<para>
210210
The <function>pg_filenode_relation</function> function (see
211211
<xref linkend="functions-admin-dblocation"/>) can help you to
212-
determine which block/relation was modified by each WAL record
213-
during original execution
212+
determine which relation was modified during original execution
214213
</para>
215214
</tip>
215+
<para>
216+
It is possible for clients to avoid the overhead of
217+
materializing block data. This may make function execution
218+
significantly faster. When <replaceable>show_data</replaceable>
219+
is set to <literal>false</literal>, <structfield>block_data</structfield>
220+
and <structfield>block_fpi_data</structfield> values are omitted
221+
(that is, the <structfield>block_data</structfield> and
222+
<structfield>block_fpi_data</structfield> <literal>OUT</literal>
223+
arguments are <literal>NULL</literal> for all rows returned).
224+
Obviously, this optimization is only feasible with queries where
225+
block data isn't truly required.
226+
</para>
216227
<para>
217228
The function raises an error if
218229
<replaceable>start_lsn</replaceable> is not available.

0 commit comments

Comments
 (0)