1-- Simple create 2CREATE TABLE reloptions_test(i INT) WITH (FiLLFaCToR=30, 3 autovacuum_enabled = false, autovacuum_analyze_scale_factor = 0.2); 4SELECT 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 11CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2); 12ERROR: value 2 out of bounds for option "fillfactor" 13DETAIL: Valid values are between "10" and "100". 14CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110); 15ERROR: value 110 out of bounds for option "fillfactor" 16DETAIL: Valid values are between "10" and "100". 17CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0); 18ERROR: value -10.0 out of bounds for option "autovacuum_analyze_scale_factor" 19DETAIL: Valid values are between "0.000000" and "100.000000". 20CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0); 21ERROR: value 110.0 out of bounds for option "autovacuum_analyze_scale_factor" 22DETAIL: Valid values are between "0.000000" and "100.000000". 23-- Fail when option and namespace do not exist 24CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2); 25ERROR: unrecognized parameter "not_existing_option" 26CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2); 27ERROR: unrecognized parameter namespace "not_existing_namespace" 28-- Fail while setting improper values 29CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=-30.1); 30ERROR: value -30.1 out of bounds for option "fillfactor" 31DETAIL: Valid values are between "10" and "100". 32CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string'); 33ERROR: invalid value for integer option "fillfactor": string 34CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true); 35ERROR: invalid value for integer option "fillfactor": true 36CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12); 37ERROR: invalid value for boolean option "autovacuum_enabled": 12 38CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5); 39ERROR: invalid value for boolean option "autovacuum_enabled": 30.5 40CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string'); 41ERROR: invalid value for boolean option "autovacuum_enabled": string 42CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string'); 43ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": string 44CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true); 45ERROR: invalid value for floating point option "autovacuum_analyze_scale_factor": true 46-- Fail if option is specified twice 47CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40); 48ERROR: parameter "fillfactor" specified more than once 49-- Specifying name only for a non-Boolean option should fail 50CREATE TABLE reloptions_test2(i INT) WITH (fillfactor); 51ERROR: invalid value for integer option "fillfactor": true 52-- Simple ALTER TABLE 53ALTER TABLE reloptions_test SET (fillfactor=31, 54 autovacuum_analyze_scale_factor = 0.3); 55SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 56 reloptions 57------------------------------------------------------------------------------ 58 {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3} 59(1 row) 60 61-- Set boolean option to true without specifying value 62ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32); 63SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 64 reloptions 65----------------------------------------------------------------------------- 66 {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true,fillfactor=32} 67(1 row) 68 69-- Check that RESET works well 70ALTER TABLE reloptions_test RESET (fillfactor); 71SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 72 reloptions 73--------------------------------------------------------------- 74 {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true} 75(1 row) 76 77-- Resetting all values causes the column to become null 78ALTER TABLE reloptions_test RESET (autovacuum_enabled, 79 autovacuum_analyze_scale_factor); 80SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND 81 reloptions IS NULL; 82 reloptions 83------------ 84 85(1 row) 86 87-- RESET fails if a value is specified 88ALTER TABLE reloptions_test RESET (fillfactor=12); 89ERROR: RESET must not include values for parameters 90-- Test vacuum_truncate option 91DROP TABLE reloptions_test; 92CREATE TABLE reloptions_test(i INT NOT NULL, j text) 93 WITH (vacuum_truncate=false, 94 toast.vacuum_truncate=false, 95 autovacuum_enabled=false); 96SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 97 reloptions 98-------------------------------------------------- 99 {vacuum_truncate=false,autovacuum_enabled=false} 100(1 row) 101 102INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); 103ERROR: null value in column "i" of relation "reloptions_test" violates not-null constraint 104DETAIL: Failing row contains (null, null). 105-- Do an aggressive vacuum to prevent page-skipping. 106VACUUM FREEZE reloptions_test; 107SELECT pg_relation_size('reloptions_test') > 0; 108 ?column? 109---------- 110 t 111(1 row) 112 113SELECT reloptions FROM pg_class WHERE oid = 114 (SELECT reltoastrelid FROM pg_class 115 WHERE oid = 'reloptions_test'::regclass); 116 reloptions 117------------------------- 118 {vacuum_truncate=false} 119(1 row) 120 121ALTER TABLE reloptions_test RESET (vacuum_truncate); 122SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 123 reloptions 124---------------------------- 125 {autovacuum_enabled=false} 126(1 row) 127 128INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); 129ERROR: null value in column "i" of relation "reloptions_test" violates not-null constraint 130DETAIL: Failing row contains (null, null). 131-- Do an aggressive vacuum to prevent page-skipping. 132VACUUM FREEZE reloptions_test; 133SELECT pg_relation_size('reloptions_test') = 0; 134 ?column? 135---------- 136 t 137(1 row) 138 139-- Test toast.* options 140DROP TABLE reloptions_test; 141CREATE TABLE reloptions_test (s VARCHAR) 142 WITH (toast.autovacuum_vacuum_cost_delay = 23); 143SELECT reltoastrelid as toast_oid 144 FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset 145SELECT reloptions FROM pg_class WHERE oid = :toast_oid; 146 reloptions 147----------------------------------- 148 {autovacuum_vacuum_cost_delay=23} 149(1 row) 150 151ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24); 152SELECT reloptions FROM pg_class WHERE oid = :toast_oid; 153 reloptions 154----------------------------------- 155 {autovacuum_vacuum_cost_delay=24} 156(1 row) 157 158ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay); 159SELECT reloptions FROM pg_class WHERE oid = :toast_oid; 160 reloptions 161------------ 162 163(1 row) 164 165-- Fail on non-existent options in toast namespace 166CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42); 167ERROR: unrecognized parameter "not_existing_option" 168-- Mix TOAST & heap 169DROP TABLE reloptions_test; 170CREATE TABLE reloptions_test (s VARCHAR) WITH 171 (toast.autovacuum_vacuum_cost_delay = 23, 172 autovacuum_vacuum_cost_delay = 24, fillfactor = 40); 173SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 174 reloptions 175------------------------------------------------- 176 {autovacuum_vacuum_cost_delay=24,fillfactor=40} 177(1 row) 178 179SELECT reloptions FROM pg_class WHERE oid = ( 180 SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass); 181 reloptions 182----------------------------------- 183 {autovacuum_vacuum_cost_delay=23} 184(1 row) 185 186-- 187-- CREATE INDEX, ALTER INDEX for btrees 188-- 189CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30); 190SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass; 191 reloptions 192----------------- 193 {fillfactor=30} 194(1 row) 195 196-- Fail when option and namespace do not exist 197CREATE INDEX reloptions_test_idx ON reloptions_test (s) 198 WITH (not_existing_option=2); 199ERROR: unrecognized parameter "not_existing_option" 200CREATE INDEX reloptions_test_idx ON reloptions_test (s) 201 WITH (not_existing_ns.fillfactor=2); 202ERROR: unrecognized parameter namespace "not_existing_ns" 203-- Check allowed ranges 204CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1); 205ERROR: value 1 out of bounds for option "fillfactor" 206DETAIL: Valid values are between "10" and "100". 207CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130); 208ERROR: value 130 out of bounds for option "fillfactor" 209DETAIL: Valid values are between "10" and "100". 210-- Check ALTER 211ALTER INDEX reloptions_test_idx SET (fillfactor=40); 212SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass; 213 reloptions 214----------------- 215 {fillfactor=40} 216(1 row) 217 218-- Check ALTER on empty reloption list 219CREATE INDEX reloptions_test_idx3 ON reloptions_test (s); 220ALTER INDEX reloptions_test_idx3 SET (fillfactor=40); 221SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass; 222 reloptions 223----------------- 224 {fillfactor=40} 225(1 row) 226 227