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