On Thu, Jan 31, 2019 at 1:52 PM John Naylor <john.nay...@2ndquadrant.com> wrote:
>
> On Thu, Jan 31, 2019 at 1:43 PM Amit Kapila <amit.kapil...@gmail.com> wrote:
> > > I have an idea -- instead of adding a bunch of records and hoping that
> > > the relation size and free space is consistent across platforms, how
> > > about we revert to the original test input, and add a BRIN index? That
> > > should have a FSM even with one record.
> > >
> >
> > Why would BRIN index allow having FSM for heap relation?
>
> Oops, I forgot this file is for testing heaps only. That said, we
> could possibly put most of the FSM tests such as
>
> SELECT * FROM fsm_page_contents(get_raw_page('test_rel_forks', 'fsm', 0));
>
> into brin.sql since we know a non-empty BRIN index will have a FSM.
As in the attached. Applies on top of v20. First to revert to HEAD,
second to move FSM tests to brin.sql. This is a much less invasive and
more readable patch, in addition to being hopefully more portable.
> And in page.sql we could just have a test that the table has no FSM.
This is not possible, since we don't know the relfilenode for the
error text, and it's not important. Better to have everything in
brin.sql.
--
John Naylor https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
commit 18b444cdfc2c50502389b84f4504ec9c7f2ab831
Author: John Naylor <jcnay...@gmail.com>
Date: Thu Jan 31 14:20:14 2019 +0100
revert contrib so I can redo
diff --git a/contrib/pageinspect/expected/page.out b/contrib/pageinspect/expected/page.out
index 89b73ca991..3fcd9fbe6d 100644
--- a/contrib/pageinspect/expected/page.out
+++ b/contrib/pageinspect/expected/page.out
@@ -1,69 +1,48 @@
CREATE EXTENSION pageinspect;
-CREATE TABLE test_rel_forks (a int);
--- Make sure there are enough blocks in the heap for the FSM to be created.
-INSERT INTO test_rel_forks SELECT i from generate_series(1,2000) i;
--- set up FSM and VM
-VACUUM test_rel_forks;
+CREATE TABLE test1 (a int, b int);
+INSERT INTO test1 VALUES (16777217, 131584);
+VACUUM test1; -- set up FSM
-- The page contents can vary, so just test that it can be read
-- successfully, but don't keep the output.
-SELECT octet_length(get_raw_page('test_rel_forks', 'main', 0)) AS main_0;
+SELECT octet_length(get_raw_page('test1', 'main', 0)) AS main_0;
main_0
--------
8192
(1 row)
-SELECT octet_length(get_raw_page('test_rel_forks', 'main', 100)) AS main_100;
-ERROR: block number 100 is out of range for relation "test_rel_forks"
-SELECT octet_length(get_raw_page('test_rel_forks', 'fsm', 0)) AS fsm_0;
+SELECT octet_length(get_raw_page('test1', 'main', 1)) AS main_1;
+ERROR: block number 1 is out of range for relation "test1"
+SELECT octet_length(get_raw_page('test1', 'fsm', 0)) AS fsm_0;
fsm_0
-------
8192
(1 row)
-SELECT octet_length(get_raw_page('test_rel_forks', 'fsm', 20)) AS fsm_20;
-ERROR: block number 20 is out of range for relation "test_rel_forks"
-SELECT octet_length(get_raw_page('test_rel_forks', 'vm', 0)) AS vm_0;
+SELECT octet_length(get_raw_page('test1', 'fsm', 1)) AS fsm_1;
+ fsm_1
+-------
+ 8192
+(1 row)
+
+SELECT octet_length(get_raw_page('test1', 'vm', 0)) AS vm_0;
vm_0
------
8192
(1 row)
-SELECT octet_length(get_raw_page('test_rel_forks', 'vm', 1)) AS vm_1;
-ERROR: block number 1 is out of range for relation "test_rel_forks"
+SELECT octet_length(get_raw_page('test1', 'vm', 1)) AS vm_1;
+ERROR: block number 1 is out of range for relation "test1"
SELECT octet_length(get_raw_page('xxx', 'main', 0));
ERROR: relation "xxx" does not exist
-SELECT octet_length(get_raw_page('test_rel_forks', 'xxx', 0));
+SELECT octet_length(get_raw_page('test1', 'xxx', 0));
ERROR: invalid fork name
HINT: Valid fork names are "main", "fsm", "vm", and "init".
-SELECT * FROM fsm_page_contents(get_raw_page('test_rel_forks', 'fsm', 0));
- fsm_page_contents
--------------------
- 0: 39 +
- 1: 39 +
- 3: 39 +
- 7: 39 +
- 15: 39 +
- 31: 39 +
- 63: 39 +
- 127: 39 +
- 255: 39 +
- 511: 39 +
- 1023: 39 +
- 2047: 39 +
- 4095: 39 +
- fp_next_slot: 0 +
-
-(1 row)
-
-SELECT get_raw_page('test_rel_forks', 0) = get_raw_page('test_rel_forks', 'main', 0);
+SELECT get_raw_page('test1', 0) = get_raw_page('test1', 'main', 0);
?column?
----------
t
(1 row)
-DROP TABLE test_rel_forks;
-CREATE TABLE test1 (a int, b int);
-INSERT INTO test1 VALUES (16777217, 131584);
SELECT pagesize, version FROM page_header(get_raw_page('test1', 0));
pagesize | version
----------+---------
@@ -83,6 +62,26 @@ SELECT tuple_data_split('test1'::regclass, t_data, t_infomask, t_infomask2, t_bi
{"\\x01000001","\\x00020200"}
(1 row)
+SELECT * FROM fsm_page_contents(get_raw_page('test1', 'fsm', 0));
+ fsm_page_contents
+-------------------
+ 0: 254 +
+ 1: 254 +
+ 3: 254 +
+ 7: 254 +
+ 15: 254 +
+ 31: 254 +
+ 63: 254 +
+ 127: 254 +
+ 255: 254 +
+ 511: 254 +
+ 1023: 254 +
+ 2047: 254 +
+ 4095: 254 +
+ fp_next_slot: 0 +
+
+(1 row)
+
DROP TABLE test1;
-- check that using any of these functions with a partitioned table or index
-- would fail
diff --git a/contrib/pageinspect/sql/page.sql b/contrib/pageinspect/sql/page.sql
index 67166ef54c..8ac9991837 100644
--- a/contrib/pageinspect/sql/page.sql
+++ b/contrib/pageinspect/sql/page.sql
@@ -1,35 +1,26 @@
CREATE EXTENSION pageinspect;
-CREATE TABLE test_rel_forks (a int);
--- Make sure there are enough blocks in the heap for the FSM to be created.
-INSERT INTO test_rel_forks SELECT i from generate_series(1,2000) i;
+CREATE TABLE test1 (a int, b int);
+INSERT INTO test1 VALUES (16777217, 131584);
--- set up FSM and VM
-VACUUM test_rel_forks;
+VACUUM test1; -- set up FSM
-- The page contents can vary, so just test that it can be read
-- successfully, but don't keep the output.
-SELECT octet_length(get_raw_page('test_rel_forks', 'main', 0)) AS main_0;
-SELECT octet_length(get_raw_page('test_rel_forks', 'main', 100)) AS main_100;
+SELECT octet_length(get_raw_page('test1', 'main', 0)) AS main_0;
+SELECT octet_length(get_raw_page('test1', 'main', 1)) AS main_1;
-SELECT octet_length(get_raw_page('test_rel_forks', 'fsm', 0)) AS fsm_0;
-SELECT octet_length(get_raw_page('test_rel_forks', 'fsm', 20)) AS fsm_20;
+SELECT octet_length(get_raw_page('test1', 'fsm', 0)) AS fsm_0;
+SELECT octet_length(get_raw_page('test1', 'fsm', 1)) AS fsm_1;
-SELECT octet_length(get_raw_page('test_rel_forks', 'vm', 0)) AS vm_0;
-SELECT octet_length(get_raw_page('test_rel_forks', 'vm', 1)) AS vm_1;
+SELECT octet_length(get_raw_page('test1', 'vm', 0)) AS vm_0;
+SELECT octet_length(get_raw_page('test1', 'vm', 1)) AS vm_1;
SELECT octet_length(get_raw_page('xxx', 'main', 0));
-SELECT octet_length(get_raw_page('test_rel_forks', 'xxx', 0));
-
-SELECT * FROM fsm_page_contents(get_raw_page('test_rel_forks', 'fsm', 0));
-
-SELECT get_raw_page('test_rel_forks', 0) = get_raw_page('test_rel_forks', 'main', 0);
+SELECT octet_length(get_raw_page('test1', 'xxx', 0));
-DROP TABLE test_rel_forks;
-
-CREATE TABLE test1 (a int, b int);
-INSERT INTO test1 VALUES (16777217, 131584);
+SELECT get_raw_page('test1', 0) = get_raw_page('test1', 'main', 0);
SELECT pagesize, version FROM page_header(get_raw_page('test1', 0));
@@ -38,6 +29,8 @@ SELECT page_checksum(get_raw_page('test1', 0), 0) IS NOT NULL AS silly_checksum_
SELECT tuple_data_split('test1'::regclass, t_data, t_infomask, t_infomask2, t_bits)
FROM heap_page_items(get_raw_page('test1', 0));
+SELECT * FROM fsm_page_contents(get_raw_page('test1', 'fsm', 0));
+
DROP TABLE test1;
-- check that using any of these functions with a partitioned table or index
commit 6c400b26128216953db962cb7698691dd04ee3bf
Author: John Naylor <jcnay...@gmail.com>
Date: Thu Jan 31 14:47:02 2019 +0100
put FSM tests in brin.sql
diff --git a/contrib/pageinspect/expected/brin.out b/contrib/pageinspect/expected/brin.out
index 71eb190380..ef1304bdef 100644
--- a/contrib/pageinspect/expected/brin.out
+++ b/contrib/pageinspect/expected/brin.out
@@ -1,6 +1,7 @@
CREATE TABLE test1 (a int, b text);
INSERT INTO test1 VALUES (1, 'one');
CREATE INDEX test1_a_idx ON test1 USING brin (a);
+VACUUM test1; -- set up FSM
SELECT brin_page_type(get_raw_page('test1_a_idx', 0));
brin_page_type
----------------
@@ -48,4 +49,39 @@ SELECT * FROM brin_page_items(get_raw_page('test1_a_idx', 2), 'test1_a_idx')
1 | 0 | 1 | f | f | f | {1 .. 1}
(1 row)
+-- FSM tests
+-- These are here rather than in page.sql since BRIN indexes don't have a
+-- minimum threshold size to have a FSM
+SELECT octet_length(get_raw_page('test1_a_idx', 'fsm', 0)) AS fsm_0;
+ fsm_0
+-------
+ 8192
+(1 row)
+
+SELECT octet_length(get_raw_page('test1_a_idx', 'fsm', 1)) AS fsm_1;
+ fsm_1
+-------
+ 8192
+(1 row)
+
+SELECT * FROM fsm_page_contents(get_raw_page('test1_a_idx', 'fsm', 0));
+ fsm_page_contents
+-------------------
+ 0: 254 +
+ 1: 254 +
+ 3: 254 +
+ 7: 254 +
+ 15: 254 +
+ 31: 254 +
+ 63: 254 +
+ 127: 254 +
+ 255: 254 +
+ 511: 254 +
+ 1023: 254 +
+ 2047: 254 +
+ 4095: 254 +
+ fp_next_slot: 0 +
+
+(1 row)
+
DROP TABLE test1;
diff --git a/contrib/pageinspect/expected/page.out b/contrib/pageinspect/expected/page.out
index 3fcd9fbe6d..afa5f9fce2 100644
--- a/contrib/pageinspect/expected/page.out
+++ b/contrib/pageinspect/expected/page.out
@@ -1,7 +1,9 @@
CREATE EXTENSION pageinspect;
CREATE TABLE test1 (a int, b int);
INSERT INTO test1 VALUES (16777217, 131584);
-VACUUM test1; -- set up FSM
+-- set up VM
+-- Note: This heap is too small to have a FSM, so FSM tests are in brin.sql
+VACUUM test1;
-- The page contents can vary, so just test that it can be read
-- successfully, but don't keep the output.
SELECT octet_length(get_raw_page('test1', 'main', 0)) AS main_0;
@@ -12,18 +14,6 @@ SELECT octet_length(get_raw_page('test1', 'main', 0)) AS main_0;
SELECT octet_length(get_raw_page('test1', 'main', 1)) AS main_1;
ERROR: block number 1 is out of range for relation "test1"
-SELECT octet_length(get_raw_page('test1', 'fsm', 0)) AS fsm_0;
- fsm_0
--------
- 8192
-(1 row)
-
-SELECT octet_length(get_raw_page('test1', 'fsm', 1)) AS fsm_1;
- fsm_1
--------
- 8192
-(1 row)
-
SELECT octet_length(get_raw_page('test1', 'vm', 0)) AS vm_0;
vm_0
------
@@ -62,26 +52,6 @@ SELECT tuple_data_split('test1'::regclass, t_data, t_infomask, t_infomask2, t_bi
{"\\x01000001","\\x00020200"}
(1 row)
-SELECT * FROM fsm_page_contents(get_raw_page('test1', 'fsm', 0));
- fsm_page_contents
--------------------
- 0: 254 +
- 1: 254 +
- 3: 254 +
- 7: 254 +
- 15: 254 +
- 31: 254 +
- 63: 254 +
- 127: 254 +
- 255: 254 +
- 511: 254 +
- 1023: 254 +
- 2047: 254 +
- 4095: 254 +
- fp_next_slot: 0 +
-
-(1 row)
-
DROP TABLE test1;
-- check that using any of these functions with a partitioned table or index
-- would fail
diff --git a/contrib/pageinspect/sql/brin.sql b/contrib/pageinspect/sql/brin.sql
index 735bc3b673..a3bd93582d 100644
--- a/contrib/pageinspect/sql/brin.sql
+++ b/contrib/pageinspect/sql/brin.sql
@@ -1,6 +1,7 @@
CREATE TABLE test1 (a int, b text);
INSERT INTO test1 VALUES (1, 'one');
CREATE INDEX test1_a_idx ON test1 USING brin (a);
+VACUUM test1; -- set up FSM
SELECT brin_page_type(get_raw_page('test1_a_idx', 0));
SELECT brin_page_type(get_raw_page('test1_a_idx', 1));
@@ -15,4 +16,11 @@ SELECT * FROM brin_revmap_data(get_raw_page('test1_a_idx', 1)) LIMIT 5;
SELECT * FROM brin_page_items(get_raw_page('test1_a_idx', 2), 'test1_a_idx')
ORDER BY blknum, attnum LIMIT 5;
+-- FSM tests
+-- These are here rather than in page.sql since BRIN indexes don't have a
+-- minimum threshold size to have a FSM
+SELECT octet_length(get_raw_page('test1_a_idx', 'fsm', 0)) AS fsm_0;
+SELECT octet_length(get_raw_page('test1_a_idx', 'fsm', 1)) AS fsm_1;
+SELECT * FROM fsm_page_contents(get_raw_page('test1_a_idx', 'fsm', 0));
+
DROP TABLE test1;
diff --git a/contrib/pageinspect/sql/page.sql b/contrib/pageinspect/sql/page.sql
index 8ac9991837..4b8c1190ab 100644
--- a/contrib/pageinspect/sql/page.sql
+++ b/contrib/pageinspect/sql/page.sql
@@ -3,7 +3,9 @@ CREATE EXTENSION pageinspect;
CREATE TABLE test1 (a int, b int);
INSERT INTO test1 VALUES (16777217, 131584);
-VACUUM test1; -- set up FSM
+-- set up VM
+-- Note: This heap is too small to have a FSM, so FSM tests are in brin.sql
+VACUUM test1;
-- The page contents can vary, so just test that it can be read
-- successfully, but don't keep the output.
@@ -11,9 +13,6 @@ VACUUM test1; -- set up FSM
SELECT octet_length(get_raw_page('test1', 'main', 0)) AS main_0;
SELECT octet_length(get_raw_page('test1', 'main', 1)) AS main_1;
-SELECT octet_length(get_raw_page('test1', 'fsm', 0)) AS fsm_0;
-SELECT octet_length(get_raw_page('test1', 'fsm', 1)) AS fsm_1;
-
SELECT octet_length(get_raw_page('test1', 'vm', 0)) AS vm_0;
SELECT octet_length(get_raw_page('test1', 'vm', 1)) AS vm_1;
@@ -29,8 +28,6 @@ SELECT page_checksum(get_raw_page('test1', 0), 0) IS NOT NULL AS silly_checksum_
SELECT tuple_data_split('test1'::regclass, t_data, t_infomask, t_infomask2, t_bits)
FROM heap_page_items(get_raw_page('test1', 0));
-SELECT * FROM fsm_page_contents(get_raw_page('test1', 'fsm', 0));
-
DROP TABLE test1;
-- check that using any of these functions with a partitioned table or index
