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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
|
CREATE TABLE @extschema@.part_config (
parent_table text NOT NULL
, control text NOT NULL
, time_encoder text
, time_decoder text
, partition_interval text NOT NULL
, partition_type text NOT NULL
, premake int NOT NULL DEFAULT 4
, automatic_maintenance text NOT NULL DEFAULT 'on'
, template_table text
, retention text
, retention_schema text
, retention_keep_index boolean NOT NULL DEFAULT true
, retention_keep_table boolean NOT NULL DEFAULT true
, epoch text NOT NULL DEFAULT 'none'
, constraint_cols text[]
, optimize_constraint int NOT NULL DEFAULT 30
, infinite_time_partitions boolean NOT NULL DEFAULT false
, datetime_string text
, jobmon boolean NOT NULL DEFAULT true
, sub_partition_set_full boolean NOT NULL DEFAULT false
, undo_in_progress boolean NOT NULL DEFAULT false
, inherit_privileges boolean DEFAULT false
, constraint_valid boolean DEFAULT true NOT NULL
, ignore_default_data boolean NOT NULL DEFAULT true
, date_trunc_interval text
, maintenance_order int
, retention_keep_publication boolean NOT NULL DEFAULT false
, maintenance_last_run timestamptz
, async_partitioning_in_progress text
, CONSTRAINT part_config_parent_table_pkey PRIMARY KEY (parent_table)
, CONSTRAINT positive_premake_check CHECK (premake > 0)
);
CREATE INDEX part_config_type_idx ON @extschema@.part_config (partition_type);
SELECT pg_catalog.pg_extension_config_dump('@extschema@.part_config'::regclass, '');
-- FK set deferrable because create_parent() & create_sub_parent() inserts to this table before part_config
CREATE TABLE @extschema@.part_config_sub (
sub_parent text
, sub_control text NOT NULL
, sub_time_encoder text
, sub_time_decoder text
, sub_partition_interval text NOT NULL
, sub_partition_type text NOT NULL
, sub_premake int NOT NULL DEFAULT 4
, sub_automatic_maintenance text NOT NULL DEFAULT 'on'
, sub_template_table text
, sub_retention text
, sub_retention_schema text
, sub_retention_keep_index boolean NOT NULL DEFAULT true
, sub_retention_keep_table boolean NOT NULL DEFAULT true
, sub_epoch text NOT NULL DEFAULT 'none'
, sub_constraint_cols text[]
, sub_optimize_constraint int NOT NULL DEFAULT 30
, sub_infinite_time_partitions boolean NOT NULL DEFAULT false
, sub_jobmon boolean NOT NULL DEFAULT true
, sub_inherit_privileges boolean DEFAULT false
, sub_constraint_valid boolean DEFAULT true NOT NULL
, sub_ignore_default_data boolean NOT NULL DEFAULT true
, sub_default_table boolean default true
, sub_date_trunc_interval TEXT
, sub_maintenance_order int
, sub_retention_keep_publication boolean NOT NULL DEFAULT false
, sub_control_not_null boolean DEFAULT true
, CONSTRAINT part_config_sub_pkey PRIMARY KEY (sub_parent)
, CONSTRAINT part_config_sub_sub_parent_fkey FOREIGN KEY (sub_parent) REFERENCES @extschema@.part_config (parent_table) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
, CONSTRAINT positive_premake_check CHECK (sub_premake > 0)
);
SELECT pg_catalog.pg_extension_config_dump('@extschema@.part_config_sub'::regclass, '');
-- Ensure the control column cannot be one of the additional constraint columns.
ALTER TABLE @extschema@.part_config ADD CONSTRAINT control_constraint_col_chk CHECK ((constraint_cols @> ARRAY[control]) <> true);
ALTER TABLE @extschema@.part_config_sub ADD CONSTRAINT control_constraint_col_chk CHECK ((sub_constraint_cols @> ARRAY[sub_control]) <> true);
ALTER TABLE @extschema@.part_config ADD CONSTRAINT retention_schema_not_empty_chk CHECK (retention_schema <> '');
ALTER TABLE @extschema@.part_config_sub ADD CONSTRAINT retention_schema_not_empty_chk CHECK (sub_retention_schema <> '');
/*
* Custom view to help improve privilege lookups for pg_partman.
* information_schema is a performance bottleneck since indexes aren't being used properly.
*/
CREATE OR REPLACE VIEW @extschema@.table_privs AS
SELECT u_grantor.rolname AS grantor,
grantee.rolname AS grantee,
nc.nspname AS table_schema,
c.relname AS table_name,
c.prtype AS privilege_type
FROM (
SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
pg_namespace nc,
pg_roles u_grantor,
(
SELECT oid, rolname FROM pg_roles
UNION ALL
SELECT 0::oid, 'PUBLIC'
) AS grantee (oid, rolname)
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r', 'v', 'p')
AND c.grantee = grantee.oid
AND c.grantor = u_grantor.oid
AND c.prtype IN (SELECT (aclexplode(acldefault('r'::"char", c.relowner))).privilege_type)
AND (pg_has_role(u_grantor.oid, 'USAGE')
OR pg_has_role(grantee.oid, 'USAGE')
OR grantee.rolname = 'PUBLIC' );
-- Put constraint functions & definitions here because having them in a separate file makes the ordering of their creation harder to control. Some require the above tables to exist first.
/*
* Check for valid config values for automatic maintenance
* (not boolean to allow future values)
*/
CREATE FUNCTION @extschema@.check_automatic_maintenance_value (p_automatic_maintenance text) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE
AS $$
DECLARE
v_result boolean;
BEGIN
SELECT p_automatic_maintenance IN ('on', 'off') INTO v_result;
RETURN v_result;
END
$$;
ALTER TABLE @extschema@.part_config
ADD CONSTRAINT part_config_automatic_maintenance_check
CHECK (@extschema@.check_automatic_maintenance_value(automatic_maintenance));
ALTER TABLE @extschema@.part_config_sub
ADD CONSTRAINT part_config_sub_automatic_maintenance_check
CHECK (@extschema@.check_automatic_maintenance_value(sub_automatic_maintenance));
/*
* Check function for config table epoch types
*/
CREATE FUNCTION @extschema@.check_epoch_type (p_type text) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE
SET search_path TO pg_catalog, pg_temp
AS $$
DECLARE
v_result boolean;
BEGIN
SELECT p_type IN ('none', 'seconds', 'milliseconds', 'microseconds', 'nanoseconds') INTO v_result;
RETURN v_result;
END
$$;
ALTER TABLE @extschema@.part_config
ADD CONSTRAINT part_config_epoch_check
CHECK (@extschema@.check_epoch_type(epoch));
ALTER TABLE @extschema@.part_config_sub
ADD CONSTRAINT part_config_sub_epoch_check
CHECK (@extschema@.check_epoch_type(sub_epoch));
/*
* Check for valid config table partition types
*/
-- Allow hash in future update
CREATE FUNCTION @extschema@.check_partition_type (p_type text) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE
SET search_path TO pg_catalog, pg_temp
AS $$
DECLARE
v_result boolean;
BEGIN
SELECT p_type IN ('range', 'list') INTO v_result;
RETURN v_result;
END
$$;
ALTER TABLE @extschema@.part_config
ADD CONSTRAINT part_config_type_check
CHECK (@extschema@.check_partition_type(partition_type));
ALTER TABLE @extschema@.part_config_sub
ADD CONSTRAINT part_config_sub_type_check
CHECK (@extschema@.check_partition_type(sub_partition_type));
|
