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;
6 ------------------------------------------------------------------------------
7 {fillfactor=30,autovacuum_enabled=false,autovacuum_analyze_scale_factor=0.2}
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.1);
30 ERROR: value -30.1 out of bounds for option "fillfactor"
31 DETAIL: Valid values are between "10" and "100".
32 CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string');
33 ERROR: invalid value for integer option "fillfactor": string
34 CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true);
35 ERROR: invalid value for integer option "fillfactor": true
36 CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12);
37 ERROR: invalid value for boolean option "autovacuum_enabled": 12
38 CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5);
39 ERROR: invalid value for boolean option "autovacuum_enabled": 30.5
40 CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string');
41 ERROR: invalid value for boolean option "autovacuum_enabled": string
42 CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string');
43 ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": string
44 CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true);
45 ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": true
46 -- Fail if option is specified twice
47 CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40);
48 ERROR: parameter "fillfactor" specified more than once
49 -- Specifying name only for a non-Boolean option should fail
50 CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
51 ERROR: invalid value for integer option "fillfactor": true
53 ALTER TABLE reloptions_test SET (fillfactor=31,
54 autovacuum_analyze_scale_factor = 0.3);
55 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
57 ------------------------------------------------------------------------------
58 {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3}
61 -- Set boolean option to true without specifying value
62 ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
63 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
65 -----------------------------------------------------------------------------
66 {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true,fillfactor=32}
69 -- Check that RESET works well
70 ALTER TABLE reloptions_test RESET (fillfactor);
71 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
73 ---------------------------------------------------------------
74 {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true}
77 -- Resetting all values causes the column to become null
78 ALTER TABLE reloptions_test RESET (autovacuum_enabled,
79 autovacuum_analyze_scale_factor);
80 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND
87 -- RESET fails if a value is specified
88 ALTER TABLE reloptions_test RESET (fillfactor=12);
89 ERROR: RESET must not include values for parameters
90 -- Test vacuum_truncate option
91 DROP TABLE reloptions_test;
92 CREATE TABLE reloptions_test(i INT NOT NULL, j text)
93 WITH (vacuum_truncate=false,
94 toast.vacuum_truncate=false,
95 autovacuum_enabled=false);
96 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
98 --------------------------------------------------
99 {vacuum_truncate=false,autovacuum_enabled=false}
102 INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
103 ERROR: null value in column "i" of relation "reloptions_test" violates not-null constraint
104 DETAIL: Failing row contains (null, null).
105 -- Do an aggressive vacuum to prevent page-skipping.
106 VACUUM FREEZE reloptions_test;
107 SELECT pg_relation_size('reloptions_test') > 0;
113 SELECT reloptions FROM pg_class WHERE oid =
114 (SELECT reltoastrelid FROM pg_class
115 WHERE oid = 'reloptions_test'::regclass);
117 -------------------------
118 {vacuum_truncate=false}
121 ALTER TABLE reloptions_test RESET (vacuum_truncate);
122 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
124 ----------------------------
125 {autovacuum_enabled=false}
128 INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
129 ERROR: null value in column "i" of relation "reloptions_test" violates not-null constraint
130 DETAIL: Failing row contains (null, null).
131 -- Do an aggressive vacuum to prevent page-skipping.
132 VACUUM FREEZE reloptions_test;
133 SELECT pg_relation_size('reloptions_test') = 0;
139 -- Test toast.* options
140 DROP TABLE reloptions_test;
141 CREATE TABLE reloptions_test (s VARCHAR)
142 WITH (toast.autovacuum_vacuum_cost_delay = 23);
143 SELECT reltoastrelid as toast_oid
144 FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset
145 SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
147 -----------------------------------
148 {autovacuum_vacuum_cost_delay=23}
151 ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
152 SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
154 -----------------------------------
155 {autovacuum_vacuum_cost_delay=24}
158 ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
159 SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
165 -- Fail on non-existent options in toast namespace
166 CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42);
167 ERROR: unrecognized parameter "not_existing_option"
169 DROP TABLE reloptions_test;
170 CREATE TABLE reloptions_test (s VARCHAR) WITH
171 (toast.autovacuum_vacuum_cost_delay = 23,
172 autovacuum_vacuum_cost_delay = 24, fillfactor = 40);
173 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
175 -------------------------------------------------
176 {autovacuum_vacuum_cost_delay=24,fillfactor=40}
179 SELECT reloptions FROM pg_class WHERE oid = (
180 SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
182 -----------------------------------
183 {autovacuum_vacuum_cost_delay=23}
187 -- CREATE INDEX, ALTER INDEX for btrees
189 CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30);
190 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
196 -- Fail when option and namespace do not exist
197 CREATE INDEX reloptions_test_idx ON reloptions_test (s)
198 WITH (not_existing_option=2);
199 ERROR: unrecognized parameter "not_existing_option"
200 CREATE INDEX reloptions_test_idx ON reloptions_test (s)
201 WITH (not_existing_ns.fillfactor=2);
202 ERROR: unrecognized parameter namespace "not_existing_ns"
203 -- Check allowed ranges
204 CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1);
205 ERROR: value 1 out of bounds for option "fillfactor"
206 DETAIL: Valid values are between "10" and "100".
207 CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130);
208 ERROR: value 130 out of bounds for option "fillfactor"
209 DETAIL: Valid values are between "10" and "100".
211 ALTER INDEX reloptions_test_idx SET (fillfactor=40);
212 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
218 -- Check ALTER on empty reloption list
219 CREATE INDEX reloptions_test_idx3 ON reloptions_test (s);
220 ALTER INDEX reloptions_test_idx3 SET (fillfactor=40);
221 SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass;