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