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