On Wed, Jan 30, 2019 at 4:33 AM Amit Kapila <amit.kapil...@gmail.com> wrote:
> There are two more failures which we need to something about.
> 1. Make fsm.sql independent of vacuum without much losing on coverage
> of newly added code. John, I guess you have an idea, see if you can
> take care of it, otherwise, I will see what I can do for it.
I've attached a patch that applies on top of v19 that uses Andrew
Gierth's idea to use fillfactor to control free space. I've also
removed tests that relied on truncation and weren't very useful to
begin with.
--
John Naylor https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/test/regress/expected/fsm.out b/src/test/regress/expected/fsm.out
index df6b15b9d2..f92d454042 100644
--- a/src/test/regress/expected/fsm.out
+++ b/src/test/regress/expected/fsm.out
@@ -2,11 +2,10 @@
-- Free Space Map test
--
CREATE TABLE fsm_check_size (num int, str text);
--- Fill 3 blocks with as many large records as will fit
--- No FSM
+-- Fill 3 blocks with one record each
+ALTER TABLE fsm_check_size SET (fillfactor=15);
INSERT INTO fsm_check_size SELECT i, rpad('', 1024, 'a')
-FROM generate_series(1,7*3) i;
-VACUUM fsm_check_size;
+FROM generate_series(1,3) i;
SELECT pg_relation_size('fsm_check_size', 'main') AS heap_size,
pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
heap_size | fsm_size
@@ -14,11 +13,12 @@ pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
24576 | 0
(1 row)
--- Clear some space on block 0
-DELETE FROM fsm_check_size WHERE num <= 5;
-VACUUM fsm_check_size;
--- Insert small record in block 2 to set the cached smgr targetBlock
-INSERT INTO fsm_check_size VALUES(99, 'b');
+-- Fill most of the last block
+ALTER TABLE fsm_check_size SET (fillfactor=100);
+INSERT INTO fsm_check_size SELECT i, rpad('', 1024, 'a')
+FROM generate_series(11,15) i;
+-- Make sure records can go into any block but the last one
+ALTER TABLE fsm_check_size SET (fillfactor=30);
-- Insert large record and make sure it goes in block 0 rather than
-- causing the relation to extend
INSERT INTO fsm_check_size VALUES (101, rpad('', 1024, 'a'));
@@ -32,38 +32,16 @@ pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
-- Extend table with enough blocks to exceed the FSM threshold
-- FSM is created and extended to 3 blocks
INSERT INTO fsm_check_size SELECT i, 'c' FROM generate_series(200,1200) i;
-VACUUM fsm_check_size;
-SELECT pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
- fsm_size
-----------
- 24576
-(1 row)
-
--- Truncate heap to 1 block
--- No change in FSM
-DELETE FROM fsm_check_size WHERE num > 7;
-VACUUM fsm_check_size;
SELECT pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
fsm_size
----------
24576
(1 row)
--- Truncate heap to 0 blocks
--- FSM now truncated to 2 blocks
-DELETE FROM fsm_check_size;
-VACUUM fsm_check_size;
-SELECT pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
- fsm_size
-----------
- 16384
-(1 row)
-
-- Add long random string to extend TOAST table to 1 block
INSERT INTO fsm_check_size
VALUES(0, (SELECT string_agg(md5(chr(i)), '')
FROM generate_series(1,100) i));
-VACUUM fsm_check_size;
SELECT pg_relation_size(reltoastrelid, 'main') AS toast_size,
pg_relation_size(reltoastrelid, 'fsm') AS toast_fsm_size
FROM pg_class WHERE relname = 'fsm_check_size';
diff --git a/src/test/regress/sql/fsm.sql b/src/test/regress/sql/fsm.sql
index 07f505591a..542bd6e203 100644
--- a/src/test/regress/sql/fsm.sql
+++ b/src/test/regress/sql/fsm.sql
@@ -4,20 +4,21 @@
CREATE TABLE fsm_check_size (num int, str text);
--- Fill 3 blocks with as many large records as will fit
--- No FSM
+-- Fill 3 blocks with one record each
+ALTER TABLE fsm_check_size SET (fillfactor=15);
INSERT INTO fsm_check_size SELECT i, rpad('', 1024, 'a')
-FROM generate_series(1,7*3) i;
-VACUUM fsm_check_size;
+FROM generate_series(1,3) i;
+
SELECT pg_relation_size('fsm_check_size', 'main') AS heap_size,
pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
--- Clear some space on block 0
-DELETE FROM fsm_check_size WHERE num <= 5;
-VACUUM fsm_check_size;
+-- Fill most of the last block
+ALTER TABLE fsm_check_size SET (fillfactor=100);
+INSERT INTO fsm_check_size SELECT i, rpad('', 1024, 'a')
+FROM generate_series(11,15) i;
--- Insert small record in block 2 to set the cached smgr targetBlock
-INSERT INTO fsm_check_size VALUES(99, 'b');
+-- Make sure records can go into any block but the last one
+ALTER TABLE fsm_check_size SET (fillfactor=30);
-- Insert large record and make sure it goes in block 0 rather than
-- causing the relation to extend
@@ -28,26 +29,12 @@ pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
-- Extend table with enough blocks to exceed the FSM threshold
-- FSM is created and extended to 3 blocks
INSERT INTO fsm_check_size SELECT i, 'c' FROM generate_series(200,1200) i;
-VACUUM fsm_check_size;
-SELECT pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
-
--- Truncate heap to 1 block
--- No change in FSM
-DELETE FROM fsm_check_size WHERE num > 7;
-VACUUM fsm_check_size;
-SELECT pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
-
--- Truncate heap to 0 blocks
--- FSM now truncated to 2 blocks
-DELETE FROM fsm_check_size;
-VACUUM fsm_check_size;
SELECT pg_relation_size('fsm_check_size', 'fsm') AS fsm_size;
-- Add long random string to extend TOAST table to 1 block
INSERT INTO fsm_check_size
VALUES(0, (SELECT string_agg(md5(chr(i)), '')
FROM generate_series(1,100) i));
-VACUUM fsm_check_size;
SELECT pg_relation_size(reltoastrelid, 'main') AS toast_size,
pg_relation_size(reltoastrelid, 'fsm') AS toast_fsm_size
FROM pg_class WHERE relname = 'fsm_check_size';
