1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
/*
* Drop constraints managed by pg_partman
*/
CREATE FUNCTION @extschema@.drop_constraints(p_parent_table text, p_child_table text, p_debug boolean DEFAULT false) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
ex_context text;
ex_detail text;
ex_hint text;
ex_message text;
v_child_schemaname text;
v_child_tablename text;
v_col text;
v_constraint_cols text[];
v_existing_constraint_name text;
v_exists boolean := FALSE;
v_job_id bigint;
v_jobmon boolean;
v_jobmon_schema text;
v_new_search_path text;
v_old_search_path text;
v_sql text;
v_step_id bigint;
BEGIN
SELECT constraint_cols
, jobmon
INTO v_constraint_cols
, v_jobmon
FROM @extschema@.part_config
WHERE parent_table = p_parent_table;
IF v_constraint_cols IS NULL THEN
RAISE EXCEPTION 'Given parent table (%) not set up for constraint management (constraint_cols is NULL)', p_parent_table;
END IF;
IF v_jobmon THEN
SELECT nspname INTO v_jobmon_schema FROM pg_catalog.pg_namespace n, pg_catalog.pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid;
IF v_jobmon_schema IS NOT NULL THEN
SELECT current_setting('search_path') INTO v_old_search_path;
IF length(v_old_search_path) > 0 THEN
v_new_search_path := '@extschema@,pg_temp,'||v_old_search_path;
ELSE
v_new_search_path := '@extschema@,pg_temp';
END IF;
v_new_search_path := format('%s,%s',v_jobmon_schema, v_new_search_path);
EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_new_search_path, 'false');
END IF;
END IF;
SELECT schemaname, tablename INTO v_child_schemaname, v_child_tablename
FROM pg_catalog.pg_tables
WHERE schemaname = split_part(p_child_table, '.', 1)::name
AND tablename = split_part(p_child_table, '.', 2)::name;
IF v_child_tablename IS NULL THEN
RAISE EXCEPTION 'Unable to find given child table in system catalogs: %', p_child_table;
END IF;
IF v_jobmon_schema IS NOT NULL THEN
v_job_id := add_job(format('PARTMAN DROP CONSTRAINT: %s', p_parent_table));
v_step_id := add_step(v_job_id, 'Entering constraint drop loop');
PERFORM update_step(v_step_id, 'OK', 'Done');
END IF;
FOREACH v_col IN ARRAY v_constraint_cols
LOOP
SELECT con.conname
INTO v_existing_constraint_name
FROM pg_catalog.pg_constraint con
JOIN pg_class c ON c.oid = con.conrelid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
JOIN pg_catalog.pg_attribute a ON con.conrelid = a.attrelid
WHERE c.relname = v_child_tablename
AND n.nspname = v_child_schemaname
AND con.conname LIKE 'partmanconstr_%'
AND con.contype = 'c'
AND a.attname = v_col
AND ARRAY[a.attnum] OPERATOR(pg_catalog.<@) con.conkey
AND a.attisdropped = false;
IF v_existing_constraint_name IS NOT NULL THEN
v_exists := TRUE;
IF v_jobmon_schema IS NOT NULL THEN
v_step_id := add_step(v_job_id, format('Dropping constraint on column: %s', v_col));
END IF;
v_sql := format('ALTER TABLE %I.%I DROP CONSTRAINT %I', v_child_schemaname, v_child_tablename, v_existing_constraint_name);
IF p_debug THEN
RAISE NOTICE 'Constraint drop query: %', v_sql;
END IF;
EXECUTE v_sql;
IF v_jobmon_schema IS NOT NULL THEN
PERFORM update_step(v_step_id, 'OK', format('Drop constraint query: %s', v_sql));
END IF;
END IF;
END LOOP;
IF v_jobmon_schema IS NOT NULL AND v_exists IS FALSE THEN
v_step_id := add_step(v_job_id, format('No constraints found to drop on child table: %s', p_child_table));
PERFORM update_step(v_step_id, 'OK', 'Done');
END IF;
IF v_jobmon_schema IS NOT NULL THEN
PERFORM close_job(v_job_id);
EXECUTE format('SELECT set_config(%L, %L, %L)', 'search_path', v_old_search_path, 'false');
END IF;
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS ex_message = MESSAGE_TEXT,
ex_context = PG_EXCEPTION_CONTEXT,
ex_detail = PG_EXCEPTION_DETAIL,
ex_hint = PG_EXCEPTION_HINT;
IF v_jobmon_schema IS NOT NULL THEN
IF v_job_id IS NULL THEN
EXECUTE format('SELECT %I.add_job(''PARTMAN DROP CONSTRAINT: %s'')', v_jobmon_schema, p_parent_table) INTO v_job_id;
EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before job logging started'')', v_jobmon_schema, v_job_id, p_parent_table) INTO v_step_id;
ELSIF v_step_id IS NULL THEN
EXECUTE format('SELECT %I.add_step(%s, ''EXCEPTION before first step logged'')', v_jobmon_schema, v_job_id) INTO v_step_id;
END IF;
EXECUTE format('SELECT %I.update_step(%s, ''CRITICAL'', %L)', v_jobmon_schema, v_step_id, 'ERROR: '||coalesce(SQLERRM,'unknown'));
EXECUTE format('SELECT %I.fail_job(%s)', v_jobmon_schema, v_job_id);
END IF;
RAISE EXCEPTION '%
CONTEXT: %
DETAIL: %
HINT: %', ex_message, ex_context, ex_detail, ex_hint;
END
$$;
|
