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

Commit1f282c2

Browse files
committed
Refactor and improve tests of pg_walinspect
The regression tests of pg_walinspect are reworked on a few aspects:- Reorganization on the validation checks done for the start and endLSNs on the six SQL functions currently available in 1.1.- Addition of a few patterns doing bound checks for invalid start LSN,invalid end LSN, and failures in reading LSN positions, for anythingthat's been missing.- Use of a consistent style across the whole, limiting blank linesacross the queries.- Addition of a new test script for upgrades. For the time being, thisis straight-forward with a check that the upgrade from 1.0 workscorrectly. This will be made more complicated once the interface ofthis extension is reworked in 1.1 with a follow-up patch.Most of the contents of this commit are extracted from a larger patch bythe same author, largely reorganized by me to minimize the differenceswith the upcoming work aimed to lift the bound checks on the input LSNsused by the functions of this extension.Author: Bharath Rupireddy, Michael PaquierDiscussion:https://postgr.es/m/CALj2ACU0_q-o4DSweyaW9NO1KBx-QkN6G_OzYQvpjf3CZVASkg@mail.gmail.com
1 parent767c598 commit1f282c2

File tree

6 files changed

+128
-34
lines changed

6 files changed

+128
-34
lines changed

‎contrib/pg_walinspect/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@ PGFILEDESC = "pg_walinspect - functions to inspect contents of PostgreSQL Write-
99
EXTENSION = pg_walinspect
1010
DATA = pg_walinspect--1.0.sql pg_walinspect--1.0--1.1.sql
1111

12-
REGRESS = pg_walinspect
12+
REGRESS = pg_walinspect oldextversions
1313

1414
REGRESS_OPTS = --temp-config$(top_srcdir)/contrib/pg_walinspect/walinspect.conf
1515

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
-- test old extension version entry points
2+
CREATE EXTENSION pg_walinspect WITH VERSION '1.0';
3+
-- List what version 1.0 contains
4+
\dx+ pg_walinspect
5+
Objects in extension "pg_walinspect"
6+
Object description
7+
-----------------------------------------------------------
8+
function pg_get_wal_record_info(pg_lsn)
9+
function pg_get_wal_records_info(pg_lsn,pg_lsn)
10+
function pg_get_wal_records_info_till_end_of_wal(pg_lsn)
11+
function pg_get_wal_stats(pg_lsn,pg_lsn,boolean)
12+
function pg_get_wal_stats_till_end_of_wal(pg_lsn,boolean)
13+
(5 rows)
14+
15+
-- Move to new version 1.1
16+
ALTER EXTENSION pg_walinspect UPDATE TO '1.1';
17+
-- List what version 1.1 contains
18+
\dx+ pg_walinspect
19+
Objects in extension "pg_walinspect"
20+
Object description
21+
-----------------------------------------------------------
22+
function pg_get_wal_block_info(pg_lsn,pg_lsn)
23+
function pg_get_wal_record_info(pg_lsn)
24+
function pg_get_wal_records_info(pg_lsn,pg_lsn)
25+
function pg_get_wal_records_info_till_end_of_wal(pg_lsn)
26+
function pg_get_wal_stats(pg_lsn,pg_lsn,boolean)
27+
function pg_get_wal_stats_till_end_of_wal(pg_lsn,boolean)
28+
(6 rows)
29+
30+
DROP EXTENSION pg_walinspect;

‎contrib/pg_walinspect/expected/pg_walinspect.out

