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
|
CREATE FUNCTION @extschema@.check_subpart_sameconfig(p_parent_table text)
RETURNS TABLE (
sub_control text
, sub_partition_interval text
, sub_partition_type text
, sub_premake int
, sub_automatic_maintenance text
, sub_template_table text
, sub_retention text
, sub_retention_schema text
, sub_retention_keep_index boolean
, sub_retention_keep_table boolean
, sub_epoch text
, sub_constraint_cols text[]
, sub_optimize_constraint int
, sub_infinite_time_partitions boolean
, sub_jobmon boolean
, sub_inherit_privileges boolean
, sub_constraint_valid boolean
, sub_date_trunc_interval text
, sub_ignore_default_data boolean
, sub_default_table boolean
, sub_maintenance_order int
, sub_retention_keep_publication boolean
, sub_control_not_null boolean
)
LANGUAGE sql STABLE
SET search_path = @extschema@,pg_temp
AS $$
/*
* Check for consistent data in part_config_sub table. Was unable to get this working properly as either a constraint or trigger.
* 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.
* This is called by run_maintainance() and at least provides a consistent way to check that I know will run.
* If anyone can get a working constraint/trigger, please help!
*/
WITH parent_info AS (
SELECT c1.oid
FROM pg_catalog.pg_class c1
JOIN pg_catalog.pg_namespace n1 ON c1.relnamespace = n1.oid
WHERE n1.nspname = split_part(p_parent_table, '.', 1)::name
AND c1.relname = split_part(p_parent_table, '.', 2)::name
)
, child_tables AS (
SELECT n.nspname||'.'||c.relname AS tablename
FROM pg_catalog.pg_inherits h
JOIN pg_catalog.pg_class c ON c.oid = h.inhrelid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
JOIN parent_info pi ON h.inhparent = pi.oid
)
-- Column order here must match the RETURNS TABLE definition
-- This column list must be kept consistent between:
-- create_parent, check_subpart_sameconfig, create_partition_id, create_partition_time, dump_partitioned_table_definition, and table definition
-- Also check return table list from this function
SELECT DISTINCT
a.sub_control
, a.sub_partition_interval
, a.sub_partition_type
, a.sub_premake
, a.sub_automatic_maintenance
, a.sub_template_table
, a.sub_retention
, a.sub_retention_schema
, a.sub_retention_keep_index
, a.sub_retention_keep_table
, a.sub_epoch
, a.sub_constraint_cols
, a.sub_optimize_constraint
, a.sub_infinite_time_partitions
, a.sub_jobmon
, a.sub_inherit_privileges
, a.sub_constraint_valid
, a.sub_date_trunc_interval
, a.sub_ignore_default_data
, a.sub_default_table
, a.sub_maintenance_order
, a.sub_retention_keep_publication
, a.sub_control_not_null
FROM @extschema@.part_config_sub a
JOIN child_tables b on a.sub_parent = b.tablename;
$$;
|
