1CREATE SCHEMA "Mx Regular User"; 2SET search_path TO "Mx Regular User"; 3-- add coordinator in idempotent way 4SELECT 1 FROM master_add_node('localhost', :master_port, groupid => 0); 5 ?column? 6--------------------------------------------------------------------- 7 1 8(1 row) 9 10-- sync the metadata to both nodes 11SELECT start_metadata_sync_to_node('localhost', :worker_1_port); 12 start_metadata_sync_to_node 13--------------------------------------------------------------------- 14 15(1 row) 16 17SELECT start_metadata_sync_to_node('localhost', :worker_2_port); 18 start_metadata_sync_to_node 19--------------------------------------------------------------------- 20 21(1 row) 22 23-- create a role and give access one each node separately 24-- and increase the error level to prevent enterprise to diverge 25SET client_min_messages TO ERROR; 26SET citus.enable_ddl_propagation TO OFF; 27CREATE USER regular_mx_user WITH LOGIN; 28SELECT 1 FROM run_command_on_workers($$CREATE USER regular_mx_user WITH LOGIN;$$); 29 ?column? 30--------------------------------------------------------------------- 31 1 32 1 33(2 rows) 34 35GRANT ALL ON SCHEMA "Mx Regular User" TO regular_mx_user; 36-- create another table owned by the super user (e.g., current user of the session) 37-- and GRANT access to the user 38CREATE SCHEMA "Mx Super User"; 39SELECT 1 FROM run_command_on_workers($$CREATE SCHEMA "Mx Super User";$$); 40 ?column? 41--------------------------------------------------------------------- 42 1 43 1 44(2 rows) 45 46SET citus.next_shard_id TO 2980000; 47SET search_path TO "Mx Super User"; 48CREATE TABLE super_user_owned_regular_user_granted (a int PRIMARY KEY, b int); 49SELECT create_reference_table ('"Mx Super User".super_user_owned_regular_user_granted'); 50 create_reference_table 51--------------------------------------------------------------------- 52 53(1 row) 54 55-- show that this table is owned by super user 56SELECT 57 rolsuper 58FROM 59 pg_roles 60 WHERE oid 61 IN 62 (SELECT relowner FROM pg_class WHERE oid = '"Mx Super User".super_user_owned_regular_user_granted'::regclass); 63 rolsuper 64--------------------------------------------------------------------- 65 t 66(1 row) 67 68-- make sure that granting produce the same output for both community and enterprise 69SET client_min_messages TO ERROR; 70GRANT USAGE ON SCHEMA "Mx Super User" TO regular_mx_user; 71GRANT INSERT ON TABLE super_user_owned_regular_user_granted TO regular_mx_user; 72SELECT 1 FROM run_command_on_workers($$GRANT USAGE ON SCHEMA "Mx Super User" TO regular_mx_user;$$); 73 ?column? 74--------------------------------------------------------------------- 75 1 76 1 77(2 rows) 78 79SELECT 1 FROM run_command_on_workers($$GRANT INSERT ON TABLE "Mx Super User".super_user_owned_regular_user_granted TO regular_mx_user;$$); 80 ?column? 81--------------------------------------------------------------------- 82 1 83 1 84(2 rows) 85 86SELECT 1 FROM run_command_on_placements('super_user_owned_regular_user_granted', $$GRANT INSERT ON TABLE %s TO regular_mx_user;$$); 87 ?column? 88--------------------------------------------------------------------- 89 1 90 1 91 1 92(3 rows) 93 94-- now that the GRANT is given, the regular user should be able to 95-- INSERT into the table 96\c - regular_mx_user - :master_port 97SET search_path TO "Mx Super User"; 98COPY super_user_owned_regular_user_granted FROM STDIN WITH CSV; 99-- however, this specific user doesn't have UPDATE/UPSERT/DELETE/TRUNCATE 100-- permission, so should fail 101INSERT INTO super_user_owned_regular_user_granted VALUES (1, 1), (2, 1) ON CONFLICT (a) DO NOTHING; 102ERROR: permission denied for table super_user_owned_regular_user_granted 103TRUNCATE super_user_owned_regular_user_granted; 104ERROR: permission denied for table super_user_owned_regular_user_granted 105CONTEXT: while executing command on localhost:xxxxx 106DELETE FROM super_user_owned_regular_user_granted; 107ERROR: permission denied for table super_user_owned_regular_user_granted 108UPDATE super_user_owned_regular_user_granted SET a = 1; 109ERROR: permission denied for table super_user_owned_regular_user_granted 110-- AccessExclusiveLock == 8 is strictly forbidden for any user 111SELECT lock_shard_resources(8, ARRAY[2980000]); 112ERROR: unsupported lockmode 8 113-- ExclusiveLock == 7 is forbidden for this user 114-- as only has INSERT rights 115SELECT lock_shard_resources(7, ARRAY[2980000]); 116ERROR: permission denied for table super_user_owned_regular_user_granted 117-- but should be able to acquire RowExclusiveLock 118BEGIN; 119 SELECT count(*) > 0 as acquired_lock from pg_locks where pid = pg_backend_pid() AND locktype = 'advisory'; 120 acquired_lock 121--------------------------------------------------------------------- 122 f 123(1 row) 124 125 SELECT lock_shard_resources(3, ARRAY[2980000]); 126 lock_shard_resources 127--------------------------------------------------------------------- 128 129(1 row) 130 131 SELECT count(*) > 0 as acquired_lock from pg_locks where pid = pg_backend_pid() AND locktype = 'advisory'; 132 acquired_lock 133--------------------------------------------------------------------- 134 t 135(1 row) 136 137COMMIT; 138-- acquring locks on non-existing shards is not meaningful but still we do not throw error as we might be in the middle 139-- of metadata syncing. We just do not acquire the locks 140BEGIN; 141 SELECT count(*) > 0 as acquired_lock from pg_locks where pid = pg_backend_pid() AND locktype = 'advisory'; 142 acquired_lock 143--------------------------------------------------------------------- 144 f 145(1 row) 146 147 SELECT lock_shard_resources(3, ARRAY[123456871]); 148 lock_shard_resources 149--------------------------------------------------------------------- 150 151(1 row) 152 153 SELECT count(*) > 0 as acquired_lock from pg_locks where pid = pg_backend_pid() AND locktype = 'advisory'; 154 acquired_lock 155--------------------------------------------------------------------- 156 f 157(1 row) 158 159COMMIT; 160\c - postgres - :master_port; 161SET search_path TO "Mx Super User"; 162SET client_min_messages TO ERROR; 163-- now allow users to do UPDATE on the tables 164GRANT UPDATE ON TABLE super_user_owned_regular_user_granted TO regular_mx_user; 165SELECT 1 FROM run_command_on_workers($$GRANT UPDATE ON TABLE "Mx Super User".super_user_owned_regular_user_granted TO regular_mx_user;$$); 166 ?column? 167--------------------------------------------------------------------- 168 1 169 1 170(2 rows) 171 172SELECT 1 FROM run_command_on_placements('super_user_owned_regular_user_granted', $$GRANT UPDATE ON TABLE %s TO regular_mx_user;$$); 173 ?column? 174--------------------------------------------------------------------- 175 1 176 1 177 1 178(3 rows) 179 180\c - regular_mx_user - :master_port 181SET search_path TO "Mx Super User"; 182UPDATE super_user_owned_regular_user_granted SET b = 1; 183-- AccessExclusiveLock == 8 is strictly forbidden for any user 184-- even after UPDATE is allowed 185SELECT lock_shard_resources(8, ARRAY[2980000]); 186ERROR: unsupported lockmode 8 187\c - postgres - :master_port; 188SET client_min_messages TO ERROR; 189DROP SCHEMA "Mx Super User" CASCADE; 190\c - postgres - :worker_1_port; 191SET client_min_messages TO ERROR; 192SET citus.enable_ddl_propagation TO OFF; 193CREATE SCHEMA "Mx Regular User"; 194GRANT ALL ON SCHEMA "Mx Regular User" TO regular_mx_user; 195\c - postgres - :worker_2_port; 196SET client_min_messages TO ERROR; 197SET citus.enable_ddl_propagation TO OFF; 198CREATE SCHEMA "Mx Regular User"; 199GRANT ALL ON SCHEMA "Mx Regular User" TO regular_mx_user; 200-- now connect with that user 201\c - regular_mx_user - :master_port 202SET search_path TO "Mx Regular User"; 203SET citus.next_shard_id TO 1560000; 204-- make sure that we sync the metadata 205SET citus.shard_replication_factor TO 1; 206CREATE TABLE partitioned_table (long_column_names_1 int, long_column_names_2 int, long_column_names_3 int, long_column_names_4 int, long_column_names_5 int, long_column_names_6 int, long_column_names_7 int, long_column_names_8 int, long_column_names_9 int, long_column_names_10 int, long_column_names_11 timestamp) PARTITION BY RANGE (long_column_names_11); 207CREATE TABLE very_long_child_partition_name_is_required_to_repro_the_bug PARTITION OF partitioned_table FOR VALUES FROM ('2011-01-01') TO ('2012-01-01'); 208SELECT create_distributed_table('partitioned_table', 'long_column_names_1'); 209 create_distributed_table 210--------------------------------------------------------------------- 211 212(1 row) 213 214SELECT bool_and(hasmetadata) FROM pg_dist_node WHERE nodename = 'localhost' and nodeport IN (:worker_1_port, :worker_2_port); 215 bool_and 216--------------------------------------------------------------------- 217 t 218(1 row) 219 220-- show that we can rollback 221BEGIN; 222 CREATE INDEX long_index_on_parent_table ON partitioned_table (long_column_names_1, long_column_names_2, long_column_names_3, long_column_names_4, long_column_names_5, long_column_names_6, long_column_names_11) INCLUDE (long_column_names_7, long_column_names_7, long_column_names_9, long_column_names_10); 223ROLLBACK; 224-- show that we can switch to sequential mode and still 225-- sync the metadata to the nodes 226BEGIN; 227 CREATE INDEX long_index_on_parent_table ON partitioned_table (long_column_names_1, long_column_names_2, long_column_names_3, long_column_names_4, long_column_names_5, long_column_names_6, long_column_names_11) INCLUDE (long_column_names_7, long_column_names_7, long_column_names_9, long_column_names_10); 228 show citus.multi_shard_modify_mode; 229 citus.multi_shard_modify_mode 230--------------------------------------------------------------------- 231 sequential 232(1 row) 233 234COMMIT; 235-- make sure that partitioned tables, columnar and conversion to columnar workes fine 236-- on Citus MX with a non-super user 237CREATE SEQUENCE my_mx_seq; 238CREATE TABLE users_table_part(col_to_drop int, user_id int, value_1 int, value_2 bigint DEFAULT nextval('my_mx_seq'::regclass), value_3 bigserial) PARTITION BY RANGE (value_1); 239CREATE TABLE users_table_part_0 PARTITION OF users_table_part FOR VALUES FROM (0) TO (1); 240CREATE TABLE users_table_part_1 PARTITION OF users_table_part FOR VALUES FROM (1) TO (2); 241SELECT create_distributed_table('users_table_part', 'user_id', colocate_with:='partitioned_table'); 242 create_distributed_table 243--------------------------------------------------------------------- 244 245(1 row) 246 247-- make sure that we can handle dropped columns nicely 248ALTER TABLE users_table_part DROP COLUMN col_to_drop; 249INSERT INTO users_table_part SELECT i, i %2, i %50 FROM generate_series(0, 100) i; 250BEGIN; 251 -- make sure to use multiple connections 252 SET LOCAL citus.force_max_query_parallelization TO ON; 253 CREATE TABLE users_table_part_2 PARTITION OF users_table_part FOR VALUES FROM (2) TO (3); 254 INSERT INTO users_table_part SELECT i, i %3, i %50 FROM generate_series(0, 100) i; 255 CREATE TABLE users_table_part_3 (user_id int, value_1 int, value_2 bigint, value_3 bigserial); 256 ALTER TABLE users_table_part ATTACH PARTITION users_table_part_3 FOR VALUES FROM (3) TO (4); 257 CREATE TABLE users_table_part_4 PARTITION OF users_table_part FOR VALUES FROM (4) TO (5) USING COLUMNAR;; 258COMMIT; 259SELECT alter_table_set_access_method('users_table_part_0', 'columnar'); 260NOTICE: creating a new table for "Mx Regular User".users_table_part_0 261NOTICE: moving the data of "Mx Regular User".users_table_part_0 262NOTICE: dropping the old "Mx Regular User".users_table_part_0 263NOTICE: renaming the new table to "Mx Regular User".users_table_part_0 264 alter_table_set_access_method 265--------------------------------------------------------------------- 266 267(1 row) 268 269SELECT alter_table_set_access_method('users_table_part_0', 'heap'); 270NOTICE: creating a new table for "Mx Regular User".users_table_part_0 271NOTICE: moving the data of "Mx Regular User".users_table_part_0 272NOTICE: dropping the old "Mx Regular User".users_table_part_0 273NOTICE: renaming the new table to "Mx Regular User".users_table_part_0 274 alter_table_set_access_method 275--------------------------------------------------------------------- 276 277(1 row) 278 279BEGIN; 280 SET LOCAL citus.force_max_query_parallelization TO ON; 281 SELECT alter_table_set_access_method('users_table_part_0', 'columnar'); 282NOTICE: creating a new table for "Mx Regular User".users_table_part_0 283NOTICE: moving the data of "Mx Regular User".users_table_part_0 284NOTICE: dropping the old "Mx Regular User".users_table_part_0 285NOTICE: renaming the new table to "Mx Regular User".users_table_part_0 286 alter_table_set_access_method 287--------------------------------------------------------------------- 288 289(1 row) 290 291 SELECT alter_table_set_access_method('users_table_part_0', 'heap'); 292NOTICE: creating a new table for "Mx Regular User".users_table_part_0 293NOTICE: moving the data of "Mx Regular User".users_table_part_0 294NOTICE: dropping the old "Mx Regular User".users_table_part_0 295NOTICE: renaming the new table to "Mx Regular User".users_table_part_0 296 alter_table_set_access_method 297--------------------------------------------------------------------- 298 299(1 row) 300 301ROLLBACK; 302BEGIN; 303 SET LOCAL citus.force_max_query_parallelization TO ON; 304 SELECT undistribute_table('users_table_part'); 305NOTICE: converting the partitions of "Mx Regular User".users_table_part 306NOTICE: creating a new table for "Mx Regular User".users_table_part_0 307NOTICE: moving the data of "Mx Regular User".users_table_part_0 308NOTICE: dropping the old "Mx Regular User".users_table_part_0 309NOTICE: renaming the new table to "Mx Regular User".users_table_part_0 310NOTICE: creating a new table for "Mx Regular User".users_table_part_1 311NOTICE: moving the data of "Mx Regular User".users_table_part_1 312NOTICE: dropping the old "Mx Regular User".users_table_part_1 313NOTICE: renaming the new table to "Mx Regular User".users_table_part_1 314NOTICE: creating a new table for "Mx Regular User".users_table_part_2 315NOTICE: moving the data of "Mx Regular User".users_table_part_2 316NOTICE: dropping the old "Mx Regular User".users_table_part_2 317NOTICE: renaming the new table to "Mx Regular User".users_table_part_2 318NOTICE: creating a new table for "Mx Regular User".users_table_part_3 319NOTICE: moving the data of "Mx Regular User".users_table_part_3 320NOTICE: dropping the old "Mx Regular User".users_table_part_3 321NOTICE: renaming the new table to "Mx Regular User".users_table_part_3 322NOTICE: creating a new table for "Mx Regular User".users_table_part_4 323NOTICE: moving the data of "Mx Regular User".users_table_part_4 324NOTICE: dropping the old "Mx Regular User".users_table_part_4 325NOTICE: renaming the new table to "Mx Regular User".users_table_part_4 326NOTICE: creating a new table for "Mx Regular User".users_table_part 327NOTICE: dropping the old "Mx Regular User".users_table_part 328NOTICE: renaming the new table to "Mx Regular User".users_table_part 329 undistribute_table 330--------------------------------------------------------------------- 331 332(1 row) 333 334 SELECT create_distributed_table('users_table_part', 'user_id'); 335NOTICE: Copying data from local table... 336NOTICE: copying the data has completed 337DETAIL: The local data in the table is no longer visible, but is still on disk. 338HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$"Mx Regular User".users_table_part_0$$) 339NOTICE: Copying data from local table... 340NOTICE: copying the data has completed 341DETAIL: The local data in the table is no longer visible, but is still on disk. 342HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$"Mx Regular User".users_table_part_1$$) 343NOTICE: Copying data from local table... 344NOTICE: copying the data has completed 345DETAIL: The local data in the table is no longer visible, but is still on disk. 346HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$"Mx Regular User".users_table_part_2$$) 347 create_distributed_table 348--------------------------------------------------------------------- 349 350(1 row) 351 352COMMIT; 353BEGIN; 354 -- make sure to use multiple connections 355 SET LOCAL citus.force_max_query_parallelization TO ON; 356 SELECT alter_distributed_table('users_table_part', shard_count:=9, cascade_to_colocated:=false); 357NOTICE: converting the partitions of "Mx Regular User".users_table_part 358NOTICE: creating a new table for "Mx Regular User".users_table_part_0 359NOTICE: moving the data of "Mx Regular User".users_table_part_0 360NOTICE: dropping the old "Mx Regular User".users_table_part_0 361NOTICE: renaming the new table to "Mx Regular User".users_table_part_0 362NOTICE: creating a new table for "Mx Regular User".users_table_part_1 363NOTICE: moving the data of "Mx Regular User".users_table_part_1 364NOTICE: dropping the old "Mx Regular User".users_table_part_1 365NOTICE: renaming the new table to "Mx Regular User".users_table_part_1 366NOTICE: creating a new table for "Mx Regular User".users_table_part_2 367NOTICE: moving the data of "Mx Regular User".users_table_part_2 368NOTICE: dropping the old "Mx Regular User".users_table_part_2 369NOTICE: renaming the new table to "Mx Regular User".users_table_part_2 370NOTICE: creating a new table for "Mx Regular User".users_table_part_3 371NOTICE: moving the data of "Mx Regular User".users_table_part_3 372NOTICE: dropping the old "Mx Regular User".users_table_part_3 373NOTICE: renaming the new table to "Mx Regular User".users_table_part_3 374NOTICE: creating a new table for "Mx Regular User".users_table_part_4 375NOTICE: moving the data of "Mx Regular User".users_table_part_4 376NOTICE: dropping the old "Mx Regular User".users_table_part_4 377NOTICE: renaming the new table to "Mx Regular User".users_table_part_4 378NOTICE: creating a new table for "Mx Regular User".users_table_part 379NOTICE: dropping the old "Mx Regular User".users_table_part 380NOTICE: renaming the new table to "Mx Regular User".users_table_part 381 alter_distributed_table 382--------------------------------------------------------------------- 383 384(1 row) 385 386ROLLBACK; 387BEGIN; 388 -- make sure to use multiple connections 389 SET LOCAL citus.force_max_query_parallelization TO ON; 390 ALTER TABLE users_table_part ADD COLUMN my_column INT DEFAULT 15; 391 CREATE INDEX test_index ON users_table_part(value_3, value_2); 392 CREATE INDEX test_index_on_child ON users_table_part_3(value_3, value_2); 393ROLLBACK; 394CREATE TABLE local_table_in_the_metadata (id int PRIMARY KEY, value_1 int); 395CREATE TABLE reference_table(id int PRIMARY KEY, value_1 int); 396SELECT create_reference_table('reference_table'); 397 create_reference_table 398--------------------------------------------------------------------- 399 400(1 row) 401 402CREATE TABLE on_delete_fkey_table(id int PRIMARY KEY, value_1 int); 403SELECT create_distributed_table('on_delete_fkey_table', 'id', colocate_with:='partitioned_table'); 404 create_distributed_table 405--------------------------------------------------------------------- 406 407(1 row) 408 409ALTER TABLE reference_table ADD CONSTRAINT fkey_to_local FOREIGN KEY(id) REFERENCES local_table_in_the_metadata(id); 410ALTER TABLE on_delete_fkey_table ADD CONSTRAINT veerrrrrrryyy_veerrrrrrryyy_veerrrrrrryyy_long_constraint_name FOREIGN KEY(value_1) REFERENCES reference_table(id) ON DELETE CASCADE; 411INSERT INTO local_table_in_the_metadata SELECT i, i FROM generate_series(0, 100) i; 412INSERT INTO reference_table SELECT i, i FROM generate_series(0, 100) i; 413INSERT INTO on_delete_fkey_table SELECT i, i % 100 FROM generate_series(0, 1000) i; 414-- make sure that we can handle switching to sequential execution nicely 415-- on MX with a regular user 416BEGIN; 417 DELETE FROM reference_table WHERE id > 50; 418 SHOW citus.multi_shard_modify_mode; 419 citus.multi_shard_modify_mode 420--------------------------------------------------------------------- 421 sequential 422(1 row) 423 424 ALTER TABLE on_delete_fkey_table ADD COLUMN t int DEFAULT 10; 425 SELECT avg(t) FROM on_delete_fkey_table; 426 avg 427--------------------------------------------------------------------- 428 10.0000000000000000 429(1 row) 430 431ROLLBACK; 432-- make sure to use multiple connections per node 433SET citus.force_max_query_parallelization TO ON; 434CREATE INDEX CONCURRENTLY concurrent_index_test ON on_delete_fkey_table(id); 435CREATE UNIQUE INDEX unique_key_example ON on_delete_fkey_table(id, value_1); 436BEGIN; 437 TRUNCATE local_table_in_the_metadata, reference_table, on_delete_fkey_table; 438NOTICE: truncate cascades to table "reference_table_xxxxx" 439 SELECT count(*) FROM local_table_in_the_metadata; 440 count 441--------------------------------------------------------------------- 442 0 443(1 row) 444 445 SELECT count(*) FROM reference_table; 446 count 447--------------------------------------------------------------------- 448 0 449(1 row) 450 451 SELECT count(*) FROM on_delete_fkey_table; 452 count 453--------------------------------------------------------------------- 454 0 455(1 row) 456 457ROLLBACK; 458BEGIN; 459 SET citus.multi_shard_modify_mode TO 'sequential'; 460 TRUNCATE on_delete_fkey_table CASCADE; 461 TRUNCATE reference_table CASCADE; 462NOTICE: truncate cascades to table "on_delete_fkey_table" 463 SELECT count(*) FROM local_table_in_the_metadata JOIN reference_table USING(id) JOIN on_delete_fkey_table USING(id); 464 count 465--------------------------------------------------------------------- 466 0 467(1 row) 468 469ROLLBACK; 470-- join involving local, reference and distributed tables 471SELECT count(*) FROM local_table_in_the_metadata JOIN reference_table USING(id) JOIN on_delete_fkey_table USING(id); 472 count 473--------------------------------------------------------------------- 474 101 475(1 row) 476 477-- query with intermediate results 478WITH cte_1 AS (SELECT * FROM on_delete_fkey_table ORDER BY 1,2 DESC LIMIT 10) 479 SELECT count(*) FROM cte_1; 480 count 481--------------------------------------------------------------------- 482 10 483(1 row) 484 485-- query with intermediate results on remote nodes 486WITH cte_1 AS (SELECT * FROM on_delete_fkey_table ORDER BY 1,2 DESC LIMIT 10) 487 SELECT count(*) FROM cte_1 JOIN on_delete_fkey_table USING(value_1); 488 count 489--------------------------------------------------------------------- 490 101 491(1 row) 492 493-- repartition joins 494SET citus.enable_repartition_joins to ON; 495SELECT count(*) FROM on_delete_fkey_table o1 JOIN on_delete_fkey_table o2 USING(value_1); 496 count 497--------------------------------------------------------------------- 498 10021 499(1 row) 500 501-- repartition INSERT .. SELECT 502INSERT INTO on_delete_fkey_table (id, value_1) SELECT value_1, id FROM on_delete_fkey_table ON CONFLICT DO NOTHING; 503-- make sure that we can create a type and use it in the same tx 504BEGIN; 505 CREATE TYPE test_type AS (a int, b int); 506 CREATE TABLE composite_key (id int PRIMARY KEY, c int, data test_type); 507 SELECT create_distributed_table('composite_key', 'id', colocate_with:='partitioned_table'); 508 create_distributed_table 509--------------------------------------------------------------------- 510 511(1 row) 512 513COMMIT; 514-- index statistics should work fine 515CREATE INDEX test_index_on_parent ON users_table_part((value_3+value_2)); 516ALTER INDEX test_index_on_parent ALTER COLUMN 1 SET STATISTICS 4646; 517DROP INDEX test_index_on_parent; 518ALTER TABLE composite_key ALTER COLUMN c TYPE float USING (b::float + 0.5); 519ERROR: column "b" does not exist 520-- make sure that rebalancer works fine with a regular user on MX 521-- first make sure that we can rollback 522BEGIN; 523 SELECT citus_move_shard_placement(1560000, 'localhost', :worker_1_port, 'localhost', :worker_2_port, 'block_writes'); 524 citus_move_shard_placement 525--------------------------------------------------------------------- 526 527(1 row) 528 529ROLLBACK; 530SELECT citus_move_shard_placement(1560000, 'localhost', :worker_1_port, 'localhost', :worker_2_port, 'block_writes'); 531 citus_move_shard_placement 532--------------------------------------------------------------------- 533 534(1 row) 535 536-- connect to the worker to see if the table has the correct owned and placement metadata 537\c - postgres - :worker_2_port 538SELECT 539 1560000, groupid = (SELECT groupid FROM pg_dist_node WHERE nodeport = :worker_2_port AND nodename = 'localhost' AND isactive) 540FROM 541 pg_dist_placement 542WHERE 543 shardid = 1560000; 544 ?column? | ?column? 545--------------------------------------------------------------------- 546 1560000 | t 547(1 row) 548 549-- also make sure that pg_dist_shard_placement is updated correctly 550SELECT 551 nodeport = :worker_2_port 552FROM pg_dist_shard_placement WHERE shardid = 1560000; 553 ?column? 554--------------------------------------------------------------------- 555 t 556(1 row) 557 558\c - postgres - :worker_1_port 559SELECT 560 1560000, groupid = (SELECT groupid FROM pg_dist_node WHERE nodeport = :worker_2_port AND nodename = 'localhost' AND isactive) 561FROM 562 pg_dist_placement 563WHERE 564 shardid = 1560000; 565 ?column? | ?column? 566--------------------------------------------------------------------- 567 1560000 | t 568(1 row) 569 570-- also make sure that pg_dist_shard_placement is updated correctly 571SELECT 572 nodeport = :worker_2_port 573FROM pg_dist_shard_placement WHERE shardid = 1560000; 574 ?column? 575--------------------------------------------------------------------- 576 t 577(1 row) 578 579-- now connect with the user to the coordinator again 580\c - regular_mx_user - :master_port 581SET search_path TO "Mx Regular User"; 582-- make sure that we can still execute queries 583SELECT count(*) FROM local_table_in_the_metadata JOIN reference_table USING(id) JOIN on_delete_fkey_table USING(id); 584 count 585--------------------------------------------------------------------- 586 101 587(1 row) 588 589-- now, call directly the rebalancer, which should also work fine 590SELECT rebalance_table_shards(shard_transfer_mode:='block_writes'); 591NOTICE: Moving shard xxxxx from localhost:xxxxx to localhost:xxxxx ... 592 rebalance_table_shards 593--------------------------------------------------------------------- 594 595(1 row) 596 597-- make sure that we can still execute queries 598SELECT count(*) FROM local_table_in_the_metadata JOIN reference_table USING(id) JOIN on_delete_fkey_table USING(id); 599 count 600--------------------------------------------------------------------- 601 101 602(1 row) 603 604-- lets run some queries from the workers 605\c - regular_mx_user - :worker_2_port 606SET search_path TO "Mx Regular User"; 607SELECT count(*) FROM local_table_in_the_metadata JOIN reference_table USING(id) JOIN on_delete_fkey_table USING(id); 608 count 609--------------------------------------------------------------------- 610 101 611(1 row) 612 613BEGIN; 614 TRUNCATE reference_table CASCADE; 615NOTICE: truncate cascades to table "on_delete_fkey_table" 616NOTICE: truncate cascades to table "on_delete_fkey_table_xxxxx" 617NOTICE: truncate cascades to table "on_delete_fkey_table_xxxxx" 618NOTICE: truncate cascades to table "on_delete_fkey_table_xxxxx" 619ROLLBACK; 620-- join involving local, reference and distributed tables 621SELECT count(*) FROM local_table_in_the_metadata JOIN reference_table USING(id) JOIN on_delete_fkey_table USING(id); 622 count 623--------------------------------------------------------------------- 624 101 625(1 row) 626 627-- query with intermediate results 628WITH cte_1 AS (SELECT * FROM on_delete_fkey_table ORDER BY 1,2 DESC LIMIT 10) 629 SELECT count(*) FROM cte_1; 630 count 631--------------------------------------------------------------------- 632 10 633(1 row) 634 635-- query with intermediate results on remote nodes 636WITH cte_1 AS (SELECT * FROM on_delete_fkey_table ORDER BY 1,2 DESC LIMIT 10) 637 SELECT count(*) FROM cte_1 JOIN on_delete_fkey_table USING(value_1); 638 count 639--------------------------------------------------------------------- 640 101 641(1 row) 642 643-- repartition joins 644SET citus.enable_repartition_joins to ON; 645SELECT count(*) FROM on_delete_fkey_table o1 JOIN on_delete_fkey_table o2 USING(value_1); 646 count 647--------------------------------------------------------------------- 648 10021 649(1 row) 650 651BEGIN; 652 SET LOCAL citus.force_max_query_parallelization TO ON; 653 DELETE FROM on_delete_fkey_table; 654 WITH cte_1 AS (SELECT * FROM on_delete_fkey_table ORDER BY 1,2 DESC LIMIT 10) 655 SELECT count(*) FROM cte_1; 656 count 657--------------------------------------------------------------------- 658 0 659(1 row) 660 661COMMIT; 662\c - postgres - :master_port 663-- resync the metadata to both nodes for test purposes and then stop 664SELECT start_metadata_sync_to_node('localhost', :worker_1_port); 665 start_metadata_sync_to_node 666--------------------------------------------------------------------- 667 668(1 row) 669 670SELECT start_metadata_sync_to_node('localhost', :worker_2_port); 671 start_metadata_sync_to_node 672--------------------------------------------------------------------- 673 674(1 row) 675 676SELECT stop_metadata_sync_to_node('localhost', :worker_1_port); 677NOTICE: dropping metadata on the node (localhost,57637) 678 stop_metadata_sync_to_node 679--------------------------------------------------------------------- 680 681(1 row) 682 683SELECT stop_metadata_sync_to_node('localhost', :worker_2_port); 684NOTICE: dropping metadata on the node (localhost,57638) 685 stop_metadata_sync_to_node 686--------------------------------------------------------------------- 687 688(1 row) 689 690DROP SCHEMA "Mx Regular User" CASCADE; 691NOTICE: drop cascades to 10 other objects 692DETAIL: drop cascades to table "Mx Regular User".partitioned_table 693drop cascades to sequence "Mx Regular User".my_mx_seq 694drop cascades to table "Mx Regular User".users_table_part 695drop cascades to table "Mx Regular User".local_table_in_the_metadata_1560131 696drop cascades to table "Mx Regular User".reference_table 697drop cascades to table "Mx Regular User".reference_table_1560126 698drop cascades to table "Mx Regular User".on_delete_fkey_table 699drop cascades to table "Mx Regular User".local_table_in_the_metadata 700drop cascades to type "Mx Regular User".test_type 701drop cascades to table "Mx Regular User".composite_key 702