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

Commitdf4f3ab

Browse files
Add show_data option to pg_get_wal_block_info.
Allow users to opt out of returning FPI data and block data frompg_get_wal_block_info as an optimization. Testing has shown that thiscan make function execution over twice as fast in some cases.When pg_get_wal_block_info is called with "show_data := false", italways returns NULL values for its block_data and block_fpi_data byteaoutput parameters. Nothing else changes. In particular, the functionwill still return the usual per-block summary of block data/FPI spaceoverhead. Use of "show_data := false" is therefore feasible with allqueries that don't specifically require these raw binary strings.Follow-up to recent work in commit122376f. There still hasn't been astable release with the pg_get_wal_block_info function, so no bump inthe 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.comDiscussion:https://postgr.es/m/CAH2-Wzm9shOkEDM10_+qOZkRSQhKVxwBFiehH6EHWQQRd_rDPw@mail.gmail.com
1 parent6ee3020 commitdf4f3ab

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
CREATEFUNCTIONpg_get_wal_block_info(IN start_lsn pg_lsn,
1414
IN end_lsn pg_lsn,
15+
IN show_databoolean 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 EXECUTEON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)FROM PUBLIC;
41-
GRANT EXECUTEON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn) TO pg_read_server_files;
41+
REVOKE EXECUTEON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn,boolean)FROM PUBLIC;
42+
GRANT EXECUTEON 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
XLogRecPtrstart_lsn,
6060
XLogRecPtrend_lsn,
6161
boolstats_per_record);
62-
staticvoidGetWALBlockInfo(FunctionCallInfofcinfo,XLogReaderState*record);
62+
staticvoidGetWALBlockInfo(FunctionCallInfofcinfo,XLogReaderState*record,
63+
boolshow_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
staticvoid
247-
GetWALBlockInfo(FunctionCallInfofcinfo,XLogReaderState*record)
248+
GetWALBlockInfo(FunctionCallInfofcinfo,XLogReaderState*record,
249+
boolshow_data)
248250
{
249251
#definePG_GET_WAL_BLOCK_INFO_COLS 20
250252
intblock_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
PGAlignedBlockbuf;
378380
Pagepage;
@@ -410,6 +412,7 @@ pg_get_wal_block_info(PG_FUNCTION_ARGS)
410412
{
411413
XLogRecPtrstart_lsn=PG_GETARG_LSN(0);
412414
XLogRecPtrend_lsn=PG_GETARG_LSN(1);
415+
boolshow_data=PG_GETARG_BOOL(2);
413416
XLogReaderState*xlogreader;
414417
MemoryContextold_cxt;
415418
MemoryContexttmp_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_filesFROM 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 EXECUTEON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn,boolean)
126126
TO regress_pg_walinspect;
127-
GRANT EXECUTEON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)
127+
GRANT EXECUTEON 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 EXECUTEON FUNCTION pg_get_wal_record_info(pg_lsn)
140140
FROM regress_pg_walinspect;
141141
REVOKE EXECUTEON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
142142
FROM regress_pg_walinspect;
143143
REVOKE EXECUTEON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn,boolean)
144144
FROM regress_pg_walinspect;
145-
REVOKE EXECUTEON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)
145+
REVOKE EXECUTEON 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)
[8]ページ先頭
©2009-2025 Movatter.jp