On Wed, Jan 30, 2019 at 2:11 PM Amit Kapila <amit.kapil...@gmail.com> wrote: > This is much better than the earlier version of test and there is no > dependency on the vacuum. However, I feel still there is some > dependency on how the rows will fit in a page and we have seen some > related failures due to alignment stuff. By looking at the test, I > can't envision any such problem, but how about if we just write some > simple tests where we can check that the FSM won't be created for very > small number of records say one or two and then when we increase the > records FSM gets created, here if we want, we can even use vacuum to > ensure FSM gets created. Once we are sure that the main patch passes > all the buildfarm tests, we can extend the test to something advanced > as you are proposing now. I think that will reduce the chances of > failure, what do you think?
That's probably a good idea to limit risk. I just very basic tests now, and vacuum before every relation size check to make sure any FSM extension (whether desired or not) is invoked. Also, in my last patch I forgot to implement explicit checks of the block number instead of assuming how many rows will fit on a page. I've used a plpgsql code block to do this. -- 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..eb2c3842e2 100644 --- a/src/test/regress/expected/fsm.out +++ b/src/test/regress/expected/fsm.out @@ -2,46 +2,30 @@ -- 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 -INSERT INTO fsm_check_size SELECT i, rpad('', 1024, 'a') -FROM generate_series(1,7*3) i; +-- With one block, there should be no FSM +INSERT INTO fsm_check_size VALUES(1, 'a'); VACUUM fsm_check_size; 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 -----------+---------- - 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'); --- 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')); -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 ------------+---------- - 24576 | 0 + 8192 | 0 (1 row) -- 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; +DO $$ +DECLARE curtid tid; +num int; +BEGIN +num = 11; + LOOP + INSERT INTO fsm_check_size VALUES (num, 'b') RETURNING ctid INTO curtid; + EXIT WHEN curtid >= tid '(4, 0)'; + num = num + 1; + END LOOP; +END; +$$; VACUUM fsm_check_size; SELECT pg_relation_size('fsm_check_size', 'fsm') AS fsm_size; fsm_size @@ -49,16 +33,6 @@ SELECT pg_relation_size('fsm_check_size', 'fsm') AS 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)), '') diff --git a/src/test/regress/sql/fsm.sql b/src/test/regress/sql/fsm.sql index 07f505591a..b1debedea1 100644 --- a/src/test/regress/sql/fsm.sql +++ b/src/test/regress/sql/fsm.sql @@ -4,42 +4,28 @@ CREATE TABLE fsm_check_size (num int, str text); --- Fill 3 blocks with as many large records as will fit --- No FSM -INSERT INTO fsm_check_size SELECT i, rpad('', 1024, 'a') -FROM generate_series(1,7*3) i; -VACUUM fsm_check_size; -SELECT pg_relation_size('fsm_check_size', 'main') AS heap_size, -pg_relation_size('fsm_check_size', 'fsm') AS fsm_size; +-- With one block, there should be no FSM +INSERT INTO fsm_check_size VALUES(1, 'a'); --- 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'); - --- 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')); SELECT pg_relation_size('fsm_check_size', 'main') AS heap_size, 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; +DO $$ +DECLARE curtid tid; +num int; +BEGIN +num = 11; + LOOP + INSERT INTO fsm_check_size VALUES (num, 'b') RETURNING ctid INTO curtid; + EXIT WHEN curtid >= tid '(4, 0)'; + num = num + 1; + END LOOP; +END; +$$; --- 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; @@ -47,6 +33,7 @@ SELECT pg_relation_size('fsm_check_size', 'fsm') AS fsm_size; 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