Lines changed: 49 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,6 @@
11
CREATE EXTENSION pg_walinspect;
2+
-- Mask DETAIL messages as these could refer to current LSN positions.
3+
\set VERBOSITY terse
24
-- Make sure checkpoints don't interfere with the test.
35
SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false);
46
?column?
@@ -7,17 +9,52 @@ SELECT 'init' FROM pg_create_physical_replication_slot('regress_pg_walinspect_sl
79
(1 row)
810

911
CREATE TABLE sample_tbl(col1 int, col2 int);
12+
-- Save some LSNs for comparisons
1013
SELECT pg_current_wal_lsn() AS wal_lsn1 \gset
1114
INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
1215
SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
1316
INSERT INTO sample_tbl SELECT * FROM generate_series(3, 4);
1417
-- ===================================================================
1518
-- Tests for input validation
1619
-- ===================================================================
17-
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1'); -- ERROR
20+
-- Invalid input LSN.
21+
SELECT * FROM pg_get_wal_record_info('0/0');
22+
ERROR: could not read WAL at LSN 0/0
23+
-- Invalid start LSN.
24+
SELECT * FROM pg_get_wal_records_info('0/0', :'wal_lsn1');
25+
ERROR: could not read WAL at LSN 0/0
26+
SELECT * FROM pg_get_wal_stats('0/0', :'wal_lsn1');
27+
ERROR: could not read WAL at LSN 0/0
28+
SELECT * FROM pg_get_wal_block_info('0/0', :'wal_lsn1');
29+
ERROR: could not read WAL at LSN 0/0
30+
-- Start LSN > End LSN.
31+
SELECT * FROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1');
1832
ERROR: WAL start LSN must be less than end LSN
19-
SELECTCOUNT(*) >= 0 AS okFROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1'); -- ERROR
33+
SELECT*FROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1');
2034
ERROR: WAL start LSN must be less than end LSN
35+
SELECT * FROM pg_get_wal_block_info(:'wal_lsn2', :'wal_lsn1');
36+
ERROR: WAL start LSN must be less than end LSN
37+
-- LSNs with the highest value possible.
38+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF');
39+
ERROR: cannot accept future input LSN
40+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF');
41+
ERROR: cannot accept future start LSN
42+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF');
43+
ERROR: cannot accept future start LSN
44+
-- failures with end LSNs
45+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
46+
ERROR: cannot accept future end LSN
47+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
48+
ERROR: cannot accept future end LSN
49+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', 'FFFFFFFF/FFFFFFFF');
50+
ERROR: cannot accept future end LSN
51+
-- failures with start LSNs
52+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
53+
ERROR: cannot accept future start LSN
54+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
55+
ERROR: cannot accept future start LSN
56+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE', 'FFFFFFFF/FFFFFFFF');
57+
ERROR: cannot accept future start LSN
2158
-- ===================================================================
2259
-- Tests for all function executions
2360
-- ===================================================================
@@ -27,13 +64,19 @@ SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info(:'wal_lsn1');
2764
t
2865
(1 row)
2966

30-
SELECT COUNT(*) >= 0 AS ok FROMpg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
67+
SELECT COUNT(*) >= 0 AS ok FROMpg_get_wal_records_info_till_end_of_wal(:'wal_lsn1');
3168
ok
3269
----
3370
t
3471
(1 row)
3572

36-
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info_till_end_of_wal(:'wal_lsn1');
73+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats_till_end_of_wal(:'wal_lsn1');
74+
ok
75+
----
76+
t
77+
(1 row)
78+
79+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
3780
ok
3881
----
3982
t
@@ -45,7 +88,7 @@ SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
4588
t
4689
(1 row)
4790

48-
SELECT COUNT(*) >= 0 AS ok FROMpg_get_wal_stats_till_end_of_wal(:'wal_lsn1');
91+
SELECT COUNT(*) >= 0 AS ok FROMpg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2');
4992
ok
5093
----
5194
t
@@ -220,3 +263,4 @@ SELECT pg_drop_replication_slot('regress_pg_walinspect_slot');
220263
(1 row)
221264

222265
DROP TABLE sample_tbl;
266+
DROP EXTENSION pg_walinspect;

‎contrib/pg_walinspect/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@ tests += {
3030
'regress': {
3131
'sql': [
3232
'pg_walinspect',
33+
'oldextversions',
3334
],
3435
# Disabled because these tests require "wal_level=replica", which
3536
# some runningcheck users do not have (e.g. buildfarm clients).
Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
-- test old extension version entry points
2+
3+
CREATE EXTENSION pg_walinspect WITH VERSION'1.0';
4+
5+
-- List what version 1.0 contains
6+
\dx+ pg_walinspect
7+
8+
-- Move to new version 1.1
9+
ALTER EXTENSION pg_walinspectUPDATE TO'1.1';
10+
11+
-- List what version 1.1 contains
12+
\dx+ pg_walinspect
13+
14+
DROP EXTENSION pg_walinspect;

‎contrib/pg_walinspect/sql/pg_walinspect.sql

Lines changed: 33 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -1,39 +1,59 @@
11
CREATE EXTENSION pg_walinspect;
22

3+
-- Mask DETAIL messages as these could refer to current LSN positions.
4+
\set VERBOSITY terse
5+
36
-- Make sure checkpoints don't interfere with the test.
47
SELECT'init'FROM pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false);
58

69
CREATETABLEsample_tbl(col1int, col2int);
710

11+
-- Save some LSNs for comparisons
812
SELECT pg_current_wal_lsn()AS wal_lsn1 \gset
9-
1013
INSERT INTO sample_tblSELECT*FROM generate_series(1,2);
11-
1214
SELECT pg_current_wal_lsn()AS wal_lsn2 \gset
13-
1415
INSERT INTO sample_tblSELECT*FROM generate_series(3,4);
1516

1617
-- ===================================================================
1718
-- Tests for input validation
1819
-- ===================================================================
1920

20-
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1');-- ERROR
21-
22-
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1');-- ERROR
21+
-- Invalid input LSN.
22+
SELECT*FROM pg_get_wal_record_info('0/0');
23+
24+
-- Invalid start LSN.
25+
SELECT*FROM pg_get_wal_records_info('0/0', :'wal_lsn1');
26+
SELECT*FROM pg_get_wal_stats('0/0', :'wal_lsn1');
27+
SELECT*FROM pg_get_wal_block_info('0/0', :'wal_lsn1');
28+
29+
-- Start LSN > End LSN.
30+
SELECT*FROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1');
31+
SELECT*FROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1');
32+
SELECT*FROM pg_get_wal_block_info(:'wal_lsn2', :'wal_lsn1');
33+
34+
-- LSNs with the highest value possible.
35+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_record_info('FFFFFFFF/FFFFFFFF');
36+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF');
37+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF');
38+
-- failures with end LSNs
39+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_records_info(:'wal_lsn1','FFFFFFFF/FFFFFFFF');
40+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_stats(:'wal_lsn1','FFFFFFFF/FFFFFFFF');
41+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_block_info(:'wal_lsn1','FFFFFFFF/FFFFFFFF');
42+
-- failures with start LSNs
43+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_records_info('FFFFFFFF/FFFFFFFE','FFFFFFFF/FFFFFFFF');
44+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_stats('FFFFFFFF/FFFFFFFE','FFFFFFFF/FFFFFFFF');
45+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_block_info('FFFFFFFF/FFFFFFFE','FFFFFFFF/FFFFFFFF');
2346

2447
-- ===================================================================
2548
-- Tests for all function executions
2649
-- ===================================================================
2750

2851
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_record_info(:'wal_lsn1');
29-
30-
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
31-
3252
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_records_info_till_end_of_wal(:'wal_lsn1');
33-
34-
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
35-
3653
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_stats_till_end_of_wal(:'wal_lsn1');
54+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
55+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
56+
SELECTCOUNT(*)>=0AS okFROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2');
3757

3858
-- ===================================================================
3959
-- Test for filtering out WAL records of a particular table
@@ -80,29 +100,22 @@ CREATE ROLE regress_pg_walinspect;
80100

81101
SELECT has_function_privilege('regress_pg_walinspect',
82102
'pg_get_wal_record_info(pg_lsn)','EXECUTE');-- no
83-
84103
SELECT has_function_privilege('regress_pg_walinspect',
85104
'pg_get_wal_records_info(pg_lsn, pg_lsn)','EXECUTE');-- no
86-
87105
SELECT has_function_privilege('regress_pg_walinspect',
88106
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean)','EXECUTE');-- no
89-
90107
SELECT has_function_privilege('regress_pg_walinspect',
91108
'pg_get_wal_block_info(pg_lsn, pg_lsn)','EXECUTE');-- no
92109

93110
-- Functions accessible by users with role pg_read_server_files
94111

95112
GRANT pg_read_server_files TO regress_pg_walinspect;
96-
97113
SELECT has_function_privilege('regress_pg_walinspect',
98114
'pg_get_wal_record_info(pg_lsn)','EXECUTE');-- yes
99-
100115
SELECT has_function_privilege('regress_pg_walinspect',
101116
'pg_get_wal_records_info(pg_lsn, pg_lsn)','EXECUTE');-- yes
102-
103117
SELECT has_function_privilege('regress_pg_walinspect',
104118
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean)','EXECUTE');-- yes
105-
106119
SELECT has_function_privilege('regress_pg_walinspect',
107120
'pg_get_wal_block_info(pg_lsn, pg_lsn)','EXECUTE');-- yes
108121

@@ -111,37 +124,28 @@ REVOKE pg_read_server_files FROM regress_pg_walinspect;
111124
-- Superuser can grant execute to other users
112125
GRANT EXECUTEON FUNCTION pg_get_wal_record_info(pg_lsn)
113126
TO regress_pg_walinspect;
114-
115127
GRANT EXECUTEON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
116128
TO regress_pg_walinspect;
117-
118129
GRANT EXECUTEON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn,boolean)
119130
TO regress_pg_walinspect;
120-
121131
GRANT EXECUTEON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)
122132
TO regress_pg_walinspect;
123133

