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
|
/*
* Logdel destroyer function.
*/
CREATE FUNCTION logdel_destroyer(p_dest_table text, p_keep_table boolean DEFAULT true, p_debug boolean DEFAULT false) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
v_dblink int;
v_dblink_name text;
v_dblink_schema text;
v_dest_schema_name text;
v_dest_table text;
v_dest_table_name text;
v_drop_dest_table text;
v_drop_function text;
v_drop_q_table text;
v_drop_trigger text;
v_link_exists boolean;
v_old_search_path text;
v_source_queue_function text;
v_source_queue_table text;
v_source_queue_trigger text;
v_src_schema_name text;
v_src_table text;
v_src_table_name text;
v_src_table_template text;
v_table_name text;
v_table_owner text;
v_username text;
BEGIN
SELECT nspname INTO v_dblink_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'dblink' AND e.extnamespace = n.oid;
SELECT current_setting('search_path') INTO v_old_search_path;
EXECUTE 'SELECT set_config(''search_path'',''@extschema@,'||v_dblink_schema||',public'',''false'')';
SELECT source_table
, dest_table
, dblink
INTO v_src_table
, v_dest_table
, v_dblink
FROM @extschema@.refresh_config_logdel
WHERE dest_table = p_dest_table;
IF NOT FOUND THEN
RAISE NOTICE 'This table is not set up for logdel replication: %', v_dest_table;
ELSE
SELECT schemaname, tablename
INTO v_dest_schema_name, v_dest_table_name
FROM pg_catalog.pg_tables
WHERE schemaname||'.'||tablename = v_dest_table;
SELECT username INTO v_username FROM @extschema@.dblink_mapping_mimeo;
v_dblink_name := 'mimeo_logdel_destroy';
PERFORM dblink_connect(v_dblinK_name, @extschema@.auth(v_dblink));
SELECT schemaname ||'_'|| tablename, schemaname, tablename, tableowner
INTO v_src_table_template, v_src_schema_name, v_src_table_name, v_table_owner
FROM dblink(v_dblink_name, 'SELECT schemaname, tablename, tableowner FROM pg_catalog.pg_tables WHERE schemaname ||''.''|| tablename = '||quote_literal(v_src_table)) t (schemaname text, tablename text, tableowner text);
IF v_table_owner <> v_username THEN
RAISE EXCEPTION 'Unable to drop the mimeo trigger on source table (%). Mimeo role must be the owner of the table to automatically drop it. Manually drop the mimeo trigger first, then run destroyer function again.', v_src_table;
END IF;
v_source_queue_table := check_name_length(v_src_table_template, '_q');
v_source_queue_function := check_name_length(v_src_table_template, '_mimeo_queue');
v_source_queue_trigger := check_name_length(v_src_table_template, '_mimeo_trig');
v_drop_trigger := format('DROP TRIGGER IF EXISTS %I ON %I.%I', v_source_queue_trigger, v_src_schema_name, v_src_table_name);
v_drop_function := format('DROP FUNCTION IF EXISTS %I.%I()', '@extschema@', v_source_queue_function);
v_drop_q_table := format('DROP TABLE IF EXISTS %I.%I', '@extschema@', v_source_queue_table);
RAISE NOTICE 'Removing mimeo objects from source database if they exist (trigger, function, queue table)';
PERFORM dblink_exec(v_dblink_name, v_drop_trigger);
PERFORM gdb(p_debug, v_drop_trigger);
PERFORM dblink_exec(v_dblink_name, v_drop_function);
PERFORM gdb(p_debug, v_drop_function);
PERFORM dblink_exec(v_dblink_name, v_drop_q_table);
PERFORM gdb(p_debug, v_drop_q_table);
PERFORM dblink_disconnect(v_dblink_name);
IF p_keep_table THEN
RAISE NOTICE 'Destination table NOT destroyed (if it existed): %', v_dest_table;
ELSE
IF v_dest_schema_name IS NOT NULL AND v_dest_table_name IS NOT NULL THEN
v_drop_dest_table := format('DROP TABLE IF EXISTS %I.%I', v_dest_schema_name, v_dest_table_name);
PERFORM gdb(p_debug, v_drop_dest_table);
EXECUTE v_drop_dest_table;
RAISE NOTICE 'Destination table destroyed: %', v_dest_table;
ELSE
RAISE NOTICE 'Destination table did not exist: %', v_dest_table;
END IF;
END IF;
RAISE NOTICE 'Removing config data';
EXECUTE 'DELETE FROM @extschema@.refresh_config_logdel WHERE dest_table = ' || quote_literal(v_dest_table);
RAISE NOTICE 'Done';
END IF;
EXECUTE 'SELECT set_config(''search_path'','''||v_old_search_path||''',''false'')';
EXCEPTION
WHEN OTHERS THEN
EXECUTE 'SELECT '||v_dblink_schema||'.dblink_get_connections() @> ARRAY['||quote_literal(v_dblink_name)||']' INTO v_link_exists;
IF v_link_exists THEN
EXECUTE 'SELECT '||v_dblink_schema||'.dblink_disconnect('||quote_literal(v_dblink_name)||')';
END IF;
RAISE EXCEPTION '%', SQLERRM;
END
$$;
|
