-- Allow tablespace inheritance from the template table in PostgreSQL 10+. Note that PG10 currently completely ignores the TABLESPACE flag to the CREATE TABLE command on a native partition parent table. So a natively partitioned parent can never live in a different tablespace other than the default. Tablespaces must be declared for each child table and pg_partman can now do that using the template table the same way it does for indexes and foreign keys (Github Issue #212). -- Obey the inherit_fk config option for natively partitioned tables. Previously foreign keys would always be inherited even if this was set to false. -- Do not allow empty string value for retention_schema in configuration tables. If this update causes an error related to this, please ensure your retention schema is actually set or leave the column value NULL (Github #214). -- Moved the pg_partman github repository to its own dedicated organization at http://github.com/pgpartman. Please update all links that previously referred to the keithf4 personal repository. 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 <> ''); CREATE OR REPLACE FUNCTION inherit_template_properties (p_parent_table text, p_child_schema text, p_child_tablename text) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_child_relkind char; v_child_schema text; v_child_tablename text; v_fk_list record; v_index_list record; v_inherit_fk boolean; v_parent_oid oid; v_parent_table text; v_sql text; v_template_oid oid; v_template_table text; v_template_tablespace text; BEGIN /* * Function to inherit the properties of the template table to newly created child tables. * Currently used for PostgreSQL 10 to inherit indexes and FKs since that is not natively available */ SELECT parent_table, template_table, inherit_fk INTO v_parent_table, v_template_table, v_inherit_fk FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF v_parent_table IS NULL THEN RAISE EXCEPTION 'Given parent table has no configuration in pg_partman: %', p_parent_table; ELSIF v_template_table IS NULL THEN RAISE EXCEPTION 'No template table set in configuration for given parent table: %', p_parent_table; END IF; SELECT c.oid INTO v_parent_oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = split_part(p_parent_table, '.', 1)::name AND c.relname = split_part(p_parent_table, '.', 2)::name; IF v_parent_oid IS NULL THEN RAISE EXCEPTION 'Unable to find given parent table in system catalogs: %', p_parent_table; END IF; SELECT n.nspname, c.relname, c.relkind INTO v_child_schema, v_child_tablename, v_child_relkind FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = p_child_schema::name AND c.relname = p_child_tablename::name; IF v_child_tablename IS NULL THEN RAISE EXCEPTION 'Unable to find given child table in system catalogs: %.%', v_child_schema, v_child_tablename; END IF; IF v_child_relkind = 'p' THEN -- Subpartitioned parent, do not apply properties RAISE DEBUG 'inherit_template_properties: found given child is subpartition parent, so properties not inherited'; RETURN false; END IF; SELECT c.oid, ts.spcname INTO v_template_oid, v_template_tablespace FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace ts ON c.reltablespace = ts.oid WHERE n.nspname = split_part(v_template_table, '.', 1)::name AND c.relname = split_part(v_template_table, '.', 2)::name; IF v_template_oid IS NULL THEN RAISE EXCEPTION 'Unable to find configured template table in system catalogs: %', v_template_table; END IF; -- Index creation (only required in 10) IF current_setting('server_version_num')::int > 100000 AND current_setting('server_version_num')::int < 110000 THEN FOR v_index_list IN SELECT array_to_string(regexp_matches(pg_get_indexdef(indexrelid), ' USING .*'),',') AS statement , i.indisprimary , ( SELECT array_agg( a.attname ORDER by x.r ) FROM pg_catalog.pg_attribute a JOIN ( SELECT k, row_number() over () as r FROM unnest(i.indkey) k ) as x ON a.attnum = x.k AND a.attrelid = i.indrelid ) AS indkey_names , c.relname AS index_name FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class c ON i.indexrelid = c.oid WHERE i.indrelid = v_template_oid AND i.indisvalid ORDER BY 1 LOOP IF v_index_list.indisprimary THEN v_sql := format('ALTER TABLE %I.%I ADD PRIMARY KEY (%s)' , v_child_schema , v_child_tablename , '"' || array_to_string(v_index_list.indkey_names, '","') || '"'); RAISE DEBUG 'Create pk: %', v_sql; EXECUTE v_sql; ELSE -- statement column should be just the portion of the index definition that defines what it actually is v_sql := format('CREATE INDEX ON %I.%I %s', v_child_schema, v_child_tablename, v_index_list.statement); RAISE DEBUG 'Create index: %', v_sql; EXECUTE v_sql; END IF; END LOOP; END IF; -- End index creation -- Foreign key creation IF v_inherit_fk THEN FOR v_fk_list IN SELECT pg_get_constraintdef(con.oid) AS constraint_def FROM pg_catalog.pg_constraint con JOIN pg_catalog.pg_class c ON con.conrelid = c.oid WHERE c.oid = v_template_oid AND contype = 'f' LOOP v_sql := format('ALTER TABLE %I.%I ADD %s', v_child_schema, v_child_tablename, v_fk_list.constraint_def); RAISE DEBUG 'Create FK: %', v_sql; EXECUTE v_sql; END LOOP; END IF; -- End foreign key creation -- Tablespace inheritance IF v_template_tablespace IS NOT NULL THEN v_sql := format('ALTER TABLE %I.%I SET TABLESPACE %I', v_child_schema, v_child_tablename, v_template_tablespace); EXECUTE v_sql; RAISE DEBUG 'Alter tablespace: %', v_sql; END IF; RETURN true; END $$; CREATE OR REPLACE FUNCTION create_partition_id(p_parent_table text, p_partition_ids bigint[], p_analyze boolean DEFAULT true, p_debug boolean DEFAULT false) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; v_analyze boolean := FALSE; v_control text; v_control_type text; v_exists text; v_grantees text[]; v_hasoids boolean; v_id bigint; v_inherit_fk boolean; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_parent_grant record; v_parent_schema text; v_parent_tablename text; v_parent_tablespace text; v_partition_interval bigint; v_partition_created boolean := false; v_partition_name text; v_partition_type text; v_revoke text; v_row record; v_sql text; v_step_id bigint; v_sub_control text; v_sub_partition_type text; v_sub_id_max bigint; v_sub_id_min bigint; v_template_table text; v_unlogged char; BEGIN /* * Function to create id partitions */ SELECT control , partition_type , partition_interval , inherit_fk , jobmon , template_table INTO v_control , v_partition_type , v_partition_interval , v_inherit_fk , v_jobmon , v_template_table FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; END IF; SELECT n.nspname, c.relname, t.spcname INTO v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid WHERE n.nspname = split_part(p_parent_table, '.', 1)::name AND c.relname = split_part(p_parent_table, '.', 2)::name; SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); IF v_control_type <> 'id' THEN RAISE EXCEPTION 'ERROR: Given parent table is not set up for id/serial partitioning'; END IF; SELECT current_setting('search_path') INTO v_old_search_path; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; IF v_jobmon_schema IS NOT NULL THEN v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); -- Determine if this table is a child of a subpartition parent. If so, get limits of what child tables can be created based on parent suffix SELECT sub_min::bigint, sub_max::bigint INTO v_sub_id_min, v_sub_id_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'id'); IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); END IF; FOREACH v_id IN ARRAY p_partition_ids LOOP -- Do not create the child table if it's outside the bounds of the top parent. IF v_sub_id_min IS NOT NULL THEN IF v_id < v_sub_id_min OR v_id > v_sub_id_max THEN CONTINUE; END IF; END IF; v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_id::text, TRUE); -- If child table already exists, skip creation -- Have to check pg_class because if subpartitioned, table will not be in pg_tables SELECT c.relname INTO v_exists FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = v_parent_schema::name AND c.relname = v_partition_name::name; IF v_exists IS NOT NULL THEN CONTINUE; END IF; -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped v_analyze := TRUE; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Creating new partition '||v_partition_name||' with interval from '||v_id||' to '||(v_id + v_partition_interval)-1); END IF; SELECT relpersistence INTO v_unlogged FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename::name AND n.nspname = v_parent_schema::name; v_sql := 'CREATE'; IF v_unlogged = 'u' THEN v_sql := v_sql || ' UNLOGGED'; END IF; -- Close parentheses on LIKE are below due to differing requirements of native subpartitioning v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS ' , v_parent_schema , v_partition_name , v_parent_schema , v_parent_tablename); SELECT sub_partition_type, sub_control INTO v_sub_partition_type, v_sub_control FROM @extschema@.part_config_sub WHERE sub_parent = p_parent_table; IF v_sub_partition_type = 'native' THEN -- NOTE: Need to handle this differently when index inheritance is supported natively -- Cannot include indexes since they cannot exist on native parents. v_sql := v_sql || format(') PARTITION BY RANGE (%I) ', v_sub_control); ELSE v_sql := v_sql || format(' INCLUDING INDEXES) ', v_sub_control); END IF; SELECT relhasoids INTO v_hasoids FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename::name AND n.nspname = v_parent_schema::name; IF v_hasoids IS TRUE THEN v_sql := v_sql || ' WITH (OIDS)'; END IF; EXECUTE v_sql; IF v_partition_type = 'native' THEN IF v_template_table IS NOT NULL THEN PERFORM @extschema@.inherit_template_properties(p_parent_table, v_parent_schema, v_partition_name); END IF; EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' , v_parent_schema , v_parent_tablename , v_parent_schema , v_partition_name , v_id , v_id + v_partition_interval); ELSE -- Handled in inherit_template_properties for native because CREATE TABLE ignores TABLESPACE flag for native partition parents IF v_parent_tablespace IS NOT NULL THEN EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); END IF; EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %s AND %I < %s )' , v_parent_schema , v_partition_name , v_partition_name||'_partition_check' , v_control , v_id , v_control , v_id + v_partition_interval); EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I', v_parent_schema, v_partition_name, v_parent_schema, v_parent_tablename); -- Indexes cannot be created on the parent, so clustering cannot be used for native yet. PERFORM @extschema@.apply_cluster(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name); -- Foreign keys to other tables not supported on native parent tables IF v_inherit_fk THEN PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); END IF; END IF; -- NOTE: Privileges currently not automatically inherited for native PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; -- Will only loop once and only if sub_partitioning is actually configured -- This seemed easier than assigning a bunch of variables then doing an IF condition FOR v_row IN SELECT sub_parent , sub_partition_type , sub_control , sub_partition_interval , sub_constraint_cols , sub_premake , sub_optimize_trigger , sub_optimize_constraint , sub_epoch , sub_inherit_fk , sub_retention , sub_retention_schema , sub_retention_keep_table , sub_retention_keep_index , sub_automatic_maintenance , sub_infinite_time_partitions , sub_jobmon , sub_trigger_exception_handling , sub_template_table FROM @extschema@.part_config_sub WHERE sub_parent = p_parent_table LOOP IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, 'Subpartitioning '||v_partition_name); END IF; v_sql := format('SELECT @extschema@.create_parent( p_parent_table := %L , p_control := %L , p_type := %L , p_interval := %L , p_constraint_cols := %L , p_premake := %L , p_automatic_maintenance := %L , p_inherit_fk := %L , p_epoch := %L , p_template_table := %L , p_jobmon := %L )' , v_parent_schema||'.'||v_partition_name , v_row.sub_control , v_row.sub_partition_type , v_row.sub_partition_interval , v_row.sub_constraint_cols , v_row.sub_premake , v_row.sub_automatic_maintenance , v_row.sub_inherit_fk , v_row.sub_epoch , v_row.sub_template_table , v_row.sub_jobmon); EXECUTE v_sql; UPDATE @extschema@.part_config SET retention_schema = v_row.sub_retention_schema , retention_keep_table = v_row.sub_retention_keep_table , retention_keep_index = v_row.sub_retention_keep_index , optimize_trigger = v_row.sub_optimize_trigger , optimize_constraint = v_row.sub_optimize_constraint , infinite_time_partitions = v_row.sub_infinite_time_partitions , trigger_exception_handling = v_row.sub_trigger_exception_handling WHERE parent_table = v_parent_schema||'.'||v_partition_name; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END LOOP; -- end sub partitioning LOOP -- Manage additonal constraints if set PERFORM @extschema@.apply_constraints(p_parent_table, p_job_id := v_job_id, p_debug := p_debug); v_partition_created := true; END LOOP; -- v_analyze is a local check if a new table is made. -- p_analyze is a parameter to say whether to run the analyze at all. Used by create_parent() to avoid long exclusive lock or run_maintenence() to avoid long creation runs. IF v_analyze AND p_analyze THEN IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); END IF; EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN IF v_partition_created = false THEN v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s', p_parent_table)); PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; PERFORM close_job(v_job_id); END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RETURN v_partition_created; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$; CREATE OR REPLACE FUNCTION create_partition_time(p_parent_table text, p_partition_times timestamptz[], p_analyze boolean DEFAULT true, p_debug boolean DEFAULT false) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE ex_context text; ex_detail text; ex_hint text; ex_message text; v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; v_analyze boolean := FALSE; v_control text; v_control_type text; v_datetime_string text; v_epoch text; v_exists smallint; v_grantees text[]; v_hasoids boolean; v_inherit_fk boolean; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_new_search_path text := '@extschema@,pg_temp'; v_old_search_path text; v_parent_grant record; v_parent_schema text; v_parent_tablename text; v_part_col text; v_partition_created boolean := false; v_partition_name text; v_partition_suffix text; v_parent_tablespace text; v_partition_expression text; v_partition_interval interval; v_partition_timestamp_end timestamptz; v_partition_timestamp_start timestamptz; v_quarter text; v_revoke text; v_row record; v_sql text; v_step_id bigint; v_step_overflow_id bigint; v_sub_control text; v_sub_parent text; v_sub_partition_type text; v_sub_timestamp_max timestamptz; v_sub_timestamp_min timestamptz; v_template_table text; v_trunc_value text; v_time timestamptz; v_partition_type text; v_unlogged char; v_year text; BEGIN /* * Function to create a child table in a time-based partition set */ SELECT partition_type , control , partition_interval , epoch , inherit_fk , jobmon , datetime_string , template_table INTO v_partition_type , v_control , v_partition_interval , v_epoch , v_inherit_fk , v_jobmon , v_datetime_string , v_template_table FROM @extschema@.part_config WHERE parent_table = p_parent_table; IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; END IF; SELECT n.nspname, c.relname, t.spcname INTO v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid WHERE n.nspname = split_part(p_parent_table, '.', 1)::name AND c.relname = split_part(p_parent_table, '.', 2)::name; SELECT general_type INTO v_control_type FROM @extschema@.check_control_type(v_parent_schema, v_parent_tablename, v_control); IF v_control_type <> 'time' THEN IF (v_control_type = 'id' AND v_epoch = 'none') OR v_control_type <> 'id' THEN RAISE EXCEPTION 'Cannot run on partition set without time based control column or epoch flag set with an id column. Found control: %, epoch: %', v_control_type, v_epoch; END IF; END IF; SELECT current_setting('search_path') INTO v_old_search_path; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon'::name AND e.extnamespace = n.oid; IF v_jobmon_schema IS NOT NULL THEN v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp'; END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false'); -- Determine if this table is a child of a subpartition parent. If so, get limits of what child tables can be created based on parent suffix SELECT sub_min::timestamptz, sub_max::timestamptz INTO v_sub_timestamp_min, v_sub_timestamp_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'time'); IF v_jobmon_schema IS NOT NULL THEN v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table)); END IF; v_partition_expression := CASE WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control) WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control) ELSE format('%I', v_control) END; IF p_debug THEN RAISE NOTICE 'create_partition_time: v_partition_expression: %', v_partition_expression; END IF; FOREACH v_time IN ARRAY p_partition_times LOOP v_partition_timestamp_start := v_time; BEGIN v_partition_timestamp_end := v_time + v_partition_interval; EXCEPTION WHEN datetime_field_overflow THEN RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. Child partition creation after time % skipped', v_time; v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.'); PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_time||' skipped'); CONTINUE; END; -- Do not create the child table if it's outside the bounds of the top parent. IF v_sub_timestamp_min IS NOT NULL THEN IF v_time < v_sub_timestamp_min OR v_time > v_sub_timestamp_max THEN CONTINUE; END IF; END IF; -- This suffix generation code is in partition_data_time() as well v_partition_suffix := to_char(v_time, v_datetime_string); v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE); -- Check if child exists. SELECT count(*) INTO v_exists FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = v_parent_schema::name AND c.relname = v_partition_name::name; IF v_exists > 0 THEN CONTINUE; END IF; -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped v_analyze := TRUE; IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Creating new partition %s.%s with interval from %s to %s' , v_parent_schema , v_partition_name , v_partition_timestamp_start , v_partition_timestamp_end-'1sec'::interval)); END IF; SELECT relpersistence INTO v_unlogged FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename::name AND n.nspname = v_parent_schema::name; v_sql := 'CREATE'; IF v_unlogged = 'u' THEN v_sql := v_sql || ' UNLOGGED'; END IF; -- Close parentheses on LIKE are below due to differing requirements of native subpartitioning v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS ' , v_parent_schema , v_partition_name , v_parent_schema , v_parent_tablename); SELECT sub_partition_type, sub_control INTO v_sub_partition_type, v_sub_control FROM @extschema@.part_config_sub WHERE sub_parent = p_parent_table; IF v_sub_partition_type = 'native' THEN -- NOTE: Need to handle this differently when index inheritance is supported natively -- Cannot include indexes since they cannot exist on native parents v_sql := v_sql || format(') PARTITION BY RANGE (%I) ', v_sub_control); ELSE v_sql := v_sql || format(' INCLUDING INDEXES) ', v_sub_control); END IF; SELECT relhasoids INTO v_hasoids FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE c.relname = v_parent_tablename::name AND n.nspname = v_parent_schema::name; IF v_hasoids IS TRUE THEN v_sql := v_sql || ' WITH (OIDS)'; END IF; IF p_debug THEN RAISE NOTICE 'create_partition_time v_sql: %', v_sql; END IF; EXECUTE v_sql; IF v_partition_type = 'native' THEN IF v_template_table IS NOT NULL THEN PERFORM @extschema@.inherit_template_properties(p_parent_table, v_parent_schema, v_partition_name); END IF; IF v_epoch = 'none' THEN -- Attach with normal, time-based values for native constraint EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' , v_parent_schema , v_parent_tablename , v_parent_schema , v_partition_name , v_partition_timestamp_start , v_partition_timestamp_end); ELSE -- Must attach with integer based values for native constraint and epoch IF v_epoch = 'seconds' THEN EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' , v_parent_schema , v_parent_tablename , v_parent_schema , v_partition_name , EXTRACT('epoch' FROM v_partition_timestamp_start) , EXTRACT('epoch' FROM v_partition_timestamp_end)); ELSIF v_epoch = 'milliseconds' THEN EXECUTE format('ALTER TABLE %I.%I ATTACH PARTITION %I.%I FOR VALUES FROM (%L) TO (%L)' , v_parent_schema , v_parent_tablename , v_parent_schema , v_partition_name , EXTRACT('epoch' FROM v_partition_timestamp_start) * 1000 , EXTRACT('epoch' FROM v_partition_timestamp_end) * 1000); END IF; -- Create secondary, time-based constraint since native's constraint is already integer based EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%s >= %L AND %4$s < %6$L)' , v_parent_schema , v_partition_name , v_partition_name||'_partition_check' , v_partition_expression , v_partition_timestamp_start , v_partition_timestamp_end); END IF; ELSE -- Handled in inherit_template_properties for native because CREATE TABLE ignores TABLESPACE flag for native partition parents IF v_parent_tablespace IS NOT NULL THEN EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace); END IF; -- Non-native always gets time-based constraint EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%s >= %L AND %4$s < %6$L)' , v_parent_schema , v_partition_name , v_partition_name||'_partition_check' , v_partition_expression , v_partition_timestamp_start , v_partition_timestamp_end); IF v_epoch = 'seconds' THEN -- Non-native needs secondary, integer based constraint for epoch EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)' , v_parent_schema , v_partition_name , v_partition_name||'_partition_int_check' , v_control , EXTRACT('epoch' from v_partition_timestamp_start) , v_control , EXTRACT('epoch' from v_partition_timestamp_end) ); ELSIF v_epoch = 'milliseconds' THEN EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)' , v_parent_schema , v_partition_name , v_partition_name||'_partition_int_check' , v_control , EXTRACT('epoch' from v_partition_timestamp_start) * 1000 , v_control , EXTRACT('epoch' from v_partition_timestamp_end) * 1000); END IF; EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I' , v_parent_schema , v_partition_name , v_parent_schema , v_parent_tablename); -- If custom time, set extra config options. IF v_partition_type = 'time-custom' THEN INSERT INTO @extschema@.custom_time_partitions (parent_table, child_table, partition_range) VALUES ( p_parent_table, v_parent_schema||'.'||v_partition_name, tstzrange(v_partition_timestamp_start, v_partition_timestamp_end, '[)') ); END IF; -- Indexes cannot be created on the parent, so clustering cannot be used for native yet. PERFORM @extschema@.apply_cluster(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name); -- Foreign keys to other tables not supported in native IF v_inherit_fk THEN PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id); END IF; END IF; -- end native check -- NOTE: Privileges currently not automatically inherited for native PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; -- Will only loop once and only if sub_partitioning is actually configured -- This seemed easier than assigning a bunch of variables then doing an IF condition FOR v_row IN SELECT sub_parent , sub_partition_type , sub_control , sub_partition_interval , sub_constraint_cols , sub_premake , sub_optimize_trigger , sub_optimize_constraint , sub_epoch , sub_inherit_fk , sub_retention , sub_retention_schema , sub_retention_keep_table , sub_retention_keep_index , sub_automatic_maintenance , sub_infinite_time_partitions , sub_jobmon , sub_trigger_exception_handling , sub_template_table FROM @extschema@.part_config_sub WHERE sub_parent = p_parent_table LOOP IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Subpartitioning %s.%s', v_parent_schema, v_partition_name)); END IF; v_sql := format('SELECT @extschema@.create_parent( p_parent_table := %L , p_control := %L , p_type := %L , p_interval := %L , p_constraint_cols := %L , p_premake := %L , p_automatic_maintenance := %L , p_inherit_fk := %L , p_epoch := %L , p_template_table := %L , p_jobmon := %L )' , v_parent_schema||'.'||v_partition_name , v_row.sub_control , v_row.sub_partition_type , v_row.sub_partition_interval , v_row.sub_constraint_cols , v_row.sub_premake , v_row.sub_automatic_maintenance , v_row.sub_inherit_fk , v_row.sub_epoch , v_row.sub_template_table , v_row.sub_jobmon); IF p_debug THEN RAISE NOTICE 'create_partition_time (create_parent loop): %', v_sql; END IF; EXECUTE v_sql; UPDATE @extschema@.part_config SET retention_schema = v_row.sub_retention_schema , retention_keep_table = v_row.sub_retention_keep_table , retention_keep_index = v_row.sub_retention_keep_index , optimize_trigger = v_row.sub_optimize_trigger , optimize_constraint = v_row.sub_optimize_constraint , infinite_time_partitions = v_row.sub_infinite_time_partitions , trigger_exception_handling = v_row.sub_trigger_exception_handling WHERE parent_table = v_parent_schema||'.'||v_partition_name; END LOOP; -- end sub partitioning LOOP -- Manage additonal constraints if set PERFORM @extschema@.apply_constraints(p_parent_table, p_job_id := v_job_id, p_debug := p_debug); v_partition_created := true; END LOOP; -- v_analyze is a local check if a new table is made. -- p_analyze is a parameter to say whether to run the analyze at all. Used by create_parent() to avoid long exclusive lock or run_maintenence() to avoid long creation runs. IF v_analyze AND p_analyze THEN IF v_jobmon_schema IS NOT NULL THEN v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table)); END IF; EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename); IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; END IF; IF v_jobmon_schema IS NOT NULL THEN IF v_partition_created = false THEN v_step_id := add_step(v_job_id, format('No partitions created for partition set: %s. Attempted intervals: %s', p_parent_table, p_partition_times)); PERFORM update_step(v_step_id, 'OK', 'Done'); END IF; IF v_step_overflow_id IS NOT NULL THEN PERFORM fail_job(v_job_id); ELSE PERFORM close_job(v_job_id); END IF; END IF; EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false'); RETURN v_partition_created; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT, ex_context = PG_EXCEPTION_CONTEXT, ex_detail = PG_EXCEPTION_DETAIL, ex_hint = PG_EXCEPTION_HINT; IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id; EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id; END IF; EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown')); EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id); END IF; RAISE EXCEPTION '% CONTEXT: % DETAIL: % HINT: %', ex_message, ex_context, ex_detail, ex_hint; END $$;