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

Commita1bc4d3

Browse files
committed
Add some basic regression tests for pg_freespacemap
The number of relation pages is tricky to get right in a portable way,particularly across 32b and 64b builds, but checking after the existenceof the FSM and if there is any space available space should be stableenough with a minimal number of tuples involved. This commit introduceschecks on a table with some btree, BRIN and hash indexes, as a firstattempt.Author: Dong Wook Lee, Fabrízio de Royes Mello, Michael PaquierDiscussion:https://postgr.es/m/CAAcByaJ5KW3bd7fJr=jPEyK8M_UzXJFHHBVuOcBe+JHD8txRyQ@mail.gmail.com
1 parent3ac7d02 commita1bc4d3

File tree

4 files changed

+123
-0
lines changed

4 files changed

+123
-0
lines changed

‎contrib/pg_freespacemap/.gitignore‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Generated subdirectories
2+
/log/
3+
/results/
4+
/tmp_check/

‎contrib/pg_freespacemap/Makefile‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,8 @@ DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
1010
pg_freespacemap--1.0--1.1.sql
1111
PGFILEDESC = "pg_freespacemap - monitoring of free space map"
1212

13+
REGRESS = pg_freespacemap
14+
1315
ifdefUSE_PGXS
1416
PG_CONFIG = pg_config
1517
PGXS :=$(shell$(PG_CONFIG) --pgxs)
Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,85 @@
1+
CREATE EXTENSION pg_freespacemap;
2+
CREATE TABLE freespace_tab (c1 int) WITH (autovacuum_enabled = off);
3+
CREATE INDEX freespace_brin ON freespace_tab USING brin (c1);
4+
CREATE INDEX freespace_btree ON freespace_tab USING btree (c1);
5+
CREATE INDEX freespace_hash ON freespace_tab USING hash (c1);
6+
-- report all the sizes of the FSMs for all the relation blocks.
7+
WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
8+
SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
9+
FROM rel, LATERAL pg_freespace(rel.id) AS fsm
10+
ORDER BY 1, 2;
11+
id | blkno | is_avail
12+
-----------------+-------+----------
13+
freespace_brin | 0 | f
14+
freespace_brin | 1 | f
15+
freespace_brin | 2 | t
16+
freespace_btree | 0 | f
17+
freespace_hash | 0 | f
18+
freespace_hash | 1 | f
19+
freespace_hash | 2 | f
20+
freespace_hash | 3 | f
21+
freespace_hash | 4 | f
22+
freespace_hash | 5 | f
23+
freespace_hash | 6 | f
24+
freespace_hash | 7 | f
25+
freespace_hash | 8 | f
26+
freespace_hash | 9 | f
27+
(14 rows)
28+
29+
INSERT INTO freespace_tab VALUES (1);
30+
VACUUM freespace_tab;
31+
WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
32+
SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
33+
FROM rel, LATERAL pg_freespace(rel.id) AS fsm
34+
ORDER BY 1, 2;
35+
id | blkno | is_avail
36+
-----------------+-------+----------
37+
freespace_tab | 0 | t
38+
freespace_brin | 0 | f
39+
freespace_brin | 1 | f
40+
freespace_brin | 2 | t
41+
freespace_btree | 0 | f
42+
freespace_btree | 1 | f
43+
freespace_hash | 0 | f
44+
freespace_hash | 1 | f
45+
freespace_hash | 2 | f
46+
freespace_hash | 3 | f
47+
freespace_hash | 4 | f
48+
freespace_hash | 5 | f
49+
freespace_hash | 6 | f
50+
freespace_hash | 7 | f
51+
freespace_hash | 8 | f
52+
freespace_hash | 9 | f
53+
(16 rows)
54+
55+
DELETE FROM freespace_tab;
56+
VACUUM freespace_tab;
57+
WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
58+
SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
59+
FROM rel, LATERAL pg_freespace(rel.id) AS fsm
60+
ORDER BY 1, 2;
61+
id | blkno | is_avail
62+
-----------------+-------+----------
63+
freespace_brin | 0 | f
64+
freespace_brin | 1 | f
65+
freespace_brin | 2 | t
66+
freespace_btree | 0 | f
67+
freespace_btree | 1 | f
68+
freespace_hash | 0 | f
69+
freespace_hash | 1 | f
70+
freespace_hash | 2 | f
71+
freespace_hash | 3 | f
72+
freespace_hash | 4 | f
73+
freespace_hash | 5 | f
74+
freespace_hash | 6 | f
75+
freespace_hash | 7 | f
76+
freespace_hash | 8 | f
77+
freespace_hash | 9 | f
78+
(15 rows)
79+
80+
-- failures with incorrect block number
81+
SELECT * FROM pg_freespace('freespace_tab', -1);
82+
ERROR: invalid block number
83+
SELECT * FROM pg_freespace('freespace_tab', 4294967295);
84+
ERROR: invalid block number
85+
DROP TABLE freespace_tab;
Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,32 @@
1+
CREATE EXTENSION pg_freespacemap;
2+
3+
CREATETABLEfreespace_tab (c1int) WITH (autovacuum_enabled= off);
4+
CREATEINDEXfreespace_brinON freespace_tab USING brin (c1);
5+
CREATEINDEXfreespace_btreeON freespace_tab USING btree (c1);
6+
CREATEINDEXfreespace_hashON freespace_tab USING hash (c1);
7+
8+
-- report all the sizes of the FSMs for all the relation blocks.
9+
WITH relAS (SELECToid::regclassAS idFROM pg_classWHERE relname ~'freespace')
10+
SELECTrel.id,fsm.blkno, (fsm.avail>0)AS is_avail
11+
FROM rel, LATERAL pg_freespace(rel.id)AS fsm
12+
ORDER BY1,2;
13+
14+
INSERT INTO freespace_tabVALUES (1);
15+
VACUUM freespace_tab;
16+
WITH relAS (SELECToid::regclassAS idFROM pg_classWHERE relname ~'freespace')
17+
SELECTrel.id,fsm.blkno, (fsm.avail>0)AS is_avail
18+
FROM rel, LATERAL pg_freespace(rel.id)AS fsm
19+
ORDER BY1,2;
20+
21+
DELETEFROM freespace_tab;
22+
VACUUM freespace_tab;
23+
WITH relAS (SELECToid::regclassAS idFROM pg_classWHERE relname ~'freespace')
24+
SELECTrel.id,fsm.blkno, (fsm.avail>0)AS is_avail
25+
FROM rel, LATERAL pg_freespace(rel.id)AS fsm
26+
ORDER BY1,2;
27+
28+
-- failures with incorrect block number
29+
SELECT*FROM pg_freespace('freespace_tab',-1);
30+
SELECT*FROM pg_freespace('freespace_tab',4294967295);
31+
32+
DROPTABLE freespace_tab;

0 commit comments

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