diff options
Diffstat (limited to 'src/test/regress/expected/reloptions.out')
| -rw-r--r-- | src/test/regress/expected/reloptions.out | 226 |
1 files changed, 226 insertions, 0 deletions
diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out new file mode 100644 index 0000000..b6aef6f --- /dev/null +++ b/src/test/regress/expected/reloptions.out @@ -0,0 +1,226 @@ +-- 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) + +-- Fail 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 namespace 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 improper values +CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=-30.1); +ERROR: value -30.1 out of bounds for option "fillfactor" +DETAIL: Valid values are between "10" and "100". +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 +-- Fail if option is specified twice +CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40); +ERROR: parameter "fillfactor" specified more than once +-- Specifying name only for a non-Boolean option should fail +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) + +-- Set boolean option to true without specifying value +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) + +-- Resetting all values causes the column to become null +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) + +-- RESET fails if a value is specified +ALTER TABLE reloptions_test RESET (fillfactor=12); +ERROR: RESET must not include values for parameters +-- Test vacuum_truncate option +DROP TABLE reloptions_test; +CREATE TEMP TABLE reloptions_test(i INT NOT NULL, j text) + WITH (vacuum_truncate=false, + toast.vacuum_truncate=false, + autovacuum_enabled=false); +SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; + reloptions +-------------------------------------------------- + {vacuum_truncate=false,autovacuum_enabled=false} +(1 row) + +INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); +ERROR: null value in column "i" of relation "reloptions_test" violates not-null constraint +DETAIL: Failing row contains (null, null). +-- Do an aggressive vacuum to prevent page-skipping. +VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) reloptions_test; +SELECT pg_relation_size('reloptions_test') > 0; + ?column? +---------- + t +(1 row) + +SELECT reloptions FROM pg_class WHERE oid = + (SELECT reltoastrelid FROM pg_class + WHERE oid = 'reloptions_test'::regclass); + reloptions +------------------------- + {vacuum_truncate=false} +(1 row) + +ALTER TABLE reloptions_test RESET (vacuum_truncate); +SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; + reloptions +---------------------------- + {autovacuum_enabled=false} +(1 row) + +INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL); +ERROR: null value in column "i" of relation "reloptions_test" violates not-null constraint +DETAIL: Failing row contains (null, null). +-- Do an aggressive vacuum to prevent page-skipping. +VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) reloptions_test; +SELECT pg_relation_size('reloptions_test') = 0; + ?column? +---------- + t +(1 row) + +-- Test toast.* options +DROP TABLE reloptions_test; +CREATE TABLE reloptions_test (s VARCHAR) + WITH (toast.autovacuum_vacuum_cost_delay = 23); +SELECT reltoastrelid as toast_oid + FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset +SELECT reloptions FROM pg_class WHERE oid = :toast_oid; + 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 = :toast_oid; + 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 = :toast_oid; + reloptions +------------ + +(1 row) + +-- Fail on non-existent options in toast namespace +CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42); +ERROR: unrecognized parameter "not_existing_option" +-- Mix 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) + +-- +-- CREATE INDEX, ALTER INDEX for btrees +-- +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 namespace 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_ns.fillfactor=2); +ERROR: unrecognized parameter namespace "not_existing_ns" +-- Check allowed 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 reloption 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) + |