1-- citus--9.5-1--10.0-4 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--9.5-1--10.0-1.sql citus--10.0-1--10.0-2.sql citus--10.0-2--10.0-3.sql > citus--9.5-1--10.0-4.sql 7 8-- copy of citus--9.5-1--10.0-1 9 10DROP FUNCTION pg_catalog.upgrade_to_reference_table(regclass); 11DROP FUNCTION IF EXISTS pg_catalog.citus_total_relation_size(regclass); 12 13#include "udfs/citus_total_relation_size/10.0-1.sql" 14#include "udfs/citus_finish_pg_upgrade/10.0-1.sql" 15#include "udfs/alter_distributed_table/10.0-1.sql" 16#include "udfs/alter_table_set_access_method/10.0-1.sql" 17#include "udfs/undistribute_table/10.0-1.sql" 18#include "udfs/create_citus_local_table/10.0-1.sql" 19#include "udfs/citus_set_coordinator_host/10.0-1.sql" 20#include "udfs/citus_add_node/10.0-1.sql" 21#include "udfs/citus_activate_node/10.0-1.sql" 22#include "udfs/citus_add_inactive_node/10.0-1.sql" 23#include "udfs/citus_add_secondary_node/10.0-1.sql" 24#include "udfs/citus_disable_node/10.0-1.sql" 25#include "udfs/citus_drain_node/10.0-1.sql" 26#include "udfs/citus_remove_node/10.0-1.sql" 27#include "udfs/citus_set_node_property/10.0-1.sql" 28#include "udfs/citus_unmark_object_distributed/10.0-1.sql" 29#include "udfs/citus_update_node/10.0-1.sql" 30#include "udfs/citus_update_shard_statistics/10.0-1.sql" 31#include "udfs/citus_update_table_statistics/10.0-1.sql" 32#include "udfs/citus_copy_shard_placement/10.0-1.sql" 33#include "udfs/citus_move_shard_placement/10.0-1.sql" 34#include "udfs/citus_drop_trigger/10.0-1.sql" 35#include "udfs/worker_change_sequence_dependency/10.0-1.sql" 36#include "udfs/remove_local_tables_from_metadata/10.0-1.sql" 37 38#include "../../columnar/sql/columnar--9.5-1--10.0-1.sql" 39 40#include "udfs/time_partition_range/10.0-1.sql" 41#include "udfs/time_partitions/10.0-1.sql" 42#include "udfs/alter_old_partitions_set_access_method/10.0-1.sql" 43 44ALTER FUNCTION pg_catalog.master_conninfo_cache_invalidate() 45RENAME TO citus_conninfo_cache_invalidate; 46ALTER FUNCTION pg_catalog.master_dist_local_group_cache_invalidate() 47RENAME TO citus_dist_local_group_cache_invalidate; 48ALTER FUNCTION pg_catalog.master_dist_node_cache_invalidate() 49RENAME TO citus_dist_node_cache_invalidate; 50ALTER FUNCTION pg_catalog.master_dist_object_cache_invalidate() 51RENAME TO citus_dist_object_cache_invalidate; 52ALTER FUNCTION pg_catalog.master_dist_partition_cache_invalidate() 53RENAME TO citus_dist_partition_cache_invalidate; 54ALTER FUNCTION pg_catalog.master_dist_placement_cache_invalidate() 55RENAME TO citus_dist_placement_cache_invalidate; 56ALTER FUNCTION pg_catalog.master_dist_shard_cache_invalidate() 57RENAME TO citus_dist_shard_cache_invalidate; 58 59#include "udfs/citus_conninfo_cache_invalidate/10.0-1.sql" 60#include "udfs/citus_dist_local_group_cache_invalidate/10.0-1.sql" 61#include "udfs/citus_dist_node_cache_invalidate/10.0-1.sql" 62#include "udfs/citus_dist_object_cache_invalidate/10.0-1.sql" 63#include "udfs/citus_dist_partition_cache_invalidate/10.0-1.sql" 64#include "udfs/citus_dist_placement_cache_invalidate/10.0-1.sql" 65#include "udfs/citus_dist_shard_cache_invalidate/10.0-1.sql" 66 67ALTER FUNCTION pg_catalog.master_drop_all_shards(regclass, text, text) 68RENAME TO citus_drop_all_shards; 69 70DROP FUNCTION pg_catalog.master_modify_multiple_shards(text); 71DROP FUNCTION pg_catalog.master_create_distributed_table(regclass, text, citus.distribution_type); 72DROP FUNCTION pg_catalog.master_create_worker_shards(text, integer, integer); 73DROP FUNCTION pg_catalog.mark_tables_colocated(regclass, regclass[]); 74#include "udfs/citus_shard_sizes/10.0-1.sql" 75#include "udfs/citus_shards/10.0-1.sql" 76 77#include "udfs/fix_pre_citus10_partitioned_table_constraint_names/10.0-1.sql" 78#include "udfs/worker_fix_pre_citus10_partitioned_table_constraint_names/10.0-1.sql" 79DROP FUNCTION pg_catalog.citus_dist_stat_activity CASCADE; 80 81CREATE OR REPLACE FUNCTION pg_catalog.citus_dist_stat_activity(OUT query_hostname text, OUT query_hostport int, OUT distributed_query_host_name text, OUT distributed_query_host_port int, 82 OUT transaction_number int8, OUT transaction_stamp timestamptz, OUT datid oid, OUT datname name, 83 OUT pid int, OUT usesysid oid, OUT usename name, OUT application_name text, OUT client_addr INET, 84 OUT client_hostname TEXT, OUT client_port int, OUT backend_start timestamptz, OUT xact_start timestamptz, 85 OUT query_start timestamptz, OUT state_change timestamptz, OUT wait_event_type text, OUT wait_event text, 86 OUT state text, OUT backend_xid xid, OUT backend_xmin xid, OUT query text, OUT backend_type text) 87RETURNS SETOF RECORD 88LANGUAGE C STRICT AS 'MODULE_PATHNAME', 89$$citus_dist_stat_activity$$; 90 91COMMENT ON FUNCTION pg_catalog.citus_dist_stat_activity(OUT query_hostname text, OUT query_hostport int, OUT distributed_query_host_name text, OUT distributed_query_host_port int, 92 OUT transaction_number int8, OUT transaction_stamp timestamptz, OUT datid oid, OUT datname name, 93 OUT pid int, OUT usesysid oid, OUT usename name, OUT application_name text, OUT client_addr INET, 94 OUT client_hostname TEXT, OUT client_port int, OUT backend_start timestamptz, OUT xact_start timestamptz, 95 OUT query_start timestamptz, OUT state_change timestamptz, OUT wait_event_type text, OUT wait_event text, 96 OUT state text, OUT backend_xid xid, OUT backend_xmin xid, OUT query text, OUT backend_type text) 97IS 'returns distributed transaction activity on distributed tables'; 98 99CREATE VIEW citus.citus_dist_stat_activity AS 100SELECT * FROM pg_catalog.citus_dist_stat_activity(); 101ALTER VIEW citus.citus_dist_stat_activity SET SCHEMA pg_catalog; 102GRANT SELECT ON pg_catalog.citus_dist_stat_activity TO PUBLIC; 103 104SET search_path = 'pg_catalog'; 105 106CREATE VIEW citus.citus_lock_waits AS 107 108WITH 109citus_dist_stat_activity AS 110( 111 SELECT * FROM citus_dist_stat_activity 112), 113unique_global_wait_edges AS 114( 115 SELECT DISTINCT ON(waiting_node_id, waiting_transaction_num, blocking_node_id, blocking_transaction_num) * FROM dump_global_wait_edges() 116), 117citus_dist_stat_activity_with_node_id AS 118( 119 SELECT 120 citus_dist_stat_activity.*, (CASE citus_dist_stat_activity.distributed_query_host_name WHEN 'coordinator_host' THEN 0 ELSE pg_dist_node.nodeid END) as initiator_node_id 121 FROM 122 citus_dist_stat_activity LEFT JOIN pg_dist_node 123 ON 124 citus_dist_stat_activity.distributed_query_host_name = pg_dist_node.nodename AND 125 citus_dist_stat_activity.distributed_query_host_port = pg_dist_node.nodeport 126) 127SELECT 128 waiting.pid AS waiting_pid, 129 blocking.pid AS blocking_pid, 130 waiting.query AS blocked_statement, 131 blocking.query AS current_statement_in_blocking_process, 132 waiting.initiator_node_id AS waiting_node_id, 133 blocking.initiator_node_id AS blocking_node_id, 134 waiting.distributed_query_host_name AS waiting_node_name, 135 blocking.distributed_query_host_name AS blocking_node_name, 136 waiting.distributed_query_host_port AS waiting_node_port, 137 blocking.distributed_query_host_port AS blocking_node_port 138FROM 139 unique_global_wait_edges 140JOIN 141 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) 142JOIN 143 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); 144 145ALTER VIEW citus.citus_lock_waits SET SCHEMA pg_catalog; 146GRANT SELECT ON pg_catalog.citus_lock_waits TO PUBLIC; 147 148DROP FUNCTION citus_worker_stat_activity CASCADE; 149 150CREATE OR REPLACE FUNCTION citus_worker_stat_activity(OUT query_hostname text, OUT query_hostport int, OUT distributed_query_host_name text, OUT distributed_query_host_port int, 151 OUT transaction_number int8, OUT transaction_stamp timestamptz, OUT datid oid, OUT datname name, 152 OUT pid int, OUT usesysid oid, OUT usename name, OUT application_name text, OUT client_addr INET, 153 OUT client_hostname TEXT, OUT client_port int, OUT backend_start timestamptz, OUT xact_start timestamptz, 154 OUT query_start timestamptz, OUT state_change timestamptz, OUT wait_event_type text, OUT wait_event text, 155 OUT state text, OUT backend_xid xid, OUT backend_xmin xid, OUT query text, OUT backend_type text) 156RETURNS SETOF RECORD 157LANGUAGE C STRICT AS 'MODULE_PATHNAME', 158$$citus_worker_stat_activity$$; 159 160COMMENT ON FUNCTION citus_worker_stat_activity(OUT query_hostname text, OUT query_hostport int, OUT distributed_query_host_name text, OUT distributed_query_host_port int, 161 OUT transaction_number int8, OUT transaction_stamp timestamptz, OUT datid oid, OUT datname name, 162 OUT pid int, OUT usesysid oid, OUT usename name, OUT application_name text, OUT client_addr INET, 163 OUT client_hostname TEXT, OUT client_port int, OUT backend_start timestamptz, OUT xact_start timestamptz, 164 OUT query_start timestamptz, OUT state_change timestamptz, OUT wait_event_type text, OUT wait_event text, 165 OUT state text, OUT backend_xid xid, OUT backend_xmin xid, OUT query text, OUT backend_type text) 166IS 'returns distributed transaction activity on shards of distributed tables'; 167 168CREATE VIEW citus.citus_worker_stat_activity AS 169SELECT * FROM pg_catalog.citus_worker_stat_activity(); 170ALTER VIEW citus.citus_worker_stat_activity SET SCHEMA pg_catalog; 171GRANT SELECT ON pg_catalog.citus_worker_stat_activity TO PUBLIC; 172 173-- copy of citus--10.0-1--10.0-2 174 175#include "../../columnar/sql/columnar--10.0-1--10.0-2.sql" 176 177-- copy of citus--10.0-2--10.0-3 178 179#include "udfs/citus_update_table_statistics/10.0-3.sql" 180 181CREATE OR REPLACE FUNCTION master_update_table_statistics(relation regclass) 182RETURNS VOID 183 LANGUAGE C STRICT 184 AS 'MODULE_PATHNAME', $$citus_update_table_statistics$$; 185COMMENT ON FUNCTION pg_catalog.master_update_table_statistics(regclass) 186 IS 'updates shard statistics of the given table'; 187 188CREATE OR REPLACE FUNCTION pg_catalog.citus_get_active_worker_nodes(OUT node_name text, OUT node_port bigint) 189 RETURNS SETOF record 190 LANGUAGE C STRICT ROWS 100 191 AS 'MODULE_PATHNAME', $$citus_get_active_worker_nodes$$; 192COMMENT ON FUNCTION pg_catalog.citus_get_active_worker_nodes() 193 IS 'fetch set of active worker nodes'; 194 195-- copy of citus--10.0-3--10.0-4 196 197-- This migration file aims to fix 2 issues with upgrades on clusters 198 199-- 1. a bug in public schema dependency for citus_tables view. 200-- 201-- Users who do not have public schema in their clusters were unable to upgrade 202-- to Citus 10.x due to the citus_tables view that used to be created in public 203-- schema 204 205#include "udfs/citus_tables/10.0-4.sql" 206 207-- 2. a bug in our PG upgrade functions 208-- 209-- Users who took the 9.5-2--10.0-1 upgrade path already have the fix, but users 210-- who took the 9.5-1--10.0-1 upgrade path do not. Hence, we repeat the CREATE OR 211-- REPLACE from the 9.5-2 definition for citus_prepare_pg_upgrade. 212 213#include "udfs/citus_prepare_pg_upgrade/9.5-2.sql" 214#include "udfs/citus_finish_pg_upgrade/10.0-4.sql" 215 216 217RESET search_path; 218