6 files changed, 154 insertions, 2 deletions

diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 0c8ae2ab970..2ef9541a8c9 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2427,3 +2427,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 67470db918b..a45e8ebeffb 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 49126fd466d..bae722fe13c 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -7,6 +7,17 @@
 create table gist_point_tbl(id int4, p point);
 create index gist_pointidx on gist_point_tbl using gist(p);
 
+-- Verify the fillfactor and buffering options
+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);
+drop index gist_pointidx2, gist_pointidx3, gist_pointidx4;
+
+-- Make sure bad values are refused
+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);
+
 -- Insert enough data to create a tree that's a couple of levels deep.
 insert into gist_point_tbl (id, p)
 select g,        point(g*10, g*10) from generate_series(1, 10000) g;
@@ -24,6 +35,9 @@ delete from gist_point_tbl where id < 10000;
 
 vacuum analyze gist_point_tbl;
 
+-- rebuild the index with a different fillfactor
+alter index gist_pointidx SET (fillfactor = 40);
+reindex index gist_pointidx;
 
 --
 -- Test Index-only plans on GiST indexes
diff --git a/src/test/regress/sql/hash_index.sql b/src/test/regress/sql/hash_index.sql
index 9af03d2bc16..4d1aa020a96 100644
--- a/src/test/regress/sql/hash_index.sql
+++ b/src/test/regress/sql/hash_index.sql
@@ -178,6 +178,10 @@ INSERT INTO hash_split_heap SELECT a/2 FROM generate_series(1, 25000) a;
 
 VACUUM hash_split_heap;
 
+-- Rebuild the index using a different fillfactor
+ALTER INDEX hash_split_index SET (fillfactor = 10);
+REINDEX INDEX hash_split_index;
+
 -- Clean up.
 DROP TABLE hash_split_heap;
 
@@ -192,3 +196,9 @@ 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;
+
+-- Test out-of-range fillfactor values
+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 00000000000..c9119fd8634
--- /dev/null
+++ b/src/test/regress/sql/reloptions.sql
@@ -0,0 +1,113 @@
+
+-- 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;
+
+-- Fail 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 namespace 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 improper values
+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 twice
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40);
+
+-- Specifying name only for a non-Boolean option should fail
+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;
+
+-- Set boolean option to true without specifying value
+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;
+
+-- Resetting all values causes the column to become null
+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;
+
+-- RESET fails if a value is specified
+ALTER TABLE reloptions_test RESET (fillfactor=12);
+
+-- The OIDS option is not stored
+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;
+
+-- Test toast.* options
+DROP TABLE reloptions_test;
+
+CREATE TABLE reloptions_test (s VARCHAR)
+	WITH (toast.autovacuum_vacuum_cost_delay = 23);
+SELECT reltoastrelid as toast_oid
+	FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset
+SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
+
+ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
+SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
+
+ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
+SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
+
+-- Fail on non-existent options in toast namespace
+CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42);
+
+-- Mix 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);
+
+--
+-- CREATE INDEX, ALTER INDEX for btrees
+--
+
+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 namespace 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_ns.fillfactor=2);
+
+-- Check allowed 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 reloption 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 5896b50865c..77b43a2d3e9 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,11 @@ 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;
+
+-- Test out-of-range fillfactor 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);
+
+-- Modify fillfactor in existing index
+alter index spgist_point_idx set (fillfactor = 90);
+reindex index spgist_point_idx;