diff options
| -rw-r--r-- | contrib/pg_freespacemap/.gitignore | 4 | ||||
| -rw-r--r-- | contrib/pg_freespacemap/Makefile | 2 | ||||
| -rw-r--r-- | contrib/pg_freespacemap/expected/pg_freespacemap.out | 85 | ||||
| -rw-r--r-- | contrib/pg_freespacemap/sql/pg_freespacemap.sql | 32 |
4 files changed, 123 insertions, 0 deletions
diff --git a/contrib/pg_freespacemap/.gitignore b/contrib/pg_freespacemap/.gitignore new file mode 100644 index 00000000000..5dcb3ff9723 --- /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 da40b80c7c9..2d525a1284e 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 00000000000..eb574c23736 --- /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 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; + 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 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; |