124134
SELECT has_function_privilege('regress_pg_walinspect',
125135
'pg_get_wal_record_info(pg_lsn)','EXECUTE');-- yes
126-
127136
SELECT has_function_privilege('regress_pg_walinspect',
128137
'pg_get_wal_records_info(pg_lsn, pg_lsn)','EXECUTE');-- yes
129-
130138
SELECT has_function_privilege('regress_pg_walinspect',
131139
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean)','EXECUTE');-- yes
132-
133140
SELECT has_function_privilege('regress_pg_walinspect',
134141
'pg_get_wal_block_info(pg_lsn, pg_lsn)','EXECUTE');-- yes
135142

136143
REVOKE EXECUTEON FUNCTION pg_get_wal_record_info(pg_lsn)
137144
FROM regress_pg_walinspect;
138-
139145
REVOKE EXECUTEON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
140146
FROM regress_pg_walinspect;
141-
142147
REVOKE EXECUTEON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn,boolean)
143148
FROM regress_pg_walinspect;
144-
145149
REVOKE EXECUTEON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn)
146150
FROM regress_pg_walinspect;
147151

@@ -154,3 +158,4 @@ DROP ROLE regress_pg_walinspect;
154158
SELECT pg_drop_replication_slot('regress_pg_walinspect_slot');
155159

156160
DROPTABLE sample_tbl;
161+
DROP EXTENSION pg_walinspect;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp