1CREATE TABLE @extschema@.part_config (
2    parent_table text NOT NULL
3    , control text NOT NULL
4    , partition_type text NOT NULL
5    , partition_interval text NOT NULL
6    , constraint_cols text[]
7    , premake int NOT NULL DEFAULT 4
8    , optimize_trigger int NOT NULL DEFAULT 4
9    , optimize_constraint int NOT NULL DEFAULT 30
10    , epoch text NOT NULL DEFAULT 'none'
11    , inherit_fk boolean NOT NULL DEFAULT true
12    , retention text
13    , retention_schema text
14    , retention_keep_table boolean NOT NULL DEFAULT true
15    , retention_keep_index boolean NOT NULL DEFAULT true
16    , infinite_time_partitions boolean NOT NULL DEFAULT false
17    , datetime_string text
18    , automatic_maintenance text NOT NULL DEFAULT 'on'
19    , jobmon boolean NOT NULL DEFAULT true
20    , sub_partition_set_full boolean NOT NULL DEFAULT false
21    , undo_in_progress boolean NOT NULL DEFAULT false
22    , trigger_exception_handling BOOLEAN DEFAULT false
23    , upsert text NOT NULL DEFAULT ''
24    , trigger_return_null boolean NOT NULL DEFAULT true
25    , template_table text
26    , publications text[]
27    , inherit_privileges boolean DEFAULT false
28    , constraint_valid boolean DEFAULT true NOT NULL
29    , subscription_refresh text
30    , CONSTRAINT part_config_parent_table_pkey PRIMARY KEY (parent_table)
31    , CONSTRAINT positive_premake_check CHECK (premake > 0)
32    , CONSTRAINT publications_no_empty_set_chk CHECK (publications <> '{}')
33);
34CREATE INDEX part_config_type_idx ON @extschema@.part_config (partition_type);
35SELECT pg_catalog.pg_extension_config_dump('part_config', '');
36
37
38-- FK set deferrable because create_parent() & create_sub_parent() inserts to this table before part_config
39CREATE TABLE @extschema@.part_config_sub (
40    sub_parent text
41    , sub_partition_type text NOT NULL
42    , sub_control text NOT NULL
43    , sub_partition_interval text NOT NULL
44    , sub_constraint_cols text[]
45    , sub_premake int NOT NULL DEFAULT 4
46    , sub_optimize_trigger int NOT NULL DEFAULT 4
47    , sub_optimize_constraint int NOT NULL DEFAULT 30
48    , sub_epoch text NOT NULL DEFAULT 'none'
49    , sub_inherit_fk boolean NOT NULL DEFAULT true
50    , sub_retention text
51    , sub_retention_schema text
52    , sub_retention_keep_table boolean NOT NULL DEFAULT true
53    , sub_retention_keep_index boolean NOT NULL DEFAULT true
54    , sub_infinite_time_partitions boolean NOT NULL DEFAULT false
55    , sub_automatic_maintenance text NOT NULL DEFAULT 'on'
56    , sub_jobmon boolean NOT NULL DEFAULT true
57    , sub_trigger_exception_handling BOOLEAN DEFAULT false
58    , sub_upsert TEXT NOT NULL DEFAULT ''
59    , sub_trigger_return_null boolean NOT NULL DEFAULT true
60    , sub_template_table text
61    , sub_inherit_privileges boolean DEFAULT false
62    , sub_constraint_valid boolean DEFAULT true NOT NULL
63    , sub_subscription_refresh text
64    , CONSTRAINT part_config_sub_pkey PRIMARY KEY (sub_parent)
65    , 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
66    , CONSTRAINT positive_premake_check CHECK (sub_premake > 0)
67);
68SELECT pg_catalog.pg_extension_config_dump('part_config_sub', '');
69
70-- Ensure the control column cannot be one of the additional constraint columns.
71ALTER TABLE @extschema@.part_config ADD CONSTRAINT control_constraint_col_chk CHECK ((constraint_cols @> ARRAY[control]) <> true);
72ALTER TABLE @extschema@.part_config_sub ADD CONSTRAINT control_constraint_col_chk CHECK ((sub_constraint_cols @> ARRAY[sub_control]) <> true);
73
74ALTER TABLE @extschema@.part_config ADD CONSTRAINT retention_schema_not_empty_chk CHECK (retention_schema <> '');
75ALTER TABLE @extschema@.part_config_sub ADD CONSTRAINT retention_schema_not_empty_chk CHECK (sub_retention_schema <> '');
76
77CREATE TABLE @extschema@.custom_time_partitions (
78    parent_table text NOT NULL
79    , child_table text NOT NULL
80    , partition_range tstzrange NOT NULL
81    , PRIMARY KEY (parent_table, child_table));
82CREATE INDEX custom_time_partitions_partition_range_idx ON @extschema@.custom_time_partitions USING gist (partition_range);
83SELECT pg_catalog.pg_extension_config_dump('custom_time_partitions', '');
84
85/*
86 * Custom view to help improve privilege lookups for pg_partman.
87 * information_schema is a performance bottleneck since indexes aren't being used properly.
88 */
89CREATE VIEW @extschema@.table_privs AS
90    SELECT u_grantor.rolname AS grantor,
91           grantee.rolname AS grantee,
92           nc.nspname AS table_schema,
93           c.relname AS table_name,
94           c.prtype AS privilege_type
95    FROM (
96            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
97         ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
98         pg_namespace nc,
99         pg_roles u_grantor,
100         (
101           SELECT oid, rolname FROM pg_roles
102           UNION ALL
103           SELECT 0::oid, 'PUBLIC'
104         ) AS grantee (oid, rolname)
105    WHERE c.relnamespace = nc.oid
106          AND c.relkind IN ('r', 'v', 'p')
107          AND c.grantee = grantee.oid
108          AND c.grantor = u_grantor.oid
109          AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
110          AND (pg_has_role(u_grantor.oid, 'USAGE')
111               OR pg_has_role(grantee.oid, 'USAGE')
112               OR grantee.rolname = 'PUBLIC' );
113
114-- 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.
115
116/*
117 * Check for valid config values for automatic maintenance
118 * (not boolean to allow future values)
119 */
120CREATE FUNCTION @extschema@.check_automatic_maintenance_value (p_automatic_maintenance text) RETURNS boolean
121    LANGUAGE plpgsql IMMUTABLE
122    AS $$
123DECLARE
124v_result    boolean;
125BEGIN
126    SELECT p_automatic_maintenance IN ('on', 'off') INTO v_result;
127    RETURN v_result;
128END
129$$;
130
131ALTER TABLE @extschema@.part_config
132ADD CONSTRAINT part_config_automatic_maintenance_check
133CHECK (@extschema@.check_automatic_maintenance_value(automatic_maintenance));
134
135ALTER TABLE @extschema@.part_config_sub
136ADD CONSTRAINT part_config_sub_automatic_maintenance_check
137CHECK (@extschema@.check_automatic_maintenance_value(sub_automatic_maintenance));
138
139
140/*
141 * Check function for config table epoch types
142 */
143CREATE FUNCTION @extschema@.check_epoch_type (p_type text) RETURNS boolean
144    LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER
145    SET search_path TO pg_catalog, pg_temp
146    AS $$
147DECLARE
148v_result    boolean;
149BEGIN
150    SELECT p_type IN ('none', 'seconds', 'milliseconds') INTO v_result;
151    RETURN v_result;
152END
153$$;
154
155ALTER TABLE @extschema@.part_config
156ADD CONSTRAINT part_config_epoch_check
157CHECK (@extschema@.check_epoch_type(epoch));
158
159ALTER TABLE @extschema@.part_config_sub
160ADD CONSTRAINT part_config_sub_epoch_check
161CHECK (@extschema@.check_epoch_type(sub_epoch));
162
163
164/*
165 * Check for valid config table partition types
166 */
167CREATE OR REPLACE FUNCTION @extschema@.check_partition_type (p_type text) RETURNS boolean
168    LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER
169    SET search_path TO pg_catalog, pg_temp
170    AS $$
171DECLARE
172v_result    boolean;
173BEGIN
174    SELECT p_type IN ('partman', 'time-custom', 'native') INTO v_result;
175    RETURN v_result;
176END
177$$;
178
179
180ALTER TABLE @extschema@.part_config
181ADD CONSTRAINT part_config_type_check
182CHECK (@extschema@.check_partition_type(partition_type));
183
184ALTER TABLE @extschema@.part_config_sub
185ADD CONSTRAINT part_config_sub_type_check
186CHECK (@extschema@.check_partition_type(sub_partition_type));
187
188
189