1 2-- Simple create 3CREATE TABLE reloptions_test(i INT) WITH (FiLLFaCToR=30, 4 autovacuum_enabled = false, autovacuum_analyze_scale_factor = 0.2); 5SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 6 7-- Fail min/max values check 8CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2); 9CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110); 10CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0); 11CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0); 12 13-- Fail when option and namespace do not exist 14CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2); 15CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2); 16 17-- Fail while setting improper values 18CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=-30.1); 19CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string'); 20CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true); 21CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12); 22CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5); 23CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string'); 24CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string'); 25CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true); 26 27-- Fail if option is specified twice 28CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40); 29 30-- Specifying name only for a non-Boolean option should fail 31CREATE TABLE reloptions_test2(i INT) WITH (fillfactor); 32 33-- Simple ALTER TABLE 34ALTER TABLE reloptions_test SET (fillfactor=31, 35 autovacuum_analyze_scale_factor = 0.3); 36SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 37 38-- Set boolean option to true without specifying value 39ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32); 40SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 41 42-- Check that RESET works well 43ALTER TABLE reloptions_test RESET (fillfactor); 44SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 45 46-- Resetting all values causes the column to become null 47ALTER TABLE reloptions_test RESET (autovacuum_enabled, 48 autovacuum_analyze_scale_factor); 49SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND 50 reloptions IS NULL; 51 52-- RESET fails if a value is specified 53ALTER TABLE reloptions_test RESET (fillfactor=12); 54 55-- Test vacuum_truncate option 56DROP TABLE reloptions_test; 57 58CREATE TABLE reloptions_test(i INT NOT NULL, j text) 59 WITH (vacuum_truncate=false, 60 toast.vacuum_truncate=false, 61 autovacuum_enabled=false); 62SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 63INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); 64VACUUM reloptions_test; 65SELECT pg_relation_size('reloptions_test') > 0; 66 67SELECT reloptions FROM pg_class WHERE oid = 68 (SELECT reltoastrelid FROM pg_class 69 WHERE oid = 'reloptions_test'::regclass); 70 71ALTER TABLE reloptions_test RESET (vacuum_truncate); 72SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 73INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); 74VACUUM reloptions_test; 75SELECT pg_relation_size('reloptions_test') = 0; 76 77-- Test toast.* options 78DROP TABLE reloptions_test; 79 80CREATE TABLE reloptions_test (s VARCHAR) 81 WITH (toast.autovacuum_vacuum_cost_delay = 23); 82SELECT reltoastrelid as toast_oid 83 FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset 84SELECT reloptions FROM pg_class WHERE oid = :toast_oid; 85 86ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24); 87SELECT reloptions FROM pg_class WHERE oid = :toast_oid; 88 89ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay); 90SELECT reloptions FROM pg_class WHERE oid = :toast_oid; 91 92-- Fail on non-existent options in toast namespace 93CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42); 94 95-- Mix TOAST & heap 96DROP TABLE reloptions_test; 97 98CREATE TABLE reloptions_test (s VARCHAR) WITH 99 (toast.autovacuum_vacuum_cost_delay = 23, 100 autovacuum_vacuum_cost_delay = 24, fillfactor = 40); 101 102SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; 103SELECT reloptions FROM pg_class WHERE oid = ( 104 SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass); 105 106-- 107-- CREATE INDEX, ALTER INDEX for btrees 108-- 109 110CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30); 111SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass; 112 113-- Fail when option and namespace do not exist 114CREATE INDEX reloptions_test_idx ON reloptions_test (s) 115 WITH (not_existing_option=2); 116CREATE INDEX reloptions_test_idx ON reloptions_test (s) 117 WITH (not_existing_ns.fillfactor=2); 118 119-- Check allowed ranges 120CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1); 121CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130); 122 123-- Check ALTER 124ALTER INDEX reloptions_test_idx SET (fillfactor=40); 125SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass; 126 127-- Check ALTER on empty reloption list 128CREATE INDEX reloptions_test_idx3 ON reloptions_test (s); 129ALTER INDEX reloptions_test_idx3 SET (fillfactor=40); 130SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass; 131