Skip to content

Commit 4b95cc1

Browse files
committed
Add more tests for reloptions
This is preparation for a future patch to extensively change how reloptions work. Author: Nikolay Shaplov Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/2615372.orqtEn8VGB@x200m
1 parent 752871b commit 4b95cc1

18 files changed

+422
-9
lines changed

contrib/bloom/expected/bloom.out

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -210,3 +210,20 @@ ORDER BY 1;
210210
text_ops | t
211211
(2 rows)
212212

213+
--
214+
-- relation options
215+
--
216+
DROP INDEX bloomidx;
217+
CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (length=7, col1=4);
218+
SELECT reloptions FROM pg_class WHERE oid = 'bloomidx'::regclass;
219+
reloptions
220+
-------------------
221+
{length=7,col1=4}
222+
(1 row)
223+
224+
-- check for min and max values
225+
\set VERBOSITY terse
226+
CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0);
227+
ERROR: value 0 out of bounds for option "length"
228+
CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=0);
229+
ERROR: value 0 out of bounds for option "col1"

contrib/bloom/sql/bloom.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -81,3 +81,14 @@ SELECT opcname, amvalidate(opc.oid)
8181
FROM pg_opclass opc JOIN pg_am am ON am.oid = opcmethod
8282
WHERE amname = 'bloom'
8383
ORDER BY 1;
84+
85+
--
86+
-- relation options
87+
--
88+
DROP INDEX bloomidx;
89+
CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (length=7, col1=4);
90+
SELECT reloptions FROM pg_class WHERE oid = 'bloomidx'::regclass;
91+
-- check for min and max values
92+
\set VERBOSITY terse
93+
CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0);
94+
CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=0);

src/test/regress/expected/alter_table.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3644,3 +3644,10 @@ create table parted_validate_test_1 partition of parted_validate_test for values
36443644
alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
36453645
alter table parted_validate_test validate constraint parted_validate_test_chka;
36463646
drop table parted_validate_test;
3647+
-- test alter column options
3648+
CREATE TABLE tmp(i integer);
3649+
INSERT INTO tmp VALUES (1);
3650+
ALTER TABLE tmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
3651+
ALTER TABLE tmp ALTER COLUMN i RESET (n_distinct_inherited);
3652+
ANALYZE tmp;
3653+
DROP TABLE tmp;

src/test/regress/expected/create_index.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2337,7 +2337,7 @@ Options: fastupdate=on, gin_pending_list_limit=128
23372337
CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
23382338
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
23392339
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
2340-
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
2340+
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
23412341
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
23422342
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
23432343
DROP TABLE unlogged_hash_table;

src/test/regress/expected/gist.out

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,21 @@
55
-- testing GiST code itself. Vacuuming in particular.
66
create table gist_point_tbl(id int4, p point);
77
create index gist_pointidx on gist_point_tbl using gist(p);
8+
-- Verify the fillfactor and buffering options
9+
create index gist_pointidx2 on gist_point_tbl using gist(p) with (buffering = on, fillfactor=50);
10+
create index gist_pointidx3 on gist_point_tbl using gist(p) with (buffering = off);
11+
create index gist_pointidx4 on gist_point_tbl using gist(p) with (buffering = auto);
12+
drop index gist_pointidx2, gist_pointidx3, gist_pointidx4;
13+
-- Make sure bad values are refused
14+
create index gist_pointidx5 on gist_point_tbl using gist(p) with (buffering = invalid_value);
15+
ERROR: invalid value for "buffering" option
16+
DETAIL: Valid values are "on", "off", and "auto".
17+
create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=9);
18+
ERROR: value 9 out of bounds for option "fillfactor"
19+
DETAIL: Valid values are between "10" and "100".
20+
create index gist_pointidx5 on gist_point_tbl using gist(p) with (fillfactor=101);
21+
ERROR: value 101 out of bounds for option "fillfactor"
22+
DETAIL: Valid values are between "10" and "100".
823
-- Insert enough data to create a tree that's a couple of levels deep.
924
insert into gist_point_tbl (id, p)
1025
select g, point(g*10, g*10) from generate_series(1, 10000) g;
@@ -17,6 +32,9 @@ delete from gist_point_tbl where id % 2 = 1;
1732
-- would exercise it)
1833
delete from gist_point_tbl where id < 10000;
1934
vacuum analyze gist_point_tbl;
35+
-- rebuild the index with a different fillfactor
36+
alter index gist_pointidx SET (fillfactor = 40);
37+
reindex index gist_pointidx;
2038
--
2139
-- Test Index-only plans on GiST indexes
2240
--

