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