1-- 2-- Tests multiple commands in transactions where 3-- there is foreign key relation between reference 4-- tables and distributed tables 5-- 6 7CREATE SCHEMA test_fkey_to_ref_in_tx; 8SET search_path TO 'test_fkey_to_ref_in_tx'; 9 10SET citus.next_shard_id TO 2380000; 11SET citus.next_placement_id TO 2380000; 12 13SET citus.shard_replication_factor TO 1; 14 15CREATE TABLE transitive_reference_table(id int PRIMARY KEY); 16SELECT create_reference_table('transitive_reference_table'); 17 18CREATE TABLE reference_table(id int PRIMARY KEY, value_1 int); 19SELECT create_reference_table('reference_table'); 20 21CREATE TABLE on_update_fkey_table(id int PRIMARY KEY, value_1 int); 22SELECT create_distributed_table('on_update_fkey_table', 'id'); 23 24CREATE TABLE unrelated_dist_table(id int PRIMARY KEY, value_1 int); 25SELECT create_distributed_table('unrelated_dist_table', 'id'); 26 27ALTER TABLE on_update_fkey_table ADD CONSTRAINT fkey FOREIGN KEY(value_1) REFERENCES reference_table(id) ON UPDATE CASCADE; 28ALTER TABLE reference_table ADD CONSTRAINT fkey FOREIGN KEY(value_1) REFERENCES transitive_reference_table(id) ON UPDATE CASCADE; 29 30INSERT INTO transitive_reference_table SELECT i FROM generate_series(0, 100) i; 31INSERT INTO reference_table SELECT i, i FROM generate_series(0, 100) i; 32 33INSERT INTO on_update_fkey_table SELECT i, i % 100 FROM generate_series(0, 1000) i; 34INSERT INTO unrelated_dist_table SELECT i, i % 100 FROM generate_series(0, 1000) i; 35 36-- in order to see when the mode automatically swithces to sequential execution 37SET client_min_messages TO DEBUG1; 38 39-- case 1.1: SELECT to a reference table is followed by a parallel SELECT to a distributed table 40BEGIN; 41 SELECT count(*) FROM reference_table; 42 SELECT count(*) FROM on_update_fkey_table; 43ROLLBACK; 44 45BEGIN; 46 SELECT count(*) FROM transitive_reference_table; 47 SELECT count(*) FROM on_update_fkey_table; 48ROLLBACK; 49 50-- case 1.2: SELECT to a reference table is followed by a multiple router SELECTs to a distributed table 51BEGIN; 52 SELECT count(*) FROM reference_table; 53 SELECT count(*) FROM on_update_fkey_table WHERE id = 15; 54 SELECT count(*) FROM on_update_fkey_table WHERE id = 16; 55 SELECT count(*) FROM on_update_fkey_table WHERE id = 17; 56 SELECT count(*) FROM on_update_fkey_table WHERE id = 18; 57ROLLBACK; 58 59BEGIN; 60 SELECT count(*) FROM transitive_reference_table; 61 SELECT count(*) FROM on_update_fkey_table WHERE id = 15; 62 SELECT count(*) FROM on_update_fkey_table WHERE id = 16; 63 SELECT count(*) FROM on_update_fkey_table WHERE id = 17; 64 SELECT count(*) FROM on_update_fkey_table WHERE id = 18; 65ROLLBACK; 66 67-- case 1.3: SELECT to a reference table is followed by a multi-shard UPDATE to a distributed table 68BEGIN; 69 SELECT count(*) FROM reference_table; 70 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 71ROLLBACK; 72 73BEGIN; 74 SELECT count(*) FROM transitive_reference_table; 75 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 76ROLLBACK; 77 78-- case 1.4: SELECT to a reference table is followed by a multiple sing-shard UPDATE to a distributed table 79BEGIN; 80 SELECT count(*) FROM reference_table; 81 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 15; 82 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 16; 83 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 17; 84 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 18; 85ROLLBACK; 86 87BEGIN; 88 SELECT count(*) FROM transitive_reference_table; 89 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 15; 90 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 16; 91 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 17; 92 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 18; 93ROLLBACK; 94 95-- case 1.5: SELECT to a reference table is followed by a DDL that touches fkey column 96BEGIN; 97 SELECT count(*) FROM reference_table; 98 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint; 99ROLLBACK; 100 101BEGIN; 102 SELECT count(*) FROM transitive_reference_table; 103 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint; 104ROLLBACK; 105 106-- case 1.6: SELECT to a reference table is followed by an unrelated DDL 107BEGIN; 108 SELECT count(*) FROM reference_table; 109 ALTER TABLE on_update_fkey_table ADD COLUMN X INT; 110ROLLBACK; 111 112BEGIN; 113 SELECT count(*) FROM transitive_reference_table; 114 ALTER TABLE on_update_fkey_table ADD COLUMN X INT; 115ROLLBACK; 116 117-- case 1.7.1: SELECT to a reference table is followed by a DDL that is on 118-- the foreign key column 119BEGIN; 120 SELECT count(*) FROM reference_table; 121 122 -- make sure that the output isn't too verbose 123 SET LOCAL client_min_messages TO ERROR; 124 ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE; 125ROLLBACK; 126 127BEGIN; 128 SELECT count(*) FROM transitive_reference_table; 129 130 -- make sure that the output isn't too verbose 131 SET LOCAL client_min_messages TO ERROR; 132 ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE; 133ROLLBACK; 134 135-- case 1.7.2: SELECT to a reference table is followed by a DDL that is on 136-- the foreign key column after a parallel query has been executed 137BEGIN; 138 SELECT count(*) FROM unrelated_dist_table; 139 SELECT count(*) FROM reference_table; 140 141 ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE; 142ROLLBACK; 143 144BEGIN; 145 SELECT count(*) FROM unrelated_dist_table; 146 SELECT count(*) FROM transitive_reference_table; 147 148 ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE; 149ROLLBACK; 150 151-- case 1.7.3: SELECT to a reference table is followed by a DDL that is not on 152-- the foreign key column, and a parallel query has already been executed 153BEGIN; 154 SELECT count(*) FROM unrelated_dist_table; 155 SELECT count(*) FROM reference_table; 156 ALTER TABLE on_update_fkey_table ADD COLUMN X INT; 157ROLLBACK; 158 159BEGIN; 160 SELECT count(*) FROM unrelated_dist_table; 161 SELECT count(*) FROM transitive_reference_table; 162 ALTER TABLE on_update_fkey_table ADD COLUMN X INT; 163ROLLBACK; 164 165-- case 1.8: SELECT to a reference table is followed by a COPY 166BEGIN; 167 SELECT count(*) FROM reference_table; 168 COPY on_update_fkey_table FROM STDIN WITH CSV; 1691001,99 1701002,99 1711003,99 1721004,99 1731005,99 174\. 175ROLLBACK; 176 177BEGIN; 178 SELECT count(*) FROM transitive_reference_table; 179 COPY on_update_fkey_table FROM STDIN WITH CSV; 1801001,99 1811002,99 1821003,99 1831004,99 1841005,99 185\. 186ROLLBACK; 187 188-- case 2.1: UPDATE to a reference table is followed by a multi-shard SELECT 189BEGIN; 190 UPDATE reference_table SET id = 101 WHERE id = 99; 191 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 192 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101; 193ROLLBACK; 194 195BEGIN; 196 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 197 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 198 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101; 199ROLLBACK; 200 201-- case 2.2: UPDATE to a reference table is followed by multiple router SELECT 202BEGIN; 203 UPDATE reference_table SET id = 101 WHERE id = 99; 204 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 99; 205 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 199; 206 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 299; 207 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 399; 208ROLLBACK; 209 210BEGIN; 211 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 212 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 99; 213 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 199; 214 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 299; 215 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 399; 216ROLLBACK; 217 218-- case 2.3: UPDATE to a reference table is followed by a multi-shard UPDATE 219BEGIN; 220 UPDATE reference_table SET id = 101 WHERE id = 99; 221 UPDATE on_update_fkey_table SET value_1 = 15; 222ROLLBACK; 223 224BEGIN; 225 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 226 UPDATE on_update_fkey_table SET value_1 = 15; 227ROLLBACK; 228 229-- case 2.4: UPDATE to a reference table is followed by multiple router UPDATEs 230BEGIN; 231 UPDATE reference_table SET id = 101 WHERE id = 99; 232 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 1; 233 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 2; 234 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 3; 235 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 4; 236ROLLBACK; 237 238BEGIN; 239 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 240 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 1; 241 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 2; 242 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 3; 243 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 4; 244ROLLBACK; 245 246-- case 2.5: UPDATE to a reference table is followed by a DDL that touches fkey column 247BEGIN; 248 UPDATE reference_table SET id = 101 WHERE id = 99; 249 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint; 250ROLLBACK; 251 252BEGIN; 253 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 254 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint; 255ROLLBACK; 256 257-- case 2.6: UPDATE to a reference table is followed by an unrelated DDL 258BEGIN; 259 UPDATE reference_table SET id = 101 WHERE id = 99; 260 ALTER TABLE on_update_fkey_table ADD COLUMN value_1_X INT; 261ROLLBACK; 262 263BEGIN; 264 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 265 ALTER TABLE on_update_fkey_table ADD COLUMN value_1_X INT; 266ROLLBACK; 267 268-- case 2.7: UPDATE to a reference table is followed by COPY 269BEGIN; 270 UPDATE reference_table SET id = 101 WHERE id = 99; 271 COPY on_update_fkey_table FROM STDIN WITH CSV; 2721001,101 2731002,101 2741003,101 2751004,101 2761005,101 277\. 278ROLLBACK; 279 280BEGIN; 281 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 282 COPY on_update_fkey_table FROM STDIN WITH CSV; 2831001,101 2841002,101 2851003,101 2861004,101 2871005,101 288\. 289ROLLBACK; 290 291-- case 2.8: UPDATE to a reference table is followed by TRUNCATE 292BEGIN; 293 UPDATE reference_table SET id = 101 WHERE id = 99; 294 TRUNCATE on_update_fkey_table; 295ROLLBACK; 296 297BEGIN; 298 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 299 TRUNCATE on_update_fkey_table; 300ROLLBACK; 301 302-- case 3.1: an unrelated DDL to a reference table is followed by a real-time SELECT 303BEGIN; 304 ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001; 305 SELECT count(*) FROM on_update_fkey_table; 306ROLLBACK; 307 308BEGIN; 309 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001; 310 SELECT count(*) FROM on_update_fkey_table; 311ROLLBACK; 312 313-- case 3.2: DDL that touches fkey column to a reference table is followed by a real-time SELECT 314BEGIN; 315 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE int; 316 SELECT count(*) FROM on_update_fkey_table; 317ROLLBACK; 318 319BEGIN; 320 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE int; 321 SELECT count(*) FROM on_update_fkey_table; 322ROLLBACK; 323 324-- case 3.3: DDL to a reference table followed by a multi shard UPDATE 325BEGIN; 326 ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001; 327 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 328ROLLBACK; 329 330BEGIN; 331 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001; 332 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 333ROLLBACK; 334 335-- case 3.4: DDL to a reference table followed by multiple router UPDATEs 336BEGIN; 337 ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001; 338 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 1; 339 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 2; 340 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 3; 341 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 4; 342ROLLBACK; 343 344BEGIN; 345 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001; 346 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 1; 347 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 2; 348 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 3; 349 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 4; 350ROLLBACK; 351 352-- case 3.5: DDL to reference table followed by a DDL to dist table 353BEGIN; 354 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 355 CREATE INDEX fkey_test_index_1 ON on_update_fkey_table(value_1); 356ROLLBACK; 357 358BEGIN; 359 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 360 CREATE INDEX fkey_test_index_1 ON on_update_fkey_table(value_1); 361ROLLBACK; 362 363-- case 4.6: DDL to reference table followed by a DDL to dist table, both touching fkey columns 364BEGIN; 365 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 366 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 367ROLLBACK; 368 369BEGIN; 370 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 371 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 372ROLLBACK; 373 374-- case 3.7: DDL to a reference table is followed by COPY 375BEGIN; 376 ALTER TABLE reference_table ADD COLUMN X int; 377 COPY on_update_fkey_table FROM STDIN WITH CSV; 3781001,99 3791002,99 3801003,99 3811004,99 3821005,99 383\. 384ROLLBACK; 385 386BEGIN; 387 ALTER TABLE transitive_reference_table ADD COLUMN X int; 388 COPY on_update_fkey_table FROM STDIN WITH CSV; 3891001,99 3901002,99 3911003,99 3921004,99 3931005,99 394\. 395ROLLBACK; 396 397-- case 3.8: DDL to a reference table is followed by TRUNCATE 398BEGIN; 399 ALTER TABLE reference_table ADD COLUMN X int; 400 TRUNCATE on_update_fkey_table; 401ROLLBACK; 402 403BEGIN; 404 ALTER TABLE transitive_reference_table ADD COLUMN X int; 405 TRUNCATE on_update_fkey_table; 406ROLLBACK; 407 408-- case 3.9: DDL to a reference table is followed by TRUNCATE 409BEGIN; 410 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 411 TRUNCATE on_update_fkey_table; 412ROLLBACK; 413 414BEGIN; 415 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 416 TRUNCATE on_update_fkey_table; 417ROLLBACK; 418 419----- 420--- Now, start testing the other way araound 421----- 422 423-- case 4.1: SELECT to a dist table is follwed by a SELECT to a reference table 424BEGIN; 425 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 426 SELECT count(*) FROM reference_table; 427ROLLBACK; 428 429BEGIN; 430 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 431 SELECT count(*) FROM transitive_reference_table; 432ROLLBACK; 433 434-- case 4.2: SELECT to a dist table is follwed by a DML to a reference table 435BEGIN; 436 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 437 UPDATE reference_table SET id = 101 WHERE id = 99; 438ROLLBACK; 439 440BEGIN; 441 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 442 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 443ROLLBACK; 444 445-- case 4.3: SELECT to a dist table is follwed by an unrelated DDL to a reference table 446BEGIN; 447 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 448 ALTER TABLE reference_table ADD COLUMN X INT; 449ROLLBACK; 450 451BEGIN; 452 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 453 ALTER TABLE transitive_reference_table ADD COLUMN X INT; 454ROLLBACK; 455 456-- case 4.4: SELECT to a dist table is follwed by a DDL to a reference table 457BEGIN; 458 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 459 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 460ROLLBACK; 461 462BEGIN; 463 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 464 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 465ROLLBACK; 466 467-- case 4.5: SELECT to a dist table is follwed by a TRUNCATE 468\set VERBOSITY terse 469SET client_min_messages to LOG; 470 471BEGIN; 472 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 473 TRUNCATE reference_table CASCADE; 474ROLLBACK; 475 476BEGIN; 477 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 478 TRUNCATE transitive_reference_table CASCADE; 479ROLLBACK; 480 481-- case 4.6: Router SELECT to a dist table is followed by a TRUNCATE 482BEGIN; 483 SELECT count(*) FROM on_update_fkey_table WHERE id = 9; 484 TRUNCATE reference_table CASCADE; 485ROLLBACK; 486 487BEGIN; 488 SELECT count(*) FROM on_update_fkey_table WHERE id = 9; 489 TRUNCATE transitive_reference_table CASCADE; 490ROLLBACK; 491 492-- case 4.7: SELECT to a dist table is followed by a DROP 493-- DROP following SELECT is important as we error out after 494-- the standart process utility hook drops the table. 495-- That could cause SIGSEGV before the patch. 496-- Below block should "successfully" error out 497BEGIN; 498 SELECT count(*) FROM on_update_fkey_table; 499 DROP TABLE reference_table CASCADE; 500ROLLBACK; 501 502-- case 4.8: Router SELECT to a dist table is followed by a TRUNCATE 503-- No errors expected from below block as SELECT there is a router 504-- query 505BEGIN; 506 SELECT count(*) FROM on_update_fkey_table WHERE id = 9; 507 DROP TABLE reference_table CASCADE; 508ROLLBACK; 509 510RESET client_min_messages; 511\set VERBOSITY default 512 513-- case 5.1: Parallel UPDATE on distributed table follow by a SELECT 514BEGIN; 515 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 516 SELECT count(*) FROM reference_table; 517ROLLBACK; 518 519BEGIN; 520 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 521 SELECT count(*) FROM transitive_reference_table; 522ROLLBACK; 523 524-- case 5.2: Parallel UPDATE on distributed table follow by a UPDATE 525BEGIN; 526 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 527 UPDATE reference_table SET id = 160 WHERE id = 15; 528ROLLBACK; 529 530BEGIN; 531 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 532 UPDATE transitive_reference_table SET id = 160 WHERE id = 15; 533ROLLBACK; 534 535BEGIN; 536 WITH cte AS (UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15 RETURNING *) 537 SELECT * FROM cte ORDER BY 1, 2; 538 UPDATE reference_table SET id = 160 WHERE id = 15; 539ROLLBACK; 540 541BEGIN; 542 WITH cte AS (UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15 RETURNING *) 543 SELECT * FROM cte ORDER BY 1, 2; 544 UPDATE transitive_reference_table SET id = 160 WHERE id = 15; 545ROLLBACK; 546 547-- case 5.3: Parallel UPDATE on distributed table follow by an unrelated DDL on reference table 548BEGIN; 549 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 550 ALTER TABLE reference_table ADD COLUMN X INT; 551ROLLBACK; 552 553BEGIN; 554 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 555 ALTER TABLE transitive_reference_table ADD COLUMN X INT; 556ROLLBACK; 557 558-- case 5.4: Parallel UPDATE on distributed table follow by a related DDL on reference table 559BEGIN; 560 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 561 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 562ROLLBACK; 563 564BEGIN; 565 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 566 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 567ROLLBACK; 568 569-- case 6:1: Unrelated parallel DDL on distributed table followed by SELECT on ref. table 570BEGIN; 571 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 572 SELECT count(*) FROM reference_table; 573ROLLBACK; 574 575BEGIN; 576 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 577 SELECT count(*) FROM transitive_reference_table; 578ROLLBACK; 579 580-- case 6:2: Related parallel DDL on distributed table followed by SELECT on ref. table 581BEGIN; 582 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 583 UPDATE reference_table SET id = 160 WHERE id = 15; 584ROLLBACK; 585 586BEGIN; 587 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 588 UPDATE transitive_reference_table SET id = 160 WHERE id = 15; 589ROLLBACK; 590 591-- case 6:3: Unrelated parallel DDL on distributed table followed by UPDATE on ref. table 592BEGIN; 593 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 594 SELECT count(*) FROM reference_table; 595ROLLBACK; 596 597BEGIN; 598 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 599 SELECT count(*) FROM transitive_reference_table; 600ROLLBACK; 601 602-- case 6:4: Related parallel DDL on distributed table followed by SELECT on ref. table 603BEGIN; 604 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 605 UPDATE reference_table SET id = 160 WHERE id = 15; 606ROLLBACK; 607 608BEGIN; 609 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 610 UPDATE transitive_reference_table SET id = 160 WHERE id = 15; 611ROLLBACK; 612 613-- case 6:5: Unrelated parallel DDL on distributed table followed by unrelated DDL on ref. table 614BEGIN; 615 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 616 ALTER TABLE reference_table ADD COLUMN X int; 617ROLLBACK; 618 619BEGIN; 620 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 621 ALTER TABLE transitive_reference_table ADD COLUMN X int; 622ROLLBACK; 623 624-- case 6:6: Unrelated parallel DDL on distributed table followed by related DDL on ref. table 625BEGIN; 626 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 627 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 628ROLLBACK; 629 630-- some more extensive tests 631 632-- UPDATE on dist table is followed by DELETE to reference table 633BEGIN; 634 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 635 DELETE FROM reference_table WHERE id = 99; 636ROLLBACK; 637 638BEGIN; 639 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 640 DELETE FROM transitive_reference_table WHERE id = 99; 641ROLLBACK; 642 643-- an unrelated update followed by update on dist table and update 644-- on reference table 645BEGIN; 646 UPDATE unrelated_dist_table SET value_1 = 15; 647 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 648 UPDATE reference_table SET id = 101 WHERE id = 99; 649ROLLBACK; 650 651BEGIN; 652 UPDATE unrelated_dist_table SET value_1 = 15; 653 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 654 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 655ROLLBACK; 656 657-- an unrelated update followed by update on the reference table and update 658-- on the cascading distributed table 659-- note that the UPDATE on the reference table will try to set the execution 660-- mode to sequential, which will fail since there is an already opened 661-- parallel connections 662BEGIN; 663 UPDATE unrelated_dist_table SET value_1 = 15; 664 UPDATE reference_table SET id = 101 WHERE id = 99; 665 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 666ROLLBACK; 667 668BEGIN; 669 CREATE TABLE test_table_1(id int PRIMARY KEY); 670 SELECT create_reference_table('test_table_1'); 671 672 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 673 SELECT create_distributed_table('test_table_2', 'id'); 674 675 -- make sure that the output isn't too verbose 676 SET LOCAL client_min_messages TO ERROR; 677 DROP TABLE test_table_1 CASCADE; 678ROLLBACK; 679 680-- the fails since we're trying to switch sequential mode after 681-- already executed a parallel query 682BEGIN; 683 CREATE TABLE test_table_1(id int PRIMARY KEY); 684 SELECT create_reference_table('test_table_1'); 685 686 CREATE TABLE tt4(id int PRIMARY KEY, value_1 int, FOREIGN KEY(id) REFERENCES tt4(id)); 687 SELECT create_distributed_table('tt4', 'id'); 688 689 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id), FOREIGN KEY(id) REFERENCES tt4(id)); 690 SELECT create_distributed_table('test_table_2', 'id'); 691 692 -- make sure that the output isn't too verbose 693 SET LOCAL client_min_messages TO ERROR; 694 DROP TABLE test_table_1 CASCADE; 695ROLLBACK; 696 697-- same test with the above, but this time using 698-- sequential mode, succeeds 699BEGIN; 700 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 701 CREATE TABLE test_table_1(id int PRIMARY KEY); 702 SELECT create_reference_table('test_table_1'); 703 704 CREATE TABLE tt4(id int PRIMARY KEY, value_1 int, FOREIGN KEY(id) REFERENCES tt4(id)); 705 SELECT create_distributed_table('tt4', 'id'); 706 707 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id), FOREIGN KEY(id) REFERENCES tt4(id)); 708 SELECT create_distributed_table('test_table_2', 'id'); 709 710 -- make sure that the output isn't too verbose 711 SET LOCAL client_min_messages TO ERROR; 712 DROP TABLE test_table_1 CASCADE; 713ROLLBACK; 714 715-- another test with ALTER TABLE fails since we're already opened 716-- parallel connection via create_distributed_table(), later 717-- adding foreign key to reference table fails 718BEGIN; 719 720 CREATE TABLE test_table_1(id int PRIMARY KEY); 721 SELECT create_reference_table('test_table_1'); 722 723 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 724 SELECT create_distributed_table('test_table_2', 'id'); 725 726 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 727 728 -- make sure that the output isn't too verbose 729 SET LOCAL client_min_messages TO ERROR; 730 DROP TABLE test_table_1, test_table_2; 731COMMIT; 732 733-- same test with the above on sequential mode should work fine 734BEGIN; 735 736 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 737 738 CREATE TABLE test_table_1(id int PRIMARY KEY); 739 SELECT create_reference_table('test_table_1'); 740 741 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 742 SELECT create_distributed_table('test_table_2', 'id'); 743 744 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 745 746 -- make sure that the output isn't too verbose 747 SET LOCAL client_min_messages TO ERROR; 748 DROP TABLE test_table_1, test_table_2; 749COMMIT; 750 751 752-- similar test with the above, but this time the order of 753-- create_distributed_table and create_reference_table is 754-- changed 755BEGIN; 756 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 757 SELECT create_distributed_table('test_table_2', 'id'); 758 759 CREATE TABLE test_table_1(id int PRIMARY KEY); 760 SELECT create_reference_table('test_table_1'); 761 762 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 763 764 -- make sure that the output isn't too verbose 765 SET LOCAL client_min_messages TO ERROR; 766 DROP TABLE test_table_1 CASCADE; 767ROLLBACK; 768 769-- same test in sequential mode should succeed 770BEGIN; 771 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 772 773 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 774 SELECT create_distributed_table('test_table_2', 'id'); 775 776 CREATE TABLE test_table_1(id int PRIMARY KEY); 777 SELECT create_reference_table('test_table_1'); 778 779 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 780 781 -- make sure that the output isn't too verbose 782 SET LOCAL client_min_messages TO ERROR; 783 DROP TABLE test_table_1 CASCADE; 784ROLLBACK; 785 786-- again a very similar test, but this time 787-- a parallel SELECT is already executed before 788-- setting the mode to sequential should fail 789BEGIN; 790 SELECT count(*) FROM on_update_fkey_table; 791 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 792 793 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 794 SELECT create_distributed_table('test_table_2', 'id'); 795 796 CREATE TABLE test_table_1(id int PRIMARY KEY); 797 SELECT create_reference_table('test_table_1'); 798 799 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 800 801 -- make sure that the output isn't too verbose 802 SET LOCAL client_min_messages TO ERROR; 803 DROP TABLE test_table_1 CASCADE; 804ROLLBACK; 805 806-- make sure that we cannot create hash distributed tables with 807-- foreign keys to reference tables when they have data in it 808BEGIN; 809 810 CREATE TABLE test_table_1(id int PRIMARY KEY); 811 INSERT INTO test_table_1 SELECT i FROM generate_series(0,100) i; 812 813 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 814 INSERT INTO test_table_2 SELECT i, i FROM generate_series(0,100) i; 815 816 SELECT create_reference_table('test_table_1'); 817 SELECT create_distributed_table('test_table_2', 'id'); 818 819 -- make sure that the output isn't too verbose 820 SET LOCAL client_min_messages TO ERROR; 821 DROP TABLE test_table_2, test_table_1; 822COMMIT; 823 824 825-- the same test with above in sequential mode would still not work 826-- since COPY cannot be executed in sequential mode 827BEGIN; 828 829 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 830 831 CREATE TABLE test_table_1(id int PRIMARY KEY); 832 INSERT INTO test_table_1 SELECT i FROM generate_series(0,100) i; 833 834 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 835 INSERT INTO test_table_2 SELECT i, i FROM generate_series(0,100) i; 836 837 SELECT create_reference_table('test_table_1'); 838 SELECT create_distributed_table('test_table_2', 'id'); 839 840 -- make sure that the output isn't too verbose 841 SET LOCAL client_min_messages TO ERROR; 842 DROP TABLE test_table_2, test_table_1; 843COMMIT; 844 845-- we should be able to execute and DML/DDL/SELECT after we've 846-- switched to sequential via create_distributed_table 847BEGIN; 848 849 CREATE TABLE test_table_1(id int PRIMARY KEY); 850 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 851 852 SELECT create_reference_table('test_table_1'); 853 SELECT create_distributed_table('test_table_2', 'id'); 854 855 -- and maybe some other test 856 CREATE INDEX i1 ON test_table_1(id); 857 ALTER TABLE test_table_2 ADD CONSTRAINT check_val CHECK (id > 0); 858 SELECT count(*) FROM test_table_2; 859 SELECT count(*) FROM test_table_1; 860 UPDATE test_table_2 SET value_1 = 15; 861 862 -- make sure that the output isn't too verbose 863 SET LOCAL client_min_messages TO ERROR; 864 DROP TABLE test_table_2, test_table_1; 865COMMIT; 866 867SET client_min_messages TO ERROR; 868DROP TABLE reference_table CASCADE; 869SET client_min_messages TO DEBUG1; 870 871-- make sure that modifications to reference tables in a CTE can 872-- set the mode to sequential for the next operations 873CREATE TABLE reference_table(id int PRIMARY KEY); 874SELECT create_reference_table('reference_table'); 875 876CREATE TABLE distributed_table(id int PRIMARY KEY, value_1 int); 877SELECT create_distributed_table('distributed_table', 'id'); 878 879ALTER TABLE 880 distributed_table 881ADD CONSTRAINT 882 fkey_delete FOREIGN KEY(value_1) 883REFERENCES 884 reference_table(id) ON DELETE CASCADE; 885 886INSERT INTO reference_table SELECT i FROM generate_series(0, 10) i; 887INSERT INTO distributed_table SELECT i, i % 10 FROM generate_series(0, 100) i; 888 889-- this query returns 100 rows in Postgres, but not in Citus 890-- see https://github.com/citusdata/citus_docs/issues/664 for the discussion 891WITH t1 AS (DELETE FROM reference_table RETURNING id) 892 DELETE FROM distributed_table USING t1 WHERE value_1 = t1.id RETURNING *; 893 894-- load some more data for one more test with real-time selects 895INSERT INTO reference_table SELECT i FROM generate_series(0, 10) i; 896INSERT INTO distributed_table SELECT i, i % 10 FROM generate_series(0, 100) i; 897 898-- this query returns 100 rows in Postgres, but not in Citus 899-- see https://github.com/citusdata/citus_docs/issues/664 for the discussion 900WITH t1 AS (DELETE FROM reference_table RETURNING id) 901 SELECT count(*) FROM distributed_table, t1 WHERE value_1 = t1.id; 902 903-- this query should fail since we first to a parallel access to a distributed table 904-- with t1, and then access to t2 905WITH t1 AS (DELETE FROM distributed_table RETURNING id), 906 t2 AS (DELETE FROM reference_table RETURNING id) 907 SELECT count(*) FROM distributed_table, t1, t2 WHERE value_1 = t1.id AND value_1 = t2.id; 908 909-- similarly this should fail since we first access to a distributed 910-- table via t1, and then access to the reference table in the main query 911WITH t1 AS (DELETE FROM distributed_table RETURNING id) 912 DELETE FROM reference_table RETURNING id; 913 914 915-- finally, make sure that we can execute the same queries 916-- in the sequential mode 917BEGIN; 918 919 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 920 921 WITH t1 AS (DELETE FROM distributed_table RETURNING id), 922 t2 AS (DELETE FROM reference_table RETURNING id) 923 SELECT count(*) FROM distributed_table, t1, t2 WHERE value_1 = t1.id AND value_1 = t2.id; 924ROLLBACK; 925 926BEGIN; 927 928 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 929 930 WITH t1 AS (DELETE FROM distributed_table RETURNING id) 931 DELETE FROM reference_table RETURNING id; 932ROLLBACK; 933 934RESET client_min_messages; 935 936\set VERBOSITY terse 937DROP SCHEMA test_fkey_to_ref_in_tx CASCADE; 938\set VERBOSITY default 939 940SET search_path TO public; 941