src/test/regress/expected/hash_index.out

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -217,6 +217,9 @@ END;
217217
DELETE FROM hash_split_heap WHERE keycol = 1;
218218
INSERT INTO hash_split_heap SELECT a/2 FROM generate_series(1, 25000) a;
219219
VACUUM hash_split_heap;
220+
-- Rebuild the index using a different fillfactor
221+
ALTER INDEX hash_split_index SET (fillfactor = 10);
222+
REINDEX INDEX hash_split_index;
220223
-- Clean up.
221224
DROP TABLE hash_split_heap;
222225
-- Index on temp table.
@@ -229,3 +232,12 @@ CREATE TABLE hash_heap_float4 (x float4, y int);
229232
INSERT INTO hash_heap_float4 VALUES (1.1,1);
230233
CREATE INDEX hash_idx ON hash_heap_float4 USING hash (x);
231234
DROP TABLE hash_heap_float4 CASCADE;
235+
-- Test out-of-range fillfactor values
236+
CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
237+
WITH (fillfactor=9);
238+
ERROR: value 9 out of bounds for option "fillfactor"
239+
DETAIL: Valid values are between "10" and "100".
240+
CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
241+
WITH (fillfactor=101);
242+
ERROR: value 101 out of bounds for option "fillfactor"
243+
DETAIL: Valid values are between "10" and "100".
Lines changed: 185 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,185 @@
1+
-- Simple create
2+
CREATE TABLE reloptions_test(i INT) WITH (FiLLFaCToR=30,
3+
autovacuum_enabled = false, autovacuum_analyze_scale_factor = 0.2);
4+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
5+
reloptions
6+
------------------------------------------------------------------------------
7+
{fillfactor=30,autovacuum_enabled=false,autovacuum_analyze_scale_factor=0.2}
8+
(1 row)
9+
10+
-- Fail min/max values check
11+
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2);
12+
ERROR: value 2 out of bounds for option "fillfactor"
13+
DETAIL: Valid values are between "10" and "100".
14+
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110);
15+
ERROR: value 110 out of bounds for option "fillfactor"
16+
DETAIL: Valid values are between "10" and "100".
17+
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0);
18+
ERROR: value -10.0 out of bounds for option "autovacuum_analyze_scale_factor"
19+
DETAIL: Valid values are between "0.000000" and "100.000000".
20+
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0);
21+
ERROR: value 110.0 out of bounds for option "autovacuum_analyze_scale_factor"
22+
DETAIL: Valid values are between "0.000000" and "100.000000".
23+
-- Fail when option and namespace do not exist
24+
CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2);
25+
ERROR: unrecognized parameter "not_existing_option"
26+
CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2);
27+
ERROR: unrecognized parameter namespace "not_existing_namespace"
28+
-- Fail while setting improper values
29+
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30.5);
30+
ERROR: invalid value for integer option "fillfactor": 30.5
31+
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string');
32+
ERROR: invalid value for integer option "fillfactor": string
33+
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true);
34+
ERROR: invalid value for integer option "fillfactor": true
35+
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12);
36+
ERROR: invalid value for boolean option "autovacuum_enabled": 12
37+
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5);
38+
ERROR: invalid value for boolean option "autovacuum_enabled": 30.5
39+
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string');
40+
ERROR: invalid value for boolean option "autovacuum_enabled": string
41+
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string');
42+
ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": string
43+
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true);
44+
ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": true
45+
-- Fail if option is specified twice
46+
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40);
47+
ERROR: parameter "fillfactor" specified more than once
48+
-- Specifying name only for a non-Boolean option should fail
49+
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
50+
ERROR: invalid value for integer option "fillfactor": true
51+
-- Simple ALTER TABLE
52+
ALTER TABLE reloptions_test SET (fillfactor=31,
53+
autovacuum_analyze_scale_factor = 0.3);
54+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
55+
reloptions
56+
------------------------------------------------------------------------------
57+
{autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3}
58+
(1 row)
59+
60+
-- Set boolean option to true without specifying value
61+
ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
62+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
63+
reloptions
64+
-----------------------------------------------------------------------------
65+
{autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true,fillfactor=32}
66+
(1 row)
67+
68+
-- Check that RESET works well
69+
ALTER TABLE reloptions_test RESET (fillfactor);
70+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
71+
reloptions
72+
---------------------------------------------------------------
73+
{autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true}
74+
(1 row)
75+
76+
-- Resetting all values causes the column to become null
77+
ALTER TABLE reloptions_test RESET (autovacuum_enabled,
78+
autovacuum_analyze_scale_factor);
79+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND
80+
reloptions IS NULL;
81+
reloptions
82+
------------
83+
84+
(1 row)
85+
86+
-- RESET fails if a value is specified
87+
ALTER TABLE reloptions_test RESET (fillfactor=12);
88+
ERROR: RESET must not include values for parameters
89+
-- The OIDS option is not stored
90+
DROP TABLE reloptions_test;
91+
CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true);
92+
SELECT reloptions, relhasoids FROM pg_class WHERE oid = 'reloptions_test'::regclass;
93+
reloptions | relhasoids
94+
-----------------+------------
95+
{fillfactor=20} | t
96+
(1 row)
97+
98+
-- Test toast.* options
99+
DROP TABLE reloptions_test;
100+
CREATE TABLE reloptions_test (s VARCHAR)
101+
WITH (toast.autovacuum_vacuum_cost_delay = 23);
102+
SELECT reltoastrelid as toast_oid
103+
FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset
104+
SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
105+
reloptions
106+
-----------------------------------
107+
{autovacuum_vacuum_cost_delay=23}
108+
(1 row)
109+
110+
ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
111+
SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
112+
reloptions
113+
-----------------------------------
114+
{autovacuum_vacuum_cost_delay=24}
115+
(1 row)
116+
117+
ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
118+
SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
119+
reloptions
120+
------------
121+
122+
(1 row)
123+
124+
-- Fail on non-existent options in toast namespace
125+
CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42);
126+
ERROR: unrecognized parameter "not_existing_option"
127+
-- Mix TOAST & heap
128+
DROP TABLE reloptions_test;
129+
CREATE TABLE reloptions_test (s VARCHAR) WITH
130+
(toast.autovacuum_vacuum_cost_delay = 23,
131+
autovacuum_vacuum_cost_delay = 24, fillfactor = 40);
132+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
133+
reloptions
134+
-------------------------------------------------
135+
{autovacuum_vacuum_cost_delay=24,fillfactor=40}
136+
(1 row)
137+
138+
SELECT reloptions FROM pg_class WHERE oid = (
139+
SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
140+
reloptions
141+
-----------------------------------
142+
{autovacuum_vacuum_cost_delay=23}
143+
(1 row)
144+
145+
--
146+
-- CREATE INDEX, ALTER INDEX for btrees
147+
--
148+
CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30);
149+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
150+
reloptions
151+
-----------------
152+
{fillfactor=30}
153+
(1 row)
154+
155+
-- Fail when option and namespace do not exist
156+
CREATE INDEX reloptions_test_idx ON reloptions_test (s)
157+
WITH (not_existing_option=2);
158+
ERROR: unrecognized parameter "not_existing_option"
159+
CREATE INDEX reloptions_test_idx ON reloptions_test (s)
160+
WITH (not_existing_ns.fillfactor=2);
161+
ERROR: unrecognized parameter namespace "not_existing_ns"
162+
-- Check allowed ranges
163+
CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1);
164+
ERROR: value 1 out of bounds for option "fillfactor"
165+
DETAIL: Valid values are between "10" and "100".
166+
CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130);
167+
ERROR: value 130 out of bounds for option "fillfactor"
168+
DETAIL: Valid values are between "10" and "100".
169+
-- Check ALTER
170+
ALTER INDEX reloptions_test_idx SET (fillfactor=40);
171+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
172+
reloptions
173+
-----------------
174+
{fillfactor=40}
175+
(1 row)
176+
177+
-- Check ALTER on empty reloption list
178+
CREATE INDEX reloptions_test_idx3 ON reloptions_test (s);
179+
ALTER INDEX reloptions_test_idx3 SET (fillfactor=40);
180+
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass;
181+
reloptions
182+
-----------------
183+
{fillfactor=40}
184+
(1 row)
185+

