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 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);