[PATCH] Tests for reloptions
Started by Nikolay Shaplovover 8 years ago9 messages
This thread continues discussion at /messages/by-id/20170903094543.kkqdbdjuxwa5z6ji@alvherre.pgsql
(Shortly: I refactored reloptions code, Alvaro offered to commit tests before
the full patch)
I see that this patch adds a few tests for reloptions, for instance in
bloom. I think we should split this in at least two commits, one that
adds those tests before the functionality change, so that they can be
committed in advance, verify that the buildfarm likes it with the
current code, and verify the coverage.
This sounds as a really good idea.
Though I have several questions. This tests also covers some functionality
that were introduced only in my patch:
1. Forbid SET and RESET options where they should not be changed
2. Forbid creating tables with toasts options when no toast table is created
3. Split StdRdOptions into HeapOptions and ToastOptions and forbid uising Heap
specific options for toast.
In the patch I've attached I've commented out this functionality. But I am not
quite sure that it is good idea to commit it this way in master.
May be it would be good to make 1-3 as a separate patches and bring it's tests
with, as a separate commit. But...
2nd can be easily ported to master. It does not depend much on my reloptions
patch as far as I remember.
It would be insane to port 1st feature to master. It highly integrated with
reloptions mechanism, It would require complete reimplementation of this
feature using old reloptions tools. I totally do not want to do it
The 3rd functionality came from philosophy one relation-type -- one options
catalog, that was implemented in my reloptions patch. It is not really needed
in master without the full patch. With some efforts I think it can be made as a
separate patch, thought I would also try to avoid it if possible.
So the questions still is: should we commit not existent functionality tests
commented, uncomented but with no proper error response in expected output, or
just remove these tests from this patch?
--
Do code for fun. Can do it for money (Perl & C/C++ ~10h/week)
Attachments:
relopt_tests_v1a.difftext/x-patch; charset=UTF-8; name=relopt_tests_v1a.diffDownload
diff --git a/contrib/bloom/expected/bloom.out b/contrib/bloom/expected/bloom.out
index cbc50f7..37fc7f9 100644
--- a/contrib/bloom/expected/bloom.out
+++ b/contrib/bloom/expected/bloom.out
@@ -210,3 +210,33 @@ ORDER BY 1;
text_ops | t
(2 rows)
+-- reloptions test
+DROP INDEX bloomidx;
+CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (length=7, col1 = 4);
+SELECT reloptions FROM pg_class WHERE oid = 'bloomidx'::regclass;
+ reloptions
+-------------------
+ {length=7,col1=4}
+(1 row)
+
+-- check for min and max values
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0);
+ERROR: value 0 out of bounds for option "length"
+DETAIL: Valid values are between "1" and "4096".
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=4097);
+ERROR: value 4097 out of bounds for option "length"
+DETAIL: Valid values are between "1" and "4096".
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=0);
+ERROR: value 0 out of bounds for option "col1"
+DETAIL: Valid values are between "1" and "4095".
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=4096);
+ERROR: value 4096 out of bounds for option "col1"
+DETAIL: Valid values are between "1" and "4095".
+---- check post_validate for colN<lengh
+--CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=10,col1=11);
+---- check that ALTERing of reloptions is forbidden
+--
+--ALTER INDEX bloomidx SET (length=4);
+--ALTER INDEX bloomidx SET (col1=4);
+--ALTER INDEX bloomidx RESET (length);
+--ALTER INDEX bloomidx RESET (col1);
diff --git a/contrib/bloom/sql/bloom.sql b/contrib/bloom/sql/bloom.sql
index 2227460..3d03e8f 100644
--- a/contrib/bloom/sql/bloom.sql
+++ b/contrib/bloom/sql/bloom.sql
@@ -81,3 +81,23 @@ SELECT opcname, amvalidate(opc.oid)
FROM pg_opclass opc JOIN pg_am am ON am.oid = opcmethod
WHERE amname = 'bloom'
ORDER BY 1;
+
+-- reloptions test
+
+DROP INDEX bloomidx;
+CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (length=7, col1 = 4);
+SELECT reloptions FROM pg_class WHERE oid = 'bloomidx'::regclass;
+-- check for min and max values
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0);
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=4097);
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=0);
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=4096);
+---- check post_validate for colN<lengh
+--CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=10,col1=11);
+
+---- check that ALTERing of reloptions is forbidden
+--
+--ALTER INDEX bloomidx SET (length=4);
+--ALTER INDEX bloomidx SET (col1=4);
+--ALTER INDEX bloomidx RESET (length);
+--ALTER INDEX bloomidx RESET (col1);
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index ed03cb9..e03d72d 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3551,3 +3551,10 @@ create table parted_validate_test_1 partition of parted_validate_test for values
alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
alter table parted_validate_test validate constraint parted_validate_test_chka;
drop table parted_validate_test;
+-- test alter column options
+CREATE TABLE tmp(i integer);
+INSERT INTO tmp VALUES (1);
+ALTER TABLE tmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
+ALTER TABLE tmp ALTER COLUMN i RESET (n_distinct_inherited);
+ANALYZE tmp;
+DROP TABLE tmp;
diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out
index ca80f00..6568b6b 100644
--- a/src/test/regress/expected/brin.out
+++ b/src/test/regress/expected/brin.out
@@ -507,3 +507,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE b = 1;
Filter: (b = 1)
(2 rows)
+---- Check that changing reloptions for brin index is not allowed
+--ALTER INDEX brinidx SET (pages_per_range = 10);
+--ALTER INDEX brinidx RESET (pages_per_range);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 064adb4..16a3b8b 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2337,7 +2337,7 @@ Options: fastupdate=on, gin_pending_list_limit=128
CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
-CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
+CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
DROP TABLE unlogged_hash_table;
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index 91f9998..5b13db8 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -12,6 +12,20 @@ insert into gist_point_tbl (id, p)
select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
-- To test vacuum, delete some entries from all over the index.
delete from gist_point_tbl where id % 2 = 1;
+-- Test buffering and fillfactor reloption takes valid values
+create index gist_pointidx2 on gist_point_tbl using gist(p) with (buffering = on, fillfactor=50);
+create index gist_pointidx3 on gist_point_tbl using gist(p) with (buffering = off);
+create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = auto);
+--Test buffering and fillfactor for refising invalid values
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (buffering = invalid_value);
+ERROR: invalid value for "buffering" option
+DETAIL: Valid values are "on", "off", and "auto".
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=9);
+ERROR: value 9 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=101);
+ERROR: value 101 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
-- And also delete some concentration of values. (GiST doesn't currently
-- attempt to delete pages even when they become empty, but if it did, this
-- would exercise it)
diff --git a/src/test/regress/expected/hash_index.out b/src/test/regress/expected/hash_index.out
index 0bbaa2a..95b4ffd 100644
--- a/src/test/regress/expected/hash_index.out
+++ b/src/test/regress/expected/hash_index.out
@@ -229,3 +229,11 @@ CREATE TABLE hash_heap_float4 (x float4, y int);
INSERT INTO hash_heap_float4 VALUES (1.1,1);
CREATE INDEX hash_idx ON hash_heap_float4 USING hash (x);
DROP TABLE hash_heap_float4 CASCADE;
+-- Check reloptions min max values and that it is not allowed to ALTER
+CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=9);
+ERROR: value 9 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=101);
+ERROR: value 101 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+-- ALTER INDEX hash_f8_index SET (fillfactor=99);
diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out
new file mode 100644
index 0000000..b96ad05
--- /dev/null
+++ b/src/test/regress/expected/reloptions.out
@@ -0,0 +1,188 @@
+-- Simple create
+CREATE TABLE reloptions_test(i INT) WITH (fillfactor=30,autovacuum_enabled = false, autovacuum_analyze_scale_factor = 0.2);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+------------------------------------------------------------------------------
+ {fillfactor=30,autovacuum_enabled=false,autovacuum_analyze_scale_factor=0.2}
+(1 row)
+
+-- Test сase insensitive
+CREATE TABLE reloptions_test3(i INT) WITH (FiLlFaCtoR=40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test3'::regclass;
+ reloptions
+-----------------
+ {fillfactor=40}
+(1 row)
+
+-- Fail on min/max values check
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2);
+ERROR: value 2 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110);
+ERROR: value 110 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0);
+ERROR: value -10.0 out of bounds for option "autovacuum_analyze_scale_factor"
+DETAIL: Valid values are between "0.000000" and "100.000000".
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0);
+ERROR: value 110.0 out of bounds for option "autovacuum_analyze_scale_factor"
+DETAIL: Valid values are between "0.000000" and "100.000000".
+-- Fail when option and namespase do not exist
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2);
+ERROR: unrecognized parameter "not_existing_option"
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2);
+ERROR: unrecognized parameter namespace "not_existing_namespace"
+-- Fail while setting unproper value
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30.5);
+ERROR: invalid value for integer option "fillfactor": 30.5
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string');
+ERROR: invalid value for integer option "fillfactor": string
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true);
+ERROR: invalid value for integer option "fillfactor": true
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12);
+ERROR: invalid value for boolean option "autovacuum_enabled": 12
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5);
+ERROR: invalid value for boolean option "autovacuum_enabled": 30.5
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string');
+ERROR: invalid value for boolean option "autovacuum_enabled": string
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string');
+ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": string
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true);
+ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": true
+-- Specifing name only should fail as if there was =ture after it
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
+ERROR: invalid value for integer option "fillfactor": true
+-- Simple ALTER TABLE
+ALTER TABLE reloptions_test SET (fillfactor=31, autovacuum_analyze_scale_factor = 0.3);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+------------------------------------------------------------------------------
+ {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3}
+(1 row)
+
+-- Check that we cat set boolean option to true just by mentioning it
+ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+-----------------------------------------------------------------------------
+ {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true,fillfactor=32}
+(1 row)
+
+-- Check that RESET works well
+ALTER TABLE reloptions_test RESET (fillfactor);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+---------------------------------------------------------------
+ {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true}
+(1 row)
+
+-- Check that RESETting all values make NULL reloptions record in pg_class
+ALTER TABLE reloptions_test RESET (autovacuum_enabled, autovacuum_analyze_scale_factor);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND reloptions IS NULL;
+ reloptions
+------------
+
+(1 row)
+
+-- Check RESET fails on att=value
+ALTER TABLE reloptions_test RESET (fillfactor=12);
+ERROR: RESET must not include values for parameters
+-- Check oids options is ignored
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+-----------------
+ {fillfactor=20}
+(1 row)
+
+-- Now testing toast.* options
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test (s VARCHAR) WITH (toast.autovacuum_vacuum_cost_delay = 23 );
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+ reloptions
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=23}
+(1 row)
+
+ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+ reloptions
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=24}
+(1 row)
+
+ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+ reloptions
+------------
+
+(1 row)
+
+-- Fail on unexisting options in toast namespace
+CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42 );
+ERROR: unrecognized parameter "not_existing_option"
+-- -- Fail on setting reloption to a table that does not have a TOAST relation
+-- CREATE TABLE reloptions_test2 (i int) WITH (toast.autovacuum_vacuum_cost_delay = 23 );
+-- DROP TABLE reloptions_test;
+-- CREATE TABLE reloptions_test(i INT);
+-- ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 23);
+-- ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
+-- autovacuum_analyze_scale_factor and autovacuum_analyze_threshold should be
+-- accepted by heap but rejected by toast (special case)
+-- DROP TABLE reloptions_test;
+-- CREATE TABLE reloptions_test (s VARCHAR) WITH (autovacuum_analyze_scale_factor=1, autovacuum_analyze_threshold=1);
+-- CREATE TABLE reloptions_test2 (s VARCHAR) WITH (toast.autovacuum_analyze_scale_factor=1);
+-- CREATE TABLE reloptions_test2 (s VARCHAR) WITH (toast.autovacuum_analyze_threshold=1);
+-- And now mixed toast + heap
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test (s VARCHAR) WITH (toast.autovacuum_vacuum_cost_delay = 23, autovacuum_vacuum_cost_delay = 24, fillfactor = 40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+-------------------------------------------------
+ {autovacuum_vacuum_cost_delay=24,fillfactor=40}
+(1 row)
+
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+ reloptions
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=23}
+(1 row)
+
+-- Same FOR CREATE and ALTER INDEX for btree indexes
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+ reloptions
+-----------------
+ {fillfactor=30}
+(1 row)
+
+-- Fail when option and namespase do not exist
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (not_existing_option=2);
+ERROR: unrecognized parameter "not_existing_option"
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (not_existing_option.fillfactor=2);
+ERROR: unrecognized parameter namespace "not_existing_option"
+-- Check ranges
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1);
+ERROR: value 1 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130);
+ERROR: value 130 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+-- Check alter
+ALTER INDEX reloptions_test_idx SET (fillfactor=40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+ reloptions
+-----------------
+ {fillfactor=40}
+(1 row)
+
+-- Check alter on empty relop list
+CREATE INDEX reloptions_test_idx3 ON reloptions_test (s);
+ALTER INDEX reloptions_test_idx3 SET (fillfactor=40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass;
+ reloptions
+-----------------
+ {fillfactor=40}
+(1 row)
+
diff --git a/src/test/regress/expected/spgist.out b/src/test/regress/expected/spgist.out
index 0691e91..bb58fb7 100644
--- a/src/test/regress/expected/spgist.out
+++ b/src/test/regress/expected/spgist.out
@@ -4,7 +4,7 @@
-- There are other tests to test different SP-GiST opclasses. This is for
-- testing SP-GiST code itself.
create table spgist_point_tbl(id int4, p point);
-create index spgist_point_idx on spgist_point_tbl using spgist(p);
+create index spgist_point_idx on spgist_point_tbl using spgist(p) with (fillfactor = 75);
-- Test vacuum-root operation. It gets invoked when the root is also a leaf,
-- i.e. the index is very small.
insert into spgist_point_tbl (id, p)
@@ -37,3 +37,12 @@ select g, 'baaaaaaaaaaaaaar' || g from generate_series(1, 1000) g;
-- tuple to be moved to another page.
insert into spgist_text_tbl (id, t)
select -g, 'f' || repeat('o', 100-g) || 'surprise' from generate_series(1, 100) g;
+-- Check reloptions min, max values
+create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 9);
+ERROR: value 9 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 101);
+ERROR: value 101 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+-- ALTER reloption should work
+alter index spgist_point_idx set (fillfactor = 76);
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 03a62bd..7f7934b 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -12,10 +12,10 @@ DROP TABLESPACE regress_tblspacewith;
CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
-- try setting and resetting some properties for the new tablespace
-ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0);
+ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
-ALTER TABLESPACE regress_tblspace RESET (random_page_cost, seq_page_cost); -- ok
+ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
-- create a schema we can use
CREATE SCHEMA testschema;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index aaedf5f..2443511 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -14,12 +14,12 @@ DROP TABLESPACE regress_tblspacewith;
-- create a tablespace we can use
CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
-- try setting and resetting some properties for the new tablespace
-ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0);
+ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
ERROR: unrecognized parameter "some_nonexistent_parameter"
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ERROR: RESET must not include values for parameters
-ALTER TABLESPACE regress_tblspace RESET (random_page_cost, seq_page_cost); -- ok
+ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2fd3f2b..7d369ba 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -109,7 +109,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# NB: temp.sql does a reconnect which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml reloptions
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 76b0de3..277ed7d 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -177,5 +177,6 @@ test: returning
test: largeobject
test: with
test: xml
+test: reloptions
test: event_trigger
test: stats
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 9a20dd1..3f5af8f 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2345,3 +2345,10 @@ create table parted_validate_test_1 partition of parted_validate_test for values
alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
alter table parted_validate_test validate constraint parted_validate_test_chka;
drop table parted_validate_test;
+-- test alter column options
+CREATE TABLE tmp(i integer);
+INSERT INTO tmp VALUES (1);
+ALTER TABLE tmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
+ALTER TABLE tmp ALTER COLUMN i RESET (n_distinct_inherited);
+ANALYZE tmp;
+DROP TABLE tmp;
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
index 11f8fe9..1e3ad74 100644
--- a/src/test/regress/sql/brin.sql
+++ b/src/test/regress/sql/brin.sql
@@ -460,3 +460,6 @@ VACUUM ANALYZE brin_test;
EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE a = 1;
-- Ensure brin index is not used when values are not correlated
EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE b = 1;
+---- Check that changing reloptions for brin index is not allowed
+--ALTER INDEX brinidx SET (pages_per_range = 10);
+--ALTER INDEX brinidx RESET (pages_per_range);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 67470db..a45e8eb 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -682,7 +682,7 @@ CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
-CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
+CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql
index 49126fd..f65ce37 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -17,6 +17,17 @@ select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
-- To test vacuum, delete some entries from all over the index.
delete from gist_point_tbl where id % 2 = 1;
+-- Test buffering and fillfactor reloption takes valid values
+create index gist_pointidx2 on gist_point_tbl using gist(p) with (buffering = on, fillfactor=50);
+create index gist_pointidx3 on gist_point_tbl using gist(p) with (buffering = off);
+create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = auto);
+--Test buffering and fillfactor for refising invalid values
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (buffering = invalid_value);
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=9);
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=101);
+
+
+
-- And also delete some concentration of values. (GiST doesn't currently
-- attempt to delete pages even when they become empty, but if it did, this
-- would exercise it)
diff --git a/src/test/regress/sql/hash_index.sql b/src/test/regress/sql/hash_index.sql
index 9af03d2..2681c80 100644
--- a/src/test/regress/sql/hash_index.sql
+++ b/src/test/regress/sql/hash_index.sql
@@ -192,3 +192,8 @@ CREATE TABLE hash_heap_float4 (x float4, y int);
INSERT INTO hash_heap_float4 VALUES (1.1,1);
CREATE INDEX hash_idx ON hash_heap_float4 USING hash (x);
DROP TABLE hash_heap_float4 CASCADE;
+
+-- Check reloptions min max values and that it is not allowed to ALTER
+CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=9);
+CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=101);
+-- ALTER INDEX hash_f8_index SET (fillfactor=99);
\ No newline at end of file
diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql
new file mode 100644
index 0000000..0092cb6
--- /dev/null
+++ b/src/test/regress/sql/reloptions.sql
@@ -0,0 +1,153 @@
+
+-- Simple create
+CREATE TABLE reloptions_test(i INT) WITH (fillfactor=30,autovacuum_enabled = false, autovacuum_analyze_scale_factor = 0.2);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Test сase insensitive
+CREATE TABLE reloptions_test3(i INT) WITH (FiLlFaCtoR=40);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test3'::regclass;
+
+-- Fail on min/max values check
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2);
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0);
+
+-- Fail when option and namespase do not exist
+
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2);
+
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2);
+
+-- Fail while setting unproper value
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30.5);
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string');
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string');
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string');
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true);
+
+-- Specifing name only should fail as if there was =ture after it
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
+
+-- Simple ALTER TABLE
+
+ALTER TABLE reloptions_test SET (fillfactor=31, autovacuum_analyze_scale_factor = 0.3);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Check that we cat set boolean option to true just by mentioning it
+
+ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Check that RESET works well
+
+ALTER TABLE reloptions_test RESET (fillfactor);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Check that RESETting all values make NULL reloptions record in pg_class
+ALTER TABLE reloptions_test RESET (autovacuum_enabled, autovacuum_analyze_scale_factor);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND reloptions IS NULL;
+
+-- Check RESET fails on att=value
+
+ALTER TABLE reloptions_test RESET (fillfactor=12);
+
+-- Check oids options is ignored
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Now testing toast.* options
+DROP TABLE reloptions_test;
+
+CREATE TABLE reloptions_test (s VARCHAR) WITH (toast.autovacuum_vacuum_cost_delay = 23 );
+
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+
+ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
+
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+
+ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
+
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+
+-- Fail on unexisting options in toast namespace
+CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42 );
+
+-- -- Fail on setting reloption to a table that does not have a TOAST relation
+-- CREATE TABLE reloptions_test2 (i int) WITH (toast.autovacuum_vacuum_cost_delay = 23 );
+-- DROP TABLE reloptions_test;
+
+-- CREATE TABLE reloptions_test(i INT);
+-- ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 23);
+-- ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
+
+-- autovacuum_analyze_scale_factor and autovacuum_analyze_threshold should be
+-- accepted by heap but rejected by toast (special case)
+-- DROP TABLE reloptions_test;
+-- CREATE TABLE reloptions_test (s VARCHAR) WITH (autovacuum_analyze_scale_factor=1, autovacuum_analyze_threshold=1);
+
+-- CREATE TABLE reloptions_test2 (s VARCHAR) WITH (toast.autovacuum_analyze_scale_factor=1);
+-- CREATE TABLE reloptions_test2 (s VARCHAR) WITH (toast.autovacuum_analyze_threshold=1);
+
+-- And now mixed toast + heap
+DROP TABLE reloptions_test;
+
+CREATE TABLE reloptions_test (s VARCHAR) WITH (toast.autovacuum_vacuum_cost_delay = 23, autovacuum_vacuum_cost_delay = 24, fillfactor = 40);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+
+-- Same FOR CREATE and ALTER INDEX for btree indexes
+
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+
+-- Fail when option and namespase do not exist
+
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (not_existing_option=2);
+
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (not_existing_option.fillfactor=2);
+
+-- Check ranges
+
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1);
+
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130);
+
+-- Check alter
+
+ALTER INDEX reloptions_test_idx SET (fillfactor=40);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+
+-- Check alter on empty relop list
+
+CREATE INDEX reloptions_test_idx3 ON reloptions_test (s);
+
+ALTER INDEX reloptions_test_idx3 SET (fillfactor=40);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass;
+
+
diff --git a/src/test/regress/sql/spgist.sql b/src/test/regress/sql/spgist.sql
index 5896b50..8d26e49 100644
--- a/src/test/regress/sql/spgist.sql
+++ b/src/test/regress/sql/spgist.sql
@@ -5,7 +5,7 @@
-- testing SP-GiST code itself.
create table spgist_point_tbl(id int4, p point);
-create index spgist_point_idx on spgist_point_tbl using spgist(p);
+create index spgist_point_idx on spgist_point_tbl using spgist(p) with (fillfactor = 75);
-- Test vacuum-root operation. It gets invoked when the root is also a leaf,
-- i.e. the index is very small.
@@ -48,3 +48,10 @@ select g, 'baaaaaaaaaaaaaar' || g from generate_series(1, 1000) g;
-- tuple to be moved to another page.
insert into spgist_text_tbl (id, t)
select -g, 'f' || repeat('o', 100-g) || 'surprise' from generate_series(1, 100) g;
+
+-- Check reloptions min, max values
+create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 9);
+create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 101);
+
+-- ALTER reloption should work
+alter index spgist_point_idx set (fillfactor = 76);
Re: [PATCH] Tests for reloptions
While working with reloptions refactoring patch, I've written series of tests
that triggers reloptions related code in all access methods. (I needed it to
make sure I did not break anything while coding)
I've included these tests to that patch.
Meanwhile Alvaro suggested to commit these tests before the main patch, in
order to make sure, that this patch does not change usual behavior.
So these tests separated from reloptions patch is in the attachment.
I've removed tests that check functionality that were introduced only in my
patch, and kept those that checks things that are already in postgres.
I also compared test coverage before and after applying this patch
(You can also compare, I put coverage results online
http://lj.nataraj.su/2017/reloptions_fix/coverage-master/
http://lj.nataraj.su/2017/reloptions_fix/coverage-patched/ )
Tests adds almost 600 lines to the test covered code (but see note at the end
of the letter)
src/backend/access/common/reloptions.c get only 7 lines, it was quite covered
by existing test, but all most of the access methods gets some coverage
increase:
src/backend/access/brin 1268 -> 1280 (+18)
src/backend/access/gin 2924 -> 2924 (0)
src/backend/access/gist 1673 -> 2108 (+435)
src/backend/access/hash 1580 -> 1638 (+58)
src/backend/access/heap 2863 -> 2866 (+3)
src/backend/access/nbtree 2565 -> 2647 (+82)
src/backend/access/spgist 2066 -> 2068 (+2)
Though I should say that incredible coverage boost for gist, is the result of
not steady results of test run. The real value should be much less...
Nevertheless tests touches the reloptions related code, checks for proper
error handling, and it is good.
I think we should commit it.
--
Do code for fun. Can do it for money (Perl & C/C++ ~10h/week)
Attachments:
relopt_tests_v2a.difftext/x-patch; charset=UTF-8; name=relopt_tests_v2a.diffDownload
diff --git a/contrib/bloom/expected/bloom.out b/contrib/bloom/expected/bloom.out
index cbc50f7..df9b7b9 100644
--- a/contrib/bloom/expected/bloom.out
+++ b/contrib/bloom/expected/bloom.out
@@ -210,3 +210,25 @@ ORDER BY 1;
text_ops | t
(2 rows)
+-- reloptions test
+DROP INDEX bloomidx;
+CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (length=7, col1 = 4);
+SELECT reloptions FROM pg_class WHERE oid = 'bloomidx'::regclass;
+ reloptions
+-------------------
+ {length=7,col1=4}
+(1 row)
+
+-- check for min and max values
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0);
+ERROR: value 0 out of bounds for option "length"
+DETAIL: Valid values are between "1" and "4096".
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=4097);
+ERROR: value 4097 out of bounds for option "length"
+DETAIL: Valid values are between "1" and "4096".
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=0);
+ERROR: value 0 out of bounds for option "col1"
+DETAIL: Valid values are between "1" and "4095".
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=4096);
+ERROR: value 4096 out of bounds for option "col1"
+DETAIL: Valid values are between "1" and "4095".
diff --git a/contrib/bloom/sql/bloom.sql b/contrib/bloom/sql/bloom.sql
index 2227460..39b0239 100644
--- a/contrib/bloom/sql/bloom.sql
+++ b/contrib/bloom/sql/bloom.sql
@@ -81,3 +81,14 @@ SELECT opcname, amvalidate(opc.oid)
FROM pg_opclass opc JOIN pg_am am ON am.oid = opcmethod
WHERE amname = 'bloom'
ORDER BY 1;
+
+-- reloptions test
+
+DROP INDEX bloomidx;
+CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (length=7, col1 = 4);
+SELECT reloptions FROM pg_class WHERE oid = 'bloomidx'::regclass;
+-- check for min and max values
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0);
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=4097);
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=0);
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=4096);
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index ed03cb9..e03d72d 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3551,3 +3551,10 @@ create table parted_validate_test_1 partition of parted_validate_test for values
alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
alter table parted_validate_test validate constraint parted_validate_test_chka;
drop table parted_validate_test;
+-- test alter column options
+CREATE TABLE tmp(i integer);
+INSERT INTO tmp VALUES (1);
+ALTER TABLE tmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
+ALTER TABLE tmp ALTER COLUMN i RESET (n_distinct_inherited);
+ANALYZE tmp;
+DROP TABLE tmp;
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 064adb4..16a3b8b 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2337,7 +2337,7 @@ Options: fastupdate=on, gin_pending_list_limit=128
CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
-CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
+CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
DROP TABLE unlogged_hash_table;
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index 91f9998..5b13db8 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -12,6 +12,20 @@ insert into gist_point_tbl (id, p)
select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
-- To test vacuum, delete some entries from all over the index.
delete from gist_point_tbl where id % 2 = 1;
+-- Test buffering and fillfactor reloption takes valid values
+create index gist_pointidx2 on gist_point_tbl using gist(p) with (buffering = on, fillfactor=50);
+create index gist_pointidx3 on gist_point_tbl using gist(p) with (buffering = off);
+create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = auto);
+--Test buffering and fillfactor for refising invalid values
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (buffering = invalid_value);
+ERROR: invalid value for "buffering" option
+DETAIL: Valid values are "on", "off", and "auto".
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=9);
+ERROR: value 9 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=101);
+ERROR: value 101 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
-- And also delete some concentration of values. (GiST doesn't currently
-- attempt to delete pages even when they become empty, but if it did, this
-- would exercise it)
diff --git a/src/test/regress/expected/hash_index.out b/src/test/regress/expected/hash_index.out
index 0bbaa2a..ecfc5eb 100644
--- a/src/test/regress/expected/hash_index.out
+++ b/src/test/regress/expected/hash_index.out
@@ -229,3 +229,10 @@ CREATE TABLE hash_heap_float4 (x float4, y int);
INSERT INTO hash_heap_float4 VALUES (1.1,1);
CREATE INDEX hash_idx ON hash_heap_float4 USING hash (x);
DROP TABLE hash_heap_float4 CASCADE;
+-- Check reloptions min max values and that it is not allowed to ALTER
+CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=9);
+ERROR: value 9 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=101);
+ERROR: value 101 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out
new file mode 100644
index 0000000..8bb69d6
--- /dev/null
+++ b/src/test/regress/expected/reloptions.out
@@ -0,0 +1,179 @@
+-- Simple create
+CREATE TABLE reloptions_test(i INT) WITH (fillfactor=30,autovacuum_enabled = false, autovacuum_analyze_scale_factor = 0.2);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+------------------------------------------------------------------------------
+ {fillfactor=30,autovacuum_enabled=false,autovacuum_analyze_scale_factor=0.2}
+(1 row)
+
+-- Test сase insensitive
+CREATE TABLE reloptions_test3(i INT) WITH (FiLlFaCtoR=40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test3'::regclass;
+ reloptions
+-----------------
+ {fillfactor=40}
+(1 row)
+
+-- Fail on min/max values check
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2);
+ERROR: value 2 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110);
+ERROR: value 110 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0);
+ERROR: value -10.0 out of bounds for option "autovacuum_analyze_scale_factor"
+DETAIL: Valid values are between "0.000000" and "100.000000".
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0);
+ERROR: value 110.0 out of bounds for option "autovacuum_analyze_scale_factor"
+DETAIL: Valid values are between "0.000000" and "100.000000".
+-- Fail when option and namespase do not exist
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2);
+ERROR: unrecognized parameter "not_existing_option"
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2);
+ERROR: unrecognized parameter namespace "not_existing_namespace"
+-- Fail while setting unproper value
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30.5);
+ERROR: invalid value for integer option "fillfactor": 30.5
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string');
+ERROR: invalid value for integer option "fillfactor": string
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true);
+ERROR: invalid value for integer option "fillfactor": true
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12);
+ERROR: invalid value for boolean option "autovacuum_enabled": 12
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5);
+ERROR: invalid value for boolean option "autovacuum_enabled": 30.5
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string');
+ERROR: invalid value for boolean option "autovacuum_enabled": string
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string');
+ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": string
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true);
+ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": true
+-- Fail if option is specified two times
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40);
+ERROR: parameter "fillfactor" specified more than once
+-- Specifing name only should fail as if there was =ture after it
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
+ERROR: invalid value for integer option "fillfactor": true
+-- Simple ALTER TABLE
+ALTER TABLE reloptions_test SET (fillfactor=31, autovacuum_analyze_scale_factor = 0.3);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+------------------------------------------------------------------------------
+ {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3}
+(1 row)
+
+-- Check that we cat set boolean option to true just by mentioning it
+ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+-----------------------------------------------------------------------------
+ {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true,fillfactor=32}
+(1 row)
+
+-- Check that RESET works well
+ALTER TABLE reloptions_test RESET (fillfactor);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+---------------------------------------------------------------
+ {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true}
+(1 row)
+
+-- Check that RESETting all values make NULL reloptions record in pg_class
+ALTER TABLE reloptions_test RESET (autovacuum_enabled, autovacuum_analyze_scale_factor);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND reloptions IS NULL;
+ reloptions
+------------
+
+(1 row)
+
+-- Check RESET fails on att=value
+ALTER TABLE reloptions_test RESET (fillfactor=12);
+ERROR: RESET must not include values for parameters
+-- Check oids options is ignored
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+-----------------
+ {fillfactor=20}
+(1 row)
+
+-- Now testing toast.* options
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test (s VARCHAR) WITH (toast.autovacuum_vacuum_cost_delay = 23 );
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+ reloptions
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=23}
+(1 row)
+
+ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+ reloptions
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=24}
+(1 row)
+
+ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+ reloptions
+------------
+
+(1 row)
+
+-- Fail on unexisting options in toast namespace
+CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42 );
+ERROR: unrecognized parameter "not_existing_option"
+-- And now mixed toast + heap
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test (s VARCHAR) WITH (toast.autovacuum_vacuum_cost_delay = 23, autovacuum_vacuum_cost_delay = 24, fillfactor = 40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+ reloptions
+-------------------------------------------------
+ {autovacuum_vacuum_cost_delay=24,fillfactor=40}
+(1 row)
+
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+ reloptions
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=23}
+(1 row)
+
+-- Same FOR CREATE and ALTER INDEX for btree indexes
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+ reloptions
+-----------------
+ {fillfactor=30}
+(1 row)
+
+-- Fail when option and namespase do not exist
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (not_existing_option=2);
+ERROR: unrecognized parameter "not_existing_option"
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (not_existing_option.fillfactor=2);
+ERROR: unrecognized parameter namespace "not_existing_option"
+-- Check ranges
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1);
+ERROR: value 1 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130);
+ERROR: value 130 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+-- Check alter
+ALTER INDEX reloptions_test_idx SET (fillfactor=40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+ reloptions
+-----------------
+ {fillfactor=40}
+(1 row)
+
+-- Check alter on empty relop list
+CREATE INDEX reloptions_test_idx3 ON reloptions_test (s);
+ALTER INDEX reloptions_test_idx3 SET (fillfactor=40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass;
+ reloptions
+-----------------
+ {fillfactor=40}
+(1 row)
+
diff --git a/src/test/regress/expected/spgist.out b/src/test/regress/expected/spgist.out
index 0691e91..bb58fb7 100644
--- a/src/test/regress/expected/spgist.out
+++ b/src/test/regress/expected/spgist.out
@@ -4,7 +4,7 @@
-- There are other tests to test different SP-GiST opclasses. This is for
-- testing SP-GiST code itself.
create table spgist_point_tbl(id int4, p point);
-create index spgist_point_idx on spgist_point_tbl using spgist(p);
+create index spgist_point_idx on spgist_point_tbl using spgist(p) with (fillfactor = 75);
-- Test vacuum-root operation. It gets invoked when the root is also a leaf,
-- i.e. the index is very small.
insert into spgist_point_tbl (id, p)
@@ -37,3 +37,12 @@ select g, 'baaaaaaaaaaaaaar' || g from generate_series(1, 1000) g;
-- tuple to be moved to another page.
insert into spgist_text_tbl (id, t)
select -g, 'f' || repeat('o', 100-g) || 'surprise' from generate_series(1, 100) g;
+-- Check reloptions min, max values
+create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 9);
+ERROR: value 9 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 101);
+ERROR: value 101 out of bounds for option "fillfactor"
+DETAIL: Valid values are between "10" and "100".
+-- ALTER reloption should work
+alter index spgist_point_idx set (fillfactor = 76);
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 03a62bd..7f7934b 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -12,10 +12,10 @@ DROP TABLESPACE regress_tblspacewith;
CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
-- try setting and resetting some properties for the new tablespace
-ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0);
+ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
-ALTER TABLESPACE regress_tblspace RESET (random_page_cost, seq_page_cost); -- ok
+ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
-- create a schema we can use
CREATE SCHEMA testschema;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index aaedf5f..2443511 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -14,12 +14,12 @@ DROP TABLESPACE regress_tblspacewith;
-- create a tablespace we can use
CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
-- try setting and resetting some properties for the new tablespace
-ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0);
+ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
ERROR: unrecognized parameter "some_nonexistent_parameter"
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
ERROR: RESET must not include values for parameters
-ALTER TABLESPACE regress_tblspace RESET (random_page_cost, seq_page_cost); -- ok
+ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2fd3f2b..7d369ba 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -109,7 +109,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# NB: temp.sql does a reconnect which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml reloptions
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 76b0de3..277ed7d 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -177,5 +177,6 @@ test: returning
test: largeobject
test: with
test: xml
+test: reloptions
test: event_trigger
test: stats
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 9a20dd1..3f5af8f 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2345,3 +2345,10 @@ create table parted_validate_test_1 partition of parted_validate_test for values
alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
alter table parted_validate_test validate constraint parted_validate_test_chka;
drop table parted_validate_test;
+-- test alter column options
+CREATE TABLE tmp(i integer);
+INSERT INTO tmp VALUES (1);
+ALTER TABLE tmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
+ALTER TABLE tmp ALTER COLUMN i RESET (n_distinct_inherited);
+ANALYZE tmp;
+DROP TABLE tmp;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 67470db..a45e8eb 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -682,7 +682,7 @@ CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
-CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
+CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql
index 49126fd..a06bedd 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -17,6 +17,15 @@ select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
-- To test vacuum, delete some entries from all over the index.
delete from gist_point_tbl where id % 2 = 1;
+-- Test buffering and fillfactor reloption takes valid values
+create index gist_pointidx2 on gist_point_tbl using gist(p) with (buffering = on, fillfactor=50);
+create index gist_pointidx3 on gist_point_tbl using gist(p) with (buffering = off);
+create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = auto);
+--Test buffering and fillfactor for refising invalid values
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (buffering = invalid_value);
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=9);
+create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=101);
+
-- And also delete some concentration of values. (GiST doesn't currently
-- attempt to delete pages even when they become empty, but if it did, this
-- would exercise it)
diff --git a/src/test/regress/sql/hash_index.sql b/src/test/regress/sql/hash_index.sql
index 9af03d2..291d5e4 100644
--- a/src/test/regress/sql/hash_index.sql
+++ b/src/test/regress/sql/hash_index.sql
@@ -192,3 +192,7 @@ CREATE TABLE hash_heap_float4 (x float4, y int);
INSERT INTO hash_heap_float4 VALUES (1.1,1);
CREATE INDEX hash_idx ON hash_heap_float4 USING hash (x);
DROP TABLE hash_heap_float4 CASCADE;
+
+-- Check reloptions min max values and that it is not allowed to ALTER
+CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=9);
+CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=101);
diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql
new file mode 100644
index 0000000..985b9e8
--- /dev/null
+++ b/src/test/regress/sql/reloptions.sql
@@ -0,0 +1,141 @@
+
+-- Simple create
+CREATE TABLE reloptions_test(i INT) WITH (fillfactor=30,autovacuum_enabled = false, autovacuum_analyze_scale_factor = 0.2);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Test сase insensitive
+CREATE TABLE reloptions_test3(i INT) WITH (FiLlFaCtoR=40);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test3'::regclass;
+
+-- Fail on min/max values check
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2);
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0);
+
+-- Fail when option and namespase do not exist
+
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2);
+
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2);
+
+-- Fail while setting unproper value
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30.5);
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string');
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5);
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string');
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string');
+
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true);
+
+-- Fail if option is specified two times
+
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40);
+
+-- Specifing name only should fail as if there was =ture after it
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
+
+-- Simple ALTER TABLE
+
+ALTER TABLE reloptions_test SET (fillfactor=31, autovacuum_analyze_scale_factor = 0.3);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Check that we cat set boolean option to true just by mentioning it
+
+ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Check that RESET works well
+
+ALTER TABLE reloptions_test RESET (fillfactor);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Check that RESETting all values make NULL reloptions record in pg_class
+ALTER TABLE reloptions_test RESET (autovacuum_enabled, autovacuum_analyze_scale_factor);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND reloptions IS NULL;
+
+-- Check RESET fails on att=value
+
+ALTER TABLE reloptions_test RESET (fillfactor=12);
+
+-- Check oids options is ignored
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+
+-- Now testing toast.* options
+DROP TABLE reloptions_test;
+
+CREATE TABLE reloptions_test (s VARCHAR) WITH (toast.autovacuum_vacuum_cost_delay = 23 );
+
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+
+ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
+
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+
+ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
+
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+
+-- Fail on unexisting options in toast namespace
+CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42 );
+
+-- And now mixed toast + heap
+DROP TABLE reloptions_test;
+
+CREATE TABLE reloptions_test (s VARCHAR) WITH (toast.autovacuum_vacuum_cost_delay = 23, autovacuum_vacuum_cost_delay = 24, fillfactor = 40);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+SELECT reloptions FROM pg_class WHERE oid = (SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+
+-- Same FOR CREATE and ALTER INDEX for btree indexes
+
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+
+-- Fail when option and namespase do not exist
+
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (not_existing_option=2);
+
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (not_existing_option.fillfactor=2);
+
+-- Check ranges
+
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1);
+
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130);
+
+-- Check alter
+
+ALTER INDEX reloptions_test_idx SET (fillfactor=40);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+
+-- Check alter on empty relop list
+
+CREATE INDEX reloptions_test_idx3 ON reloptions_test (s);
+
+ALTER INDEX reloptions_test_idx3 SET (fillfactor=40);
+
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass;
+
+
diff --git a/src/test/regress/sql/spgist.sql b/src/test/regress/sql/spgist.sql
index 5896b50..8d26e49 100644
--- a/src/test/regress/sql/spgist.sql
+++ b/src/test/regress/sql/spgist.sql
@@ -5,7 +5,7 @@
-- testing SP-GiST code itself.
create table spgist_point_tbl(id int4, p point);
-create index spgist_point_idx on spgist_point_tbl using spgist(p);
+create index spgist_point_idx on spgist_point_tbl using spgist(p) with (fillfactor = 75);
-- Test vacuum-root operation. It gets invoked when the root is also a leaf,
-- i.e. the index is very small.
@@ -48,3 +48,10 @@ select g, 'baaaaaaaaaaaaaar' || g from generate_series(1, 1000) g;
-- tuple to be moved to another page.
insert into spgist_text_tbl (id, t)
select -g, 'f' || repeat('o', 100-g) || 'surprise' from generate_series(1, 100) g;
+
+-- Check reloptions min, max values
+create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 9);
+create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 101);
+
+-- ALTER reloption should work
+alter index spgist_point_idx set (fillfactor = 76);
Re: [PATCH] Tests for reloptions
On Sun, Oct 1, 2017 at 5:18 AM, Nikolay Shaplov <dhyan@nataraj.su> wrote:
src/backend/access/common/reloptions.c get only 7 lines, it was quite covered
by existing test, but all most of the access methods gets some coverage
increase:src/backend/access/brin 1268 -> 1280 (+18)
src/backend/access/gin 2924 -> 2924 (0)
src/backend/access/gist 1673 -> 2108 (+435)
src/backend/access/hash 1580 -> 1638 (+58)
src/backend/access/heap 2863 -> 2866 (+3)
src/backend/access/nbtree 2565 -> 2647 (+82)
src/backend/access/spgist 2066 -> 2068 (+2)Though I should say that incredible coverage boost for gist, is the result of
not steady results of test run. The real value should be much less...
+-- Test buffering and fillfactor reloption takes valid values
+create index gist_pointidx2 on gist_point_tbl using gist(p) with
(buffering = on, fillfactor=50);
+create index gist_pointidx3 on gist_point_tbl using gist(p) with
(buffering = off);
+create index gist_pointidx4 on gist_point_tbl using gist(p) with
(buffering = auto);
Those are the important bits doing the boost in gist visibly. To be kept.-CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
+CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random
float8_ops) WITH (fillfactor=60);
Woah. So that alone increases hash by 58 steps.+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0);
+ERROR: value 0 out of bounds for option "length"
+DETAIL: Valid values are between "1" and "4096".
contrib/bloom contributes to the coverage of reloptions.c thanks to
its coverage of the add_ routines when the library is loaded. And
those don't actually improve any coverage, right?Nevertheless tests touches the reloptions related code, checks for proper
error handling, and it is good.
Per your measurements reloptions.c is improved by 1.7%, or 7 lines as
you say. Five of them are in parse_one_reloption for integer (2) and
reals (2), plus one error at the top. Two are in transformRelOptions
for a valid namespace handling. In your patch reloptions.sql is 141
lines. Couldn't it be shorter with the same improvements? It looks
that a lot of tests are overlapping with existing ones.
I think we should commit it.
My take is that a lighter version could be committed instead. My
suggestion is to keep the new test reloptions minimal so as it tests
the relopt types and their bounds, and I think that you could remove
the same bounding checks in the other tests that you added: bloom,
gist, etc.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Tests for reloptions
В письме от 3 октября 2017 11:48:43 пользователь Michael Paquier написал:
src/backend/access/common/reloptions.c get only 7 lines, it was quite
covered by existing test, but all most of the access methods gets some
coverage increase:src/backend/access/brin 1268 -> 1280 (+18)
src/backend/access/gin 2924 -> 2924 (0)
src/backend/access/gist 1673 -> 2108 (+435)
src/backend/access/hash 1580 -> 1638 (+58)
src/backend/access/heap 2863 -> 2866 (+3)
src/backend/access/nbtree 2565 -> 2647 (+82)
src/backend/access/spgist 2066 -> 2068 (+2)Though I should say that incredible coverage boost for gist, is the result
of not steady results of test run. The real value should be much less...+-- Test buffering and fillfactor reloption takes valid values +create index gist_pointidx2 on gist_point_tbl using gist(p) with (buffering = on, fillfactor=50); +create index gist_pointidx3 on gist_point_tbl using gist(p) with (buffering = off); +create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = auto); Those are the important bits doing the boost in gist visibly. To be kept.-CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops); +CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60); Woah. So that alone increases hash by 58 steps.
Might be... As I have noticed, tests for indexes filled with random data test
coverage, gives random coverage. There needed more random data to give steady
results. I am gathering statistics now, and later I hope I will offer another
patch(es) to fix it. So not all of 58 lines might be result of this patch :-)
+CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0); +ERROR: value 0 out of bounds for option "length" +DETAIL: Valid values are between "1" and "4096". contrib/bloom contributes to the coverage of reloptions.c thanks to its coverage of the add_ routines when the library is loaded. And those don't actually improve any coverage, right?
I actually run only simple "make check". running "make check" for bloom will
also add some add_ routines to coverage.
Nevertheless tests touches the reloptions related code, checks for proper
error handling, and it is good.Per your measurements reloptions.c is improved by 1.7%, or 7 lines as
you say. Five of them are in parse_one_reloption for integer (2) and
reals (2), plus one error at the top. Two are in transformRelOptions
for a valid namespace handling. In your patch reloptions.sql is 141
lines. Couldn't it be shorter with the same improvements? It looks
that a lot of tests are overlapping with existing ones.I think we should commit it.
My take is that a lighter version could be committed instead. My
suggestion is to keep the new test reloptions minimal so as it tests
the relopt types and their bounds, and I think that you could remove
the same bounding checks in the other tests that you added: bloom,
gist, etc.
I've been thinking a lot, and rereading the patch. When I reread it I've been
thinking that I would like to add more tests to it now... ;-)
If the only purpose of tests is to get better coverage, then I would agree
with you. But I've been thinking that tests also should check that everything
behaves as expected (or as written in documentation).
I would say that options names is a of part of SQL dialect that postgres uses,
kind of part of the syntax. It is good to be sure that they still supported.
So I would add a test for every option heap supports.
Checking minimum and maximum values are more disputable. The argumentation for
it is that min and max are written in documentation, and it is good to check
that postgres behaves according to documentation...
But it you tell me for sure that test only for code coverage and should not do
anything more, than I surely remove all tests that are not increase test
coverage.
--
Do code for fun. Can do it for money (Perl & C/C++ ~10h/week)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Tests for reloptions
Nikolay Shaplov wrote:
В письме от 3 октября 2017 11:48:43 пользователь Michael Paquier написал:
I've been thinking a lot, and rereading the patch. When I reread it I've been
thinking that I would like to add more tests to it now... ;-)If the only purpose of tests is to get better coverage, then I would agree
with you. But I've been thinking that tests also should check that everything
behaves as expected (or as written in documentation).I would say that options names is a of part of SQL dialect that postgres uses,
kind of part of the syntax. It is good to be sure that they still supported.
So I would add a test for every option heap supports.
Yeah, it would perhaps be good idea to ensure we don't break things that
are documented to work. If the tests don't take too long, I'm not
opposed to testing every single option. As you say, code coverage is
important but it's not the only goal.
I'm hesitant to hardcode things like the number of bits in bloom, as you
had in the original. If I understand correctly, that number could
change with compile options (different blocksize?), so I removed that
part. I also fixed a few spelling errors.
And pushed. Let's see what the buildfarm says about this.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Tests for reloptions
Oh, one more thing: be careful when editing parallel_schedule. There
are constraints on the number of entries in each group; you had added a
20th entry after the comment that the group can only have 19.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Tests for reloptions
В письме от 19 октября 2017 14:20:52 Вы написали:
I'm hesitant to hardcode things like the number of bits in bloom, as you
had in the original. If I understand correctly, that number could
change with compile options (different blocksize?), so I removed that
part.
#define MAX_BLOOM_LENGTH (256 * SIGNWORDBITS)
#define SIGNWORDBITS ((int) (BITS_PER_BYTE * sizeof(BloomSignatureWord)))
typedef uint16 BloomSignatureWord;
Here everything is based on uint16, and it is platform independent, as far as
I can get.
But this is not really important now...
I also fixed a few spelling errors.
Thank you. I am not so good with natural languages :-)
And pushed.
Thanx!
Let's see what the buildfarm says about this.
It seems to me that it is quite happy about these tests :-)
Oh, one more thing: be careful when editing parallel_schedule. There
are constraints on the number of entries
Oh, I did not payed attention to this issue, through it it mentioned in
parallel_schedule comments. I've added it to the wiki
https://wiki.postgresql.org/wiki/Regression_test_authoring So it was all
described in one place.
in each group; you had added a
20th entry after the comment that the group can only have 19.
Oups it was definitely my mistake. I should be more attentive... :-(
--
Do code for fun. Can do it for money (Perl & C/C++ ~10h/week)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Tests for reloptions
В письме от 19 октября 2017 14:20:52 Вы написали:
Yeah, it would perhaps be good idea to ensure we don't break things that
are documented to work. If the tests don't take too long, I'm not
opposed to testing every single option. As you say, code coverage is
important but it's not the only goal.I'm hesitant to hardcode things like the number of bits in bloom, as you
had in the original. If I understand correctly, that number could
change with compile options (different blocksize?), so I removed that
part. I also fixed a few spelling errors.And pushed. Let's see what the buildfarm says about this.
While merging this commit to my branch, I found two issues that as I think
needs fixing. Hope this does not require creating new commit request...
First is missing tab.
Second i think it is better to write "The OIDS option is not stored as
_reloption_" otherwise it cat be read as if it is not stored at all.
See patch in the attachment.
Thank you again for your work with the patch. I've seen how much you have
change it.
PS do I get right that 80 character code width rule is applied to SQL tests
too?
--
Do code for fun. Can do it for money (Perl & C/C++ ~10h/week)
Attachments:
reloptions.sql.difftext/x-patch; charset=UTF-8; name=reloptions.sql.diffDownload
diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql
index c9119fd..21a315b 100644
--- a/src/test/regress/sql/reloptions.sql
+++ b/src/test/regress/sql/reloptions.sql
@@ -47,12 +47,12 @@ SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
ALTER TABLE reloptions_test RESET (autovacuum_enabled,
autovacuum_analyze_scale_factor);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND
-reloptions IS NULL;
+ reloptions IS NULL;
-- RESET fails if a value is specified
ALTER TABLE reloptions_test RESET (fillfactor=12);
--- The OIDS option is not stored
+-- The OIDS option is not stored as reloption
DROP TABLE reloptions_test;
CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true);
SELECT reloptions, relhasoids FROM pg_class WHERE oid = 'reloptions_test'::regclass;
Re: [HACKERS] [PATCH] Tests for reloptions
В письме от 29 октября 2017 17:08:29 пользователь Nikolay Shaplov написал:
While merging this commit to my branch, I found two issues that as I think
needs fixing. Hope this does not require creating new commit request...
To draw more attention to this issue, I've filed it as a new thread
/messages/by-id/3951112.MkJ1MAZpIQ@x200m
with a entry for next commit fest.
Hope this will help fixing missing tab.
--
Do code for fun.