On Mon, Mar 21, 2022 at 09:12:37PM +0900, Dong Wook Lee wrote: > 2022년 3월 20일 (일) 03:13, Fabrízio de Royes Mello <fabriziome...@gmail.com>님이 > 작성: >> On Sat, Mar 19, 2022 at 1:18 PM Dong Wook Lee <sh95...@gmail.com> wrote: >>>> Well, my guess is that you basically just care about being able to >>>> detect if there is free space in the map or not, which goes down to >>>> detecting if pg_freespace() returns 0 or a number strictly higher than >>>> 0, so wouldn't it be enough to stick some > 0 in your test queries? >>> >>> I edited the previous patch file. >>> Am I correct in understanding that? >>> >> >> I think what Michael meant is something like attached. > > I think you’re right, thank you for sending it instead of me.
Yes, something like v3 was what I was referring to as we cannot rely on exact numbers for this test suite. At least, we can check if there is a FSM for a given block, even if that can be limited. After review, I don't like much the idea of allowing concurrent autovacuums to run in parallel of the table(s) of this test, so we'd better disable it explicitely. "t1" is also a very generic name to use in a regression test. Another thing that itched me is that we could also test more with indexes, particularly with btree, BRIN and hash (the latter should not have a FSM with 10 pages as per the first group batch, and each one has a stable an initial state). Finally, making the tests stable across 32-bit compilations (say gcc -m32) is proving to be tricky, but it should be safe enough to check if the FSM is computed or not with a minimal number of tuples. Btw, a .gitignore was also forgotten. I have extended the set of tests as of the attached, running these across everything I could (CI, all my hosts including Windows, macos, Linux). We could do more later, of course, but this looks enough to me as a first step. And I think that this will not upset the buildfarm. -- Michael
From 10002eaed60e3128cfe93c3908254eed81341e52 Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@paquier.xyz>
Date: Wed, 23 Mar 2022 15:00:13 +0900
Subject: [PATCH v4] Add some regression tests for pg_freespacemap
---
contrib/pg_freespacemap/.gitignore | 4 +
contrib/pg_freespacemap/Makefile | 2 +
.../expected/pg_freespacemap.out | 85 +++++++++++++++++++
.../pg_freespacemap/sql/pg_freespacemap.sql | 32 +++++++
4 files changed, 123 insertions(+)
create mode 100644 contrib/pg_freespacemap/.gitignore
create mode 100644 contrib/pg_freespacemap/expected/pg_freespacemap.out
create mode 100644 contrib/pg_freespacemap/sql/pg_freespacemap.sql
diff --git a/contrib/pg_freespacemap/.gitignore b/contrib/pg_freespacemap/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/contrib/pg_freespacemap/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index da40b80c7c..2d525a1284 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -10,6 +10,8 @@ DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
pg_freespacemap--1.0--1.1.sql
PGFILEDESC = "pg_freespacemap - monitoring of free space map"
+REGRESS = pg_freespacemap
+
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/pg_freespacemap/expected/pg_freespacemap.out b/contrib/pg_freespacemap/expected/pg_freespacemap.out
new file mode 100644
index 0000000000..c0f71ba8aa
--- /dev/null
+++ b/contrib/pg_freespacemap/expected/pg_freespacemap.out
@@ -0,0 +1,85 @@
+CREATE EXTENSION pg_freespacemap;
+CREATE TABLE freespace_tab (c1 int) WITH (autovacuum_enabled = off);
+CREATE INDEX freespace_brin ON freespace_tab USING brin (c1);
+CREATE INDEX freespace_btree ON freespace_tab USING btree (c1);
+CREATE INDEX freespace_hash ON freespace_tab USING hash (c1);
+-- Report all the sizes of FSMs for all the relation blocks involved here.
+WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
+ SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
+ FROM rel, LATERAL pg_freespace(rel.id) AS fsm
+ ORDER BY 1, 2;
+ id | blkno | is_avail
+-----------------+-------+----------
+ freespace_brin | 0 | f
+ freespace_brin | 1 | f
+ freespace_brin | 2 | t
+ freespace_btree | 0 | f
+ freespace_hash | 0 | f
+ freespace_hash | 1 | f
+ freespace_hash | 2 | f
+ freespace_hash | 3 | f
+ freespace_hash | 4 | f
+ freespace_hash | 5 | f
+ freespace_hash | 6 | f
+ freespace_hash | 7 | f
+ freespace_hash | 8 | f
+ freespace_hash | 9 | f
+(14 rows)
+
+INSERT INTO freespace_tab VALUES (1);
+VACUUM freespace_tab;
+WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
+ SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
+ FROM rel, LATERAL pg_freespace(rel.id) AS fsm
+ ORDER BY 1, 2;
+ id | blkno | is_avail
+-----------------+-------+----------
+ freespace_tab | 0 | t
+ freespace_brin | 0 | f
+ freespace_brin | 1 | f
+ freespace_brin | 2 | t
+ freespace_btree | 0 | f
+ freespace_btree | 1 | f
+ freespace_hash | 0 | f
+ freespace_hash | 1 | f
+ freespace_hash | 2 | f
+ freespace_hash | 3 | f
+ freespace_hash | 4 | f
+ freespace_hash | 5 | f
+ freespace_hash | 6 | f
+ freespace_hash | 7 | f
+ freespace_hash | 8 | f
+ freespace_hash | 9 | f
+(16 rows)
+
+DELETE FROM freespace_tab;
+VACUUM freespace_tab;
+WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
+ SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
+ FROM rel, LATERAL pg_freespace(rel.id) AS fsm
+ ORDER BY 1, 2;
+ id | blkno | is_avail
+-----------------+-------+----------
+ freespace_brin | 0 | f
+ freespace_brin | 1 | f
+ freespace_brin | 2 | t
+ freespace_btree | 0 | f
+ freespace_btree | 1 | f
+ freespace_hash | 0 | f
+ freespace_hash | 1 | f
+ freespace_hash | 2 | f
+ freespace_hash | 3 | f
+ freespace_hash | 4 | f
+ freespace_hash | 5 | f
+ freespace_hash | 6 | f
+ freespace_hash | 7 | f
+ freespace_hash | 8 | f
+ freespace_hash | 9 | f
+(15 rows)
+
+-- failures with incorrect block number
+SELECT * FROM pg_freespace('freespace_tab', -1);
+ERROR: invalid block number
+SELECT * FROM pg_freespace('freespace_tab', 4294967295);
+ERROR: invalid block number
+DROP TABLE freespace_tab;
diff --git a/contrib/pg_freespacemap/sql/pg_freespacemap.sql b/contrib/pg_freespacemap/sql/pg_freespacemap.sql
new file mode 100644
index 0000000000..b8d0405c63
--- /dev/null
+++ b/contrib/pg_freespacemap/sql/pg_freespacemap.sql
@@ -0,0 +1,32 @@
+CREATE EXTENSION pg_freespacemap;
+
+CREATE TABLE freespace_tab (c1 int) WITH (autovacuum_enabled = off);
+CREATE INDEX freespace_brin ON freespace_tab USING brin (c1);
+CREATE INDEX freespace_btree ON freespace_tab USING btree (c1);
+CREATE INDEX freespace_hash ON freespace_tab USING hash (c1);
+
+-- Report all the sizes of FSMs for all the relation blocks involved here.
+WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
+ SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
+ FROM rel, LATERAL pg_freespace(rel.id) AS fsm
+ ORDER BY 1, 2;
+
+INSERT INTO freespace_tab VALUES (1);
+VACUUM freespace_tab;
+WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
+ SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
+ FROM rel, LATERAL pg_freespace(rel.id) AS fsm
+ ORDER BY 1, 2;
+
+DELETE FROM freespace_tab;
+VACUUM freespace_tab;
+WITH rel AS (SELECT oid::regclass AS id FROM pg_class WHERE relname ~ 'freespace')
+ SELECT rel.id, fsm.blkno, (fsm.avail > 0) AS is_avail
+ FROM rel, LATERAL pg_freespace(rel.id) AS fsm
+ ORDER BY 1, 2;
+
+-- failures with incorrect block number
+SELECT * FROM pg_freespace('freespace_tab', -1);
+SELECT * FROM pg_freespace('freespace_tab', 4294967295);
+
+DROP TABLE freespace_tab;
--
2.35.1
signature.asc
Description: PGP signature