src/test/regress/expected/spgist.out

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
-- There are other tests to test different SP-GiST opclasses. This is for
55
-- testing SP-GiST code itself.
66
create table spgist_point_tbl(id int4, p point);
7-
create index spgist_point_idx on spgist_point_tbl using spgist(p);
7+
create index spgist_point_idx on spgist_point_tbl using spgist(p) with (fillfactor = 75);
88
-- Test vacuum-root operation. It gets invoked when the root is also a leaf,
99
-- i.e. the index is very small.
1010
insert into spgist_point_tbl (id, p)
@@ -37,3 +37,13 @@ select g, 'baaaaaaaaaaaaaar' || g from generate_series(1, 1000) g;
3737
-- tuple to be moved to another page.
3838
insert into spgist_text_tbl (id, t)
3939
select -g, 'f' || repeat('o', 100-g) || 'surprise' from generate_series(1, 100) g;
40+
-- Test out-of-range fillfactor values
41+
create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 9);
42+
ERROR: value 9 out of bounds for option "fillfactor"
43+
DETAIL: Valid values are between "10" and "100".
44+
create index spgist_point_idx2 on spgist_point_tbl using spgist(p) with (fillfactor = 101);
45+
ERROR: value 101 out of bounds for option "fillfactor"
46+
DETAIL: Valid values are between "10" and "100".
47+
-- Modify fillfactor in existing index
48+
alter index spgist_point_idx set (fillfactor = 90);
49+
reindex index spgist_point_idx;

