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 existence
of the FSM and if there is any space available space should be stable
enough with a minimal number of tuples involved.  This commit introduces
checks on a table with some btree, BRIN and hash indexes, as a first
attempt.

Author: Dong Wook Lee, Fabrízio de Royes Mello, Michael Paquier
Discussion: https://postgr.es/m/CAAcByaJ5KW3bd7fJr=jPEyK8M_UzXJFHHBVuOcBe+JHD8txRyQ@mail.gmail.com

1 files changed, 32 insertions, 0 deletions

diff --git a/contrib/pg_freespacemap/sql/pg_freespacemap.sql b/contrib/pg_freespacemap/sql/pg_freespacemap.sql
new file mode 100644
index 00000000000..06275d8fac8
--- /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 the FSMs for all the relation blocks.
+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;