· 10 years ago · Sep 24, 2016, 03:44 AM
1-- Improve performance of new partition creation for serial partition sets by allowing the p_analyze parameter to run_maintenance() to be passed further down the function call hierarchy. Also improves performance when moving data from the parent table to children if analyze is disabled (Github Pull Request #137).
2-- Improve performance during privilege inheritance when creating new partitions or when running reapply_privileges() function (Github Issue #139).
3
4
5CREATE TEMP TABLE partman_preserve_privs_temp (statement text);
6
7INSERT INTO partman_preserve_privs_temp
8SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.partition_data_id(text, int, bigint, numeric, text, boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';'
9FROM information_schema.routine_privileges
10WHERE routine_schema = '@extschema@'
11AND routine_name = 'partition_data_id';
12
13INSERT INTO partman_preserve_privs_temp
14SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.create_function_id(text, bigint, boolean) TO '||array_to_string(array_agg(grantee::text), ',')||';'
15FROM information_schema.routine_privileges
16WHERE routine_schema = '@extschema@'
17AND routine_name = 'create_function_id';
18
19DROP FUNCTION partition_data_id(text, int, bigint, numeric, text);
20DROP FUNCTION create_function_id(text, bigint);
21
22
23-- TODO Add to tables.sql
24/*
25 * Custom view to help improve privilege lookups for pg_partman.
26 * information_schema is a performance bottleneck since indexes aren't being used properly.
27 */
28CREATE VIEW partman.table_privs AS
29 SELECT u_grantor.rolname AS grantor,
30 grantee.rolname AS grantee,
31 nc.nspname AS table_schema,
32 c.relname AS table_name,
33 c.prtype AS privilege_type
34 FROM (
35 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
36 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
37 pg_namespace nc,
38 pg_authid u_grantor,
39 (
40 SELECT oid, rolname FROM pg_authid
41 UNION ALL
42 SELECT 0::oid, 'PUBLIC'
43 ) AS grantee (oid, rolname)
44 WHERE c.relnamespace = nc.oid
45 AND c.relkind IN ('r', 'v')
46 AND c.grantee = grantee.oid
47 AND c.grantor = u_grantor.oid
48 AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
49 AND (pg_has_role(u_grantor.oid, 'USAGE')
50 OR pg_has_role(grantee.oid, 'USAGE')
51 OR grantee.rolname = 'PUBLIC' );
52
53
54/*
55 * Apply privileges that exist on a given parent to the given child table
56 */
57CREATE OR REPLACE FUNCTION apply_privileges(p_parent_schema text, p_parent_tablename text, p_child_schema text, p_child_tablename text, p_job_id bigint DEFAULT NULL) RETURNS void
58 LANGUAGE plpgsql SECURITY DEFINER
59 AS $$
60DECLARE
61
62ex_context text;
63ex_detail text;
64ex_hint text;
65ex_message text;
66v_all text[] := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER'];
67v_child_grant record;
68v_child_owner text;
69v_grantees text[];
70v_job_id bigint;
71v_jobmon boolean;
72v_jobmon_schema text;
73v_match boolean;
74v_parent_grant record;
75v_parent_owner text;
76v_revoke text;
77v_row_revoke record;
78v_sql text;
79v_step_id bigint;
80
81BEGIN
82
83SELECT jobmon INTO v_jobmon FROM @extschema@.part_config WHERE parent_table = p_parent_schema ||'.'|| p_parent_tablename;
84IF v_jobmon IS NULL THEN
85 RAISE EXCEPTION 'Given table is not managed by this extention: %.%', p_parent_schema, p_parent_tablename;
86END IF;
87
88SELECT tableowner INTO v_parent_owner FROM pg_catalog.pg_tables WHERE schemaname = p_parent_schema::name AND tablename = p_parent_tablename::name;
89SELECT tableowner INTO v_child_owner FROM pg_catalog.pg_tables WHERE schemaname = p_child_schema::name AND tablename = p_child_tablename::name;
90IF v_parent_owner IS NULL THEN
91 RAISE EXCEPTION 'Given parent table does not exist: %.%', v_parent_schema, v_parent_tablename;
92END IF;
93IF v_child_owner IS NULL THEN
94 RAISE EXCEPTION 'Given child table does not exist: %.%', v_child_schema, v_child_tablename;
95END IF;
96
97IF v_jobmon THEN
98 SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid;
99END IF;
100
101IF v_jobmon_schema IS NOT NULL THEN
102 IF p_job_id IS NULL THEN
103 EXECUTE format('SELECT %I.add_job(%L)', v_jobmon_schema, format('PARTMAN APPLYING PRIVILEGES TO CHILD TABLE: %s.%s', p_child_schema, p_child_tablename)) INTO v_job_id;
104 ELSE
105 v_job_id := p_job_id;
106 END IF;
107 EXECUTE format('SELECT %I.add_step(%L, %L)', v_jobmon_schema, v_job_id, format('Setting new child table privileges for %s.%s', p_child_schema, p_child_tablename)) INTO v_step_id;
108END IF;
109
110IF v_jobmon_schema IS NOT NULL THEN
111
112 EXECUTE format('SELECT %I.update_step(%L, %L, %L)'
113 , v_jobmon_schema
114 , v_step_id
115 , 'PENDING'
116 , format('Applying privileges on child partition: %s.%s'
117 , p_child_schema
118 , p_child_tablename)
119 );
120END IF;
121
122FOR v_parent_grant IN
123 SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types
124 , grantee
125 FROM @extschema@.table_privs
126 WHERE table_schema = p_parent_schema::name AND table_name = p_parent_tablename::name
127 GROUP BY grantee
128LOOP
129 -- Compare parent & child grants. Don't re-apply if it already exists
130 v_match := false;
131 v_sql := NULL;
132 FOR v_child_grant IN
133 SELECT array_agg(DISTINCT privilege_type::text ORDER BY privilege_type::text) AS types
134 , grantee
135 FROM @extschema@.table_privs
136 WHERE table_schema = p_child_schema::name AND table_name = p_child_tablename::name
137 GROUP BY grantee
138 LOOP
139 IF v_parent_grant.types = v_child_grant.types AND v_parent_grant.grantee = v_child_grant.grantee THEN
140 v_match := true;
141 END IF;
142 END LOOP;
143
144 IF v_match = false THEN
145 IF v_parent_grant.grantee = 'PUBLIC' THEN
146 v_sql := 'GRANT %s ON %I.%I TO %s';
147 ELSE
148 v_sql := 'GRANT %s ON %I.%I TO %I';
149 END IF;
150 EXECUTE format(v_sql
151 , array_to_string(v_parent_grant.types, ',')
152 , p_child_schema
153 , p_child_tablename
154 , v_parent_grant.grantee);
155 v_sql := NULL;
156 SELECT string_agg(r, ',') INTO v_revoke FROM (SELECT unnest(v_all) AS r EXCEPT SELECT unnest(v_parent_grant.types)) x;
157 IF v_revoke IS NOT NULL THEN
158 IF v_parent_grant.grantee = 'PUBLIC' THEN
159 v_sql := 'REVOKE %s ON %I.%I FROM %s CASCADE';
160 ELSE
161 v_sql := 'REVOKE %s ON %I.%I FROM %I CASCADE';
162 END IF;
163 EXECUTE format(v_sql
164 , v_revoke
165 , p_child_schema
166 , p_child_tablename
167 , v_parent_grant.grantee);
168 v_sql := NULL;
169 END IF;
170 END IF;
171
172 v_grantees := array_append(v_grantees, v_parent_grant.grantee::text);
173
174END LOOP;
175
176-- Revoke all privileges from roles that have none on the parent
177IF v_grantees IS NOT NULL THEN
178 FOR v_row_revoke IN
179 SELECT role FROM (
180 SELECT DISTINCT grantee::text AS role FROM @extschema@.table_privs WHERE table_schema = p_child_schema::name AND table_name = p_child_tablename::name
181 EXCEPT
182 SELECT unnest(v_grantees)) x
183 LOOP
184 IF v_row_revoke.role IS NOT NULL THEN
185 IF v_row_revoke.role = 'PUBLIC' THEN
186 v_sql := 'REVOKE ALL ON %I.%I FROM %s';
187 ELSE
188 v_sql := 'REVOKE ALL ON %I.%I FROM %I';
189 END IF;
190 EXECUTE format(v_sql
191 , p_child_schema
192 , p_child_tablename
193 , v_row_revoke.role);
194 END IF;
195 END LOOP;
196
197END IF;
198
199IF v_parent_owner <> v_child_owner THEN
200 EXECUTE format('ALTER TABLE %I.%I OWNER TO %I'
201 , p_child_schema
202 , p_child_tablename
203 , v_parent_owner);
204END IF;
205
206IF v_jobmon_schema IS NOT NULL THEN
207 EXECUTE format('SELECT %I.update_step(%L, %L, %L)', v_jobmon_schema, v_step_id, 'OK', 'Done');
208 IF p_job_id IS NULL THEN
209 EXECUTE format('SELECT %I.close_job(%L)', v_jobmon_schema, v_job_id);
210 END IF;
211END IF;
212
213EXCEPTION
214 WHEN OTHERS THEN
215 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
216 ex_context = PG_EXCEPTION_CONTEXT,
217 ex_detail = PG_EXCEPTION_DETAIL,
218 ex_hint = PG_EXCEPTION_HINT;
219 IF v_jobmon_schema IS NOT NULL THEN
220 IF v_job_id IS NULL THEN
221 EXECUTE format('SELECT %I.add_job(''PARTMAN RE-APPLYING PRIVILEGES TO ALL CHILD TABLES OF: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id;
222 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;
223 ELSIF v_step_id IS NULL THEN
224 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
225 END IF;
226 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
227 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
228 END IF;
229 RAISE EXCEPTION '%
230CONTEXT: %
231DETAIL: %
232HINT: %', ex_message, ex_context, ex_detail, ex_hint;
233END
234$$;
235
236
237/*
238 * Create the trigger function for the parent table of an id-based partition set
239 */
240CREATE FUNCTION create_function_id(p_parent_table text, p_job_id bigint DEFAULT NULL, p_analyze boolean DEFAULT true) RETURNS void
241 LANGUAGE plpgsql SECURITY DEFINER
242 AS $$
243DECLARE
244
245ex_context text;
246ex_detail text;
247ex_hint text;
248ex_message text;
249v_control text;
250v_count int;
251v_current_partition_name text;
252v_current_partition_id bigint;
253v_datetime_string text;
254v_final_partition_id bigint;
255v_function_name text;
256v_higher_parent_schema text := split_part(p_parent_table, '.', 1);
257v_higher_parent_table text := split_part(p_parent_table, '.', 2);
258v_id_position int;
259v_job_id bigint;
260v_jobmon text;
261v_jobmon_schema text;
262v_last_partition text;
263v_max bigint;
264v_next_partition_id bigint;
265v_next_partition_name text;
266v_new_search_path text := '@extschema@,pg_temp';
267v_old_search_path text;
268v_optimize_trigger int;
269v_parent_schema text;
270v_parent_tablename text;
271v_partition_interval bigint;
272v_premake int;
273v_prev_partition_id bigint;
274v_prev_partition_name text;
275v_row_max_id record;
276v_run_maint boolean;
277v_step_id bigint;
278v_top_parent text := p_parent_table;
279v_trig_func text;
280v_trigger_exception_handling boolean;
281v_trigger_return_null boolean;
282v_upsert text;
283
284BEGIN
285
286SELECT partition_interval::bigint
287 , control
288 , premake
289 , optimize_trigger
290 , use_run_maintenance
291 , jobmon
292 , trigger_exception_handling
293 , upsert
294 , trigger_return_null
295INTO v_partition_interval
296 , v_control
297 , v_premake
298 , v_optimize_trigger
299 , v_run_maint
300 , v_jobmon
301 , v_trigger_exception_handling
302 , v_upsert
303 , v_trigger_return_null
304FROM @extschema@.part_config
305WHERE parent_table = p_parent_table
306AND partition_type = 'id';
307
308IF NOT FOUND THEN
309 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table;
310END IF;
311
312SELECT current_setting('search_path') INTO v_old_search_path;
313IF v_jobmon THEN
314 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;
315 IF v_jobmon_schema IS NOT NULL THEN
316 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp';
317 END IF;
318END IF;
319EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
320
321SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(p_parent_table, 'DESC') LIMIT 1;
322
323IF v_jobmon_schema IS NOT NULL THEN
324 IF p_job_id IS NULL THEN
325 v_job_id := add_job(format('PARTMAN CREATE FUNCTION: %s', p_parent_table));
326 ELSE
327 v_job_id = p_job_id;
328 END IF;
329 v_step_id := add_step(v_job_id, format('Creating partition function for table %s', p_parent_table));
330END IF;
331
332SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename
333FROM pg_catalog.pg_tables
334WHERE schemaname = split_part(p_parent_table, '.', 1)::name
335AND tablename = split_part(p_parent_table, '.', 2)::name;
336v_function_name := @extschema@.check_name_length(v_parent_tablename, '_part_trig_func', FALSE);
337
338-- Get the highest level top parent if multi-level partitioned in order to get proper max() value below
339WHILE v_higher_parent_table IS NOT NULL LOOP -- initially set in DECLARE
340 WITH top_oid AS (
341 SELECT i.inhparent AS top_parent_oid
342 FROM pg_catalog.pg_inherits i
343 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid
344 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
345 WHERE n.nspname = v_higher_parent_schema::name
346 AND c.relname = v_higher_parent_table::name
347 ) SELECT n.nspname, c.relname
348 INTO v_higher_parent_schema, v_higher_parent_table
349 FROM pg_catalog.pg_class c
350 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
351 JOIN top_oid t ON c.oid = t.top_parent_oid
352 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname
353 WHERE p.partition_type = 'id';
354
355 IF v_higher_parent_table IS NOT NULL THEN
356 -- initially set in DECLARE
357 v_top_parent := v_higher_parent_schema||'.'||v_higher_parent_table;
358 END IF;
359
360END LOOP;
361
362-- Loop through child tables starting from highest to get current max value in partition set
363-- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent.
364FOR v_row_max_id IN
365 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(v_top_parent, 'DESC')
366LOOP
367 EXECUTE format('SELECT max(%I) FROM %I.%I', v_control, v_row_max_id.partition_schemaname, v_row_max_id.partition_tablename) INTO v_max;
368 IF v_max IS NOT NULL THEN
369 EXIT;
370 END IF;
371END LOOP;
372IF v_max IS NULL THEN
373 v_max := 0;
374END IF;
375v_current_partition_id = v_max - (v_max % v_partition_interval);
376v_next_partition_id := v_current_partition_id + v_partition_interval;
377v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_current_partition_id::text, TRUE);
378
379v_trig_func := format('CREATE OR REPLACE FUNCTION %I.%I() RETURNS trigger LANGUAGE plpgsql AS $t$
380 DECLARE
381 v_count int;
382 v_current_partition_id bigint;
383 v_current_partition_name text;
384 v_id_position int;
385 v_last_partition text := %L;
386 v_next_partition_id bigint;
387 v_next_partition_name text;
388 v_partition_created boolean;
389 BEGIN
390 IF TG_OP = ''INSERT'' THEN
391 IF NEW.%I >= %s AND NEW.%I < %s THEN '
392 , v_parent_schema
393 , v_function_name
394 , v_last_partition
395 , v_control
396 , v_current_partition_id
397 , v_control
398 , v_next_partition_id
399 );
400 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_current_partition_name::name;
401 IF v_count > 0 THEN
402 v_trig_func := v_trig_func || format('
403 INSERT INTO %I.%I VALUES (NEW.*) %s; ', v_parent_schema, v_current_partition_name, v_upsert);
404 ELSE
405 v_trig_func := v_trig_func || '
406 -- Child table for current values does not exist in this partition set, so write to parent
407 RETURN NEW;';
408 END IF;
409
410 FOR i IN 1..v_optimize_trigger LOOP
411 v_prev_partition_id := v_current_partition_id - (v_partition_interval * i);
412 v_next_partition_id := v_current_partition_id + (v_partition_interval * i);
413 v_final_partition_id := v_next_partition_id + v_partition_interval;
414 v_prev_partition_name := @extschema@.check_name_length(v_parent_tablename, v_prev_partition_id::text, TRUE);
415 v_next_partition_name := @extschema@.check_name_length(v_parent_tablename, v_next_partition_id::text, TRUE);
416
417 -- Check that child table exist before making a rule to insert to them.
418 -- 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().
419 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_prev_partition_name::name;
420 IF v_count > 0 THEN
421 -- Only handle previous partitions if they're starting above zero
422 IF v_prev_partition_id >= 0 THEN
423 v_trig_func := v_trig_func ||format('
424 ELSIF NEW.%I >= %s AND NEW.%I < %s THEN
425 INSERT INTO %I.%I VALUES (NEW.*) %s; '
426 , v_control
427 , v_prev_partition_id
428 , v_control
429 , v_prev_partition_id + v_partition_interval
430 , v_parent_schema
431 , v_prev_partition_name
432 , v_upsert
433 );
434 END IF;
435 END IF;
436
437 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = v_parent_schema::name AND tablename = v_next_partition_name::name;
438 IF v_count > 0 THEN
439 v_trig_func := v_trig_func ||format('
440 ELSIF NEW.%I >= %s AND NEW.%I < %s THEN
441 INSERT INTO %I.%I VALUES (NEW.*) %s;'
442 , v_control
443 , v_next_partition_id
444 , v_control
445 , v_final_partition_id
446 , v_parent_schema
447 , v_next_partition_name
448 , v_upsert
449 );
450 END IF;
451 END LOOP;
452 v_trig_func := v_trig_func ||format('
453 ELSE
454 v_current_partition_id := NEW.%I - (NEW.%I %% %s);
455 v_current_partition_name := @extschema@.check_name_length(%L, v_current_partition_id::text, TRUE);
456 SELECT count(*) INTO v_count FROM pg_catalog.pg_tables WHERE schemaname = %L::name AND tablename = v_current_partition_name::name;
457 IF v_count > 0 THEN
458 EXECUTE format(''INSERT INTO %%I.%%I VALUES($1.*) %s'', %L, v_current_partition_name) USING NEW;
459 ELSE
460 RETURN NEW;
461 END IF;
462 END IF;'
463 , v_control
464 , v_control
465 , v_partition_interval
466 , v_parent_tablename
467 , v_parent_schema
468 , v_upsert
469 , v_parent_schema
470 );
471
472 IF v_run_maint IS FALSE THEN
473 v_trig_func := v_trig_func ||format('
474 v_current_partition_id := NEW.%I - (NEW.%I %% %s);
475 IF (NEW.%I %% %s) > (%s / 2) THEN
476 v_id_position := (length(v_last_partition) - position(''p_'' in reverse(v_last_partition))) + 2;
477 v_next_partition_id := (substring(v_last_partition from v_id_position)::bigint) + %s;
478 WHILE ((v_next_partition_id - v_current_partition_id) / %s) <= %s LOOP
479 v_partition_created := @extschema@.create_partition_id(%L, ARRAY[v_next_partition_id], p_analyze := %L);
480 IF v_partition_created THEN
481 PERFORM @extschema@.create_function_id(%L, p_analyze := %L);
482 PERFORM @extschema@.apply_constraints(%L);
483 END IF;
484 v_next_partition_id := v_next_partition_id + %s;
485 END LOOP;
486 END IF;'
487 , v_control
488 , v_control
489 , v_partition_interval
490 , v_control
491 , v_partition_interval
492 , v_partition_interval
493 , v_partition_interval
494 , v_partition_interval
495 , v_premake
496 , p_parent_table
497 , p_analyze
498 , p_parent_table
499 , p_analyze
500 , p_parent_table
501 , v_partition_interval
502 );
503 END IF;
504
505 v_trig_func := v_trig_func ||'
506 END IF;';
507
508 IF v_trigger_return_null IS TRUE THEN
509 v_trig_func := v_trig_func ||'
510 RETURN NULL;';
511 ELSE
512 v_trig_func := v_trig_func ||'
513 RETURN NEW;';
514 END IF;
515
516 IF v_trigger_exception_handling THEN
517 v_trig_func := v_trig_func ||'
518 EXCEPTION WHEN OTHERS THEN
519 RAISE WARNING ''pg_partman insert into child table failed, row inserted into parent (%.%). ERROR: %'', TG_TABLE_SCHEMA, TG_TABLE_NAME, COALESCE(SQLERRM, ''unknown'');
520 RETURN NEW;';
521 END IF;
522
523 v_trig_func := v_trig_func ||'
524 END $t$;';
525
526EXECUTE v_trig_func;
527
528IF v_jobmon_schema IS NOT NULL THEN
529 PERFORM update_step(v_step_id, 'OK', format('Added function for current id interval: %s to %s', v_current_partition_id, v_final_partition_id-1));
530END IF;
531
532IF v_jobmon_schema IS NOT NULL THEN
533 PERFORM close_job(v_job_id);
534END IF;
535
536EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
537
538EXCEPTION
539 WHEN OTHERS THEN
540 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
541 ex_context = PG_EXCEPTION_CONTEXT,
542 ex_detail = PG_EXCEPTION_DETAIL,
543 ex_hint = PG_EXCEPTION_HINT;
544 IF v_jobmon_schema IS NOT NULL THEN
545 IF v_job_id IS NULL THEN
546 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE FUNCTION: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id;
547 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;
548 ELSIF v_step_id IS NULL THEN
549 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
550 END IF;
551 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
552 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
553 END IF;
554 RAISE EXCEPTION '%
555CONTEXT: %
556DETAIL: %
557HINT: %', ex_message, ex_context, ex_detail, ex_hint;
558END
559$$;
560
561
562
563/*
564 * Populate the child table(s) of an id-based partition set with old data from the original parent
565 */
566CREATE FUNCTION partition_data_id(p_parent_table text
567 , p_batch_count int DEFAULT 1
568 , p_batch_interval bigint DEFAULT NULL
569 , p_lock_wait numeric DEFAULT 0
570 , p_order text DEFAULT 'ASC'
571 , p_analyze boolean DEFAULT true)
572 RETURNS bigint
573 LANGUAGE plpgsql SECURITY DEFINER
574 AS $$
575DECLARE
576
577v_control text;
578v_current_partition_name text;
579v_lock_iter int := 1;
580v_lock_obtained boolean := FALSE;
581v_max_partition_id bigint;
582v_min_partition_id bigint;
583v_new_search_path text := '@extschema@,pg_temp';
584v_old_search_path text;
585v_parent_schema text;
586v_parent_tablename text;
587v_partition_interval bigint;
588v_partition_id bigint[];
589v_rowcount bigint;
590v_sql text;
591v_start_control bigint;
592v_total_rows bigint := 0;
593
594BEGIN
595
596SELECT partition_interval::bigint
597 , control
598INTO v_partition_interval
599 , v_control
600FROM @extschema@.part_config
601WHERE parent_table = p_parent_table
602AND partition_type = 'id';
603IF NOT FOUND THEN
604 RAISE EXCEPTION 'ERROR: no config found for %', p_parent_table;
605END IF;
606
607SELECT current_setting('search_path') INTO v_old_search_path;
608EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
609
610SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename
611FROM pg_catalog.pg_tables
612WHERE schemaname = split_part(p_parent_table, '.', 1)::name
613AND tablename = split_part(p_parent_table, '.', 2)::name;
614
615IF p_batch_interval IS NULL OR p_batch_interval > v_partition_interval THEN
616 p_batch_interval := v_partition_interval;
617END IF;
618
619FOR i IN 1..p_batch_count LOOP
620
621 IF p_order = 'ASC' THEN
622 EXECUTE format('SELECT min(%I) FROM ONLY %I.%I', v_control, v_parent_schema, v_parent_tablename) INTO v_start_control;
623 IF v_start_control IS NULL THEN
624 EXIT;
625 END IF;
626 v_min_partition_id = v_start_control - (v_start_control % v_partition_interval);
627 v_partition_id := ARRAY[v_min_partition_id];
628 -- Check if custom batch interval overflows current partition maximum
629 IF (v_start_control + p_batch_interval) >= (v_min_partition_id + v_partition_interval) THEN
630 v_max_partition_id := v_min_partition_id + v_partition_interval;
631 ELSE
632 v_max_partition_id := v_start_control + p_batch_interval;
633 END IF;
634
635 ELSIF p_order = 'DESC' THEN
636 EXECUTE 'SELECT max('||v_control||') FROM ONLY '||p_parent_table INTO v_start_control;
637 IF v_start_control IS NULL THEN
638 EXIT;
639 END IF;
640 v_min_partition_id = v_start_control - (v_start_control % v_partition_interval);
641 -- Must be greater than max value still in parent table since query below grabs < max
642 v_max_partition_id := v_min_partition_id + v_partition_interval;
643 v_partition_id := ARRAY[v_min_partition_id];
644 -- Make sure minimum doesn't underflow current partition minimum
645 IF (v_start_control - p_batch_interval) >= v_min_partition_id THEN
646 v_min_partition_id = v_start_control - p_batch_interval;
647 END IF;
648 ELSE
649 RAISE EXCEPTION 'Invalid value for p_order. Must be ASC or DESC';
650 END IF;
651
652 -- do some locking with timeout, if required
653 IF p_lock_wait > 0 THEN
654 v_lock_iter := 0;
655 WHILE v_lock_iter <= 5 LOOP
656 v_lock_iter := v_lock_iter + 1;
657 BEGIN
658 v_sql := format('SELECT * FROM ONLY %I.%I WHERE %I >= %s AND %I < %s FOR UPDATE NOWAIT'
659 , v_parent_schema
660 , v_parent_tablename
661 , v_control
662 , v_min_partition_id
663 , v_control
664 , v_max_partition_id);
665 EXECUTE v_sql;
666 v_lock_obtained := TRUE;
667 EXCEPTION
668 WHEN lock_not_available THEN
669 PERFORM pg_sleep( p_lock_wait / 5.0 );
670 CONTINUE;
671 END;
672 EXIT WHEN v_lock_obtained;
673 END LOOP;
674 IF NOT v_lock_obtained THEN
675 RETURN -1;
676 END IF;
677 END IF;
678
679 PERFORM @extschema@.create_partition_id(p_parent_table, v_partition_id, p_analyze);
680 v_current_partition_name := @extschema@.check_name_length(v_parent_tablename, v_min_partition_id::text, TRUE);
681
682 EXECUTE format('WITH partition_data AS (
683 DELETE FROM ONLY %I.%I WHERE %I >= %s AND %I < %s RETURNING *)
684 INSERT INTO %I.%I SELECT * FROM partition_data'
685 , v_parent_schema
686 , v_parent_tablename
687 , v_control
688 , v_min_partition_id
689 , v_control
690 , v_max_partition_id
691 , v_parent_schema
692 , v_current_partition_name);
693
694 GET DIAGNOSTICS v_rowcount = ROW_COUNT;
695 v_total_rows := v_total_rows + v_rowcount;
696 IF v_rowcount = 0 THEN
697 EXIT;
698 END IF;
699
700END LOOP;
701
702PERFORM @extschema@.create_function_id(p_parent_table, NULL, p_analyze);
703
704EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
705
706RETURN v_total_rows;
707
708END
709$$;
710
711
712/*
713 * Function to turn a table into the parent of a partition set
714 */
715CREATE OR REPLACE FUNCTION create_parent(
716 p_parent_table text
717 , p_control text
718 , p_type text
719 , p_interval text
720 , p_constraint_cols text[] DEFAULT NULL
721 , p_premake int DEFAULT 4
722 , p_use_run_maintenance boolean DEFAULT NULL
723 , p_start_partition text DEFAULT NULL
724 , p_inherit_fk boolean DEFAULT true
725 , p_epoch boolean DEFAULT false
726 , p_upsert text DEFAULT ''
727 , p_trigger_return_null boolean DEFAULT true
728 , p_jobmon boolean DEFAULT true
729 , p_debug boolean DEFAULT false)
730RETURNS boolean
731 LANGUAGE plpgsql SECURITY DEFINER
732 AS $$
733DECLARE
734
735ex_context text;
736ex_detail text;
737ex_hint text;
738ex_message text;
739v_base_timestamp timestamptz;
740v_count int := 1;
741v_datetime_string text;
742v_higher_parent_schema text := split_part(p_parent_table, '.', 1);
743v_higher_parent_table text := split_part(p_parent_table, '.', 2);
744v_id_interval bigint;
745v_job_id bigint;
746v_jobmon_schema text;
747v_last_partition_created boolean;
748v_max bigint;
749v_notnull boolean;
750v_new_search_path text := '@extschema@,pg_temp';
751v_old_search_path text;
752v_parent_partition_id bigint;
753v_parent_partition_timestamp timestamptz;
754v_parent_schema text;
755v_parent_tablename text;
756v_partition_time timestamptz;
757v_partition_time_array timestamptz[];
758v_partition_id_array bigint[];
759v_row record;
760v_run_maint boolean;
761v_sql text;
762v_start_time timestamptz;
763v_starting_partition_id bigint;
764v_step_id bigint;
765v_step_overflow_id bigint;
766v_sub_parent text;
767v_success boolean := false;
768v_time_interval interval;
769v_top_datetime_string text;
770v_top_parent_schema text := split_part(p_parent_table, '.', 1);
771v_top_parent_table text := split_part(p_parent_table, '.', 2);
772
773BEGIN
774
775IF position('.' in p_parent_table) = 0 THEN
776 RAISE EXCEPTION 'Parent table must be schema qualified';
777END IF;
778
779IF p_upsert <> '' AND @extschema@.check_version('9.5.0') = 'false' THEN
780 RAISE EXCEPTION 'INSERT ... ON CONFLICT (UPSERT) feature is only supported in PostgreSQL 9.5 and later';
781END IF;
782
783SELECT schemaname, tablename INTO v_parent_schema, v_parent_tablename
784FROM pg_catalog.pg_tables
785WHERE schemaname = split_part(p_parent_table, '.', 1)::name
786AND tablename = split_part(p_parent_table, '.', 2)::name;
787 IF v_parent_tablename IS NULL THEN
788 RAISE EXCEPTION 'Unable to find given parent table in system catalogs. Please create parent table first: %', p_parent_table;
789 END IF;
790
791SELECT attnotnull INTO v_notnull
792FROM pg_catalog.pg_attribute a
793JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
794JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
795WHERE c.relname = v_parent_tablename::name
796AND n.nspname = v_parent_schema::name
797AND a.attname = p_control::name;
798 IF v_notnull = false OR v_notnull IS NULL THEN
799 RAISE EXCEPTION 'Control column given (%) for parent table (%) does not exist or must be set to NOT NULL', p_control, p_parent_table;
800 END IF;
801
802IF p_type = 'id' AND p_epoch = true THEN
803 RAISE EXCEPTION 'p_epoch can only be used with time-based partitioning';
804END IF;
805
806IF NOT @extschema@.check_partition_type(p_type) THEN
807 RAISE EXCEPTION '% is not a valid partitioning type', p_type;
808END IF;
809
810SELECT current_setting('search_path') INTO v_old_search_path;
811IF p_jobmon THEN
812 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;
813 IF v_jobmon_schema IS NOT NULL THEN
814 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp';
815 END IF;
816END IF;
817EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
818
819IF p_use_run_maintenance IS NOT NULL THEN
820 IF p_use_run_maintenance IS FALSE AND (p_type = 'time' OR p_type = 'time-custom') THEN
821 RAISE EXCEPTION 'p_run_maintenance cannot be set to false for time based partitioning';
822 END IF;
823 v_run_maint := p_use_run_maintenance;
824ELSIF p_type = 'time' OR p_type = 'time-custom' THEN
825 v_run_maint := TRUE;
826ELSIF p_type = 'id' THEN
827 v_run_maint := FALSE;
828ELSE
829 RAISE EXCEPTION 'use_run_maintenance value cannot be set NULL';
830END IF;
831
832EXECUTE format('LOCK TABLE %I.%I IN ACCESS EXCLUSIVE MODE', v_parent_schema, v_parent_tablename);
833
834IF v_jobmon_schema IS NOT NULL THEN
835 v_job_id := add_job(format('PARTMAN SETUP PARENT: %s', p_parent_table));
836 v_step_id := add_step(v_job_id, format('Creating initial partitions on new parent table: %s', p_parent_table));
837END IF;
838
839-- 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
840-- 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.)
841FOR v_row IN
842 WITH parent_table AS (
843 SELECT h.inhparent AS parent_oid
844 FROM pg_catalog.pg_inherits h
845 JOIN pg_catalog.pg_class c ON h.inhrelid = c.oid
846 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
847 WHERE c.relname = v_parent_tablename::name
848 AND n.nspname = v_parent_schema::name
849 ), sibling_children AS (
850 SELECT i.inhrelid::regclass::text AS tablename
851 FROM pg_inherits i
852 JOIN parent_table p ON i.inhparent = p.parent_oid
853 )
854 SELECT DISTINCT sub_partition_type
855 , sub_control
856 , sub_partition_interval
857 , sub_constraint_cols
858 , sub_premake
859 , sub_inherit_fk
860 , sub_retention
861 , sub_retention_schema
862 , sub_retention_keep_table
863 , sub_retention_keep_index
864 , sub_use_run_maintenance
865 , sub_epoch
866 , sub_optimize_trigger
867 , sub_optimize_constraint
868 , sub_infinite_time_partitions
869 , sub_jobmon
870 , sub_trigger_exception_handling
871 , sub_upsert
872 , sub_trigger_return_null
873 FROM @extschema@.part_config_sub a
874 JOIN sibling_children b on a.sub_parent = b.tablename LIMIT 1
875LOOP
876 INSERT INTO @extschema@.part_config_sub (
877 sub_parent
878 , sub_partition_type
879 , sub_control
880 , sub_partition_interval
881 , sub_constraint_cols
882 , sub_premake
883 , sub_inherit_fk
884 , sub_retention
885 , sub_retention_schema
886 , sub_retention_keep_table
887 , sub_retention_keep_index
888 , sub_use_run_maintenance
889 , sub_epoch
890 , sub_optimize_trigger
891 , sub_optimize_constraint
892 , sub_infinite_time_partitions
893 , sub_jobmon
894 , sub_trigger_exception_handling
895 , sub_upsert
896 , sub_trigger_return_null)
897 VALUES (
898 p_parent_table
899 , v_row.sub_partition_type
900 , v_row.sub_control
901 , v_row.sub_partition_interval
902 , v_row.sub_constraint_cols
903 , v_row.sub_premake
904 , v_row.sub_inherit_fk
905 , v_row.sub_retention
906 , v_row.sub_retention_schema
907 , v_row.sub_retention_keep_table
908 , v_row.sub_retention_keep_index
909 , v_row.sub_use_run_maintenance
910 , v_row.sub_epoch
911 , v_row.sub_optimize_trigger
912 , v_row.sub_optimize_constraint
913 , v_row.sub_infinite_time_partitions
914 , v_row.sub_jobmon
915 , v_row.sub_trigger_exception_handling
916 , v_row.sub_upsert
917 , v_row.sub_trigger_return_null);
918END LOOP;
919
920IF p_type = 'time' OR p_type = 'time-custom' THEN
921
922 CASE
923 WHEN p_interval = 'yearly' THEN
924 v_time_interval := '1 year';
925 WHEN p_interval = 'quarterly' THEN
926 v_time_interval := '3 months';
927 WHEN p_interval = 'monthly' THEN
928 v_time_interval := '1 month';
929 WHEN p_interval = 'weekly' THEN
930 v_time_interval := '1 week';
931 WHEN p_interval = 'daily' THEN
932 v_time_interval := '1 day';
933 WHEN p_interval = 'hourly' THEN
934 v_time_interval := '1 hour';
935 WHEN p_interval = 'half-hour' THEN
936 v_time_interval := '30 mins';
937 WHEN p_interval = 'quarter-hour' THEN
938 v_time_interval := '15 mins';
939 ELSE
940 IF p_type <> 'time-custom' THEN
941 RAISE EXCEPTION 'Must use a predefined time interval if not using type "time-custom". See documentation.';
942 END IF;
943 v_time_interval := p_interval::interval;
944 IF v_time_interval < '1 second'::interval THEN
945 RAISE EXCEPTION 'Partitioning interval must be 1 second or greater';
946 END IF;
947 END CASE;
948
949 -- First partition is either the min premake or p_start_partition
950 v_start_time := COALESCE(p_start_partition::timestamptz, CURRENT_TIMESTAMP - (v_time_interval * p_premake));
951
952 IF v_time_interval >= '1 year' THEN
953 v_base_timestamp := date_trunc('year', v_start_time);
954 IF v_time_interval >= '10 years' THEN
955 v_base_timestamp := date_trunc('decade', v_start_time);
956 IF v_time_interval >= '100 years' THEN
957 v_base_timestamp := date_trunc('century', v_start_time);
958 IF v_time_interval >= '1000 years' THEN
959 v_base_timestamp := date_trunc('millennium', v_start_time);
960 END IF; -- 1000
961 END IF; -- 100
962 END IF; -- 10
963 END IF; -- 1
964
965 v_datetime_string := 'YYYY';
966 IF v_time_interval < '1 year' THEN
967 IF p_interval = 'quarterly' THEN
968 v_base_timestamp := date_trunc('quarter', v_start_time);
969 v_datetime_string = 'YYYY"q"Q';
970 ELSE
971 v_base_timestamp := date_trunc('month', v_start_time);
972 v_datetime_string := v_datetime_string || '_MM';
973 END IF;
974 IF v_time_interval < '1 month' THEN
975 IF p_interval = 'weekly' THEN
976 v_base_timestamp := date_trunc('week', v_start_time);
977 v_datetime_string := 'IYYY"w"IW';
978 ELSE
979 v_base_timestamp := date_trunc('day', v_start_time);
980 v_datetime_string := v_datetime_string || '_DD';
981 END IF;
982 IF v_time_interval < '1 day' THEN
983 v_base_timestamp := date_trunc('hour', v_start_time);
984 v_datetime_string := v_datetime_string || '_HH24MI';
985 IF v_time_interval < '1 minute' THEN
986 v_base_timestamp := date_trunc('minute', v_start_time);
987 v_datetime_string := v_datetime_string || 'SS';
988 END IF; -- minute
989 END IF; -- day
990 END IF; -- month
991 END IF; -- year
992
993 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp);
994 LOOP
995 -- If current loop value is less than or equal to the value of the max premake, add time to array.
996 IF (v_base_timestamp + (v_time_interval * v_count)) < (CURRENT_TIMESTAMP + (v_time_interval * p_premake)) THEN
997 BEGIN
998 v_partition_time := (v_base_timestamp + (v_time_interval * v_count))::timestamptz;
999 v_partition_time_array := array_append(v_partition_time_array, v_partition_time);
1000 EXCEPTION WHEN datetime_field_overflow THEN
1001 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range.
1002 Child partition creation after time % skipped', v_partition_time;
1003 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.');
1004 PERFORM update_step(v_step_overflow_id, 'CRITICAL', 'Child partition creation after time '||v_partition_time||' skipped');
1005 CONTINUE;
1006 END;
1007 ELSE
1008 EXIT; -- all needed partitions added to array. Exit the loop.
1009 END IF;
1010 v_count := v_count + 1;
1011 END LOOP;
1012
1013 INSERT INTO @extschema@.part_config (
1014 parent_table
1015 , partition_type
1016 , partition_interval
1017 , epoch
1018 , control
1019 , premake
1020 , constraint_cols
1021 , datetime_string
1022 , use_run_maintenance
1023 , inherit_fk
1024 , jobmon
1025 , upsert
1026 , trigger_return_null)
1027 VALUES (
1028 p_parent_table
1029 , p_type
1030 , v_time_interval
1031 , p_epoch
1032 , p_control
1033 , p_premake
1034 , p_constraint_cols
1035 , v_datetime_string
1036 , v_run_maint
1037 , p_inherit_fk
1038 , p_jobmon
1039 , p_upsert
1040 , p_trigger_return_null);
1041
1042 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false);
1043
1044 IF v_last_partition_created = false THEN
1045 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent
1046 -- First see if this parent is a subpartition managed by pg_partman
1047 WITH top_oid AS (
1048 SELECT i.inhparent AS top_parent_oid
1049 FROM pg_catalog.pg_inherits i
1050 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid
1051 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
1052 WHERE c.relname = v_parent_tablename::name
1053 AND n.nspname = v_parent_schema::name
1054 ) SELECT n.nspname, c.relname
1055 INTO v_top_parent_schema, v_top_parent_table
1056 FROM pg_catalog.pg_class c
1057 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
1058 JOIN top_oid t ON c.oid = t.top_parent_oid
1059 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname;
1060 IF v_top_parent_table IS NOT NULL THEN
1061 -- If so create the lowest possible partition that is within the boundary of the parent
1062 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);
1063 IF v_base_timestamp >= v_parent_partition_timestamp THEN
1064 WHILE v_base_timestamp >= v_parent_partition_timestamp LOOP
1065 v_base_timestamp := v_base_timestamp - v_time_interval;
1066 END LOOP;
1067 v_base_timestamp := v_base_timestamp + v_time_interval; -- add one back since while loop set it one lower than is needed
1068 ELSIF v_base_timestamp < v_parent_partition_timestamp THEN
1069 WHILE v_base_timestamp < v_parent_partition_timestamp LOOP
1070 v_base_timestamp := v_base_timestamp + v_time_interval;
1071 END LOOP;
1072 -- Don't need to remove one since new starting time will fit in top parent interval
1073 END IF;
1074 v_partition_time_array := NULL;
1075 v_partition_time_array := array_append(v_partition_time_array, v_base_timestamp);
1076 v_last_partition_created := @extschema@.create_partition_time(p_parent_table, v_partition_time_array, false);
1077 ELSE
1078 -- Currently unknown edge case if code gets here
1079 RAISE EXCEPTION 'No child tables created. Unexpected edge case encountered. Please report this error to author with conditions that led to it.';
1080 END IF;
1081 END IF;
1082
1083 IF v_jobmon_schema IS NOT NULL THEN
1084 PERFORM update_step(v_step_id, 'OK', format('Time partitions premade: %s', p_premake));
1085 END IF;
1086END IF;
1087
1088IF p_type = 'id' THEN
1089 v_id_interval := p_interval::bigint;
1090 IF v_id_interval < 10 THEN
1091 RAISE EXCEPTION 'Interval for serial partitioning must be greater than or equal to 10';
1092 END IF;
1093
1094 -- Check if parent table is a subpartition of an already existing id partition set managed by pg_partman.
1095 WHILE v_higher_parent_table IS NOT NULL LOOP -- initially set in DECLARE
1096 WITH top_oid AS (
1097 SELECT i.inhparent AS top_parent_oid
1098 FROM pg_catalog.pg_inherits i
1099 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid
1100 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
1101 WHERE n.nspname = v_higher_parent_schema::name
1102 AND c.relname = v_higher_parent_table::name
1103 ) SELECT n.nspname, c.relname
1104 INTO v_higher_parent_schema, v_higher_parent_table
1105 FROM pg_catalog.pg_class c
1106 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
1107 JOIN top_oid t ON c.oid = t.top_parent_oid
1108 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname
1109 WHERE p.partition_type = 'id';
1110
1111 IF v_higher_parent_table IS NOT NULL THEN
1112 -- v_top_parent initially set in DECLARE
1113 v_top_parent_schema := v_higher_parent_schema;
1114 v_top_parent_table := v_higher_parent_table;
1115 END IF;
1116 END LOOP;
1117
1118 -- If custom start partition is set, use that.
1119 -- 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
1120 IF p_start_partition IS NOT NULL THEN
1121 v_max := p_start_partition::bigint;
1122 ELSE
1123 v_sql := format('SELECT COALESCE(max(%I)::bigint, 0) FROM %I.%I LIMIT 1'
1124 , p_control
1125 , v_top_parent_schema
1126 , v_top_parent_table);
1127 EXECUTE v_sql INTO v_max;
1128 END IF;
1129 v_starting_partition_id := v_max - (v_max % v_id_interval);
1130 FOR i IN 0..p_premake LOOP
1131 -- Only make previous partitions if ID value is less than the starting value and positive (and custom start partition wasn't set)
1132 IF p_start_partition IS NULL AND
1133 (v_starting_partition_id - (v_id_interval*i)) > 0 AND
1134 (v_starting_partition_id - (v_id_interval*i)) < v_starting_partition_id
1135 THEN
1136 v_partition_id_array = array_append(v_partition_id_array, (v_starting_partition_id - v_id_interval*i));
1137 END IF;
1138 v_partition_id_array = array_append(v_partition_id_array, (v_id_interval*i) + v_starting_partition_id);
1139 END LOOP;
1140
1141 INSERT INTO @extschema@.part_config (
1142 parent_table
1143 , partition_type
1144 , partition_interval
1145 , control
1146 , premake
1147 , constraint_cols
1148 , use_run_maintenance
1149 , inherit_fk
1150 , jobmon
1151 , upsert
1152 , trigger_return_null)
1153 VALUES (
1154 p_parent_table
1155 , p_type
1156 , v_id_interval
1157 , p_control
1158 , p_premake
1159 , p_constraint_cols
1160 , v_run_maint
1161 , p_inherit_fk
1162 , p_jobmon
1163 , p_upsert
1164 , p_trigger_return_null);
1165 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false);
1166 IF v_last_partition_created = false THEN
1167 -- This can happen with subpartitioning when future or past partitions prevent child creation because they're out of range of the parent
1168 -- See if it's actually a subpartition of a parent id partition
1169 WITH top_oid AS (
1170 SELECT i.inhparent AS top_parent_oid
1171 FROM pg_catalog.pg_inherits i
1172 JOIN pg_catalog.pg_class c ON c.oid = i.inhrelid
1173 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
1174 WHERE c.relname = v_parent_tablename::name
1175 AND n.nspname = v_parent_schema::name
1176 ) SELECT n.nspname||'.'||c.relname
1177 INTO v_top_parent_table
1178 FROM pg_catalog.pg_class c
1179 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
1180 JOIN top_oid t ON c.oid = t.top_parent_oid
1181 JOIN @extschema@.part_config p ON p.parent_table = n.nspname||'.'||c.relname
1182 WHERE p.partition_type = 'id';
1183 IF v_top_parent_table IS NOT NULL THEN
1184 -- Create the lowest possible partition that is within the boundary of the parent
1185 SELECT child_start_id INTO v_parent_partition_id FROM @extschema@.show_partition_info(p_parent_table, p_parent_table := v_top_parent_table);
1186 IF v_starting_partition_id >= v_parent_partition_id THEN
1187 WHILE v_starting_partition_id >= v_parent_partition_id LOOP
1188 v_starting_partition_id := v_starting_partition_id - v_id_interval;
1189 END LOOP;
1190 v_starting_partition_id := v_starting_partition_id + v_id_interval; -- add one back since while loop set it one lower than is needed
1191 ELSIF v_starting_partition_id < v_parent_partition_id THEN
1192 WHILE v_starting_partition_id < v_parent_partition_id LOOP
1193 v_starting_partition_id := v_starting_partition_id + v_id_interval;
1194 END LOOP;
1195 -- Don't need to remove one since new starting id will fit in top parent interval
1196 END IF;
1197 v_partition_id_array = NULL;
1198 v_partition_id_array = array_append(v_partition_id_array, v_starting_partition_id);
1199 v_last_partition_created := @extschema@.create_partition_id(p_parent_table, v_partition_id_array, false);
1200 ELSE
1201 -- Currently unknown edge case if code gets here
1202 RAISE EXCEPTION 'No child tables created. Unexpected edge case encountered. Please report this error to author with conditions that led to it.';
1203 END IF;
1204 END IF;
1205END IF;
1206
1207IF v_jobmon_schema IS NOT NULL THEN
1208 v_step_id := add_step(v_job_id, 'Creating partition function');
1209END IF;
1210IF p_type = 'time' OR p_type = 'time-custom' THEN
1211 PERFORM @extschema@.create_function_time(p_parent_table, v_job_id);
1212 IF v_jobmon_schema IS NOT NULL THEN
1213 PERFORM update_step(v_step_id, 'OK', 'Time function created');
1214 END IF;
1215ELSIF p_type = 'id' THEN
1216 PERFORM @extschema@.create_function_id(p_parent_table, v_job_id);
1217 IF v_jobmon_schema IS NOT NULL THEN
1218 PERFORM update_step(v_step_id, 'OK', 'ID function created');
1219 END IF;
1220END IF;
1221
1222IF v_jobmon_schema IS NOT NULL THEN
1223 v_step_id := add_step(v_job_id, 'Creating partition trigger');
1224END IF;
1225PERFORM @extschema@.create_trigger(p_parent_table);
1226
1227IF v_jobmon_schema IS NOT NULL THEN
1228 PERFORM update_step(v_step_id, 'OK', 'Done');
1229 IF v_step_overflow_id IS NOT NULL THEN
1230 PERFORM fail_job(v_job_id);
1231 ELSE
1232 PERFORM close_job(v_job_id);
1233 END IF;
1234END IF;
1235
1236EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1237
1238v_success := true;
1239
1240RETURN v_success;
1241
1242EXCEPTION
1243 WHEN OTHERS THEN
1244 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
1245 ex_context = PG_EXCEPTION_CONTEXT,
1246 ex_detail = PG_EXCEPTION_DETAIL,
1247 ex_hint = PG_EXCEPTION_HINT;
1248 IF v_jobmon_schema IS NOT NULL THEN
1249 IF v_job_id IS NULL THEN
1250 EXECUTE format('SELECT %I.add_job(''PARTMAN CREATE PARENT: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id;
1251 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;
1252 ELSIF v_step_id IS NULL THEN
1253 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
1254 END IF;
1255 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
1256 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
1257 END IF;
1258 RAISE EXCEPTION '%
1259CONTEXT: %
1260DETAIL: %
1261HINT: %', ex_message, ex_context, ex_detail, ex_hint;
1262END
1263$$;
1264
1265
1266-- Removed unneeded variable
1267/*
1268 * Function to manage pre-creation of the next partitions in a set.
1269 * Also manages dropping old partitions if the retention option is set.
1270 * 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)
1271 * Otherwise, will run on all tables in the config table with p_run_maintenance() set to true.
1272 * 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.
1273 * Be aware that constraint exclusion may not work properly until an analyze on the partition set is run.
1274 */
1275CREATE 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
1276 LANGUAGE plpgsql SECURITY DEFINER
1277 AS $$
1278DECLARE
1279
1280ex_context text;
1281ex_detail text;
1282ex_hint text;
1283ex_message text;
1284v_adv_lock boolean;
1285v_check_subpart int;
1286v_create_count int := 0;
1287v_current_partition text;
1288v_current_partition_id bigint;
1289v_current_partition_timestamp timestamptz;
1290v_drop_count int := 0;
1291v_job_id bigint;
1292v_jobmon boolean;
1293v_jobmon_schema text;
1294v_last_partition text;
1295v_last_partition_created boolean;
1296v_last_partition_id bigint;
1297v_last_partition_timestamp timestamptz;
1298v_max_id_parent bigint;
1299v_max_time_parent timestamptz;
1300v_new_search_path text := '@extschema@,pg_temp';
1301v_next_partition_id bigint;
1302v_next_partition_timestamp timestamptz;
1303v_old_search_path text;
1304v_parent_schema text;
1305v_parent_tablename text;
1306v_premade_count int;
1307v_premake_id_max bigint;
1308v_premake_id_min bigint;
1309v_premake_timestamp_min timestamptz;
1310v_premake_timestamp_max timestamptz;
1311v_row record;
1312v_row_max_id record;
1313v_row_max_time record;
1314v_row_sub record;
1315v_step_id bigint;
1316v_step_overflow_id bigint;
1317v_sub_id_max bigint;
1318v_sub_id_max_suffix bigint;
1319v_sub_id_min bigint;
1320v_sub_parent text;
1321v_sub_timestamp_max timestamptz;
1322v_sub_timestamp_max_suffix timestamptz;
1323v_sub_timestamp_min timestamptz;
1324v_tablename text;
1325v_tables_list_sql text;
1326
1327BEGIN
1328
1329v_adv_lock := pg_try_advisory_xact_lock(hashtext('pg_partman run_maintenance'));
1330IF v_adv_lock = 'false' THEN
1331 RAISE NOTICE 'Partman maintenance already running.';
1332 RETURN;
1333END IF;
1334
1335SELECT current_setting('search_path') INTO v_old_search_path;
1336IF p_jobmon THEN
1337 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;
1338 IF v_jobmon_schema IS NOT NULL THEN
1339 v_new_search_path := '@extschema@,'||v_jobmon_schema||',pg_temp';
1340 END IF;
1341END IF;
1342EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
1343
1344IF v_jobmon_schema IS NOT NULL THEN
1345 v_job_id := add_job('PARTMAN RUN MAINTENANCE');
1346 v_step_id := add_step(v_job_id, 'Running maintenance loop');
1347END IF;
1348
1349v_row := NULL; -- Ensure it's reset
1350
1351v_tables_list_sql := 'SELECT parent_table
1352 , partition_type
1353 , partition_interval
1354 , control
1355 , premake
1356 , undo_in_progress
1357 , sub_partition_set_full
1358 , epoch
1359 , infinite_time_partitions
1360 FROM @extschema@.part_config
1361 WHERE sub_partition_set_full = false';
1362
1363IF p_parent_table IS NULL THEN
1364 v_tables_list_sql := v_tables_list_sql || ' AND use_run_maintenance = true';
1365ELSE
1366 v_tables_list_sql := v_tables_list_sql || format(' AND parent_table = %L', p_parent_table);
1367END IF;
1368
1369FOR v_row IN EXECUTE v_tables_list_sql
1370LOOP
1371
1372 CONTINUE WHEN v_row.undo_in_progress;
1373
1374 -- Check for consistent data in part_config_sub table. Was unable to get this working properly as either a constraint or trigger.
1375 -- 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.
1376 -- 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!
1377 -- Don't have to worry about this in the serial trigger maintenance since subpartitioning requires run_maintenance().
1378 SELECT sub_parent INTO v_sub_parent FROM @extschema@.part_config_sub WHERE sub_parent = v_row.parent_table;
1379 IF v_sub_parent IS NOT NULL THEN
1380 SELECT count(*) INTO v_check_subpart FROM @extschema@.check_subpart_sameconfig(v_row.parent_table);
1381 IF v_check_subpart > 1 THEN
1382 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.
1383 Run this query: "SELECT * FROM @extschema@.check_subpart_sameconfig(''%'');" This should only return a single row or nothing.
1384 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;
1385 END IF;
1386 END IF;
1387
1388 SELECT schemaname, tablename
1389 INTO v_parent_schema, v_parent_tablename
1390 FROM pg_catalog.pg_tables
1391 WHERE schemaname = split_part(v_row.parent_table, '.', 1)::name
1392 AND tablename = split_part(v_row.parent_table, '.', 2)::name;
1393
1394 SELECT partition_tablename INTO v_last_partition FROM @extschema@.show_partitions(v_row.parent_table, 'DESC') LIMIT 1;
1395 IF p_debug THEN
1396 RAISE NOTICE 'run_maint: parent_table: %, v_last_partition: %', v_row.parent_table, v_last_partition;
1397 END IF;
1398
1399 IF v_row.partition_type = 'time' OR v_row.partition_type = 'time-custom' THEN
1400
1401 SELECT child_start_time INTO v_last_partition_timestamp
1402 FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table);
1403
1404 -- Loop through child tables starting from highest to get current max value in partition set
1405 -- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent.
1406 FOR v_row_max_time IN
1407 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(v_row.parent_table, 'DESC')
1408 LOOP
1409 IF v_row.epoch = false THEN
1410 EXECUTE format('SELECT max(%I)::text FROM %I.%I'
1411 , v_row.control
1412 , v_row_max_time.partition_schemaname
1413 , v_row_max_time.partition_tablename
1414 ) INTO v_current_partition_timestamp;
1415 ELSE
1416 EXECUTE format('SELECT to_timestamp(max(%I))::text FROM %I.%I'
1417 , v_row.control
1418 , v_row_max_time.partition_schemaname
1419 , v_row_max_time.partition_tablename
1420 ) INTO v_current_partition_timestamp;
1421 END IF;
1422 IF v_current_partition_timestamp IS NOT NULL THEN
1423 SELECT suffix_timestamp INTO v_current_partition_timestamp FROM @extschema@.show_partition_name(v_row.parent_table, v_current_partition_timestamp::text);
1424 EXIT;
1425 END IF;
1426 END LOOP;
1427 -- Check for values in the parent table. If they are there and greater than all child values, use that instead
1428 -- 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
1429 IF v_row.epoch = false THEN
1430 EXECUTE format('SELECT max(%I) FROM ONLY %I.%I', v_row.control, v_parent_schema, v_parent_tablename) INTO v_max_time_parent;
1431 ELSE
1432 EXECUTE format('SELECT to_timestamp(max(%I)) FROM ONLY %I.%I', v_row.control, v_parent_schema, v_parent_tablename) INTO v_max_time_parent;
1433 END IF;
1434 IF p_debug THEN
1435 RAISE NOTICE 'run_maint: v_current_partition_timestamp: %, v_max_time_parent: %', v_current_partition_timestamp, v_max_time_parent;
1436 END IF;
1437 IF v_max_time_parent > v_current_partition_timestamp THEN
1438 SELECT suffix_timestamp INTO v_current_partition_timestamp FROM @extschema@.show_partition_name(v_row.parent_table, v_max_time_parent::text);
1439 END IF;
1440 IF v_current_partition_timestamp IS NULL THEN -- Partition set is completely empty
1441 IF v_row.infinite_time_partitions IS TRUE THEN
1442 -- Set it to now so new partitions continue to be created
1443 v_current_partition_timestamp = CURRENT_TIMESTAMP;
1444 ELSE
1445 -- Nothing to do
1446 CONTINUE;
1447 END IF;
1448 END IF;
1449
1450 -- 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
1451 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');
1452 IF v_sub_timestamp_max IS NOT NULL THEN
1453 SELECT suffix_timestamp INTO v_sub_timestamp_max_suffix FROM @extschema@.show_partition_name(v_row.parent_table, v_sub_timestamp_max::text);
1454 IF v_sub_timestamp_max_suffix = v_last_partition_timestamp THEN
1455 -- Final partition for this set is created. Set full and skip it
1456 UPDATE @extschema@.part_config SET sub_partition_set_full = true WHERE parent_table = v_row.parent_table;
1457 CONTINUE;
1458 END IF;
1459 END IF;
1460
1461 -- Check and see how many premade partitions there are.
1462 v_premade_count = round(EXTRACT('epoch' FROM age(v_last_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.partition_interval::interval));
1463 v_next_partition_timestamp := v_last_partition_timestamp;
1464 IF p_debug THEN
1465 RAISE NOTICE 'run_maint before loop: current_partition_timestamp: %, v_premade_count: %, v_sub_timestamp_min: %, v_sub_timestamp_max: %'
1466 , v_current_partition_timestamp
1467 , v_premade_count
1468 , v_sub_timestamp_min
1469 , v_sub_timestamp_max;
1470 END IF;
1471 -- Loop premaking until config setting is met. Allows it to catch up if it fell behind or if premake changed
1472 WHILE (v_premade_count < v_row.premake) LOOP
1473 IF p_debug THEN
1474 RAISE NOTICE 'run_maint: parent_table: %, v_premade_count: %, v_next_partition_timestamp: %', v_row.parent_table, v_premade_count, v_next_partition_timestamp;
1475 END IF;
1476 IF v_next_partition_timestamp < v_sub_timestamp_min OR v_next_partition_timestamp > v_sub_timestamp_max THEN
1477 -- With subpartitioning, no need to run if the timestamp is not in the parent table's range
1478 EXIT;
1479 END IF;
1480 BEGIN
1481 v_next_partition_timestamp := v_next_partition_timestamp + v_row.partition_interval::interval;
1482 EXCEPTION WHEN datetime_field_overflow THEN
1483 v_premade_count := v_row.premake; -- do this so it can exit the premake check loop and continue in the outer for loop
1484 IF v_jobmon_schema IS NOT NULL THEN
1485 v_step_overflow_id := add_step(v_job_id, 'Attempted partition time interval is outside PostgreSQL''s supported time range.');
1486 PERFORM update_step(v_step_overflow_id, 'CRITICAL', format('Child partition creation skipped for parent table: %s', v_partition_time));
1487 END IF;
1488 RAISE WARNING 'Attempted partition time interval is outside PostgreSQL''s supported time range. Child partition creation skipped for parent table %', v_row.parent_table;
1489 CONTINUE;
1490 END;
1491 v_last_partition_created := @extschema@.create_partition_time(v_row.parent_table, ARRAY[v_next_partition_timestamp], p_analyze);
1492 IF v_last_partition_created THEN
1493 v_create_count := v_create_count + 1;
1494 PERFORM @extschema@.create_function_time(v_row.parent_table, v_job_id);
1495 END IF;
1496
1497 v_premade_count = round(EXTRACT('epoch' FROM age(v_next_partition_timestamp, v_current_partition_timestamp)) / EXTRACT('epoch' FROM v_row.partition_interval::interval));
1498 END LOOP;
1499 ELSIF v_row.partition_type = 'id' THEN
1500 -- Loop through child tables starting from highest to get current max value in partition set
1501 -- Avoids doing a scan on entire partition set and/or getting any values accidentally in parent.
1502 FOR v_row_max_id IN
1503 SELECT partition_schemaname, partition_tablename FROM @extschema@.show_partitions(v_row.parent_table, 'DESC')
1504 LOOP
1505 EXECUTE format('SELECT max(%I)::text FROM %I.%I'
1506 , v_row.control
1507 , v_row_max_id.partition_schemaname
1508 , v_row_max_id.partition_tablename) INTO v_current_partition_id;
1509 IF v_current_partition_id IS NOT NULL THEN
1510 SELECT suffix_id INTO v_current_partition_id FROM @extschema@.show_partition_name(v_row.parent_table, v_current_partition_id::text);
1511 EXIT;
1512 END IF;
1513 END LOOP;
1514 -- Check for values in the parent table. If they are there and greater than all child values, use that instead
1515 -- 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
1516 EXECUTE format('SELECT max(%I) FROM ONLY %I.%I', v_row.control, v_parent_schema, v_parent_tablename) INTO v_max_id_parent;
1517 IF v_max_id_parent > v_current_partition_id THEN
1518 SELECT suffix_id INTO v_current_partition_id FROM @extschema@.show_partition_name(v_row.parent_table, v_max_id_parent::text);
1519 END IF;
1520 IF v_current_partition_id IS NULL THEN
1521 -- Partition set is completely empty. Nothing to do
1522 CONTINUE;
1523 END IF;
1524
1525 SELECT child_start_id INTO v_last_partition_id
1526 FROM @extschema@.show_partition_info(v_parent_schema||'.'||v_last_partition, v_row.partition_interval, v_row.parent_table);
1527 -- 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.
1528 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');
1529 IF v_sub_id_max IS NOT NULL THEN
1530 SELECT suffix_id INTO v_sub_id_max_suffix FROM @extschema@.show_partition_name(v_row.parent_table, v_sub_id_max::text);
1531 IF v_sub_id_max_suffix = v_last_partition_id THEN
1532 -- Final partition for this set is created. Set full and skip it
1533 UPDATE @extschema@.part_config SET sub_partition_set_full = true WHERE parent_table = v_row.parent_table;
1534 CONTINUE;
1535 END IF;
1536 END IF;
1537
1538 v_next_partition_id := v_last_partition_id;
1539 v_premade_count := ((v_last_partition_id - v_current_partition_id) / v_row.partition_interval::bigint);
1540 -- Loop premaking until config setting is met. Allows it to catch up if it fell behind or if premake changed.
1541 WHILE (v_premade_count < v_row.premake) LOOP
1542 IF p_debug THEN
1543 RAISE NOTICE 'run_maint: parent_table: %, v_premade_count: %, v_next_partition_id: %', v_row.parent_table, v_premade_count, v_next_partition_id;
1544 END IF;
1545 IF v_next_partition_id < v_sub_id_min OR v_next_partition_id > v_sub_id_max THEN
1546 -- With subpartitioning, no need to run if the id is not in the parent table's range
1547 EXIT;
1548 END IF;
1549 v_next_partition_id := v_next_partition_id + v_row.partition_interval::bigint;
1550 v_last_partition_created := @extschema@.create_partition_id(v_row.parent_table, ARRAY[v_next_partition_id], p_analyze);
1551 IF v_last_partition_created THEN
1552 v_create_count := v_create_count + 1;
1553 PERFORM @extschema@.create_function_id(v_row.parent_table, v_job_id);
1554 END IF;
1555 v_premade_count := ((v_next_partition_id - v_current_partition_id) / v_row.partition_interval::bigint);
1556 END LOOP;
1557
1558 END IF; -- end main IF check for time or id
1559
1560END LOOP; -- end of creation loop
1561
1562-- Manage dropping old partitions if retention option is set
1563FOR v_row IN
1564 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND
1565 (partition_type = 'time' OR partition_type = 'time-custom')
1566LOOP
1567 IF p_parent_table IS NULL THEN
1568 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table);
1569 ELSE -- Only run retention on table given in parameter
1570 IF p_parent_table <> v_row.parent_table THEN
1571 CONTINUE;
1572 ELSE
1573 v_drop_count := v_drop_count + @extschema@.drop_partition_time(v_row.parent_table);
1574 END IF;
1575 END IF;
1576 IF v_drop_count > 0 THEN
1577 PERFORM @extschema@.create_function_time(v_row.parent_table, v_job_id);
1578 END IF;
1579END LOOP;
1580FOR v_row IN
1581 SELECT parent_table FROM @extschema@.part_config WHERE retention IS NOT NULL AND undo_in_progress = false AND partition_type = 'id'
1582LOOP
1583 IF p_parent_table IS NULL THEN
1584 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table);
1585 ELSE -- Only run retention on table given in parameter
1586 IF p_parent_table <> v_row.parent_table THEN
1587 CONTINUE;
1588 ELSE
1589 v_drop_count := v_drop_count + @extschema@.drop_partition_id(v_row.parent_table);
1590 END IF;
1591 END IF;
1592 IF v_drop_count > 0 THEN
1593 PERFORM @extschema@.create_function_id(v_row.parent_table, v_job_id);
1594 END IF;
1595END LOOP;
1596
1597IF v_jobmon_schema IS NOT NULL THEN
1598 PERFORM update_step(v_step_id, 'OK', format('Partition maintenance finished. %s partitons made. %s partitions dropped.', v_create_count, v_drop_count));
1599 IF v_step_overflow_id IS NOT NULL THEN
1600 PERFORM fail_job(v_job_id);
1601 ELSE
1602 PERFORM close_job(v_job_id);
1603 END IF;
1604END IF;
1605
1606EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
1607
1608EXCEPTION
1609 WHEN OTHERS THEN
1610 GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
1611 ex_context = PG_EXCEPTION_CONTEXT,
1612 ex_detail = PG_EXCEPTION_DETAIL,
1613 ex_hint = PG_EXCEPTION_HINT;
1614 IF v_jobmon_schema IS NOT NULL THEN
1615 IF v_job_id IS NULL THEN
1616 EXECUTE format('SELECT %I.add_job(''PARTMAN RUN MAINTENANCE'')', v_jobmon_schema) INTO v_job_id;
1617 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;
1618 ELSIF v_step_id IS NULL THEN
1619 EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
1620 END IF;
1621 EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
1622 EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
1623 END IF;
1624 RAISE EXCEPTION '%
1625CONTEXT: %
1626DETAIL: %
1627HINT: %', ex_message, ex_context, ex_detail, ex_hint;
1628END
1629$$;
1630
1631-- Restore dropped object privileges
1632DO $$
1633DECLARE
1634v_row record;
1635BEGIN
1636 FOR v_row IN SELECT statement FROM partman_preserve_privs_temp LOOP
1637 IF v_row.statement IS NOT NULL THEN
1638 EXECUTE v_row.statement;
1639 END IF;
1640 END LOOP;
1641END
1642$$;
1643
1644DROP TABLE IF EXISTS partman_preserve_privs_temp;