· 9 years ago · Dec 07, 2016, 11:58 PM
1-- Allow option for millisecond epoch partitioning. (Github Issue #150, Also requested back in #75)
2 -- The "epoch" parameter to any function and the column in the part_config table has changed from boolean to text and only accepts the values: "seconds", "milliseconds", "none".
3 -- The default is "none".
4 -- Any current epoch partition sets will automatically have this value set to "seconds".
5-- TODO Change internal view table_privs to use information_schema instead of pg catalog. Allows more flexibility under limited privileges (Amazon RDS). (Push Request #151)
6
7CREATE TEMP TABLE partman_preserve_privs_temp (statement text);
8
9INSERT INTO partman_preserve_privs_temp
10SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.check_subpart_sameconfig(p_parent_table text) TO '||array_to_string(array_agg(grantee::text), ',')||';'
11FROM information_schema.routine_privileges
12WHERE routine_schema = '@extschema@'
13AND routine_name = 'check_subpart_sameconfig';
14
15INSERT INTO partman_preserve_privs_temp
16SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_parent(text, text, text, text, text[], int, boolean, text, boolean, text, text, boolean, boolean, boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';'
17FROM information_schema.routine_privileges
18WHERE routine_schema = '@extschema@'
19AND routine_name = 'create_parent';
20
21INSERT INTO partman_preserve_privs_temp
22SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_sub_parent(text, text, text, text, text[], int, text, boolean, text, text, boolean boolean, boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';'
23FROM information_schema.routine_privileges
24WHERE routine_schema = '@extschema@'
25AND routine_name = 'create_sub_parent';
26
27DROP FUNCTION @extschema@.check_subpart_sameconfig(p_parent_table text);
28DROP FUNCTION @extschema@.create_parent(text, text, text, text, text[], int, boolean, text, boolean, boolean, text, boolean, boolean, boolean);
29DROP FUNCTION @extschema@.create_sub_parent(text, text, text, text, text[], int, text, boolean, boolean, text, boolean, boolean, boolean);
30
31--TODO Add to tables.sql file
32ALTER TABLE @extschema@.part_config ALTER COLUMN epoch TYPE text;
33UPDATE @extschema@.part_config SET epoch = 'seconds' WHERE epoch = 'true';
34UPDATE @extschema@.part_config SET epoch = 'none' WHERE epoch = 'false';
35ALTER TABLE @extschema@.part_config ALTER COLUMN epoch SET DEFAULT 'none';
36
37ALTER TABLE @extschema@.part_config_sub ALTER COLUMN sub_epoch TYPE text;
38UPDATE @extschema@.part_config_sub SET sub_epoch = 'seconds' WHERE sub_epoch = 'true';
39UPDATE @extschema@.part_config_sub SET sub_epoch = 'none' WHERE sub_epoch = 'false';
40ALTER TABLE @extschema@.part_config_sub ALTER COLUMN sub_epoch SET DEFAULT 'none';
41
42/*
43 * Check function for config table epoch types
44 */
45CREATE FUNCTION @extschema@.check_epoch_type (p_type text) RETURNS boolean
46 LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER
47 AS $$
48DECLARE
49v_result boolean;
50BEGIN
51 SELECT p_type IN ('none', 'seconds', 'milliseconds') INTO v_result;
52 RETURN v_result;
53END
54$$;
55
56ALTER TABLE @extschema@.part_config
57ADD CONSTRAINT part_config_epoch_check
58CHECK (@extschema@.check_epoch_type(epoch));
59
60ALTER TABLE @extschema@.part_config_sub
61ADD CONSTRAINT part_config_sub_epoch_check
62CHECK (@extschema@.check_epoch_type(sub_epoch));
63
64
65/*
66 * Check for consistent data in part_config_sub table. Was unable to get this working properly as either a constraint or trigger.
67 * Would either delay raising an error until the next write (which I cannot predict) or disallow future edits to update a sub-partition set's configuration.
68 * This is called by run_maintainance() and at least provides a consistent way to check that I know will run.
69 * If anyone can get a working constraint/trigger, please help!
70*/
71CREATE FUNCTION @extschema@.check_subpart_sameconfig(p_parent_table text)
72 RETURNS TABLE (sub_partition_type text
73 , sub_control text
74 , sub_partition_interval text
75 , sub_constraint_cols text[]
76 , sub_premake int
77 , sub_inherit_fk boolean
78 , sub_retention text
79 , sub_retention_schema text
80 , sub_retention_keep_table boolean
81 , sub_retention_keep_index boolean
82 , sub_use_run_maintenance boolean
83 , sub_epoch text
84 , sub_optimize_trigger int
85 , sub_optimize_constraint int
86 , sub_infinite_time_partitions boolean
87 , sub_jobmon boolean
88 , sub_trigger_exception_handling boolean
89 , sub_upsert text
90 , sub_trigger_return_null boolean)
91 LANGUAGE sql STABLE SECURITY DEFINER
92 SET search_path = @extschema@,pg_temp
93AS $$
94
95 WITH parent_info AS (
96 SELECT c1.oid
97 FROM pg_catalog.pg_class c1
98 JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid
99 WHERE n1.nspname = split_part(p_parent_table, '.', 1)::name
100 AND c1.relname = split_part(p_parent_table, '.', 2)::name
101 )
102 , child_tables AS (
103 SELECT n.nspname||'.'||c.relname AS tablename
104 FROM pg_catalog.pg_inherits h
105 JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid
106 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
107 JOIN parent_info pi ON h.inhparent = pi.oid
108 )
109 -- Column order here must match the RETURNS TABLE definition
110 SELECT DISTINCT a.sub_partition_type
111 , a.sub_control
112 , a.sub_partition_interval
113 , a.sub_constraint_cols
114 , a.sub_premake
115 , a.sub_inherit_fk
116 , a.sub_retention
117 , a.sub_retention_schema
118 , a.sub_retention_keep_table
119 , a.sub_retention_keep_index
120 , a.sub_use_run_maintenance
121 , a.sub_epoch
122 , a.sub_optimize_trigger
123 , a.sub_optimize_constraint
124 , a.sub_infinite_time_partitions
125 , a.sub_jobmon
126 , a.sub_trigger_exception_handling
127 , a.sub_upsert
128 , a.sub_trigger_return_null
129 FROM @extschema@.part_config_sub a
130 JOIN child_tables b on a.sub_parent = b.tablename;
131$$;
132
133
134/*
135 * Create the trigger function for the parent table of a time-based partition set
136 */
137CREATE OR REPLACE FUNCTION create_function_time(p_parent_table text, p_job_id bigint DEFAULT NULL) RETURNS void
138 LANGUAGE plpgsql SECURITY DEFINER
139 AS $$
140DECLARE
141
142ex_context text;
143ex_detail text;
144ex_hint text;
145ex_message text;
146v_control text;
147v_count int;
148v_current_partition_name text;
149v_current_partition_timestamp timestamptz;
150v_datetime_string text;
151v_epoch text;
152v_final_partition_timestamp timestamptz;
153v_function_name text;
154v_job_id bigint;
155v_jobmon boolean;
156v_jobmon_schema text;
157v_new_search_path text := '@extschema@,pg_temp';
158v_old_search_path text;
159v_new_length int;
160v_next_partition_name text;
161v_next_partition_timestamp timestamptz;
162v_parent_schema text;
163v_parent_tablename text;
164v_partition_expression text;
165v_partition_interval interval;
166v_prev_partition_name text;
167v_prev_partition_timestamp timestamptz;
168v_step_id bigint;
169v_trig_func text;
170v_optimize_trigger int;
171v_trigger_exception_handling boolean;
172v_trigger_return_null boolean;
173v_type text;
174v_upsert text;
175
176BEGIN
177
178SELECT partition_type
179 , partition_interval::interval
180 , epoch
181 , control
182 , optimize_trigger
183 , datetime_string
184 , jobmon
185 , trigger_exception_handling
186 , upsert
187 , trigger_return_null
188INTO v_type
189 , v_partition_interval
190 , v_epoch
191 , v_control
192 , v_optimize_trigger
193 , v_datetime_string
194 , v_jobmon
195 , v_trigger_exception_handling
196 , v_upsert
197 , v_trigger_return_null
198FROM @extschema@.part_config
199WHERE parent_table = p_parent_table
200AND (partition_type = 'time' OR partition_type = 'time-custom');
201
202IF NOT FOUND THEN
203 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table;
204END IF;
205
206SELECT current_setting('search_path') INTO v_old_search_path;
207IF v_jobmon THEN
208 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;
209 IF v_jobmon_schema IS NOT NULL THEN
210 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp';
211 END IF;
212END IF;
213EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
214
215IF v_jobmon_schema IS NOT NULL THEN
216 IF p_job_id IS NULL THEN
217 v_job_id := add_job(format('PARTMAN CREATE FUNCTION: %s', p_parent_table));
218 ELSE
219 v_job_id = p_job_id;
220 END IF;
221 v_step_id := add_step(v_job_id, format('Creating partition function for table %s', p_parent_table));
222END IF;
223
224SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename
225FROM pg_catalog.pg_tables
226WHERE schemaname = split_part(p_parent_table, '.', 1)::name
227AND tablename = split_part(p_parent_table, '.', 2)::name;
228
229v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE);
230
231v_partition_expression := CASE
232 WHEN v_epoch = 'seconds' THEN format('to_timestamp(NEW.%I)', v_control)
233 WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((NEW.%I/1000)::float)', v_control)
234 ELSE format('NEW.%I', v_control)
235END;
236
237IF v_type = 'time' THEN
238 v_trig_func := format('CREATE OR REPLACE FUNCTION %I.%I() RETURNS trigger LANGUAGE plpgsql AS $t$
239 DECLARE
240 v_count int;
241 v_partition_name text;
242 v_partition_timestamp timestamptz;
243 BEGIN
244 IF TG_OP = ''INSERT'' THEN
245 '
246 , v_parent_schema
247 , v_function_name);
248
249 CASE
250 WHEN v_partition_interval = '15 mins' THEN
251 v_trig_func := v_trig_func||format('v_partition_timestamp := date_trunc(''hour'', %s) +
252 ''15min''::interval * floor(date_part(''minute'', %1$s) / 15.0);' , v_partition_expression);
253 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) +
254 '15min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 15.0);
255 WHEN v_partition_interval = '30 mins' THEN
256 v_trig_func := v_trig_func||format('v_partition_timestamp := date_trunc(''hour'', %s) +
257 ''30min''::interval * floor(date_part(''minute'', %1$s) / 30.0);' , v_partition_expression);
258 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP) +
259 '30min'::interval * floor(date_part('minute', CURRENT_TIMESTAMP) / 30.0);
260 WHEN v_partition_interval = '1 hour' THEN
261 v_trig_func := v_trig_func||format('v_partition_timestamp := date_trunc(''hour'', %s);', v_partition_expression);
262 v_current_partition_timestamp := date_trunc('hour', CURRENT_TIMESTAMP);
263 WHEN v_partition_interval = '1 day' THEN
264 v_trig_func := v_trig_func||format('v_partition_timestamp := date_trunc(''day'', %s);', v_partition_expression);
265 v_current_partition_timestamp := date_trunc('day', CURRENT_TIMESTAMP);
266 WHEN v_partition_interval = '1 week' THEN
267 v_trig_func := v_trig_func||format('v_partition_timestamp := date_trunc(''week'', %s);', v_partition_expression);
268 v_current_partition_timestamp := date_trunc('week', CURRENT_TIMESTAMP);
269 WHEN v_partition_interval = '1 month' THEN
270 v_trig_func := v_trig_func||format('v_partition_timestamp := date_trunc(''month'', %s);', v_partition_expression);
271 v_current_partition_timestamp := date_trunc('month', CURRENT_TIMESTAMP);
272 WHEN v_partition_interval = '3 months' THEN
273 v_trig_func := v_trig_func||format('v_partition_timestamp := date_trunc(''quarter'', %s);', v_partition_expression);
274 v_current_partition_timestamp := date_trunc('quarter', CURRENT_TIMESTAMP);
275 WHEN v_partition_interval = '1 year' THEN
276 v_trig_func := v_trig_func||format('v_partition_timestamp := date_trunc(''year'', %s);', v_partition_expression);
277 v_current_partition_timestamp := date_trunc('year', CURRENT_TIMESTAMP);
278 END CASE;
279
280 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, to_char(v_current_partition_timestamp, v_datetime_string), TRUE);
281 v_next_partition_timestamp := v_current_partition_timestamp + v_partition_interval::interval;
282
283 v_trig_func := v_trig_func ||format('
284 IF %s >= %L AND %1$s < %3$L THEN '
285 , v_partition_expression
286 , v_current_partition_timestamp
287 , v_next_partition_timestamp);
288
289 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_current_partition_name::name;
290 IF v_count > 0 THEN
291 v_trig_func := v_trig_func || format('
292 INSERT INTO %I.%I VALUES (NEW.*) %s; ', v_parent_schema, v_current_partition_name, v_upsert);
293 ELSE
294 v_trig_func := v_trig_func || '
295 -- Child table for current values does not exist in this partition set, so write to parent
296 RETURN NEW;';
297 END IF;
298
299 FOR i IN 1..v_optimize_trigger LOOP
300 v_prev_partition_timestamp := v_current_partition_timestamp - (v_partition_interval::interval * i);
301 v_next_partition_timestamp := v_current_partition_timestamp + (v_partition_interval::interval * i);
302 v_final_partition_timestamp := v_next_partition_timestamp + (v_partition_interval::interval);
303 v_prev_partition_name := @extschema@.check_name_length(v_parent_tablename, to_char(v_prev_partition_timestamp, v_datetime_string), TRUE);
304 v_next_partition_name := @extschema@.check_name_length(v_parent_tablename, to_char(v_next_partition_timestamp, v_datetime_string), TRUE);
305
306 -- Check that child table exist before making a rule to insert to them.
307 -- Handles optimize_trigger being larger than premake (to go back in time further) and edge case of changing optimize_trigger immediately after running create_parent().
308 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_prev_partition_name::name;
309 IF v_count > 0 THEN
310 v_trig_func := v_trig_func ||format('
311 ELSIF %s >= %L AND %1$s < %3$L THEN
312 INSERT INTO %I.%I VALUES (NEW.*) %s;'
313 , v_partition_expression
314 , v_prev_partition_timestamp
315 , v_prev_partition_timestamp + v_partition_interval::interval
316 , v_parent_schema
317 , v_prev_partition_name
318 , v_upsert);
319 END IF;
320 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_next_partition_name::name;
321 IF v_count > 0 THEN
322 v_trig_func := v_trig_func ||format('
323 ELSIF %s >= %L AND %1$s < %3$L THEN
324 INSERT INTO %I.%I VALUES (NEW.*) %s;'
325 , v_partition_expression
326 , v_next_partition_timestamp
327 , v_final_partition_timestamp
328 , v_parent_schema
329 , v_next_partition_name
330 , v_upsert);
331 END IF;
332
333 END LOOP;
334
335 v_trig_func := v_trig_func||format('
336 ELSE
337 v_partition_name := @extschema@.check_name_length(%L, to_char(v_partition_timestamp, %L), TRUE);
338 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = %L::name AND tablename = v_partition_name::name;
339 IF v_count > 0 THEN
340 EXECUTE format(''INSERT INTO %%I.%%I VALUES($1.*) %s'', %L, v_partition_name) USING NEW;
341 ELSE
342 RETURN NEW;
343 END IF;
344 END IF;'
345 , v_parent_tablename
346 , v_datetime_string
347 , v_parent_schema
348 , v_upsert
349 , v_parent_schema);
350
351 v_trig_func := v_trig_func ||'
352 END IF;';
353
354 IF v_trigger_return_null IS TRUE THEN
355 v_trig_func := v_trig_func ||'
356 RETURN NULL;';
357 ELSE
358 v_trig_func := v_trig_func ||'
359 RETURN NEW;';
360 END IF;
361
362 IF v_trigger_exception_handling THEN
363 v_trig_func := v_trig_func ||'
364 EXCEPTION WHEN OTHERS THEN
365 RAISE WARNING ''pg_partman insert into child table failed, row inserted into parent (%.%). ERROR: %'', TG_TABLE_SCHEMA, TG_TABLE_NAME, COALESCE(SQLERRM, ''unknown'');
366 RETURN NEW;';
367 END IF;
368 v_trig_func := v_trig_func ||'
369 END $t$;';
370
371 EXECUTE v_trig_func;
372
373 IF v_jobmon_schema IS NOT NULL THEN
374 PERFORM update_step(v_step_id, 'OK', format('Added function for current time interval: %s to %s'
375 , v_current_partition_timestamp
376 , v_final_partition_timestamp-'1sec'::interval));
377 END IF;
378
379ELSIF v_type = 'time-custom' THEN
380
381 v_trig_func := format('CREATE OR REPLACE FUNCTION %I.%I() RETURNS trigger LANGUAGE plpgsql AS $t$
382 DECLARE
383 v_child_schemaname text;
384 v_child_table text;
385 v_child_tablename text;
386 v_upsert text;
387 BEGIN
388 '
389 , v_parent_schema
390 , v_function_name);
391
392 v_trig_func := v_trig_func || format('
393 SELECT c.child_table, p.upsert INTO v_child_table, v_upsert
394 FROM @extschema@.custom_time_partitions c
395 JOIN @extschema@.part_config p ON c.parent_table = p.parent_table
396 WHERE c.partition_range @> %s
397 AND c.parent_table = %L;'
398 , v_partition_expression
399 , v_parent_schema||'.'||v_parent_tablename);
400
401 v_trig_func := v_trig_func || '
402 SELECT schemaname, tablename INTO v_child_schemaname, v_child_tablename
403 FROM pg_catalog.pg_tables
404 WHERE schemaname = split_part(v_child_table, ''.'', 1)::name
405 AND tablename = split_part(v_child_table, ''.'', 2)::name;
406 IF v_child_schemaname IS NOT NULL AND v_child_tablename IS NOT NULL THEN
407 EXECUTE format(''INSERT INTO %I.%I VALUES ($1.*) %s'', v_child_schemaname, v_child_tablename, v_upsert) USING NEW;
408 ELSE
409 RETURN NEW;
410 END IF;';
411
412 IF v_trigger_return_null IS TRUE THEN
413 v_trig_func := v_trig_func ||'
414 RETURN NULL;';
415 ELSE
416 v_trig_func := v_trig_func ||'
417 RETURN NEW;';
418 END IF;
419
420 IF v_trigger_exception_handling THEN
421 v_trig_func := v_trig_func ||'
422 EXCEPTION WHEN OTHERS THEN
423 RAISE WARNING ''pg_partman insert into child table failed, row inserted into parent (%.%). ERROR: %'', TG_TABLE_SCHEMA, TG_TABLE_NAME, COALESCE(SQLERRM, ''unknown'');
424 RETURN NEW;';
425 END IF;
426 v_trig_func := v_trig_func ||'
427 END $t$;';
428
429 EXECUTE v_trig_func;
430
431 IF v_jobmon_schema IS NOT NULL THEN
432 PERFORM update_step(v_step_id, 'OK', format('Added function for custom time table: %s', p_parent_table));
433 END IF;
434
435ELSE
436 RAISE EXCEPTION 'ERROR: Invalid time partitioning type given: %', v_type;
437END IF;
438
439IF v_jobmon_schema IS NOT NULL THEN
440 PERFORM close_job(v_job_id);
441END IF;
442
443EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
444
445EXCEPTION
446 WHEN OTHERS THEN
447 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
448 ex_context = PG_EXCEPTION_CONTEXT,
449 ex_detail = PG_EXCEPTION_DETAIL,
450 ex_hint = PG_EXCEPTION_HINT;
451 IF v_jobmon_schema IS NOT NULL THEN
452 IF v_job_id IS NULL THEN
453 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE FUNCTION: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id;
454 EXECUTE format('SELECT %I.add_step(%s, ''Partition function maintenance for table %s failed'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id;
455 ELSIF v_step_id IS NULL THEN
456 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
457 END IF;
458 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
459 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
460 END IF;
461 RAISE EXCEPTION '%
462CONTEXT: %
463DETAIL: %
464HINT: %', ex_message, ex_context, ex_detail, ex_hint;
465END
466$$;
467
468
469/*
470 * Function to turn a table into the parent of a partition set
471 */
472CREATE FUNCTION create_parent(
473 p_parent_table text
474 , p_control text
475 , p_type text
476 , p_interval text
477 , p_constraint_cols text[] DEFAULT NULL
478 , p_premake int DEFAULT 4
479 , p_use_run_maintenance boolean DEFAULT NULL
480 , p_start_partition text DEFAULT NULL
481 , p_inherit_fk boolean DEFAULT true
482 , p_epoch text DEFAULT 'none'
483 , p_upsert text DEFAULT ''
484 , p_trigger_return_null boolean DEFAULT true
485 , p_jobmon boolean DEFAULT true
486 , p_debug boolean DEFAULT false)
487RETURNS boolean
488 LANGUAGE plpgsql SECURITY DEFINER
489 AS $$
490DECLARE
491
492ex_context text;
493ex_detail text;
494ex_hint text;
495ex_message text;
496v_base_timestamp timestamptz;
497v_count int := 1;
498v_datetime_string text;
499v_higher_parent_schema text := split_part(p_parent_table, '.', 1);
500v_higher_parent_table text := split_part(p_parent_table, '.', 2);
501v_id_interval bigint;
502v_job_id bigint;
503v_jobmon_schema text;
504v_last_partition_created boolean;
505v_max bigint;
506v_notnull boolean;
507v_new_search_path text := '@extschema@,pg_temp';
508v_old_search_path text;
509v_parent_partition_id bigint;
510v_parent_partition_timestamp timestamptz;
511v_parent_schema text;
512v_parent_tablename text;
513v_partition_time timestamptz;
514v_partition_time_array timestamptz[];
515v_partition_id_array bigint[];
516v_row record;
517v_run_maint boolean;
518v_sql text;
519v_start_time timestamptz;
520v_starting_partition_id bigint;
521v_step_id bigint;
522v_step_overflow_id bigint;
523v_sub_parent text;
524v_success boolean := false;
525v_time_interval interval;
526v_top_datetime_string text;
527v_top_parent_schema text := split_part(p_parent_table, '.', 1);
528v_top_parent_table text := split_part(p_parent_table, '.', 2);
529
530BEGIN
531
532IF position('.' in p_parent_table) = 0 THEN
533 RAISE EXCEPTION 'Parent table must be schema qualified';
534END IF;
535
536IF p_upsert <> '' AND @extschema@.check_version('9.5.0') = 'false' THEN
537 RAISE EXCEPTION 'INSERT ... ON CONFLICT (UPSERT) feature is only supported in PostgreSQL 9.5 and later';
538END IF;
539
540SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename
541FROM pg_catalog.pg_tables
542WHERE schemaname = split_part(p_parent_table, '.', 1)::name
543AND tablename = split_part(p_parent_table, '.', 2)::name;
544 IF v_parent_tablename IS NULL THEN
545 RAISE EXCEPTION 'Unable to find given parent table in system catalogs. Please create parent table first: %', p_parent_table;
546 END IF;
547
548SELECT attnotnull INTO v_notnull
549FROM pg_catalog.pg_attribute a
550JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
551JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
552WHERE c.relname = v_parent_tablename::name
553AND n.nspname = v_parent_schema::name
554AND a.attname = p_control::name;
555 IF v_notnull = false OR v_notnull IS NULL THEN
556 RAISE EXCEPTION 'Control column given (%) for parent table (%) does not exist or must be set to NOT NULL', p_control, p_parent_table;
557 END IF;
558
559IF p_type = 'id' AND p_epoch <> 'none' THEN
560 RAISE EXCEPTION 'p_epoch can only be used with time-based partitioning';
561END IF;
562
563IF NOT @extschema@.check_partition_type(p_type) THEN
564 RAISE EXCEPTION '% is not a valid partitioning type', p_type;
565END IF;
566
567SELECT current_setting('search_path') INTO v_old_search_path;
568IF p_jobmon THEN
569 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;
570 IF v_jobmon_schema IS NOT NULL THEN
571 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp';
572 END IF;
573END IF;
574EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
575
576IF p_use_run_maintenance IS NOT NULL THEN
577 IF p_use_run_maintenance IS FALSE AND (p_type = 'time' OR p_type = 'time-custom') THEN
578 RAISE EXCEPTION 'p_run_maintenance cannot be set to false for time based partitioning';
579 END IF;
580 v_run_maint := p_use_run_maintenance;
581ELSIF p_type = 'time' OR p_type = 'time-custom' THEN
582 v_run_maint := TRUE;
583ELSIF p_type = 'id' THEN
584 v_run_maint := FALSE;
585ELSE
586 RAISE EXCEPTION 'use_run_maintenance value cannot be set NULL';
587END IF;
588
589EXECUTE format('LOCK TABLE %I.%I IN ACCESS EXCLUSIVE MODE', v_parent_schema, v_parent_tablename);
590
591IF v_jobmon_schema IS NOT NULL THEN
592 v_job_id := add_job(format('PARTMAN SETUP PARENT: %s', p_parent_table));
593 v_step_id := add_step(v_job_id, format('Creating initial partitions on new parent table: %s', p_parent_table));
594END IF;
595
596-- If this parent table has siblings that are also partitioned (subpartitions), ensure this parent gets added to part_config_sub table so future maintenance will subpartition it
597-- Just doing in a loop to avoid having to assign a bunch of variables (should only run once, if at all; constraint should enforce only one value.)
598FOR v_row IN
599 WITH parent_table AS (
600 SELECT h.inhparent AS parent_oid
601 FROM pg_catalog.pg_inherits h
602 JOIN pg_catalog.pg_class c ON h.inhrelid = c.oid
603 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
604 WHERE c.relname = v_parent_tablename::name
605 AND n.nspname = v_parent_schema::name
606 ), sibling_children AS (
607 SELECT i.inhrelid::regclass::text AS tablename
608 FROM pg_inherits i
609 JOIN parent_table p ON i.inhparent = p.parent_oid
610 )
611 SELECT DISTINCT sub_partition_type
612 , sub_control
613 , sub_partition_interval
614 , sub_constraint_cols
615 , sub_premake
616 , sub_inherit_fk
617 , sub_retention
618 , sub_retention_schema
619 , sub_retention_keep_table
620 , sub_retention_keep_index
621 , sub_use_run_maintenance
622 , sub_epoch
623 , sub_optimize_trigger
624 , sub_optimize_constraint
625 , sub_infinite_time_partitions
626 , sub_jobmon
627 , sub_trigger_exception_handling
628 , sub_upsert
629 , sub_trigger_return_null
630 FROM @extschema@.part_config_sub a
631 JOIN sibling_children b on a.sub_parent = b.tablename LIMIT 1
632LOOP
633 INSERT INTO @extschema@.part_config_sub (
634 sub_parent
635 , sub_partition_type
636 , sub_control
637 , sub_partition_interval
638 , sub_constraint_cols
639 , sub_premake
640 , sub_inherit_fk
641 , sub_retention
642 , sub_retention_schema
643 , sub_retention_keep_table
644 , sub_retention_keep_index
645 , sub_use_run_maintenance
646 , sub_epoch
647 , sub_optimize_trigger
648 , sub_optimize_constraint
649 , sub_infinite_time_partitions
650 , sub_jobmon
651 , sub_trigger_exception_handling
652 , sub_upsert
653 , sub_trigger_return_null)
654 VALUES (
655 p_parent_table
656 , v_row.sub_partition_type
657 , v_row.sub_control
658 , v_row.sub_partition_interval
659 , v_row.sub_constraint_cols
660 , v_row.sub_premake
661 , v_row.sub_inherit_fk
662 , v_row.sub_retention
663 , v_row.sub_retention_schema
664 , v_row.sub_retention_keep_table
665 , v_row.sub_retention_keep_index
666 , v_row.sub_use_run_maintenance
667 , v_row.sub_epoch
668 , v_row.sub_optimize_trigger
669 , v_row.sub_optimize_constraint
670 , v_row.sub_infinite_time_partitions
671 , v_row.sub_jobmon
672 , v_row.sub_trigger_exception_handling
673 , v_row.sub_upsert
674 , v_row.sub_trigger_return_null);
675END LOOP;
676
677IF p_type = 'time' OR p_type = 'time-custom' THEN
678
679 CASE
680 WHEN p_interval = 'yearly' THEN
681 v_time_interval := '1 year';
682 WHEN p_interval = 'quarterly' THEN
683 v_time_interval := '3 months';
684 WHEN p_interval = 'monthly' THEN
685 v_time_interval := '1 month';
686 WHEN p_interval = 'weekly' THEN
687 v_time_interval := '1 week';
688 WHEN p_interval = 'daily' THEN
689 v_time_interval := '1 day';
690 WHEN p_interval = 'hourly' THEN
691 v_time_interval := '1 hour';
692 WHEN p_interval = 'half-hour' THEN
693 v_time_interval := '30 mins';
694 WHEN p_interval = 'quarter-hour' THEN
695 v_time_interval := '15 mins';
696 ELSE
697 IF p_type <> 'time-custom' THEN
698 RAISE EXCEPTION 'Must use a predefined time interval if not using type "time-custom". See documentation.';
699 END IF;
700 v_time_interval := p_interval::interval;
701 IF v_time_interval < '1 second'::interval THEN
702 RAISE EXCEPTION 'Partitioning interval must be 1 second or greater';
703 END IF;
704 END CASE;
705
706 -- First partition is either the min premake or p_start_partition
707 v_start_time := COALESCE(p_start_partition::timestamptz, CURRENT_TIMESTAMP - (v_time_interval * p_premake));
708
709 IF v_time_interval >= '1 year' THEN
710 v_base_timestamp := date_trunc('year', v_start_time);
711 IF v_time_interval >= '10 years' THEN
712 v_base_timestamp := date_trunc('decade', v_start_time);
713 IF v_time_interval >= '100 years' THEN
714 v_base_timestamp := date_trunc('century', v_start_time);
715 IF v_time_interval >= '1000 years' THEN
716 v_base_timestamp := date_trunc('millennium', v_start_time);
717 END IF; -- 1000
718 END IF; -- 100
719 END IF; -- 10
720 END IF; -- 1
721
722 v_datetime_string := 'YYYY';
723 IF v_time_interval < '1 year' THEN
724 IF p_interval = 'quarterly' THEN
725 v_base_timestamp := date_trunc('quarter', v_start_time);
726 v_datetime_string = 'YYYY"q"Q';
727 ELSE
728 v_base_timestamp := date_trunc('month', v_start_time);
729 v_datetime_string := v_datetime_string || '_MM';
730 END IF;
731 IF v_time_interval < '1 month' THEN
732 IF p_interval = 'weekly' THEN
733 v_base_timestamp := date_trunc('week', v_start_time);
734 v_datetime_string := 'IYYY"w"IW';
735 ELSE
736 v_base_timestamp := date_trunc('day', v_start_time);
737 v_datetime_string := v_datetime_string || '_DD';
738 END IF;
739 IF v_time_interval < '1 day' THEN
740 v_base_timestamp := date_trunc('hour', v_start_time);
741 v_datetime_string := v_datetime_string || '_HH24MI';
742 IF v_time_interval < '1 minute' THEN
743 v_base_timestamp := date_trunc('minute', v_start_time);
744 v_datetime_string := v_datetime_string || 'SS';
745 END IF; -- minute
746 END IF; -- day
747 END IF; -- month
748 END IF; -- year
749
750 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp);
751 LOOP
752 -- If current loop value is less than or equal to the value of the max premake, add time to array.
753 IF (v_base_timestamp + (v_time_interval * v_count)) < (CURRENT_TIMESTAMP + (v_time_interval * p_premake)) THEN
754 BEGIN
755 v_partition_time := (v_base_timestamp + (v_time_interval * v_count))::timestamptz;
756 v_partition_time_array := array_append(v_partition_time_array, v_partition_time);
757 EXCEPTION WHEN datetime_field_overflow THEN
758 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range.
759 Child partition creation after time % skipped', v_partition_time;
760 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.');
761 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_partition_time||' skipped');
762 CONTINUE;
763 END;
764 ELSE
765 EXIT; -- all needed partitions added to array. Exit the loop.
766 END IF;
767 v_count := v_count + 1;
768 END LOOP;
769
770 INSERT INTO @extschema@.part_config (
771 parent_table
772 , partition_type
773 , partition_interval
774 , epoch
775 , control
776 , premake
777 , constraint_cols
778 , datetime_string
779 , use_run_maintenance
780 , inherit_fk
781 , jobmon
782 , upsert
783 , trigger_return_null)
784 VALUES (
785 p_parent_table
786 , p_type
787 , v_time_interval
788 , p_epoch
789 , p_control
790 , p_premake
791 , p_constraint_cols
792 , v_datetime_string
793 , v_run_maint
794 , p_inherit_fk
795 , p_jobmon
796 , p_upsert
797 , p_trigger_return_null);
798
799 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false);
800
801 IF v_last_partition_created = false THEN
802 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent
803 -- First see if this parent is a subpartition managed by pg_partman
804 WITH top_oid AS (
805 SELECT i.inhparent AS top_parent_oid
806 FROM pg_catalog.pg_inherits i
807 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid
808 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
809 WHERE c.relname = v_parent_tablename::name
810 AND n.nspname = v_parent_schema::name
811 ) SELECT n.nspname, c.relname
812 INTO v_top_parent_schema, v_top_parent_table
813 FROM pg_catalog.pg_class c
814 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
815 JOIN top_oid t ON c.oid = t.top_parent_oid
816 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname;
817 IF v_top_parent_table IS NOT NULL THEN
818 -- If so create the lowest possible partition that is within the boundary of the parent
819 SELECT child_start_time INTO v_parent_partition_timestamp FROM @extschema@.show_partition_info(p_parent_table, p_parent_table := v_top_parent_schema||'.'||v_top_parent_table);
820 IF v_base_timestamp >= v_parent_partition_timestamp THEN
821 WHILE v_base_timestamp >= v_parent_partition_timestamp LOOP
822 v_base_timestamp := v_base_timestamp - v_time_interval;
823 END LOOP;
824 v_base_timestamp := v_base_timestamp + v_time_interval; -- add one back since while loop set it one lower than is needed
825 ELSIF v_base_timestamp < v_parent_partition_timestamp THEN
826 WHILE v_base_timestamp < v_parent_partition_timestamp LOOP
827 v_base_timestamp := v_base_timestamp + v_time_interval;
828 END LOOP;
829 -- Don't need to remove one since new starting time will fit in top parent interval
830 END IF;
831 v_partition_time_array := NULL;
832 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp);
833 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false);
834 ELSE
835 -- Currently unknown edge case if code gets here
836 RAISE EXCEPTION 'No child tables created. Unexpected edge case encountered. Please report this error to author with conditions that led to it.';
837 END IF;
838 END IF;
839
840 IF v_jobmon_schema IS NOT NULL THEN
841 PERFORM update_step(v_step_id, 'OK', format('Time partitions premade: %s', p_premake));
842 END IF;
843END IF;
844
845IF p_type = 'id' THEN
846 v_id_interval := p_interval::bigint;
847 IF v_id_interval < 10 THEN
848 RAISE EXCEPTION 'Interval for serial partitioning must be greater than or equal to 10';
849 END IF;
850
851 -- Check if parent table is a subpartition of an already existing id partition set managed by pg_partman.
852 WHILE v_higher_parent_table IS NOT NULL LOOP -- initially set in DECLARE
853 WITH top_oid AS (
854 SELECT i.inhparent AS top_parent_oid
855 FROM pg_catalog.pg_inherits i
856 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid
857 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
858 WHERE n.nspname = v_higher_parent_schema::name
859 AND c.relname = v_higher_parent_table::name
860 ) SELECT n.nspname, c.relname
861 INTO v_higher_parent_schema, v_higher_parent_table
862 FROM pg_catalog.pg_class c
863 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
864 JOIN top_oid t ON c.oid = t.top_parent_oid
865 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname
866 WHERE p.partition_type = 'id';
867
868 IF v_higher_parent_table IS NOT NULL THEN
869 -- v_top_parent initially set in DECLARE
870 v_top_parent_schema := v_higher_parent_schema;
871 v_top_parent_table := v_higher_parent_table;
872 END IF;
873 END LOOP;
874
875 -- If custom start partition is set, use that.
876 -- If custom start is not set and there is already data, start partitioning with the highest current value and ensure it's grabbed from highest top parent table
877 IF p_start_partition IS NOT NULL THEN
878 v_max := p_start_partition::bigint;
879 ELSE
880 v_sql := format('SELECT COALESCE(max(%I)::bigint, 0) FROM %I.%I LIMIT 1'
881 , p_control
882 , v_top_parent_schema
883 , v_top_parent_table);
884 EXECUTE v_sql INTO v_max;
885 END IF;
886 v_starting_partition_id := v_max - (v_max % v_id_interval);
887 FOR i IN 0..p_premake LOOP
888 -- Only make previous partitions if ID value is less than the starting value and positive (and custom start partition wasn't set)
889 IF p_start_partition IS NULL AND
890 (v_starting_partition_id - (v_id_interval*i)) > 0 AND
891 (v_starting_partition_id - (v_id_interval*i)) < v_starting_partition_id
892 THEN
893 v_partition_id_array = array_append(v_partition_id_array, (v_starting_partition_id - v_id_interval*i));
894 END IF;
895 v_partition_id_array = array_append(v_partition_id_array, (v_id_interval*i) + v_starting_partition_id);
896 END LOOP;
897
898 INSERT INTO @extschema@.part_config (
899 parent_table
900 , partition_type
901 , partition_interval
902 , control
903 , premake
904 , constraint_cols
905 , use_run_maintenance
906 , inherit_fk
907 , jobmon
908 , upsert
909 , trigger_return_null)
910 VALUES (
911 p_parent_table
912 , p_type
913 , v_id_interval
914 , p_control
915 , p_premake
916 , p_constraint_cols
917 , v_run_maint
918 , p_inherit_fk
919 , p_jobmon
920 , p_upsert
921 , p_trigger_return_null);
922 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false);
923 IF v_last_partition_created = false THEN
924 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent
925 -- See if it's actually a subpartition of a parent id partition
926 WITH top_oid AS (
927 SELECT i.inhparent AS top_parent_oid
928 FROM pg_catalog.pg_inherits i
929 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid
930 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
931 WHERE c.relname = v_parent_tablename::name
932 AND n.nspname = v_parent_schema::name
933 ) SELECT n.nspname||'.'||c.relname
934 INTO v_top_parent_table
935 FROM pg_catalog.pg_class c
936 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
937 JOIN top_oid t ON c.oid = t.top_parent_oid
938 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname
939 WHERE p.partition_type = 'id';
940 IF v_top_parent_table IS NOT NULL THEN
941 -- Create the lowest possible partition that is within the boundary of the parent
942 SELECT child_start_id INTO v_parent_partition_id FROM @extschema@.show_partition_info(p_parent_table, p_parent_table := v_top_parent_table);
943 IF v_starting_partition_id >= v_parent_partition_id THEN
944 WHILE v_starting_partition_id >= v_parent_partition_id LOOP
945 v_starting_partition_id := v_starting_partition_id - v_id_interval;
946 END LOOP;
947 v_starting_partition_id := v_starting_partition_id + v_id_interval; -- add one back since while loop set it one lower than is needed
948 ELSIF v_starting_partition_id < v_parent_partition_id THEN
949 WHILE v_starting_partition_id < v_parent_partition_id LOOP
950 v_starting_partition_id := v_starting_partition_id + v_id_interval;
951 END LOOP;
952 -- Don't need to remove one since new starting id will fit in top parent interval
953 END IF;
954 v_partition_id_array = NULL;
955 v_partition_id_array = array_append(v_partition_id_array, v_starting_partition_id);
956 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false);
957 ELSE
958 -- Currently unknown edge case if code gets here
959 RAISE EXCEPTION 'No child tables created. Unexpected edge case encountered. Please report this error to author with conditions that led to it.';
960 END IF;
961 END IF;
962END IF;
963
964IF v_jobmon_schema IS NOT NULL THEN
965 v_step_id := add_step(v_job_id, 'Creating partition function');
966END IF;
967IF p_type = 'time' OR p_type = 'time-custom' THEN
968 PERFORM @extschema@.create_function_time(p_parent_table, v_job_id);
969 IF v_jobmon_schema IS NOT NULL THEN
970 PERFORM update_step(v_step_id, 'OK', 'Time function created');
971 END IF;
972ELSIF p_type = 'id' THEN
973 PERFORM @extschema@.create_function_id(p_parent_table, v_job_id);
974 IF v_jobmon_schema IS NOT NULL THEN
975 PERFORM update_step(v_step_id, 'OK', 'ID function created');
976 END IF;
977END IF;
978
979IF v_jobmon_schema IS NOT NULL THEN
980 v_step_id := add_step(v_job_id, 'Creating partition trigger');
981END IF;
982PERFORM @extschema@.create_trigger(p_parent_table);
983
984IF v_jobmon_schema IS NOT NULL THEN
985 PERFORM update_step(v_step_id, 'OK', 'Done');
986 IF v_step_overflow_id IS NOT NULL THEN
987 PERFORM fail_job(v_job_id);
988 ELSE
989 PERFORM close_job(v_job_id);
990 END IF;
991END IF;
992
993EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
994
995v_success := true;
996
997RETURN v_success;
998
999EXCEPTION
1000 WHEN OTHERS THEN
1001 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
1002 ex_context = PG_EXCEPTION_CONTEXT,
1003 ex_detail = PG_EXCEPTION_DETAIL,
1004 ex_hint = PG_EXCEPTION_HINT;
1005 IF v_jobmon_schema IS NOT NULL THEN
1006 IF v_job_id IS NULL THEN
1007 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE PARENT: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id;
1008 EXECUTE format('SELECT %I.add_step(%s, ''Partition creation for table '||p_parent_table||' failed'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id;
1009 ELSIF v_step_id IS NULL THEN
1010 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
1011 END IF;
1012 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
1013 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
1014 END IF;
1015 RAISE EXCEPTION '%
1016CONTEXT: %
1017DETAIL: %
1018HINT: %', ex_message, ex_context, ex_detail, ex_hint;
1019END
1020$$;
1021
1022
1023/*
1024 * Function to create a child table in a time-based partition set
1025 */
1026CREATE OR REPLACE FUNCTION create_partition_time(p_parent_table text, p_partition_times timestamptz[], p_analyze boolean DEFAULT true, p_debug boolean DEFAULT false)
1027RETURNS boolean
1028 LANGUAGE plpgsql SECURITY DEFINER
1029 AS $$
1030DECLARE
1031
1032ex_context text;
1033ex_detail text;
1034ex_hint text;
1035ex_message text;
1036v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER'];
1037v_analyze boolean := FALSE;
1038v_control text;
1039v_datetime_string text;
1040v_exists text;
1041v_epoch text;
1042v_grantees text[];
1043v_hasoids boolean;
1044v_inherit_fk boolean;
1045v_job_id bigint;
1046v_jobmon boolean;
1047v_jobmon_schema text;
1048v_new_search_path text := '@extschema@,pg_temp';
1049v_old_search_path text;
1050v_parent_grant record;
1051v_parent_owner text;
1052v_parent_schema text;
1053v_parent_tablename text;
1054v_partition_created boolean := false;
1055v_partition_name text;
1056v_partition_suffix text;
1057v_parent_tablespace text;
1058v_partition_expression text;
1059v_partition_interval interval;
1060v_partition_timestamp_end timestamptz;
1061v_partition_timestamp_start timestamptz;
1062v_quarter text;
1063v_revoke text;
1064v_row record;
1065v_sql text;
1066v_step_id bigint;
1067v_step_overflow_id bigint;
1068v_sub_timestamp_max timestamptz;
1069v_sub_timestamp_min timestamptz;
1070v_trunc_value text;
1071v_time timestamptz;
1072v_type text;
1073v_unlogged char;
1074v_year text;
1075
1076BEGIN
1077
1078SELECT partition_type
1079 , control
1080 , partition_interval
1081 , epoch
1082 , inherit_fk
1083 , jobmon
1084 , datetime_string
1085INTO v_type
1086 , v_control
1087 , v_partition_interval
1088 , v_epoch
1089 , v_inherit_fk
1090 , v_jobmon
1091 , v_datetime_string
1092FROM @extschema@.part_config
1093WHERE parent_table = p_parent_table
1094AND (partition_type = 'time' OR partition_type = 'time-custom');
1095
1096IF NOT FOUND THEN
1097 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table;
1098END IF;
1099
1100SELECT current_setting('search_path') INTO v_old_search_path;
1101IF v_jobmon THEN
1102 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;
1103 IF v_jobmon_schema IS NOT NULL THEN
1104 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp';
1105 END IF;
1106END IF;
1107EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
1108
1109-- 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
1110SELECT sub_min::timestamptz, sub_max::timestamptz INTO v_sub_timestamp_min, v_sub_timestamp_max FROM @extschema@.check_subpartition_limits(p_parent_table, 'time');
1111
1112SELECT tableowner, schemaname, tablename, tablespace
1113INTO v_parent_owner, v_parent_schema, v_parent_tablename, v_parent_tablespace
1114FROM pg_catalog.pg_tables
1115WHERE schemaname = split_part(p_parent_table, '.', 1)::name
1116AND tablename = split_part(p_parent_table, '.', 2)::name;
1117
1118IF v_jobmon_schema IS NOT NULL THEN
1119 v_job_id := add_job(format('PARTMAN CREATE TABLE: %s', p_parent_table));
1120END IF;
1121
1122v_partition_expression := CASE
1123 WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control)
1124 WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control)
1125 ELSE format('%I', v_control)
1126END;
1127IF p_debug THEN
1128 RAISE NOTICE 'create_partition_time: v_partition_expression: %', v_partition_expression;
1129END IF;
1130
1131FOREACH v_time IN ARRAY p_partition_times LOOP
1132 v_partition_timestamp_start := v_time;
1133 BEGIN
1134 v_partition_timestamp_end := v_time + v_partition_interval;
1135 EXCEPTION WHEN datetime_field_overflow THEN
1136 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range.
1137 Child partition creation after time % skipped', v_time;
1138 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.');
1139 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_time||' skipped');
1140 CONTINUE;
1141 END;
1142
1143 -- Do not create the child table if it's outside the bounds of the top parent.
1144 IF v_sub_timestamp_min IS NOT NULL THEN
1145 IF v_time < v_sub_timestamp_min OR v_time > v_sub_timestamp_max THEN
1146 CONTINUE;
1147 END IF;
1148 END IF;
1149
1150 -- This suffix generation code is in partition_data_time() as well
1151 v_partition_suffix := to_char(v_time, v_datetime_string);
1152 v_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE);
1153 SELECT tablename INTO v_exists FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_partition_name::name;
1154 IF v_exists IS NOT NULL THEN
1155 CONTINUE;
1156 END IF;
1157
1158 -- Ensure analyze is run if a new partition is created. Otherwise if one isn't, will be false and analyze will be skipped
1159 v_analyze := TRUE;
1160
1161 IF v_jobmon_schema IS NOT NULL THEN
1162 v_step_id := add_step(v_job_id, format('Creating new partition %s.%s with interval from %s to %s'
1163 , v_parent_schema
1164 , v_partition_name
1165 , v_partition_timestamp_start
1166 , v_partition_timestamp_end-'1sec'::interval));
1167 END IF;
1168
1169 SELECT relpersistence INTO v_unlogged
1170 FROM pg_catalog.pg_class c
1171 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
1172 WHERE c.relname = v_parent_tablename::name
1173 AND n.nspname = v_parent_schema::name;
1174 v_sql := 'CREATE';
1175 IF v_unlogged = 'u' THEN
1176 v_sql := v_sql || ' UNLOGGED';
1177 END IF;
1178 v_sql := v_sql || format(' TABLE %I.%I (LIKE %I.%I INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS)'
1179 , v_parent_schema
1180 , v_partition_name
1181 , v_parent_schema
1182 , v_parent_tablename);
1183 SELECT relhasoids INTO v_hasoids
1184 FROM pg_catalog.pg_class c
1185 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
1186 WHERE c.relname = v_parent_tablename::name
1187 AND n.nspname = v_parent_schema::name;
1188 IF v_hasoids IS TRUE THEN
1189 v_sql := v_sql || ' WITH (OIDS)';
1190 END IF;
1191 EXECUTE v_sql;
1192 IF v_parent_tablespace IS NOT NULL THEN
1193 EXECUTE format('ALTER TABLE %I.%I SET TABLESPACE %I', v_parent_schema, v_partition_name, v_parent_tablespace);
1194 END IF;
1195 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%s >= %L AND %4$s < %6$L)'
1196 , v_parent_schema
1197 , v_partition_name
1198 , v_partition_name||'_partition_check'
1199 , v_partition_expression
1200 , v_partition_timestamp_start
1201 , v_partition_timestamp_end);
1202 IF v_epoch = 'seconds' THEN
1203 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)'
1204 , v_parent_schema
1205 , v_partition_name
1206 , v_partition_name||'_partition_int_check'
1207 , v_control
1208 , EXTRACT('epoch' from v_partition_timestamp_start)
1209 , v_control
1210 , EXTRACT('epoch' from v_partition_timestamp_end) );
1211 ELSIF v_epoch = 'milliseconds' THEN
1212 EXECUTE format('ALTER TABLE %I.%I ADD CONSTRAINT %I CHECK (%I >= %L AND %I < %L)'
1213 , v_parent_schema
1214 , v_partition_name
1215 , v_partition_name||'_partition_int_check'
1216 , v_control
1217 , EXTRACT('epoch' from v_partition_timestamp_start) * 1000
1218 , v_control
1219 , EXTRACT('epoch' from v_partition_timestamp_end) * 1000);
1220 END IF;
1221
1222 EXECUTE format('ALTER TABLE %I.%I INHERIT %I.%I'
1223 , v_parent_schema
1224 , v_partition_name
1225 , v_parent_schema
1226 , v_parent_tablename);
1227
1228 -- If custom time, set extra config options.
1229 IF v_type = 'time-custom' THEN
1230 INSERT INTO @extschema@.custom_time_partitions (parent_table, child_table, partition_range)
1231 VALUES ( p_parent_table, v_parent_schema||'.'||v_partition_name, tstzrange(v_partition_timestamp_start, v_partition_timestamp_end, '[)') );
1232 END IF;
1233
1234 PERFORM @extschema@.apply_privileges(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name, v_job_id);
1235
1236 PERFORM @extschema@.apply_cluster(v_parent_schema, v_parent_tablename, v_parent_schema, v_partition_name);
1237
1238 IF v_inherit_fk THEN
1239 PERFORM @extschema@.apply_foreign_keys(p_parent_table, v_parent_schema||'.'||v_partition_name, v_job_id);
1240 END IF;
1241
1242 IF v_jobmon_schema IS NOT NULL THEN
1243 PERFORM update_step(v_step_id, 'OK', 'Done');
1244 END IF;
1245
1246 -- Will only loop once and only if sub_partitioning is actually configured
1247 -- This seemed easier than assigning a bunch of variables then doing an IF condition
1248 FOR v_row IN
1249 SELECT sub_parent
1250 , sub_partition_type
1251 , sub_control
1252 , sub_partition_interval
1253 , sub_constraint_cols
1254 , sub_premake
1255 , sub_optimize_trigger
1256 , sub_optimize_constraint
1257 , sub_epoch
1258 , sub_inherit_fk
1259 , sub_retention
1260 , sub_retention_schema
1261 , sub_retention_keep_table
1262 , sub_retention_keep_index
1263 , sub_use_run_maintenance
1264 , sub_infinite_time_partitions
1265 , sub_jobmon
1266 , sub_trigger_exception_handling
1267 FROM @extschema@.part_config_sub
1268 WHERE sub_parent = p_parent_table
1269 LOOP
1270 IF v_jobmon_schema IS NOT NULL THEN
1271 v_step_id := add_step(v_job_id, format('Subpartitioning %s.%s', v_parent_schema, v_partition_name));
1272 END IF;
1273 v_sql := format('SELECT @extschema@.create_parent(
1274 p_parent_table := %L
1275 , p_control := %L
1276 , p_type := %L
1277 , p_interval := %L
1278 , p_constraint_cols := %L
1279 , p_premake := %L
1280 , p_use_run_maintenance := %L
1281 , p_inherit_fk := %L
1282 , p_epoch := %L
1283 , p_jobmon := %L )'
1284 , v_parent_schema||'.'||v_partition_name
1285 , v_row.sub_control
1286 , v_row.sub_partition_type
1287 , v_row.sub_partition_interval
1288 , v_row.sub_constraint_cols
1289 , v_row.sub_premake
1290 , v_row.sub_use_run_maintenance
1291 , v_row.sub_inherit_fk
1292 , v_row.sub_epoch
1293 , v_row.sub_jobmon);
1294 EXECUTE v_sql;
1295
1296 UPDATE @extschema@.part_config SET
1297 retention_schema = v_row.sub_retention_schema
1298 , retention_keep_table = v_row.sub_retention_keep_table
1299 , retention_keep_index = v_row.sub_retention_keep_index
1300 , optimize_trigger = v_row.sub_optimize_trigger
1301 , optimize_constraint = v_row.sub_optimize_constraint
1302 , infinite_time_partitions = v_row.sub_infinite_time_partitions
1303 , trigger_exception_handling = v_row.sub_trigger_exception_handling
1304 WHERE parent_table = v_parent_schema||'.'||v_partition_name;
1305
1306 END LOOP; -- end sub partitioning LOOP
1307
1308 -- Manage additonal constraints if set
1309 PERFORM @extschema@.apply_constraints(p_parent_table, p_job_id := v_job_id, p_debug := p_debug);
1310
1311 v_partition_created := true;
1312
1313END LOOP;
1314
1315-- v_analyze is a local check if a new table is made.
1316-- 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.
1317IF v_analyze AND p_analyze THEN
1318 IF v_jobmon_schema IS NOT NULL THEN
1319 v_step_id := add_step(v_job_id, format('Analyzing partition set: %s', p_parent_table));
1320 END IF;
1321
1322 EXECUTE format('ANALYZE %I.%I', v_parent_schema, v_parent_tablename);
1323
1324 IF v_jobmon_schema IS NOT NULL THEN
1325 PERFORM update_step(v_step_id, 'OK', 'Done');
1326 END IF;
1327END IF;
1328
1329IF v_jobmon_schema IS NOT NULL THEN
1330 IF v_partition_created = false THEN
1331 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));
1332 PERFORM update_step(v_step_id, 'OK', 'Done');
1333 END IF;
1334
1335 IF v_step_overflow_id IS NOT NULL THEN
1336 PERFORM fail_job(v_job_id);
1337 ELSE
1338 PERFORM close_job(v_job_id);
1339 END IF;
1340END IF;
1341
1342EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1343
1344RETURN v_partition_created;
1345
1346EXCEPTION
1347 WHEN OTHERS THEN
1348 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
1349 ex_context = PG_EXCEPTION_CONTEXT,
1350 ex_detail = PG_EXCEPTION_DETAIL,
1351 ex_hint = PG_EXCEPTION_HINT;
1352 IF v_jobmon_schema IS NOT NULL THEN
1353 IF v_job_id IS NULL THEN
1354 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE TABLE: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id;
1355 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;
1356 ELSIF v_step_id IS NULL THEN
1357 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
1358 END IF;
1359 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
1360 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
1361 END IF;
1362 RAISE EXCEPTION '%
1363CONTEXT: %
1364DETAIL: %
1365HINT: %', ex_message, ex_context, ex_detail, ex_hint;
1366END
1367$$;
1368
1369
1370/*
1371 * Create a partition set that is a subpartition of an already existing partition set.
1372 * Given the parent table of any current partition set, it will turn all existing children into parent tables of their own partition sets
1373 * using the configuration options given as parameters to this function.
1374 * Uses another config table that allows for turning all future child partitions into a new parent automatically.
1375 * To avoid logical complications and contention issues, ALL subpartitions must be maintained using run_maintenance().
1376 * This means the automatic, trigger based partition creation for serial partitioning will not work if it is a subpartition.
1377 */
1378CREATE FUNCTION create_sub_parent(
1379 p_top_parent text
1380 , p_control text
1381 , p_type text
1382 , p_interval text
1383 , p_constraint_cols text[] DEFAULT NULL
1384 , p_premake int DEFAULT 4
1385 , p_start_partition text DEFAULT NULL
1386 , p_inherit_fk boolean DEFAULT true
1387 , p_epoch text DEFAULT 'none'
1388 , p_upsert text DEFAULT ''
1389 , p_trigger_return_null boolean DEFAULT true
1390 , p_jobmon boolean DEFAULT true
1391 , p_debug boolean DEFAULT false)
1392RETURNS boolean
1393 LANGUAGE plpgsql SECURITY DEFINER
1394 AS $$
1395DECLARE
1396
1397v_last_partition text;
1398v_new_search_path text := '@extschema@,pg_temp';
1399v_old_search_path text;
1400v_parent_interval text;
1401v_parent_type text;
1402v_row record;
1403v_row_last_part record;
1404v_run_maint boolean;
1405v_sql text;
1406v_success boolean := false;
1407v_top_type text;
1408
1409BEGIN
1410
1411SELECT use_run_maintenance INTO v_run_maint FROM @extschema@.part_config WHERE parent_table = p_top_parent;
1412IF v_run_maint IS NULL THEN
1413 RAISE EXCEPTION 'Cannot subpartition a table that is not managed by pg_partman already. Given top parent table not found in @extschema@.part_config: %', p_top_parent;
1414ELSIF v_run_maint = false THEN
1415 RAISE EXCEPTION 'Any parent table that will be part of a sub-partitioned set (on any level) must have use_run_maintenance set to true in part_config table, even for serial partitioning. See documentation for more info.';
1416END IF;
1417
1418IF p_upsert <> '' AND @extschema@.check_version('9.5.0') = 'false' THEN
1419 RAISE EXCEPTION 'INSERT ... ON CONFLICT (UPSERT) feature is only supported in PostgreSQL 9.5 and later';
1420END IF;
1421
1422SELECT current_setting('search_path') INTO v_old_search_path;
1423EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
1424
1425FOR v_row IN
1426 -- Loop through all current children to turn them into partitioned tables
1427 SELECT partition_schemaname||'.'||partition_tablename AS child_table FROM @extschema@.show_partitions(p_top_parent)
1428LOOP
1429 SELECT partition_type, partition_interval INTO v_parent_type, v_parent_interval FROM @extschema@.part_config WHERE parent_table = v_row.child_table;
1430
1431 IF v_parent_interval = p_interval THEN
1432 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1433 RAISE EXCEPTION 'Sub-partition interval cannot be equal to parent interval';
1434 END IF;
1435
1436 IF (v_parent_type = 'time' OR v_parent_type = 'time-custom')
1437 AND (p_type = 'time' OR p_type = 'time-custom')
1438 THEN
1439 IF p_interval::interval > v_parent_interval::interval THEN
1440 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1441 RAISE EXCEPTION 'Sub-partition interval cannot be greater than the given parent interval';
1442 END IF;
1443 IF p_interval = 'weekly' AND v_parent_interval::interval > '1 week'::interval THEN
1444 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1445 RAISE EXCEPTION 'Due to conflicting data boundaries between ISO weeks and any larger interval of time, pg_partman cannot support a sub-partition interval of weekly';
1446 END IF;
1447 ELSIF v_parent_type = 'id' THEN
1448 IF p_interval::bigint > v_parent_interval::bigint THEN
1449 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1450 RAISE EXCEPTION 'Sub-partition interval cannot be greater than the given parent interval';
1451 END IF;
1452 END IF;
1453
1454 -- Just call existing create_parent() function but add the given parameters to the part_config_sub table as well
1455 v_sql := format('SELECT @extschema@.create_parent(
1456 p_parent_table := %L
1457 , p_control := %L
1458 , p_type := %L
1459 , p_interval := %L
1460 , p_constraint_cols := %L
1461 , p_premake := %L
1462 , p_use_run_maintenance := %L
1463 , p_start_partition := %L
1464 , p_inherit_fk := %L
1465 , p_epoch := %L
1466 , p_upsert := %L
1467 , p_trigger_return_null := %L
1468 , p_jobmon := %L
1469 , p_debug := %L )'
1470 , v_row.child_table
1471 , p_control
1472 , p_type
1473 , p_interval
1474 , p_constraint_cols
1475 , p_premake
1476 , true
1477 , p_start_partition
1478 , p_inherit_fk
1479 , p_epoch
1480 , p_upsert
1481 , p_trigger_return_null
1482 , p_jobmon
1483 , p_debug);
1484 EXECUTE v_sql;
1485
1486END LOOP;
1487
1488INSERT INTO @extschema@.part_config_sub (
1489 sub_parent
1490 , sub_control
1491 , sub_partition_type
1492 , sub_partition_interval
1493 , sub_constraint_cols
1494 , sub_premake
1495 , sub_inherit_fk
1496 , sub_use_run_maintenance
1497 , sub_epoch
1498 , sub_upsert
1499 , sub_jobmon
1500 , sub_trigger_return_null)
1501VALUES (
1502 p_top_parent
1503 , p_control
1504 , p_type
1505 , p_interval
1506 , p_constraint_cols
1507 , p_premake
1508 , p_inherit_fk
1509 , true
1510 , p_epoch
1511 , p_upsert
1512 , p_jobmon
1513 , p_trigger_return_null);
1514
1515v_success := true;
1516
1517EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1518
1519RETURN v_success;
1520
1521END
1522$$;
1523
1524
1525/*
1526 * Populate the child table(s) of a time-based partition set with old data from the original parent
1527 */
1528CREATE OR REPLACE FUNCTION partition_data_time(
1529 p_parent_table text
1530 , p_batch_count int DEFAULT 1
1531 , p_batch_interval interval DEFAULT NULL
1532 , p_lock_wait numeric DEFAULT 0
1533 , p_order text DEFAULT 'ASC'
1534 , p_analyze boolean DEFAULT true)
1535 RETURNS bigint
1536 LANGUAGE plpgsql SECURITY DEFINER
1537 AS $$
1538DECLARE
1539
1540v_control text;
1541v_datetime_string text;
1542v_current_partition_name text;
1543v_epoch text;
1544v_last_partition text;
1545v_lock_iter int := 1;
1546v_lock_obtained boolean := FALSE;
1547v_max_partition_timestamp timestamptz;
1548v_min_partition_timestamp timestamptz;
1549v_new_search_path text := '@extschema@,pg_temp';
1550v_old_search_path text;
1551v_parent_schema text;
1552v_parent_tablename text;
1553v_partition_expression text;
1554v_partition_interval interval;
1555v_partition_suffix text;
1556v_partition_timestamp timestamptz[];
1557v_quarter text;
1558v_rowcount bigint;
1559v_start_control timestamptz;
1560v_time_position int;
1561v_total_rows bigint := 0;
1562v_type text;
1563v_year text;
1564
1565BEGIN
1566
1567SELECT current_setting('search_path') INTO v_old_search_path;
1568EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
1569
1570SELECT partition_type
1571 , partition_interval::interval
1572 , control
1573 , datetime_string
1574 , epoch
1575INTO v_type
1576 , v_partition_interval
1577 , v_control
1578 , v_datetime_string
1579 , v_epoch
1580FROM @extschema@.part_config
1581WHERE parent_table = p_parent_table
1582AND (partition_type = 'time' OR partition_type = 'time-custom');
1583IF NOT FOUND THEN
1584 EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1585 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table;
1586END IF;
1587
1588IF p_batch_interval IS NULL OR p_batch_interval > v_partition_interval THEN
1589 p_batch_interval := v_partition_interval;
1590END IF;
1591
1592SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename
1593FROM pg_catalog.pg_tables
1594WHERE schemaname = split_part(p_parent_table, '.', 1)::name
1595AND tablename = split_part(p_parent_table, '.', 2)::name;
1596
1597SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1;
1598
1599v_partition_expression := CASE
1600 WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control)
1601 WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control)
1602 ELSE format('%I', v_control)
1603END;
1604
1605FOR i IN 1..p_batch_count LOOP
1606
1607 IF p_order = 'ASC' THEN
1608 EXECUTE format('SELECT min(%s) FROM ONLY %I.%I', v_partition_expression, v_parent_schema, v_parent_tablename) INTO v_start_control;
1609 ELSIF p_order = 'DESC' THEN
1610 EXECUTE format('SELECT max(%s) FROM ONLY %I.%I', v_partition_expression, v_parent_schema, v_parent_tablename) INTO v_start_control;
1611 ELSE
1612 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC';
1613 END IF;
1614
1615 IF v_start_control IS NULL THEN
1616 EXIT;
1617 END IF;
1618
1619 IF v_type = 'time' THEN
1620 CASE
1621 WHEN v_partition_interval = '15 mins' THEN
1622 v_min_partition_timestamp := date_trunc('hour', v_start_control) +
1623 '15min'::interval * floor(date_part('minute', v_start_control) / 15.0);
1624 WHEN v_partition_interval = '30 mins' THEN
1625 v_min_partition_timestamp := date_trunc('hour', v_start_control) +
1626 '30min'::interval * floor(date_part('minute', v_start_control) / 30.0);
1627 WHEN v_partition_interval = '1 hour' THEN
1628 v_min_partition_timestamp := date_trunc('hour', v_start_control);
1629 WHEN v_partition_interval = '1 day' THEN
1630 v_min_partition_timestamp := date_trunc('day', v_start_control);
1631 WHEN v_partition_interval = '1 week' THEN
1632 v_min_partition_timestamp := date_trunc('week', v_start_control);
1633 WHEN v_partition_interval = '1 month' THEN
1634 v_min_partition_timestamp := date_trunc('month', v_start_control);
1635 WHEN v_partition_interval = '3 months' THEN
1636 v_min_partition_timestamp := date_trunc('quarter', v_start_control);
1637 WHEN v_partition_interval = '1 year' THEN
1638 v_min_partition_timestamp := date_trunc('year', v_start_control);
1639 END CASE;
1640 ELSIF v_type = 'time-custom' THEN
1641 SELECT child_start_time INTO v_min_partition_timestamp FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition
1642 , v_partition_interval::text
1643 , p_parent_table);
1644 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval;
1645 LOOP
1646 IF v_start_control >= v_min_partition_timestamp AND v_start_control < v_max_partition_timestamp THEN
1647 EXIT;
1648 ELSE
1649 BEGIN
1650 IF v_start_control > v_max_partition_timestamp THEN
1651 -- Keep going forward in time, checking if child partition time interval encompasses the current v_start_control value
1652 v_min_partition_timestamp := v_max_partition_timestamp;
1653 v_max_partition_timestamp := v_max_partition_timestamp + v_partition_interval;
1654
1655 ELSE
1656 -- Keep going backwards in time, checking if child partition time interval encompasses the current v_start_control value
1657 v_max_partition_timestamp := v_min_partition_timestamp;
1658 v_min_partition_timestamp := v_min_partition_timestamp - v_partition_interval;
1659 END IF;
1660 EXCEPTION WHEN datetime_field_overflow THEN
1661 RAISE EXCEPTION 'Attempted partition time interval is outside PostgreSQL''s supported time range.
1662 Unable to create partition with interval before timestamp % ', v_min_partition_interval;
1663 END;
1664 END IF;
1665 END LOOP;
1666
1667 END IF;
1668
1669 v_partition_timestamp := ARRAY[v_min_partition_timestamp];
1670 IF p_order = 'ASC' THEN
1671 -- Ensure batch interval given as parameter doesn't cause maximum to overflow the current partition maximum
1672 IF (v_start_control + p_batch_interval) >= (v_min_partition_timestamp + v_partition_interval) THEN
1673 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval;
1674 ELSE
1675 v_max_partition_timestamp := v_start_control + p_batch_interval;
1676 END IF;
1677 ELSIF p_order = 'DESC' THEN
1678 -- Must be greater than max value still in parent table since query below grabs < max
1679 v_max_partition_timestamp := v_min_partition_timestamp + v_partition_interval;
1680 -- Ensure batch interval given as parameter doesn't cause minimum to underflow current partition minimum
1681 IF (v_start_control - p_batch_interval) >= v_min_partition_timestamp THEN
1682 v_min_partition_timestamp = v_start_control - p_batch_interval;
1683 END IF;
1684 ELSE
1685 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC';
1686 END IF;
1687
1688-- do some locking with timeout, if required
1689 IF p_lock_wait > 0 THEN
1690 v_lock_iter := 0;
1691 WHILE v_lock_iter <= 5 LOOP
1692 v_lock_iter := v_lock_iter + 1;
1693 BEGIN
1694 EXECUTE format('SELECT * FROM ONLY %I.%I WHERE %s >= %L AND %3$s < %5$L FOR UPDATE NOWAIT'
1695 , v_parent_schema
1696 , v_parent_tablename
1697 , v_partition_expression
1698 , v_min_partition_timestamp
1699 , v_max_partition_timestamp);
1700 v_lock_obtained := TRUE;
1701 EXCEPTION
1702 WHEN lock_not_available THEN
1703 PERFORM pg_sleep( p_lock_wait / 5.0 );
1704 CONTINUE;
1705 END;
1706 EXIT WHEN v_lock_obtained;
1707 END LOOP;
1708 IF NOT v_lock_obtained THEN
1709 RETURN -1;
1710 END IF;
1711 END IF;
1712
1713 PERFORM @extschema@.create_partition_time(p_parent_table, v_partition_timestamp, p_analyze);
1714 -- This suffix generation code is in create_partition_time() as well
1715 v_partition_suffix := to_char(v_min_partition_timestamp, v_datetime_string);
1716 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_partition_suffix, TRUE);
1717
1718 EXECUTE format('WITH partition_data AS (
1719 DELETE FROM ONLY %I.%I WHERE %s >= %L AND %3$s < %5$L RETURNING *)
1720 INSERT INTO %I.%I SELECT * FROM partition_data'
1721 , v_parent_schema
1722 , v_parent_tablename
1723 , v_partition_expression
1724 , v_min_partition_timestamp
1725 , v_max_partition_timestamp
1726 , v_parent_schema
1727 , v_current_partition_name);
1728 GET DIAGNOSTICS v_rowcount = ROW_COUNT;
1729 v_total_rows := v_total_rows + v_rowcount;
1730 IF v_rowcount = 0 THEN
1731 EXIT;
1732 END IF;
1733
1734END LOOP;
1735
1736PERFORM @extschema@.create_function_time(p_parent_table);
1737
1738EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1739
1740RETURN v_total_rows;
1741
1742END
1743$$;
1744
1745
1746/*
1747 * Function to manage pre-creation of the next partitions in a set.
1748 * Also manages dropping old partitions if the retention option is set.
1749 * If p_parent_table is passed, will only run run_maintenance() on that one table (no matter what the configuration table may have set for it)
1750 * Otherwise, will run on all tables in the config table with p_run_maintenance() set to true.
1751 * For large partition sets, running analyze can cause maintenance to take longer than expected. Can set p_analyze to false to avoid a forced analyze run.
1752 * Be aware that constraint exclusion may not work properly until an analyze on the partition set is run.
1753 */
1754CREATE OR REPLACE FUNCTION run_maintenance(p_parent_table text DEFAULT NULL, p_analyze boolean DEFAULT true, p_jobmon boolean DEFAULT true, p_debug boolean DEFAULT false) RETURNS void
1755 LANGUAGE plpgsql SECURITY DEFINER
1756 AS $$
1757DECLARE
1758
1759ex_context text;
1760ex_detail text;
1761ex_hint text;
1762ex_message text;
1763v_adv_lock boolean;
1764v_check_subpart int;
1765v_create_count int := 0;
1766v_current_partition text;
1767v_current_partition_id bigint;
1768v_current_partition_timestamp timestamptz;
1769v_drop_count int := 0;
1770v_job_id bigint;
1771v_jobmon boolean;
1772v_jobmon_schema text;
1773v_last_partition text;
1774v_last_partition_created boolean;
1775v_last_partition_id bigint;
1776v_last_partition_timestamp timestamptz;
1777v_max_id_parent bigint;
1778v_max_time_parent timestamptz;
1779v_new_search_path text := '@extschema@,pg_temp';
1780v_next_partition_id bigint;
1781v_next_partition_timestamp timestamptz;
1782v_old_search_path text;
1783v_parent_schema text;
1784v_parent_tablename text;
1785v_partition_expression text;
1786v_premade_count int;
1787v_premake_id_max bigint;
1788v_premake_id_min bigint;
1789v_premake_timestamp_min timestamptz;
1790v_premake_timestamp_max timestamptz;
1791v_row record;
1792v_row_max_id record;
1793v_row_max_time record;
1794v_row_sub record;
1795v_step_id bigint;
1796v_step_overflow_id bigint;
1797v_sub_id_max bigint;
1798v_sub_id_max_suffix bigint;
1799v_sub_id_min bigint;
1800v_sub_parent text;
1801v_sub_timestamp_max timestamptz;
1802v_sub_timestamp_max_suffix timestamptz;
1803v_sub_timestamp_min timestamptz;
1804v_tablename text;
1805v_tables_list_sql text;
1806
1807BEGIN
1808
1809v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman run_maintenance'));
1810IF v_adv_lock = 'false' THEN
1811 RAISE NOTICE 'Partman maintenance already running.';
1812 RETURN;
1813END IF;
1814
1815SELECT current_setting('search_path') INTO v_old_search_path;
1816IF p_jobmon THEN
1817 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;
1818 IF v_jobmon_schema IS NOT NULL THEN
1819 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp';
1820 END IF;
1821END IF;
1822EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
1823
1824IF v_jobmon_schema IS NOT NULL THEN
1825 v_job_id := add_job('PARTMAN RUN MAINTENANCE');
1826 v_step_id := add_step(v_job_id, 'Running maintenance loop');
1827END IF;
1828
1829v_row := NULL; -- Ensure it's reset
1830
1831v_tables_list_sql := 'SELECT parent_table
1832 , partition_type
1833 , partition_interval
1834 , control
1835 , premake
1836 , undo_in_progress
1837 , sub_partition_set_full
1838 , epoch
1839 , infinite_time_partitions
1840 FROM @extschema@.part_config
1841 WHERE sub_partition_set_full = false';
1842
1843IF p_parent_table IS NULL THEN
1844 v_tables_list_sql := v_tables_list_sql || ' AND use_run_maintenance = true';
1845ELSE
1846 v_tables_list_sql := v_tables_list_sql || format(' AND parent_table = %L', p_parent_table);
1847END IF;
1848
1849FOR v_row IN EXECUTE v_tables_list_sql
1850LOOP
1851
1852 CONTINUE WHEN v_row.undo_in_progress;
1853
1854 -- Check for consistent data in part_config_sub table. Was unable to get this working properly as either a constraint or trigger.
1855 -- Would either delay raising an error until the next write (which I cannot predict) or disallow future edits to update a sub-partition set's configuration.
1856 -- This way at least provides a consistent way to check that I know will run. If anyone can get a working constraint/trigger, please help!
1857 -- Don't have to worry about this in the serial trigger maintenance since subpartitioning requires run_maintenance().
1858 SELECT sub_parent INTO v_sub_parent FROM @extschema@.part_config_sub WHERE sub_parent = v_row.parent_table;
1859 IF v_sub_parent IS NOT NULL THEN
1860 SELECT count(*) INTO v_check_subpart FROM @extschema@.check_subpart_sameconfig(v_row.parent_table);
1861 IF v_check_subpart > 1 THEN
1862 RAISE EXCEPTION 'Inconsistent data in part_config_sub table. Sub-partition tables that are themselves sub-partitions cannot have differing configuration values among their siblings.
1863 Run this query: "SELECT * FROM @extschema@.check_subpart_sameconfig(''%'');" This should only return a single row or nothing.
1864 If multiple rows are returned, results are all children of the given parent. Update the differing values to be consistent for your desired values.', v_row.sub_parent;
1865 END IF;
1866 END IF;
1867
1868 SELECT schemaname, tablename
1869 INTO v_parent_schema, v_parent_tablename
1870 FROM pg_catalog.pg_tables
1871 WHERE schemaname = split_part(v_row.parent_table, '.', 1)::name
1872 AND tablename = split_part(v_row.parent_table, '.', 2)::name;
1873
1874 v_partition_expression := CASE
1875 WHEN v_row.epoch = 'seconds' THEN format('to_timestamp(%I)', v_row.control)
1876 WHEN v_row.epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_row.control)
1877 ELSE format('%I', v_row.control)
1878 END;
1879 IF p_debug THEN
1880 RAISE NOTICE 'run_maint: v_partition_expression: %', v_partition_expression;
1881 END IF;
1882
1883 SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(v_row.parent_table, 'DESC') LIMIT 1;
1884 IF p_debug THEN
1885 RAISE NOTICE 'run_maint: parent_table: %, v_last_partition: %', v_row.parent_table, v_last_partition;
1886 END IF;
1887
1888 IF v_row.partition_type = 'time' OR v_row.partition_type = 'time-custom' THEN
1889
1890 SELECT child_start_time INTO v_last_partition_timestamp
1891 FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table);
1892
1893 -- Loop through child tables starting from highest to get current max value in partition set
1894 -- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent.
1895 FOR v_row_max_time IN
1896 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(v_row.parent_table, 'DESC')
1897 LOOP
1898 EXECUTE format('SELECT max(%s)::text FROM %I.%I'
1899 , v_partition_expression
1900 , v_row_max_time.partition_schemaname
1901 , v_row_max_time.partition_tablename
1902 ) INTO v_current_partition_timestamp;
1903 IF v_current_partition_timestamp IS NOT NULL THEN
1904 SELECT suffix_timestamp INTO v_current_partition_timestamp FROM @extschema@.show_partition_name(v_row.parent_table, v_current_partition_timestamp::text);
1905 EXIT;
1906 END IF;
1907 END LOOP;
1908 -- Check for values in the parent table. If they are there and greater than all child values, use that instead
1909 -- This allows maintenance to continue working properly if there is a large gap in data insertion. Data will remain in parent, but new tables will be created
1910 EXECUTE format('SELECT max(%s) FROM ONLY %I.%I', v_partition_expression, v_parent_schema, v_parent_tablename) INTO v_max_time_parent;
1911 IF p_debug THEN
1912 RAISE NOTICE 'run_maint: v_current_partition_timestamp: %, v_max_time_parent: %', v_current_partition_timestamp, v_max_time_parent;
1913 END IF;
1914 IF v_max_time_parent > v_current_partition_timestamp THEN
1915 SELECT suffix_timestamp INTO v_current_partition_timestamp FROM @extschema@.show_partition_name(v_row.parent_table, v_max_time_parent::text);
1916 END IF;
1917 IF v_current_partition_timestamp IS NULL THEN -- Partition set is completely empty
1918 IF v_row.infinite_time_partitions IS TRUE THEN
1919 -- Set it to now so new partitions continue to be created
1920 v_current_partition_timestamp = CURRENT_TIMESTAMP;
1921 ELSE
1922 -- Nothing to do
1923 CONTINUE;
1924 END IF;
1925 END IF;
1926
1927 -- If this is a subpartition, determine if the last child table has been made. If so, mark it as full so future maintenance runs can skip it
1928 SELECT sub_min::timestamptz, sub_max::timestamptz INTO v_sub_timestamp_min, v_sub_timestamp_max FROM @extschema@.check_subpartition_limits(v_row.parent_table, 'time');
1929 IF v_sub_timestamp_max IS NOT NULL THEN
1930 SELECT suffix_timestamp INTO v_sub_timestamp_max_suffix FROM @extschema@.show_partition_name(v_row.parent_table, v_sub_timestamp_max::text);
1931 IF v_sub_timestamp_max_suffix = v_last_partition_timestamp THEN
1932 -- Final partition for this set is created. Set full and skip it
1933 UPDATE @extschema@.part_config SET sub_partition_set_full = true WHERE parent_table = v_row.parent_table;
1934 CONTINUE;
1935 END IF;
1936 END IF;
1937
1938 -- Check and see how many premade partitions there are.
1939 v_premade_count = round(EXTRACT('epoch' FROM age(v_last_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.partition_interval::interval));
1940 v_next_partition_timestamp := v_last_partition_timestamp;
1941 IF p_debug THEN
1942 RAISE NOTICE 'run_maint before loop: current_partition_timestamp: %, v_premade_count: %, v_sub_timestamp_min: %, v_sub_timestamp_max: %'
1943 , v_current_partition_timestamp
1944 , v_premade_count
1945 , v_sub_timestamp_min
1946 , v_sub_timestamp_max;
1947 END IF;
1948 -- Loop premaking until config setting is met. Allows it to catch up if it fell behind or if premake changed
1949 WHILE (v_premade_count < v_row.premake) LOOP
1950 IF p_debug THEN
1951 RAISE NOTICE 'run_maint: parent_table: %, v_premade_count: %, v_next_partition_timestamp: %', v_row.parent_table, v_premade_count, v_next_partition_timestamp;
1952 END IF;
1953 IF v_next_partition_timestamp < v_sub_timestamp_min OR v_next_partition_timestamp > v_sub_timestamp_max THEN
1954 -- With subpartitioning, no need to run if the timestamp is not in the parent table's range
1955 EXIT;
1956 END IF;
1957 BEGIN
1958 v_next_partition_timestamp := v_next_partition_timestamp + v_row.partition_interval::interval;
1959 EXCEPTION WHEN datetime_field_overflow THEN
1960 v_premade_count := v_row.premake; -- do this so it can exit the premake check loop and continue in the outer for loop
1961 IF v_jobmon_schema IS NOT NULL THEN
1962 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.');
1963 PERFORM update_step(v_step_overflow_id, 'CRITICAL', format('Child partition creation skipped for parent table: %s', v_partition_time));
1964 END IF;
1965 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. Child partition creation skipped for parent table %', v_row.parent_table;
1966 CONTINUE;
1967 END;
1968 v_last_partition_created := @extschema@.create_partition_time(v_row.parent_table, ARRAY[v_next_partition_timestamp], p_analyze);
1969 IF v_last_partition_created THEN
1970 v_create_count := v_create_count + 1;
1971 PERFORM @extschema@.create_function_time(v_row.parent_table, v_job_id);
1972 END IF;
1973
1974 v_premade_count = round(EXTRACT('epoch' FROM age(v_next_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.partition_interval::interval));
1975 END LOOP;
1976 ELSIF v_row.partition_type = 'id' THEN
1977 -- Loop through child tables starting from highest to get current max value in partition set
1978 -- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent.
1979 FOR v_row_max_id IN
1980 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(v_row.parent_table, 'DESC')
1981 LOOP
1982 EXECUTE format('SELECT max(%I)::text FROM %I.%I'
1983 , v_row.control
1984 , v_row_max_id.partition_schemaname
1985 , v_row_max_id.partition_tablename) INTO v_current_partition_id;
1986 IF v_current_partition_id IS NOT NULL THEN
1987 SELECT suffix_id INTO v_current_partition_id FROM @extschema@.show_partition_name(v_row.parent_table, v_current_partition_id::text);
1988 EXIT;
1989 END IF;
1990 END LOOP;
1991 -- Check for values in the parent table. If they are there and greater than all child values, use that instead
1992 -- This allows maintenance to continue working properly if there is a large gap in data insertion. Data will remain in parent, but new tables will be created
1993 EXECUTE format('SELECT max(%I) FROM ONLY %I.%I', v_row.control, v_parent_schema, v_parent_tablename) INTO v_max_id_parent;
1994 IF v_max_id_parent > v_current_partition_id THEN
1995 SELECT suffix_id INTO v_current_partition_id FROM @extschema@.show_partition_name(v_row.parent_table, v_max_id_parent::text);
1996 END IF;
1997 IF v_current_partition_id IS NULL THEN
1998 -- Partition set is completely empty. Nothing to do
1999 CONTINUE;
2000 END IF;
2001
2002 SELECT child_start_id INTO v_last_partition_id
2003 FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table);
2004 -- Determine if this table is a child of a subpartition parent. If so, get limits to see if run_maintenance even needs to run for it.
2005 SELECT sub_min::bigint, sub_max::bigint INTO v_sub_id_min, v_sub_id_max FROM @extschema@.check_subpartition_limits(v_row.parent_table, 'id');
2006 IF v_sub_id_max IS NOT NULL THEN
2007 SELECT suffix_id INTO v_sub_id_max_suffix FROM @extschema@.show_partition_name(v_row.parent_table, v_sub_id_max::text);
2008 IF v_sub_id_max_suffix = v_last_partition_id THEN
2009 -- Final partition for this set is created. Set full and skip it
2010 UPDATE @extschema@.part_config SET sub_partition_set_full = true WHERE parent_table = v_row.parent_table;
2011 CONTINUE;
2012 END IF;
2013 END IF;
2014
2015 v_next_partition_id := v_last_partition_id;
2016 v_premade_count := ((v_last_partition_id - v_current_partition_id) / v_row.partition_interval::bigint);
2017 -- Loop premaking until config setting is met. Allows it to catch up if it fell behind or if premake changed.
2018 WHILE (v_premade_count < v_row.premake) LOOP
2019 IF p_debug THEN
2020 RAISE NOTICE 'run_maint: parent_table: %, v_premade_count: %, v_next_partition_id: %', v_row.parent_table, v_premade_count, v_next_partition_id;
2021 END IF;
2022 IF v_next_partition_id < v_sub_id_min OR v_next_partition_id > v_sub_id_max THEN
2023 -- With subpartitioning, no need to run if the id is not in the parent table's range
2024 EXIT;
2025 END IF;
2026 v_next_partition_id := v_next_partition_id + v_row.partition_interval::bigint;
2027 v_last_partition_created := @extschema@.create_partition_id(v_row.parent_table, ARRAY[v_next_partition_id], p_analyze);
2028 IF v_last_partition_created THEN
2029 v_create_count := v_create_count + 1;
2030 PERFORM @extschema@.create_function_id(v_row.parent_table, v_job_id);
2031 END IF;
2032 v_premade_count := ((v_next_partition_id - v_current_partition_id) / v_row.partition_interval::bigint);
2033 END LOOP;
2034
2035 END IF; -- end main IF check for time or id
2036
2037END LOOP; -- end of creation loop
2038
2039-- Manage dropping old partitions if retention option is set
2040FOR v_row IN
2041 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND
2042 (partition_type = 'time' OR partition_type = 'time-custom')
2043LOOP
2044 IF p_parent_table IS NULL THEN
2045 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table);
2046 ELSE -- Only run retention on table given in parameter
2047 IF p_parent_table <> v_row.parent_table THEN
2048 CONTINUE;
2049 ELSE
2050 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table);
2051 END IF;
2052 END IF;
2053 IF v_drop_count > 0 THEN
2054 PERFORM @extschema@.create_function_time(v_row.parent_table, v_job_id);
2055 END IF;
2056END LOOP;
2057FOR v_row IN
2058 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND partition_type = 'id'
2059LOOP
2060 IF p_parent_table IS NULL THEN
2061 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table);
2062 ELSE -- Only run retention on table given in parameter
2063 IF p_parent_table <> v_row.parent_table THEN
2064 CONTINUE;
2065 ELSE
2066 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table);
2067 END IF;
2068 END IF;
2069 IF v_drop_count > 0 THEN
2070 PERFORM @extschema@.create_function_id(v_row.parent_table, v_job_id);
2071 END IF;
2072END LOOP;
2073
2074IF v_jobmon_schema IS NOT NULL THEN
2075 PERFORM update_step(v_step_id, 'OK', format('Partition maintenance finished. %s partitons made. %s partitions dropped.', v_create_count, v_drop_count));
2076 IF v_step_overflow_id IS NOT NULL THEN
2077 PERFORM fail_job(v_job_id);
2078 ELSE
2079 PERFORM close_job(v_job_id);
2080 END IF;
2081END IF;
2082
2083EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
2084
2085EXCEPTION
2086 WHEN OTHERS THEN
2087 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
2088 ex_context = PG_EXCEPTION_CONTEXT,
2089 ex_detail = PG_EXCEPTION_DETAIL,
2090 ex_hint = PG_EXCEPTION_HINT;
2091 IF v_jobmon_schema IS NOT NULL THEN
2092 IF v_job_id IS NULL THEN
2093 EXECUTE format('SELECT %I.add_job(''PARTMAN RUN MAINTENANCE'')', v_jobmon_schema) INTO v_job_id;
2094 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;
2095 ELSIF v_step_id IS NULL THEN
2096 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
2097 END IF;
2098 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
2099 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
2100 END IF;
2101 RAISE EXCEPTION '%
2102CONTEXT: %
2103DETAIL: %
2104HINT: %', ex_message, ex_context, ex_detail, ex_hint;
2105END
2106$$;
2107
2108
2109/*
2110 * Function to undo time-based partitioning created by this extension
2111 */
2112CREATE OR REPLACE FUNCTION undo_partition_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_keep_table boolean DEFAULT true, p_lock_wait numeric DEFAULT 0) RETURNS bigint
2113 LANGUAGE plpgsql SECURITY DEFINER
2114 AS $$
2115DECLARE
2116
2117ex_context text;
2118ex_detail text;
2119ex_hint text;
2120ex_message text;
2121v_adv_lock boolean;
2122v_batch_loop_count int := 0;
2123v_child_loop_total bigint := 0;
2124v_child_min timestamptz;
2125v_child_table text;
2126v_control text;
2127v_epoch text;
2128v_function_name text;
2129v_inner_loop_count int;
2130v_lock_iter int := 1;
2131v_lock_obtained boolean := FALSE;
2132v_job_id bigint;
2133v_jobmon boolean;
2134v_jobmon_schema text;
2135v_new_search_path text := '@extschema@,pg_temp';
2136v_old_search_path text;
2137v_parent_schema text;
2138v_parent_tablename text;
2139v_partition_expression text;
2140v_partition_interval interval;
2141v_row record;
2142v_rowcount bigint;
2143v_step_id bigint;
2144v_sub_count int;
2145v_total bigint := 0;
2146v_trig_name text;
2147v_type text;
2148v_undo_count int := 0;
2149
2150BEGIN
2151
2152v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman undo_partition_time'));
2153IF v_adv_lock = 'false' THEN
2154 RAISE NOTICE 'undo_partition_time already running.';
2155 RETURN 0;
2156END IF;
2157
2158SELECT partition_type
2159 , partition_interval::interval
2160 , control
2161 , jobmon
2162 , epoch
2163INTO v_type
2164 , v_partition_interval
2165 , v_control
2166 , v_jobmon
2167 , v_epoch
2168FROM @extschema@.part_config
2169WHERE parent_table = p_parent_table
2170AND (partition_type = 'time' OR partition_type = 'time-custom');
2171
2172IF v_partition_interval IS NULL THEN
2173 RAISE EXCEPTION 'Configuration for given parent table not found: %', p_parent_table;
2174END IF;
2175
2176SELECT current_setting('search_path') INTO v_old_search_path;
2177IF v_jobmon THEN
2178 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;
2179 IF v_jobmon_schema IS NOT NULL THEN
2180 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp';
2181 END IF;
2182END IF;
2183EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
2184
2185-- Check if any child tables are themselves partitioned or part of an inheritance tree. Prevent undo at this level if so.
2186-- Need to either lock child tables at all levels or handle the proper removal of triggers on all child tables first
2187-- before multi-level undo can be performed safely.
2188FOR v_row IN
2189 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(p_parent_table)
2190LOOP
2191 SELECT count(*) INTO v_sub_count
2192 FROM pg_catalog.pg_inherits i
2193 JOIN pg_catalog.pg_class c ON i.inhparent = c.oid
2194 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
2195 WHERE c.relname = v_row.partition_tablename::name
2196 AND n.nspname = v_row.partition_schemaname::name;
2197 IF v_sub_count > 0 THEN
2198 RAISE EXCEPTION 'Child table for this parent has child table(s) itself (%). Run undo partitioning on this table or remove inheritance first to ensure all data is properly moved to parent', v_row.partition_schemaname||'.'||v_row.partition_tablename;
2199 END IF;
2200END LOOP;
2201
2202IF v_jobmon_schema IS NOT NULL THEN
2203 v_job_id := add_job(format('PARTMAN UNDO PARTITIONING: %s', p_parent_table));
2204 v_step_id := add_step(v_job_id, format('Undoing partitioning for table %s', p_parent_table));
2205END IF;
2206
2207IF p_batch_interval IS NULL THEN
2208 p_batch_interval := v_partition_interval;
2209END IF;
2210
2211SELECT schemaname, tablename
2212INTO v_parent_schema, v_parent_tablename
2213FROM pg_catalog.pg_tables
2214WHERE schemaname = split_part(p_parent_table, '.', 1)::name
2215AND tablename = split_part(p_parent_table, '.', 2)::name;
2216
2217v_partition_expression := CASE
2218 WHEN v_epoch = 'seconds' THEN format('to_timestamp(%I)', v_control)
2219 WHEN v_epoch = 'milliseconds' THEN format('to_timestamp((%I/1000)::float)', v_control)
2220 ELSE format('%I', v_control)
2221end;
2222
2223-- Stops new time partitons from being made as well as stopping child tables from being dropped if they were configured with a retention period.
2224UPDATE @extschema@.part_config SET undo_in_progress = true WHERE parent_table = p_parent_table;
2225-- Stop data going into child tables.
2226v_trig_name := @extschema@.check_name_length(p_object_name := v_parent_tablename, p_suffix := '_part_trig');
2227v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE);
2228
2229SELECT tgname INTO v_trig_name
2230FROM pg_catalog.pg_trigger t
2231JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid
2232WHERE tgname = v_trig_name::name
2233AND c.relname = v_parent_tablename::name;
2234
2235SELECT proname INTO v_function_name FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = v_parent_schema::name AND proname = v_function_name::name;
2236
2237IF v_trig_name IS NOT NULL THEN
2238 -- lockwait for trigger drop
2239 IF p_lock_wait > 0 THEN
2240 v_lock_iter := 0;
2241 WHILE v_lock_iter <= 5 LOOP
2242 v_lock_iter := v_lock_iter + 1;
2243 BEGIN
2244 EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_parent_tablename);
2245 v_lock_obtained := TRUE;
2246 EXCEPTION
2247 WHEN lock_not_available THEN
2248 PERFORM pg_sleep( p_lock_wait / 5.0 );
2249 CONTINUE;
2250 END;
2251 EXIT WHEN v_lock_obtained;
2252 END LOOP;
2253 IF NOT v_lock_obtained THEN
2254 RAISE NOTICE 'Unable to obtain lock on parent table to remove trigger';
2255 RETURN -1;
2256 END IF;
2257 END IF; -- END p_lock_wait IF
2258 EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_trig_name, v_parent_schema, v_parent_tablename);
2259END IF; -- END trigger IF
2260v_lock_obtained := FALSE; -- reset for reuse later
2261
2262IF v_function_name IS NOT NULL THEN
2263 EXECUTE format('DROP FUNCTION IF EXISTS %I.%I()', v_parent_schema, v_function_name);
2264END IF;
2265
2266IF v_jobmon_schema IS NOT NULL THEN
2267 IF (v_trig_name IS NOT NULL OR v_function_name IS NOT NULL) THEN
2268 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process. Removed trigger & trigger function');
2269 ELSE
2270 PERFORM update_step(v_step_id, 'OK', 'Stopped partition creation process.');
2271 END IF;
2272END IF;
2273
2274<<outer_child_loop>>
2275LOOP
2276 -- Get ordered list of child table in set. Store in variable one at a time per loop until none are left or batch count is reached.
2277 -- This easily allows it to loop over same child table until empty or move onto next child table after it's dropped
2278 SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC') LIMIT 1;
2279
2280 EXIT outer_child_loop WHEN v_child_table IS NULL;
2281
2282 IF v_jobmon_schema IS NOT NULL THEN
2283 v_step_id := add_step(v_job_id, format('Removing child partition: %s.%s', v_parent_schema, v_child_table));
2284 END IF;
2285
2286 EXECUTE format('SELECT min(%s) FROM %I.%I', v_partition_expression, v_parent_schema, v_child_table) INTO v_child_min;
2287 IF v_child_min IS NULL THEN
2288 -- No rows left in this child table. Remove from partition set.
2289
2290 -- lockwait timeout for table drop
2291 IF p_lock_wait > 0 THEN
2292 v_lock_iter := 0;
2293 WHILE v_lock_iter <= 5 LOOP
2294 v_lock_iter := v_lock_iter + 1;
2295 BEGIN
2296 EXECUTE format('LOCK TABLE ONLY %I.%I IN ACCESS EXCLUSIVE MODE NOWAIT', v_parent_schema, v_child_table);
2297 v_lock_obtained := TRUE;
2298 EXCEPTION
2299 WHEN lock_not_available THEN
2300 PERFORM pg_sleep( p_lock_wait / 5.0 );
2301 CONTINUE;
2302 END;
2303 EXIT WHEN v_lock_obtained;
2304 END LOOP;
2305 IF NOT v_lock_obtained THEN
2306 RAISE NOTICE 'Unable to obtain lock on child table for removal from partition set';
2307 RETURN -1;
2308 END IF;
2309 END IF; -- END p_lock_wait IF
2310 v_lock_obtained := FALSE; -- reset for reuse later
2311
2312 EXECUTE format('ALTER TABLE %I.%I NO INHERIT %I.%I'
2313 , v_parent_schema
2314 , v_child_table
2315 , v_parent_schema
2316 , v_parent_tablename);
2317 IF p_keep_table = false THEN
2318 EXECUTE format('DROP TABLE %I.%I', v_parent_schema, v_child_table);
2319 IF v_jobmon_schema IS NOT NULL THEN
2320 PERFORM update_step(v_step_id, 'OK', format('Child table DROPPED. Moved %s rows to parent', v_child_loop_total));
2321 END IF;
2322 ELSE
2323 IF v_jobmon_schema IS NOT NULL THEN
2324 PERFORM update_step(v_step_id, 'OK', format('Child table UNINHERITED, not DROPPED. Moved %s rows to parent', v_child_loop_total));
2325 END IF;
2326 END IF;
2327 IF v_type = 'time-custom' THEN
2328 DELETE FROM @extschema@.custom_time_partitions WHERE parent_table = p_parent_table AND child_table = v_parent_schema||'.'||v_child_table;
2329 END IF;
2330 v_undo_count := v_undo_count + 1;
2331 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached
2332 CONTINUE outer_child_loop; -- skip data moving steps below
2333 END IF;
2334 v_inner_loop_count := 1;
2335 v_child_loop_total := 0;
2336 <<inner_child_loop>>
2337 LOOP
2338 -- do some locking with timeout, if required
2339 IF p_lock_wait > 0 THEN
2340 v_lock_iter := 0;
2341 WHILE v_lock_iter <= 5 LOOP
2342 v_lock_iter := v_lock_iter + 1;
2343 BEGIN
2344 EXECUTE format('SELECT * FROM %I.%I WHERE %I <= %L FOR UPDATE NOWAIT'
2345 , v_parent_schema
2346 , v_child_table
2347 , v_control
2348 , v_child_min + (p_batch_interval * v_inner_loop_count));
2349 v_lock_obtained := TRUE;
2350 EXCEPTION
2351 WHEN lock_not_available THEN
2352 PERFORM pg_sleep( p_lock_wait / 5.0 );
2353 CONTINUE;
2354 END;
2355 EXIT WHEN v_lock_obtained;
2356 END LOOP;
2357 IF NOT v_lock_obtained THEN
2358 RAISE NOTICE 'Unable to obtain lock on batch of rows to move';
2359 RETURN -1;
2360 END IF;
2361 END IF;
2362
2363 -- Get everything from the current child minimum up to the multiples of the given interval
2364 EXECUTE format('WITH move_data AS (
2365 DELETE FROM %I.%I WHERE %s <= %L RETURNING *)
2366 INSERT INTO %I.%I SELECT * FROM move_data'
2367 , v_parent_schema
2368 , v_child_table
2369 , v_partition_expression
2370 , v_child_min + (p_batch_interval * v_inner_loop_count)
2371 , v_parent_schema
2372 , v_parent_tablename);
2373 GET DIAGNOSTICS v_rowcount = ROW_COUNT;
2374 v_total := v_total + v_rowcount;
2375 v_child_loop_total := v_child_loop_total + v_rowcount;
2376 IF v_jobmon_schema IS NOT NULL THEN
2377 PERFORM update_step(v_step_id, 'OK', format('Moved %s rows to parent.', v_child_loop_total));
2378 END IF;
2379 EXIT inner_child_loop WHEN v_rowcount = 0; -- exit before loop incr if table is empty
2380 v_inner_loop_count := v_inner_loop_count + 1;
2381 v_batch_loop_count := v_batch_loop_count + 1;
2382
2383 -- Check again if table is empty and go to outer loop again to drop it if so
2384 EXECUTE format('SELECT min(%s) FROM %I.%I', v_partition_expression, v_parent_schema, v_child_table) INTO v_child_min;
2385 CONTINUE outer_child_loop WHEN v_child_min IS NULL;
2386
2387 EXIT outer_child_loop WHEN v_batch_loop_count >= p_batch_count; -- Exit outer FOR loop if p_batch_count is reached
2388 END LOOP inner_child_loop;
2389END LOOP outer_child_loop;
2390
2391SELECT partition_tablename INTO v_child_table FROM @extschema@.show_partitions(p_parent_table, 'ASC') LIMIT 1;
2392IF v_child_table IS NULL THEN
2393 DELETE FROM @extschema@.part_config WHERE parent_table = p_parent_table;
2394 IF v_jobmon_schema IS NOT NULL THEN
2395 v_step_id := add_step(v_job_id, 'Removing config from pg_partman');
2396 PERFORM update_step(v_step_id, 'OK', 'Done');
2397 END IF;
2398END IF;
2399
2400RAISE NOTICE 'Copied % row(s) to the parent. Removed % partitions.', v_total, v_undo_count;
2401IF v_jobmon_schema IS NOT NULL THEN
2402 v_step_id := add_step(v_job_id, 'Final stats');
2403 PERFORM update_step(v_step_id, 'OK', format('Copied %s row(s) to the parent. Removed %s partitions.', v_total, v_undo_count));
2404END IF;
2405
2406IF v_jobmon_schema IS NOT NULL THEN
2407 PERFORM close_job(v_job_id);
2408END IF;
2409
2410EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
2411
2412RETURN v_total;
2413
2414EXCEPTION
2415 WHEN OTHERS THEN
2416 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
2417 ex_context = PG_EXCEPTION_CONTEXT,
2418 ex_detail = PG_EXCEPTION_DETAIL,
2419 ex_hint = PG_EXCEPTION_HINT;
2420 IF v_jobmon_schema IS NOT NULL THEN
2421 IF v_job_id IS NULL THEN
2422 EXECUTE format('SELECT %I.add_job(''PARTMAN UNDO PARTITIONING: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id;
2423 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;
2424 ELSIF v_step_id IS NULL THEN
2425 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
2426 END IF;
2427 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
2428 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
2429 END IF;
2430 RAISE EXCEPTION '%
2431CONTEXT: %
2432DETAIL: %
2433HINT: %', ex_message, ex_context, ex_detail, ex_hint;
2434END
2435$$;
2436
2437-- Restore dropped object privileges
2438DO $$
2439DECLARE
2440v_row record;
2441BEGIN
2442 FOR v_row IN SELECT statement FROM partman_preserve_privs_temp LOOP
2443 IF v_row.statement IS NOT NULL THEN
2444 EXECUTE v_row.statement;
2445 END IF;
2446 END LOOP;
2447END
2448$$;
2449
2450DROP TABLE IF EXISTS partman_preserve_privs_temp;