1-- citus--10.0-4--9.5-1 2 3-- This migration file aims to fix the issues with upgrades on clusters without public schema. 4 5-- This file is created by the following command, and some more changes in a separate commit 6-- cat citus--10.0-3--10.0-2.sql citus--10.0-2--10.0-1.sql citus--10.0-1--9.5-1.sql > citus--10.0-4--9.5-1.sql 7 8-- copy of citus--10.0-4--10.0-3 9-- 10-- 10.0-3--10.0-4 was added later as a patch to fix a bug in our PG upgrade functions 11-- 12-- The upgrade fixes a bug in citus_(prepare|finish)_pg_upgrade. Given the old versions of 13-- these functions contain a bug it is better to _not_ restore the old version and keep 14-- the patched version of the function. 15-- 16-- This is inline with the downgrade scripts for earlier versions of this patch 17-- 18 19-- copy of citus--10.0-3--10.0-2 20-- this is a downgrade path that will revert the changes made in citus--10.0-2--10.0-3.sql 21 22DROP FUNCTION pg_catalog.citus_update_table_statistics(regclass); 23 24#include "../udfs/citus_update_table_statistics/10.0-1.sql" 25 26CREATE OR REPLACE FUNCTION master_update_table_statistics(relation regclass) 27RETURNS VOID AS $$ 28DECLARE 29 colocated_tables regclass[]; 30BEGIN 31 SELECT get_colocated_table_array(relation) INTO colocated_tables; 32 33 PERFORM 34 master_update_shard_statistics(shardid) 35 FROM 36 pg_dist_shard 37 WHERE 38 logicalrelid = ANY (colocated_tables); 39END; 40$$ LANGUAGE 'plpgsql'; 41COMMENT ON FUNCTION master_update_table_statistics(regclass) 42 IS 'updates shard statistics of the given table and its colocated tables'; 43 44DROP FUNCTION pg_catalog.citus_get_active_worker_nodes(OUT text, OUT bigint); 45-- copy of citus--10.0-2--10.0-1.sql 46#include "../../../columnar/sql/downgrades/columnar--10.0-2--10.0-1.sql" 47 48-- copy of citus--10.0-1--9.5-1 49 50-- In Citus 10.0, we added another internal udf (notify_constraint_dropped) 51-- to be called by citus_drop_trigger. Since this script is executed when 52-- downgrading Citus, we don't have notify_constraint_dropped in citus.so. 53-- For this reason, we first need to downgrade citus_drop_trigger so it doesn't 54-- call notify_constraint_dropped. 55-- To downgrade citus_drop_trigger, we first need to have the old version of 56-- citus_drop_all_shards as we renamed it in Citus 10.0. 57ALTER FUNCTION pg_catalog.citus_drop_all_shards(regclass, text, text) 58RENAME TO master_drop_all_shards; 59#include "../udfs/citus_drop_trigger/9.5-1.sql" 60 61-- Now we can safely drop notify_constraint_dropped as we downgraded citus_drop_trigger. 62DROP FUNCTION pg_catalog.notify_constraint_dropped(); 63 64#include "../udfs/citus_finish_pg_upgrade/9.5-1.sql" 65 66#include "../../../columnar/sql/downgrades/columnar--10.0-1--9.5-1.sql" 67 68DROP VIEW IF EXISTS pg_catalog.citus_tables; 69DROP VIEW IF EXISTS public.citus_tables; 70DROP FUNCTION pg_catalog.alter_distributed_table(regclass, text, int, text, boolean); 71DROP FUNCTION pg_catalog.alter_table_set_access_method(regclass, text); 72DROP FUNCTION pg_catalog.citus_total_relation_size(regclass,boolean); 73DROP FUNCTION pg_catalog.undistribute_table(regclass,boolean); 74DROP FUNCTION pg_catalog.citus_add_local_table_to_metadata(regclass,boolean); 75DROP FUNCTION pg_catalog.citus_add_node(text, integer, integer, noderole, name); 76DROP FUNCTION pg_catalog.citus_activate_node(text, integer); 77DROP FUNCTION pg_catalog.citus_add_inactive_node(text, integer, integer, noderole, name); 78DROP FUNCTION pg_catalog.citus_add_secondary_node(text, integer, text, integer, name); 79DROP FUNCTION pg_catalog.citus_disable_node(text, integer); 80DROP FUNCTION pg_catalog.citus_drain_node(text, integer, citus.shard_transfer_mode, name); 81DROP FUNCTION pg_catalog.citus_remove_node(text, integer); 82DROP FUNCTION pg_catalog.citus_set_node_property(text, integer, text, boolean); 83DROP FUNCTION pg_catalog.citus_unmark_object_distributed(oid, oid, int); 84DROP FUNCTION pg_catalog.citus_update_node(int, text, int, bool, int); 85DROP FUNCTION pg_catalog.citus_update_shard_statistics(bigint); 86DROP FUNCTION pg_catalog.citus_update_table_statistics(regclass); 87DROP FUNCTION pg_catalog.citus_copy_shard_placement(bigint, text, integer, text, integer, bool, citus.shard_transfer_mode); 88DROP FUNCTION pg_catalog.citus_move_shard_placement(bigint, text, integer, text, integer, citus.shard_transfer_mode); 89 90ALTER FUNCTION pg_catalog.citus_conninfo_cache_invalidate() 91RENAME TO master_conninfo_cache_invalidate; 92ALTER FUNCTION pg_catalog.citus_dist_local_group_cache_invalidate() 93RENAME TO master_dist_local_group_cache_invalidate; 94ALTER FUNCTION pg_catalog.citus_dist_node_cache_invalidate() 95RENAME TO master_dist_node_cache_invalidate; 96ALTER FUNCTION pg_catalog.citus_dist_object_cache_invalidate() 97RENAME TO master_dist_object_cache_invalidate; 98ALTER FUNCTION pg_catalog.citus_dist_partition_cache_invalidate() 99RENAME TO master_dist_partition_cache_invalidate; 100ALTER FUNCTION pg_catalog.citus_dist_placement_cache_invalidate() 101RENAME TO master_dist_placement_cache_invalidate; 102ALTER FUNCTION pg_catalog.citus_dist_shard_cache_invalidate() 103RENAME TO master_dist_shard_cache_invalidate; 104 105#include "../udfs/citus_conninfo_cache_invalidate/9.5-1.sql" 106#include "../udfs/citus_dist_local_group_cache_invalidate/9.5-1.sql" 107#include "../udfs/citus_dist_node_cache_invalidate/9.5-1.sql" 108#include "../udfs/citus_dist_object_cache_invalidate/9.5-1.sql" 109#include "../udfs/citus_dist_partition_cache_invalidate/9.5-1.sql" 110#include "../udfs/citus_dist_placement_cache_invalidate/9.5-1.sql" 111#include "../udfs/citus_dist_shard_cache_invalidate/9.5-1.sql" 112 113DROP VIEW pg_catalog.time_partitions; 114DROP FUNCTION pg_catalog.time_partition_range(regclass); 115DROP PROCEDURE pg_catalog.alter_old_partitions_set_access_method(regclass,timestamptz,name); 116 117DROP FUNCTION pg_catalog.citus_set_coordinator_host(text,int,noderole,name); 118DROP FUNCTION pg_catalog.worker_change_sequence_dependency(regclass, regclass, regclass); 119 120CREATE FUNCTION pg_catalog.mark_tables_colocated(source_table_name regclass, target_table_names regclass[]) 121 RETURNS void 122 LANGUAGE C STRICT 123 AS 'MODULE_PATHNAME', $$mark_tables_colocated$$; 124COMMENT ON FUNCTION pg_catalog.mark_tables_colocated(source_table_name regclass, target_table_names regclass[]) 125 IS 'mark target distributed tables as colocated with the source table'; 126 127CREATE FUNCTION pg_catalog.master_modify_multiple_shards(text) 128 RETURNS integer 129 LANGUAGE C STRICT 130 AS 'MODULE_PATHNAME', $$master_modify_multiple_shards$$; 131COMMENT ON FUNCTION master_modify_multiple_shards(text) 132 IS 'push delete and update queries to shards'; 133 134CREATE FUNCTION pg_catalog.master_create_distributed_table(table_name regclass, 135 distribution_column text, 136 distribution_method citus.distribution_type) 137 RETURNS void 138 LANGUAGE C STRICT 139 AS 'MODULE_PATHNAME', $$master_create_distributed_table$$; 140COMMENT ON FUNCTION pg_catalog.master_create_distributed_table(table_name regclass, 141 distribution_column text, 142 distribution_method citus.distribution_type) 143 IS 'define the table distribution functions'; 144 145CREATE FUNCTION pg_catalog.master_create_worker_shards(table_name text, shard_count integer, 146 replication_factor integer DEFAULT 2) 147 RETURNS void 148 AS 'MODULE_PATHNAME' 149 LANGUAGE C STRICT; 150 151DROP FUNCTION pg_catalog.remove_local_tables_from_metadata(); 152 153#include "../udfs/citus_total_relation_size/7.0-1.sql" 154#include "../udfs/upgrade_to_reference_table/8.0-1.sql" 155#include "../udfs/undistribute_table/9.5-1.sql" 156#include "../udfs/create_citus_local_table/9.5-1.sql" 157DROP VIEW pg_catalog.citus_shards CASCADE; 158DROP FUNCTION pg_catalog.citus_shard_sizes(OUT table_name text, OUT size bigint); 159 160DROP FUNCTION pg_catalog.fix_pre_citus10_partitioned_table_constraint_names(); 161DROP FUNCTION pg_catalog.fix_pre_citus10_partitioned_table_constraint_names(regclass); 162DROP FUNCTION pg_catalog.worker_fix_pre_citus10_partitioned_table_constraint_names(regclass,bigint,text); 163DROP FUNCTION pg_catalog.citus_dist_stat_activity CASCADE; 164DROP FUNCTION pg_catalog.citus_worker_stat_activity CASCADE; 165 166CREATE OR REPLACE FUNCTION pg_catalog.citus_dist_stat_activity(OUT query_hostname text, OUT query_hostport int, OUT master_query_host_name text, OUT master_query_host_port int, 167 OUT transaction_number int8, OUT transaction_stamp timestamptz, OUT datid oid, OUT datname name, 168 OUT pid int, OUT usesysid oid, OUT usename name, OUT application_name text, OUT client_addr INET, 169 OUT client_hostname TEXT, OUT client_port int, OUT backend_start timestamptz, OUT xact_start timestamptz, 170 OUT query_start timestamptz, OUT state_change timestamptz, OUT wait_event_type text, OUT wait_event text, 171 OUT state text, OUT backend_xid xid, OUT backend_xmin xid, OUT query text, OUT backend_type text) 172RETURNS SETOF RECORD 173LANGUAGE C STRICT AS 'MODULE_PATHNAME', 174$$citus_dist_stat_activity$$; 175 176COMMENT ON FUNCTION pg_catalog.citus_dist_stat_activity(OUT query_hostname text, OUT query_hostport int, OUT master_query_host_name text, OUT master_query_host_port int, 177 OUT transaction_number int8, OUT transaction_stamp timestamptz, OUT datid oid, OUT datname name, 178 OUT pid int, OUT usesysid oid, OUT usename name, OUT application_name text, OUT client_addr INET, 179 OUT client_hostname TEXT, OUT client_port int, OUT backend_start timestamptz, OUT xact_start timestamptz, 180 OUT query_start timestamptz, OUT state_change timestamptz, OUT wait_event_type text, OUT wait_event text, 181 OUT state text, OUT backend_xid xid, OUT backend_xmin xid, OUT query text, OUT backend_type text) 182IS 'returns distributed transaction activity on distributed tables'; 183 184CREATE VIEW citus.citus_dist_stat_activity AS 185SELECT * FROM pg_catalog.citus_dist_stat_activity(); 186ALTER VIEW citus.citus_dist_stat_activity SET SCHEMA pg_catalog; 187GRANT SELECT ON pg_catalog.citus_dist_stat_activity TO PUBLIC; 188 189SET search_path = 'pg_catalog'; 190 191CREATE VIEW citus.citus_lock_waits AS 192 193WITH 194citus_dist_stat_activity AS 195( 196 SELECT * FROM citus_dist_stat_activity 197), 198unique_global_wait_edges AS 199( 200 SELECT DISTINCT ON(waiting_node_id, waiting_transaction_num, blocking_node_id, blocking_transaction_num) * FROM dump_global_wait_edges() 201), 202citus_dist_stat_activity_with_node_id AS 203( 204 SELECT 205 citus_dist_stat_activity.*, (CASE citus_dist_stat_activity.master_query_host_name WHEN 'coordinator_host' THEN 0 ELSE pg_dist_node.nodeid END) as initiator_node_id 206 FROM 207 citus_dist_stat_activity LEFT JOIN pg_dist_node 208 ON 209 citus_dist_stat_activity.master_query_host_name = pg_dist_node.nodename AND 210 citus_dist_stat_activity.master_query_host_port = pg_dist_node.nodeport 211) 212SELECT 213 waiting.pid AS waiting_pid, 214 blocking.pid AS blocking_pid, 215 waiting.query AS blocked_statement, 216 blocking.query AS current_statement_in_blocking_process, 217 waiting.initiator_node_id AS waiting_node_id, 218 blocking.initiator_node_id AS blocking_node_id, 219 waiting.master_query_host_name AS waiting_node_name, 220 blocking.master_query_host_name AS blocking_node_name, 221 waiting.master_query_host_port AS waiting_node_port, 222 blocking.master_query_host_port AS blocking_node_port 223FROM 224 unique_global_wait_edges 225JOIN 226 citus_dist_stat_activity_with_node_id waiting ON (unique_global_wait_edges.waiting_transaction_num = waiting.transaction_number AND unique_global_wait_edges.waiting_node_id = waiting.initiator_node_id) 227JOIN 228 citus_dist_stat_activity_with_node_id blocking ON (unique_global_wait_edges.blocking_transaction_num = blocking.transaction_number AND unique_global_wait_edges.blocking_node_id = blocking.initiator_node_id); 229 230ALTER VIEW citus.citus_lock_waits SET SCHEMA pg_catalog; 231GRANT SELECT ON pg_catalog.citus_lock_waits TO PUBLIC; 232 233CREATE OR REPLACE FUNCTION citus_worker_stat_activity(OUT query_hostname text, OUT query_hostport int, OUT master_query_host_name text, OUT master_query_host_port int, 234 OUT transaction_number int8, OUT transaction_stamp timestamptz, OUT datid oid, OUT datname name, 235 OUT pid int, OUT usesysid oid, OUT usename name, OUT application_name text, OUT client_addr INET, 236 OUT client_hostname TEXT, OUT client_port int, OUT backend_start timestamptz, OUT xact_start timestamptz, 237 OUT query_start timestamptz, OUT state_change timestamptz, OUT wait_event_type text, OUT wait_event text, 238 OUT state text, OUT backend_xid xid, OUT backend_xmin xid, OUT query text, OUT backend_type text) 239RETURNS SETOF RECORD 240LANGUAGE C STRICT AS 'MODULE_PATHNAME', 241$$citus_worker_stat_activity$$; 242 243COMMENT ON FUNCTION citus_worker_stat_activity(OUT query_hostname text, OUT query_hostport int, OUT master_query_host_name text, OUT master_query_host_port int, 244 OUT transaction_number int8, OUT transaction_stamp timestamptz, OUT datid oid, OUT datname name, 245 OUT pid int, OUT usesysid oid, OUT usename name, OUT application_name text, OUT client_addr INET, 246 OUT client_hostname TEXT, OUT client_port int, OUT backend_start timestamptz, OUT xact_start timestamptz, 247 OUT query_start timestamptz, OUT state_change timestamptz, OUT wait_event_type text, OUT wait_event text, 248 OUT state text, OUT backend_xid xid, OUT backend_xmin xid, OUT query text, OUT backend_type text) 249IS 'returns distributed transaction activity on shards of distributed tables'; 250 251CREATE VIEW citus.citus_worker_stat_activity AS 252SELECT * FROM pg_catalog.citus_worker_stat_activity(); 253ALTER VIEW citus.citus_worker_stat_activity SET SCHEMA pg_catalog; 254GRANT SELECT ON pg_catalog.citus_worker_stat_activity TO PUBLIC; 255 256RESET search_path; 257