blob: 37fbf41f7d5d70f1dea22ee14c5a701c52c1b034 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
-- 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;
-- Fail min/max values check
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2);
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110);
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0);
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0);
-- Fail when option and namespace do not exist
CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2);
CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2);
-- Fail while setting improper values
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30.5);
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string');
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true);
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12);
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5);
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string');
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string');
CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true);
-- Fail if option is specified twice
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40);
-- Specifying name only for a non-Boolean option should fail
CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
-- 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;
-- 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;
-- Check that RESET works well
ALTER TABLE reloptions_test RESET (fillfactor);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
-- 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;
-- RESET fails if a value is specified
ALTER TABLE reloptions_test RESET (fillfactor=12);
-- The OIDS option is not stored as reloption
DROP TABLE reloptions_test;
CREATE TABLE reloptions_test(i INT) WITH (fillfactor=20, oids=true);
SELECT reloptions, relhasoids FROM pg_class WHERE oid = 'reloptions_test'::regclass;
-- 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;
ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
-- Fail on non-existent options in toast namespace
CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42);
-- 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;
SELECT reloptions FROM pg_class WHERE oid = (
SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
--
-- 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;
-- Fail when option and namespace do not exist
CREATE INDEX reloptions_test_idx ON reloptions_test (s)
WITH (not_existing_option=2);
CREATE INDEX reloptions_test_idx ON reloptions_test (s)
WITH (not_existing_ns.fillfactor=2);
-- Check allowed ranges
CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1);
CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130);
-- Check ALTER
ALTER INDEX reloptions_test_idx SET (fillfactor=40);
SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
-- 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;
|
