/usr/share/postgresql/9.6/extension/mimeo--0.2.3--0.3.0.sql is in postgresql-9.6-mimeo 1.4.4-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 | -- Added new snapshot_maker and snapshot_destroyer functions
/*
* Snapshot maker function. Assumes source and destination are the same tablename.
*
*/
CREATE FUNCTION snapshot_maker(p_src_table text, p_dblink_id int) RETURNS void
LANGUAGE plpgsql
AS $_$
declare
v_insert_refresh_config text;
v_data_source text;
BEGIN
SELECT data_source INTO v_data_source FROM @extschema@.dblink_mapping WHERE data_source_id = p_dblink_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'ERROR: database link ID is incorrect %', p_dblink_id;
END IF;
v_insert_refresh_config := 'INSERT INTO @extschema@.refresh_config(source_table, dest_table, type, dblink) VALUES('||quote_literal(p_src_table)||', '||quote_literal(p_src_table)||',''snap'', '|| p_dblink_id||');';
RAISE NOTICE 'Inserting record in @extschema@.refresh_config';
EXECUTE v_insert_refresh_config;
RAISE NOTICE 'Insert successful';
RAISE NOTICE 'attempting first snapshot';
PERFORM @extschema@.refresh_snap(p_src_table, FALSE);
RAISE NOTICE 'attempting second snapshot';
PERFORM @extschema@.refresh_snap(p_src_table, FALSE);
RAISE NOTICE 'all done';
RETURN;
END
$_$;
/*
* Snapshot maker function. Accepts custom destination name.
*
*/
CREATE FUNCTION snapshot_maker(p_src_table text, p_dest_table text, p_dblink_id int) RETURNS void
LANGUAGE plpgsql
AS $_$
declare
v_insert_refresh_config text;
v_data_source text;
BEGIN
SELECT data_source INTO v_data_source FROM @extschema@.dblink_mapping WHERE data_source_id = p_dblink_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'ERROR: database link ID is incorrect %', p_dblink_id;
END IF;
v_insert_refresh_config := 'INSERT INTO @extschema@.refresh_config(source_table, dest_table, type, dblink) VALUES('||quote_literal(p_src_table)||', '||quote_literal(p_dest_table)||',''snap'', '|| p_dblink_id||');';
RAISE NOTICE 'Inserting record in @extschema@.refresh_config';
EXECUTE v_insert_refresh_config;
RAISE NOTICE 'Insert successful';
RAISE NOTICE 'attempting first snapshot';
PERFORM @extschema@.refresh_snap(p_dest_table, FALSE);
RAISE NOTICE 'attempting second snapshot';
PERFORM @extschema@.refresh_snap(p_dest_table, FALSE);
RAISE NOTICE 'all done';
RETURN;
END
$_$;
/*
* Snapshot destroyer function. Pass archive to keep permanent copy of snap view.
*
*/
CREATE FUNCTION snapshot_destroyer(p_dest_table text, p_option text) RETURNS void
LANGUAGE plpgsql
AS $_$
DECLARE
v_dest_table text;
v_exists int;
v_snap_suffix text;
v_src_table text;
v_view_definition text;
BEGIN
SELECT source_table, dest_table INTO v_src_table, v_dest_table
FROM @extschema@.refresh_config WHERE dest_table = p_dest_table;
IF NOT FOUND THEN
RAISE EXCEPTION 'This table is not set up for snapshot replication: %', v_dest_table;
END IF;
-- Make a brand new, real table to keep the data that is not part of the snap system anymore
IF p_option = 'ARCHIVE' THEN
SELECT definition INTO v_view_definition FROM pg_views WHERE schemaname || '.' || viewname = v_dest_table;
v_exists := strpos(v_view_definition, 'snap1');
IF v_exists > 0 THEN
v_snap_suffix := 'snap1';
ELSE
v_snap_suffix := 'snap2';
END IF;
EXECUTE 'DROP VIEW ' || v_dest_table;
EXECUTE 'CREATE TEMPORARY TABLE tmp_snapshot_destroy AS SELECT * FROM ' || v_dest_table || '_' || v_snap_suffix;
EXECUTE 'CREATE TABLE ' || v_dest_table || ' AS SELECT * FROM tmp_snapshot_destroy';
ELSE
EXECUTE 'DROP VIEW ' || v_dest_table;
END IF;
EXECUTE 'DROP TABLE ' || v_dest_table || '_snap1';
EXECUTE 'DROP TABLE ' || v_dest_table || '_snap2';
EXECUTE 'DELETE FROM @extschema@.refresh_config WHERE dest_table = ' || quote_literal(v_dest_table);
EXECUTE 'DROP TABLE IF EXISTS tmp_snapshot_destroy';
END
$_$;
|