src/test/regress/input/tablespace.source

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,10 +12,10 @@ DROP TABLESPACE regress_tblspacewith;
1212
CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
1313

1414
-- try setting and resetting some properties for the new tablespace
15-
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0);
15+
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
1616
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
1717
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
18-
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, seq_page_cost); -- ok
18+
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
1919

2020
-- create a schema we can use
2121
CREATE SCHEMA testschema;

src/test/regress/output/tablespace.source

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,12 +14,12 @@ DROP TABLESPACE regress_tblspacewith;
1414
-- create a tablespace we can use
1515
CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
1616
-- try setting and resetting some properties for the new tablespace
17-
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0);
17+
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
1818
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
1919
ERROR: unrecognized parameter "some_nonexistent_parameter"
2020
ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail
2121
ERROR: RESET must not include values for parameters
22-
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, seq_page_cost); -- ok
22+
ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok
2323
-- create a schema we can use
2424
CREATE SCHEMA testschema;
2525
-- try a table

src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c
116116
# ----------
117117
# Another group of parallel tests
118118
# ----------
119-
test: identity partition_join
119+
test: identity partition_join reloptions
120120

121121
# event triggers cannot run concurrently with any test that runs DDL
122122
test: event_trigger

src/test/regress/serial_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -180,5 +180,6 @@ test: with
180180
test: xml
181181
test: identity
182182
test: partition_join
183+
test: reloptions
183184
test: event_trigger
184185
test: stats

src/test/regress/sql/alter_table.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2427,3 +2427,10 @@ create table parted_validate_test_1 partition of parted_validate_test for values
24272427
alter table parted_validate_test add constraint parted_validate_test_chka check (a > 0) not valid;
24282428
alter table parted_validate_test validate constraint parted_validate_test_chka;
24292429
drop table parted_validate_test;
2430+
-- test alter column options
2431+
CREATE TABLE tmp(i integer);
2432+
INSERT INTO tmp VALUES (1);
2433+
ALTER TABLE tmp ALTER COLUMN i SET (n_distinct = 1, n_distinct_inherited = 2);
2434+
ALTER TABLE tmp ALTER COLUMN i RESET (n_distinct_inherited);
2435+
ANALYZE tmp;
2436+
DROP TABLE tmp;

src/test/regress/sql/create_index.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -682,7 +682,7 @@ CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
682682

683683
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
684684

685-
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
685+
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60);
686686

687687
CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
688688
CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);

0 commit comments

Comments
 (0)