This thread continues discussion at
https://www.postgresql.org/message-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)
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);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
