/* * Function to create a child table in a time-based partition set */ CREATE FUNCTION create_time_partition (p_parent_table text, p_partition_times timestamp[]) RETURNS text LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']; v_analyze boolean := FALSE; v_control text; v_grantees text[]; v_hasoids boolean; v_job_id bigint; v_jobmon boolean; v_jobmon_schema text; v_old_search_path text; v_parent_grant record; v_parent_owner text; v_parent_schema text; v_parent_tablename text; v_partition_name text; v_partition_suffix text; v_parent_tablespace text; v_part_interval interval; v_partition_timestamp_end timestamp; v_partition_timestamp_start timestamp; v_quarter text; v_revoke text[]; v_sql text; v_step_id bigint; v_step_overflow_id bigint; v_tablename text; v_trunc_value text; v_time timestamp; v_type text; v_year text; BEGIN SELECT type , control , part_interval , jobmon INTO v_type , v_control , v_part_interval , v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_table AND (type = 'time-static' OR type = 'time-dynamic' OR type = 'time-custom'); IF NOT FOUND THEN RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table; END IF; IF v_jobmon THEN SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid; IF v_jobmon_schema IS NOT NULL THEN SELECT current_setting('search_path') INTO v_old_search_path; EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_jobmon_schema||''',''false'')'; END IF; END IF; SELECT tableowner, schemaname, tablename, tablespace INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace FROM pg_tables WHERE schemaname ||'.'|| tablename = p_parent_table; FOREACH v_time IN ARRAY p_partition_times LOOP v_partition_suffix := to_char(v_time, 'YYYY'); IF v_part_interval < '1 year' AND v_part_interval <> '1 week' THEN v_partition_suffix := v_partition_suffix ||'_'|| to_char(v_time, 'MM'); IF v_part_interval < '1 month' AND v_part_interval <> '1 week' THEN v_partition_suffix := v_partition_suffix ||'_'|| to_char(v_time, 'DD'); IF v_part_interval < '1 day' THEN v_partition_suffix := v_partition_suffix || '_' || to_char(v_time, 'HH24MI'); IF v_part_interval < '1 minute' THEN v_partition_suffix := v_partition_suffix || to_char(v_time, 'SS'); END IF; -- end < minute IF END IF; -- end < day IF END IF; -- end < month IF END IF; -- end < year IF v_partition_timestamp_start := v_time; BEGIN v_partition_timestamp_end := v_time + v_part_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; IF v_part_interval = '1 week' THEN v_partition_suffix := to_char(v_time, 'IYYY') || 'w' || to_char(v_time, 'IW'); END IF; -- "Q" is ignored in to_timestamp, so handle special case IF v_part_interval = '3 months' AND (v_type = 'time-static' OR v_type = 'time-dynamic') THEN v_year := to_char(v_time, 'YYYY'); v_quarter := to_char(v_time, 'Q'); v_partition_suffix := v_year || 'q' || v_quarter; END IF; v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_parent_schema, v_partition_suffix, TRUE); SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name; IF v_tablename 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_job_id := add_job('PARTMAN CREATE TABLE: '||p_parent_table); v_step_id := add_step(v_job_id, 'Creating new partition '||v_partition_name||' with interval from '||v_partition_timestamp_start||' to '||(v_partition_timestamp_end-'1sec'::interval)); END IF; v_sql := 'CREATE TABLE '||v_partition_name||' (LIKE '||p_parent_table||' INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)'; SELECT relhasoids INTO v_hasoids FROM pg_class WHERE oid::regclass = p_parent_table::regclass; IF v_hasoids IS TRUE THEN v_sql := v_sql || ' WITH (OIDS)'; END IF; EXECUTE v_sql; SELECT tablename INTO v_tablename FROM pg_catalog.pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name; IF v_parent_tablespace IS NOT NULL THEN EXECUTE 'ALTER TABLE '||v_partition_name||' SET TABLESPACE '||v_parent_tablespace; END IF; EXECUTE 'ALTER TABLE '||v_partition_name||' ADD CONSTRAINT '||v_tablename||'_partition_check CHECK ('||v_control||'>='||quote_literal(v_partition_timestamp_start)||' AND '||v_control||'<'||quote_literal(v_partition_timestamp_end)||')'; EXECUTE 'ALTER TABLE '||v_partition_name||' INHERIT '||p_parent_table; -- If custom time, set extra config options. IF v_type = 'time-custom' THEN INSERT INTO @extschema@.custom_time_partitions (parent_table, child_table, partition_range) VALUES ( p_parent_table, v_partition_name, tstzrange(v_partition_timestamp_start, v_partition_timestamp_end, '[)') ); END IF; FOR v_parent_grant IN SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types, grantee FROM information_schema.table_privileges WHERE table_schema ||'.'|| table_name = p_parent_table GROUP BY grantee LOOP EXECUTE 'GRANT '||array_to_string(v_parent_grant.types, ',')||' ON '||v_partition_name||' TO '||v_parent_grant.grantee; SELECT array_agg(r) INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x; IF v_revoke IS NOT NULL THEN EXECUTE 'REVOKE '||array_to_string(v_revoke, ',')||' ON '||v_partition_name||' FROM '||v_parent_grant.grantee||' CASCADE'; END IF; v_grantees := array_append(v_grantees, v_parent_grant.grantee::text); END LOOP; -- Revoke all privileges from roles that have none on the parent IF v_grantees IS NOT NULL THEN SELECT array_agg(r) INTO v_revoke FROM ( SELECT DISTINCT grantee::text AS r FROM information_schema.table_privileges WHERE table_schema ||'.'|| table_name = v_partition_name EXCEPT SELECT unnest(v_grantees)) x; IF v_revoke IS NOT NULL THEN EXECUTE 'REVOKE ALL ON '||v_partition_name||' FROM '||array_to_string(v_revoke, ','); END IF; END IF; EXECUTE 'ALTER TABLE '||v_partition_name||' OWNER TO '||v_parent_owner; IF v_jobmon_schema IS NOT NULL THEN PERFORM update_step(v_step_id, 'OK', 'Done'); 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; END LOOP; IF v_analyze THEN EXECUTE 'ANALYZE '||p_parent_table; END IF; IF v_jobmon_schema IS NOT NULL THEN EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')'; END IF; RETURN v_partition_name; EXCEPTION WHEN OTHERS THEN IF v_jobmon_schema IS NOT NULL THEN IF v_job_id IS NULL THEN EXECUTE 'SELECT '||v_jobmon_schema||'.add_job(''PARTMAN CREATE TABLE: '||p_parent_table||''')' INTO v_job_id; EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before job logging started'')' INTO v_step_id; ELSIF v_step_id IS NULL THEN EXECUTE 'SELECT '||v_jobmon_schema||'.add_step('||v_job_id||', ''EXCEPTION before first step logged'')' INTO v_step_id; END IF; EXECUTE 'SELECT '||v_jobmon_schema||'.update_step('||v_step_id||', ''CRITICAL'', ''ERROR: '||coalesce(SQLERRM,'unknown')||''')'; EXECUTE 'SELECT '||v_jobmon_schema||'.fail_job('||v_job_id||')'; END IF; RAISE EXCEPTION '%', SQLERRM; END $$;