1-- 2-- MULTI_MX_SCHEMA_SUPPORT 3-- 4-- connect to a worker node and run some queries 5\c - - - :worker_1_port 6-- test very basic queries 7SELECT * FROM nation_hash ORDER BY n_nationkey LIMIT 4; 8 n_nationkey | n_name | n_regionkey | n_comment 9--------------------------------------------------------------------- 10 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai 11 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 12 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 13 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 14(4 rows) 15 16SELECT * FROM citus_mx_test_schema.nation_hash ORDER BY n_nationkey LIMIT 4; 17 n_nationkey | n_name | n_regionkey | n_comment 18--------------------------------------------------------------------- 19 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai 20 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 21 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 22 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 23(4 rows) 24 25-- test cursors 26SET search_path TO public; 27BEGIN; 28DECLARE test_cursor CURSOR FOR 29 SELECT * 30 FROM nation_hash 31 WHERE n_nationkey = 1; 32FETCH test_cursor; 33 n_nationkey | n_name | n_regionkey | n_comment 34--------------------------------------------------------------------- 35 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 36(1 row) 37 38FETCH test_cursor; 39 n_nationkey | n_name | n_regionkey | n_comment 40--------------------------------------------------------------------- 41(0 rows) 42 43FETCH BACKWARD test_cursor; 44 n_nationkey | n_name | n_regionkey | n_comment 45--------------------------------------------------------------------- 46 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 47(1 row) 48 49END; 50-- test with search_path is set 51SET search_path TO citus_mx_test_schema; 52BEGIN; 53DECLARE test_cursor CURSOR FOR 54 SELECT * 55 FROM nation_hash 56 WHERE n_nationkey = 1; 57FETCH test_cursor; 58 n_nationkey | n_name | n_regionkey | n_comment 59--------------------------------------------------------------------- 60 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 61(1 row) 62 63FETCH test_cursor; 64 n_nationkey | n_name | n_regionkey | n_comment 65--------------------------------------------------------------------- 66(0 rows) 67 68FETCH BACKWARD test_cursor; 69 n_nationkey | n_name | n_regionkey | n_comment 70--------------------------------------------------------------------- 71 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 72(1 row) 73 74END; 75-- test inserting to table in different schema 76SET search_path TO public; 77INSERT INTO citus_mx_test_schema.nation_hash(n_nationkey, n_name, n_regionkey) VALUES (100, 'TURKEY', 3); 78-- verify insertion 79SELECT * FROM citus_mx_test_schema.nation_hash WHERE n_nationkey = 100; 80 n_nationkey | n_name | n_regionkey | n_comment 81--------------------------------------------------------------------- 82 100 | TURKEY | 3 | 83(1 row) 84 85-- test with search_path is set 86SET search_path TO citus_mx_test_schema; 87INSERT INTO nation_hash(n_nationkey, n_name, n_regionkey) VALUES (101, 'GERMANY', 3); 88-- verify insertion 89SELECT * FROM nation_hash WHERE n_nationkey = 101; 90 n_nationkey | n_name | n_regionkey | n_comment 91--------------------------------------------------------------------- 92 101 | GERMANY | 3 | 93(1 row) 94 95-- TODO: add UPDATE/DELETE/UPSERT 96-- test UDFs with schemas 97SET search_path TO public; 98-- UDF in public, table in a schema other than public, search_path is not set 99SELECT simpleTestFunction(n_nationkey)::int FROM citus_mx_test_schema.nation_hash GROUP BY 1 ORDER BY 1 DESC LIMIT 5; 100 simpletestfunction 101--------------------------------------------------------------------- 102 152 103 151 104 37 105 35 106 34 107(5 rows) 108 109-- UDF in public, table in a schema other than public, search_path is set 110SET search_path TO citus_mx_test_schema; 111SELECT public.simpleTestFunction(n_nationkey)::int FROM citus_mx_test_schema.nation_hash GROUP BY 1 ORDER BY 1 DESC LIMIT 5; 112 simpletestfunction 113--------------------------------------------------------------------- 114 152 115 151 116 37 117 35 118 34 119(5 rows) 120 121-- UDF in schema, table in a schema other than public, search_path is not set 122SET search_path TO public; 123SELECT citus_mx_test_schema.simpleTestFunction2(n_nationkey)::int FROM citus_mx_test_schema.nation_hash GROUP BY 1 ORDER BY 1 DESC LIMIT 5; 124 simpletestfunction2 125--------------------------------------------------------------------- 126 152 127 151 128 37 129 35 130 34 131(5 rows) 132 133-- UDF in schema, table in a schema other than public, search_path is set 134SET search_path TO citus_mx_test_schema; 135SELECT simpleTestFunction2(n_nationkey)::int FROM nation_hash GROUP BY 1 ORDER BY 1 DESC LIMIT 5; 136 simpletestfunction2 137--------------------------------------------------------------------- 138 152 139 151 140 37 141 35 142 34 143(5 rows) 144 145-- test operators with schema 146SET search_path TO public; 147-- test with search_path is not set 148SELECT * FROM citus_mx_test_schema.nation_hash WHERE n_nationkey OPERATOR(citus_mx_test_schema.===) 1; 149 n_nationkey | n_name | n_regionkey | n_comment 150--------------------------------------------------------------------- 151 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 152(1 row) 153 154-- test with search_path is set 155SET search_path TO citus_mx_test_schema; 156SELECT * FROM nation_hash WHERE n_nationkey OPERATOR(===) 1; 157 n_nationkey | n_name | n_regionkey | n_comment 158--------------------------------------------------------------------- 159 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 160(1 row) 161 162SELECT * FROM citus_mx_test_schema.nation_hash_collation_search_path ORDER BY 1; 163 n_nationkey | n_name | n_regionkey | n_comment 164--------------------------------------------------------------------- 165 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai 166 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 167 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 168 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 169 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d 170 5 | ETHIOPIA | 0 | ven packages wake quickly. regu 171(6 rows) 172 173SELECT n_comment FROM citus_mx_test_schema.nation_hash_collation_search_path ORDER BY n_comment COLLATE citus_mx_test_schema.english; 174 n_comment 175--------------------------------------------------------------------- 176 al foxes promise slyly according to the regular accounts. bold requests alon 177 eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 178 haggle. carefully final deposits detect slyly agai 179 ven packages wake quickly. regu 180 y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d 181 y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 182(6 rows) 183 184SET search_path TO citus_mx_test_schema; 185SELECT * FROM nation_hash_collation_search_path ORDER BY 1 DESC; 186 n_nationkey | n_name | n_regionkey | n_comment 187--------------------------------------------------------------------- 188 5 | ETHIOPIA | 0 | ven packages wake quickly. regu 189 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d 190 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 191 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 192 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 193 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai 194(6 rows) 195 196SELECT n_comment FROM nation_hash_collation_search_path ORDER BY n_comment COLLATE english; 197 n_comment 198--------------------------------------------------------------------- 199 al foxes promise slyly according to the regular accounts. bold requests alon 200 eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 201 haggle. carefully final deposits detect slyly agai 202 ven packages wake quickly. regu 203 y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d 204 y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 205(6 rows) 206 207SELECT * FROM citus_mx_test_schema.nation_hash_composite_types WHERE test_col = '(a,a)'::citus_mx_test_schema.new_composite_type ORDER BY 1::int DESC; 208 n_nationkey | n_name | n_regionkey | n_comment | test_col 209--------------------------------------------------------------------- 210 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai | (a,a) 211(1 row) 212 213--test with search_path is set 214SET search_path TO citus_mx_test_schema; 215SELECT * FROM nation_hash_composite_types WHERE test_col = '(a,a)'::new_composite_type ORDER BY 1::int DESC; 216 n_nationkey | n_name | n_regionkey | n_comment | test_col 217--------------------------------------------------------------------- 218 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai | (a,a) 219(1 row) 220 221SET citus.enable_repartition_joins to ON; 222-- check when search_path is public, 223-- join of two tables which are in different schemas, 224-- join on partition column 225SET search_path TO public; 226SELECT 227 count (*) 228FROM 229 citus_mx_test_schema_join_1.nation_hash n1, citus_mx_test_schema_join_2.nation_hash n2 230WHERE 231 n1.n_nationkey = n2.n_nationkey; 232 count 233--------------------------------------------------------------------- 234 25 235(1 row) 236 237-- check when search_path is different than public, 238-- join of two tables which are in different schemas, 239-- join on partition column 240SET search_path TO citus_mx_test_schema_join_1; 241SELECT 242 count (*) 243FROM 244 nation_hash n1, citus_mx_test_schema_join_2.nation_hash n2 245WHERE 246 n1.n_nationkey = n2.n_nationkey; 247 count 248--------------------------------------------------------------------- 249 25 250(1 row) 251 252-- check when search_path is public, 253-- join of two tables which are in same schemas, 254-- join on partition column 255SET search_path TO public; 256SELECT 257 count (*) 258FROM 259 citus_mx_test_schema_join_1.nation_hash n1, citus_mx_test_schema_join_1.nation_hash_2 n2 260WHERE 261 n1.n_nationkey = n2.n_nationkey; 262 count 263--------------------------------------------------------------------- 264 25 265(1 row) 266 267-- check when search_path is different than public, 268-- join of two tables which are in same schemas, 269-- join on partition column 270SET search_path TO citus_mx_test_schema_join_1; 271SELECT 272 count (*) 273FROM 274 nation_hash n1, nation_hash_2 n2 275WHERE 276 n1.n_nationkey = n2.n_nationkey; 277 count 278--------------------------------------------------------------------- 279 25 280(1 row) 281 282-- single repartition joins 283-- check when search_path is public, 284-- join of two tables which are in different schemas, 285-- join on partition column and non-partition column 286--SET search_path TO public; 287SELECT 288 count (*) 289FROM 290 citus_mx_test_schema_join_1.nation_hash n1, citus_mx_test_schema_join_2.nation_hash n2 291WHERE 292 n1.n_nationkey = n2.n_regionkey; 293 count 294--------------------------------------------------------------------- 295 25 296(1 row) 297 298-- check when search_path is different than public, 299-- join of two tables which are in different schemas, 300-- join on partition column and non-partition column 301SET search_path TO citus_mx_test_schema_join_1; 302SELECT 303 count (*) 304FROM 305 nation_hash n1, citus_mx_test_schema_join_2.nation_hash n2 306WHERE 307 n1.n_nationkey = n2.n_regionkey; 308 count 309--------------------------------------------------------------------- 310 25 311(1 row) 312 313-- check when search_path is different than public, 314-- join of two tables which are in same schemas, 315-- join on partition column and non-partition column 316SET search_path TO citus_mx_test_schema_join_1; 317SELECT 318 count (*) 319FROM 320 nation_hash n1, nation_hash_2 n2 321WHERE 322 n1.n_nationkey = n2.n_regionkey; 323 count 324--------------------------------------------------------------------- 325 25 326(1 row) 327 328-- hash repartition joins 329-- check when search_path is public, 330-- join of two tables which are in different schemas, 331-- join on non-partition column 332SET search_path TO public; 333SELECT 334 count (*) 335FROM 336 citus_mx_test_schema_join_1.nation_hash n1, citus_mx_test_schema_join_2.nation_hash n2 337WHERE 338 n1.n_regionkey = n2.n_regionkey; 339 count 340--------------------------------------------------------------------- 341 125 342(1 row) 343 344-- check when search_path is different than public, 345-- join of two tables which are in different schemas, 346-- join on non-partition column 347SET search_path TO citus_mx_test_schema_join_1; 348SELECT 349 count (*) 350FROM 351 nation_hash n1, citus_mx_test_schema_join_2.nation_hash n2 352WHERE 353 n1.n_regionkey = n2.n_regionkey; 354 count 355--------------------------------------------------------------------- 356 125 357(1 row) 358 359-- check when search_path is different than public, 360-- join of two tables which are in same schemas, 361-- join on non-partition column 362SET search_path TO citus_mx_test_schema_join_1; 363SELECT 364 count (*) 365FROM 366 nation_hash n1, nation_hash_2 n2 367WHERE 368 n1.n_regionkey = n2.n_regionkey; 369 count 370--------------------------------------------------------------------- 371 125 372(1 row) 373 374-- set task_executor back to adaptive 375-- connect to the master and do some test 376-- regarding DDL support on schemas where 377-- the search_path is set 378\c - - - :master_port 379CREATE SCHEMA mx_ddl_schema_1; 380CREATE SCHEMA mx_ddl_schema_2; 381CREATE SCHEMA "CiTuS.TeAeN"; 382SET citus.shard_count TO 4; 383SET citus.shard_replication_factor TO 1; 384-- in the first test make sure that we handle DDLs 385-- when search path is set 386SET search_path TO mx_ddl_schema_1; 387CREATE TABLE table_1 (key int PRIMARY KEY, value text); 388SELECT create_distributed_table('table_1', 'key'); 389 create_distributed_table 390--------------------------------------------------------------------- 391 392(1 row) 393 394CREATE INDEX i1 ON table_1(value); 395CREATE INDEX CONCURRENTLY i2 ON table_1(value); 396-- now create a foriegn key on tables that are on seperate schemas 397SET search_path TO mx_ddl_schema_1, mx_ddl_schema_2; 398CREATE TABLE mx_ddl_schema_2.table_2 (key int PRIMARY KEY, value text); 399SELECT create_distributed_table('mx_ddl_schema_2.table_2', 'key'); 400 create_distributed_table 401--------------------------------------------------------------------- 402 403(1 row) 404 405ALTER TABLE table_2 ADD CONSTRAINT test_constraint FOREIGN KEY (key) REFERENCES table_1(key); 406-- we can also handle schema/table names with quotation 407SET search_path TO "CiTuS.TeAeN"; 408CREATE TABLE "TeeNTabLE.1!?!"(id int, "TeNANt_Id" int); 409SELECT create_distributed_table('"TeeNTabLE.1!?!"', 'id'); 410 create_distributed_table 411--------------------------------------------------------------------- 412 413(1 row) 414 415CREATE INDEX "MyTenantIndex" ON "CiTuS.TeAeN"."TeeNTabLE.1!?!"("TeNANt_Id"); 416SET search_path TO "CiTuS.TeAeN", mx_ddl_schema_1, mx_ddl_schema_2; 417ALTER TABLE "TeeNTabLE.1!?!" ADD CONSTRAINT test_constraint_2 FOREIGN KEY (id) REFERENCES table_1(key); 418ALTER TABLE "TeeNTabLE.1!?!" ADD COLUMN new_col INT; 419-- same semantics with CREATE INDEX CONCURRENTLY such that 420-- it uses a single connection to execute all the commands 421SET citus.multi_shard_modify_mode TO 'sequential'; 422ALTER TABLE "TeeNTabLE.1!?!" DROP COLUMN new_col; 423-- set it back to the default value 424SET citus.multi_shard_modify_mode TO 'parallel'; 425-- test with a not existing schema is in the search path 426SET search_path TO not_existing_schema, "CiTuS.TeAeN"; 427ALTER TABLE "TeeNTabLE.1!?!" ADD COLUMN new_col INT; 428-- test with a public schema is in the search path 429SET search_path TO public, "CiTuS.TeAeN"; 430ALTER TABLE "TeeNTabLE.1!?!" DROP COLUMN new_col; 431-- make sure that we handle transaction blocks properly 432BEGIN; 433 SET search_path TO public, "CiTuS.TeAeN"; 434 ALTER TABLE "TeeNTabLE.1!?!" ADD COLUMN new_col INT; 435 SET search_path TO mx_ddl_schema_1; 436 CREATE INDEX i55 ON table_1(value); 437 SET search_path TO mx_ddl_schema_1, public, "CiTuS.TeAeN"; 438 ALTER TABLE "TeeNTabLE.1!?!" DROP COLUMN new_col; 439 DROP INDEX i55; 440COMMIT; 441-- set the search_path to null 442SET search_path TO ''; 443ALTER TABLE "CiTuS.TeAeN"."TeeNTabLE.1!?!" ADD COLUMN new_col INT; 444-- set the search_path to not existing schema 445SET search_path TO not_existing_schema; 446ALTER TABLE "CiTuS.TeAeN"."TeeNTabLE.1!?!" DROP COLUMN new_col; 447DROP SCHEMA mx_ddl_schema_1, mx_ddl_schema_2, "CiTuS.TeAeN" CASCADE; 448NOTICE: drop cascades to 3 other objects 449DETAIL: drop cascades to table "CiTuS.TeAeN"."TeeNTabLE.1!?!" 450drop cascades to table mx_ddl_schema_2.table_2 451drop cascades to table mx_ddl_schema_1.table_1 452-- test if ALTER TABLE SET SCHEMA sets the original table in the worker 453SET search_path TO public; 454CREATE SCHEMA mx_old_schema; 455CREATE TABLE mx_old_schema.table_set_schema (id int); 456SELECT create_distributed_table('mx_old_schema.table_set_schema', 'id'); 457 create_distributed_table 458--------------------------------------------------------------------- 459 460(1 row) 461 462CREATE SCHEMA mx_new_schema; 463SELECT objid::oid::regnamespace as "Distributed Schemas" 464 FROM citus.pg_dist_object 465 WHERE objid::oid::regnamespace IN ('mx_old_schema', 'mx_new_schema'); 466 Distributed Schemas 467--------------------------------------------------------------------- 468 mx_old_schema 469(1 row) 470 471\c - - - :worker_1_port 472SELECT table_schema AS "Table's Schema" FROM information_schema.tables WHERE table_name='table_set_schema'; 473 Table's Schema 474--------------------------------------------------------------------- 475 mx_old_schema 476(1 row) 477 478SELECT table_schema AS "Shards' Schema" 479 FROM information_schema.tables 480 WHERE table_name LIKE 'table\_set\_schema\_%' 481 GROUP BY table_schema; 482 Shards' Schema 483--------------------------------------------------------------------- 484 mx_old_schema 485(1 row) 486 487\c - - - :master_port 488ALTER TABLE mx_old_schema.table_set_schema SET SCHEMA mx_new_schema; 489SELECT objid::oid::regnamespace as "Distributed Schemas" 490 FROM citus.pg_dist_object 491 WHERE objid::oid::regnamespace IN ('mx_old_schema', 'mx_new_schema'); 492 Distributed Schemas 493--------------------------------------------------------------------- 494 mx_old_schema 495 mx_new_schema 496(2 rows) 497 498\c - - - :worker_1_port 499SELECT table_schema AS "Table's Schema" FROM information_schema.tables WHERE table_name='table_set_schema'; 500 Table's Schema 501--------------------------------------------------------------------- 502 mx_new_schema 503(1 row) 504 505SELECT table_schema AS "Shards' Schema" 506 FROM information_schema.tables 507 WHERE table_name LIKE 'table\_set\_schema\_%' 508 GROUP BY table_schema; 509 Shards' Schema 510--------------------------------------------------------------------- 511 mx_new_schema 512(1 row) 513 514\c - - - :master_port 515SELECT * FROM mx_new_schema.table_set_schema; 516 id 517--------------------------------------------------------------------- 518(0 rows) 519 520DROP SCHEMA mx_old_schema CASCADE; 521DROP SCHEMA mx_new_schema CASCADE; 522NOTICE: drop cascades to table mx_new_schema.table_set_schema 523