1-- 2-- Tests multiple commands in transactions where 3-- there is foreign key relation between reference 4-- tables and distributed tables 5-- 6CREATE SCHEMA test_fkey_to_ref_in_tx; 7SET search_path TO 'test_fkey_to_ref_in_tx'; 8SET citus.next_shard_id TO 2380000; 9SET citus.next_placement_id TO 2380000; 10SET citus.shard_replication_factor TO 1; 11CREATE TABLE transitive_reference_table(id int PRIMARY KEY); 12SELECT create_reference_table('transitive_reference_table'); 13 create_reference_table 14--------------------------------------------------------------------- 15 16(1 row) 17 18CREATE TABLE reference_table(id int PRIMARY KEY, value_1 int); 19SELECT create_reference_table('reference_table'); 20 create_reference_table 21--------------------------------------------------------------------- 22 23(1 row) 24 25CREATE TABLE on_update_fkey_table(id int PRIMARY KEY, value_1 int); 26SELECT create_distributed_table('on_update_fkey_table', 'id'); 27 create_distributed_table 28--------------------------------------------------------------------- 29 30(1 row) 31 32CREATE TABLE unrelated_dist_table(id int PRIMARY KEY, value_1 int); 33SELECT create_distributed_table('unrelated_dist_table', 'id'); 34 create_distributed_table 35--------------------------------------------------------------------- 36 37(1 row) 38 39ALTER TABLE on_update_fkey_table ADD CONSTRAINT fkey FOREIGN KEY(value_1) REFERENCES reference_table(id) ON UPDATE CASCADE; 40ALTER TABLE reference_table ADD CONSTRAINT fkey FOREIGN KEY(value_1) REFERENCES transitive_reference_table(id) ON UPDATE CASCADE; 41INSERT INTO transitive_reference_table SELECT i FROM generate_series(0, 100) i; 42INSERT INTO reference_table SELECT i, i FROM generate_series(0, 100) i; 43INSERT INTO on_update_fkey_table SELECT i, i % 100 FROM generate_series(0, 1000) i; 44INSERT INTO unrelated_dist_table SELECT i, i % 100 FROM generate_series(0, 1000) i; 45-- in order to see when the mode automatically swithces to sequential execution 46SET client_min_messages TO DEBUG1; 47-- case 1.1: SELECT to a reference table is followed by a parallel SELECT to a distributed table 48BEGIN; 49 SELECT count(*) FROM reference_table; 50 count 51--------------------------------------------------------------------- 52 101 53(1 row) 54 55 SELECT count(*) FROM on_update_fkey_table; 56 count 57--------------------------------------------------------------------- 58 1001 59(1 row) 60 61ROLLBACK; 62BEGIN; 63 SELECT count(*) FROM transitive_reference_table; 64 count 65--------------------------------------------------------------------- 66 101 67(1 row) 68 69 SELECT count(*) FROM on_update_fkey_table; 70 count 71--------------------------------------------------------------------- 72 1001 73(1 row) 74 75ROLLBACK; 76-- case 1.2: SELECT to a reference table is followed by a multiple router SELECTs to a distributed table 77BEGIN; 78 SELECT count(*) FROM reference_table; 79 count 80--------------------------------------------------------------------- 81 101 82(1 row) 83 84 SELECT count(*) FROM on_update_fkey_table WHERE id = 15; 85 count 86--------------------------------------------------------------------- 87 1 88(1 row) 89 90 SELECT count(*) FROM on_update_fkey_table WHERE id = 16; 91 count 92--------------------------------------------------------------------- 93 1 94(1 row) 95 96 SELECT count(*) FROM on_update_fkey_table WHERE id = 17; 97 count 98--------------------------------------------------------------------- 99 1 100(1 row) 101 102 SELECT count(*) FROM on_update_fkey_table WHERE id = 18; 103 count 104--------------------------------------------------------------------- 105 1 106(1 row) 107 108ROLLBACK; 109BEGIN; 110 SELECT count(*) FROM transitive_reference_table; 111 count 112--------------------------------------------------------------------- 113 101 114(1 row) 115 116 SELECT count(*) FROM on_update_fkey_table WHERE id = 15; 117 count 118--------------------------------------------------------------------- 119 1 120(1 row) 121 122 SELECT count(*) FROM on_update_fkey_table WHERE id = 16; 123 count 124--------------------------------------------------------------------- 125 1 126(1 row) 127 128 SELECT count(*) FROM on_update_fkey_table WHERE id = 17; 129 count 130--------------------------------------------------------------------- 131 1 132(1 row) 133 134 SELECT count(*) FROM on_update_fkey_table WHERE id = 18; 135 count 136--------------------------------------------------------------------- 137 1 138(1 row) 139 140ROLLBACK; 141-- case 1.3: SELECT to a reference table is followed by a multi-shard UPDATE to a distributed table 142BEGIN; 143 SELECT count(*) FROM reference_table; 144 count 145--------------------------------------------------------------------- 146 101 147(1 row) 148 149 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 150ROLLBACK; 151BEGIN; 152 SELECT count(*) FROM transitive_reference_table; 153 count 154--------------------------------------------------------------------- 155 101 156(1 row) 157 158 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 159ROLLBACK; 160-- case 1.4: SELECT to a reference table is followed by a multiple sing-shard UPDATE to a distributed table 161BEGIN; 162 SELECT count(*) FROM reference_table; 163 count 164--------------------------------------------------------------------- 165 101 166(1 row) 167 168 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 15; 169 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 16; 170 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 17; 171 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 18; 172ROLLBACK; 173BEGIN; 174 SELECT count(*) FROM transitive_reference_table; 175 count 176--------------------------------------------------------------------- 177 101 178(1 row) 179 180 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 15; 181 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 16; 182 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 17; 183 UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 18; 184ROLLBACK; 185-- case 1.5: SELECT to a reference table is followed by a DDL that touches fkey column 186BEGIN; 187 SELECT count(*) FROM reference_table; 188 count 189--------------------------------------------------------------------- 190 101 191(1 row) 192 193 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint; 194DEBUG: rewriting table "on_update_fkey_table" 195DEBUG: validating foreign key constraint "fkey" 196ROLLBACK; 197BEGIN; 198 SELECT count(*) FROM transitive_reference_table; 199 count 200--------------------------------------------------------------------- 201 101 202(1 row) 203 204 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint; 205DEBUG: rewriting table "on_update_fkey_table" 206DEBUG: validating foreign key constraint "fkey" 207ROLLBACK; 208-- case 1.6: SELECT to a reference table is followed by an unrelated DDL 209BEGIN; 210 SELECT count(*) FROM reference_table; 211 count 212--------------------------------------------------------------------- 213 101 214(1 row) 215 216 ALTER TABLE on_update_fkey_table ADD COLUMN X INT; 217DEBUG: switching to sequential query execution mode 218DETAIL: cannot execute parallel DDL on table "on_update_fkey_table" after SELECT command on reference table "reference_table" because there is a foreign key between them and "reference_table" has been accessed in this transaction 219ROLLBACK; 220BEGIN; 221 SELECT count(*) FROM transitive_reference_table; 222 count 223--------------------------------------------------------------------- 224 101 225(1 row) 226 227 ALTER TABLE on_update_fkey_table ADD COLUMN X INT; 228DEBUG: switching to sequential query execution mode 229DETAIL: cannot execute parallel DDL on table "on_update_fkey_table" after SELECT command on reference table "transitive_reference_table" because there is a foreign key between them and "transitive_reference_table" has been accessed in this transaction 230ROLLBACK; 231-- case 1.7.1: SELECT to a reference table is followed by a DDL that is on 232-- the foreign key column 233BEGIN; 234 SELECT count(*) FROM reference_table; 235 count 236--------------------------------------------------------------------- 237 101 238(1 row) 239 240 -- make sure that the output isn't too verbose 241 SET LOCAL client_min_messages TO ERROR; 242 ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE; 243ROLLBACK; 244BEGIN; 245 SELECT count(*) FROM transitive_reference_table; 246 count 247--------------------------------------------------------------------- 248 101 249(1 row) 250 251 -- make sure that the output isn't too verbose 252 SET LOCAL client_min_messages TO ERROR; 253 ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE; 254ROLLBACK; 255-- case 1.7.2: SELECT to a reference table is followed by a DDL that is on 256-- the foreign key column after a parallel query has been executed 257BEGIN; 258 SELECT count(*) FROM unrelated_dist_table; 259 count 260--------------------------------------------------------------------- 261 1001 262(1 row) 263 264 SELECT count(*) FROM reference_table; 265 count 266--------------------------------------------------------------------- 267 101 268(1 row) 269 270 ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE; 271ERROR: cannot modify table "on_update_fkey_table" because there was a parallel operation on a distributed table in the transaction 272DETAIL: When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 273HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 274ROLLBACK; 275BEGIN; 276 SELECT count(*) FROM unrelated_dist_table; 277 count 278--------------------------------------------------------------------- 279 1001 280(1 row) 281 282 SELECT count(*) FROM transitive_reference_table; 283 count 284--------------------------------------------------------------------- 285 101 286(1 row) 287 288 ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE; 289ERROR: cannot modify table "on_update_fkey_table" because there was a parallel operation on a distributed table in the transaction 290DETAIL: When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 291HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 292ROLLBACK; 293-- case 1.7.3: SELECT to a reference table is followed by a DDL that is not on 294-- the foreign key column, and a parallel query has already been executed 295BEGIN; 296 SELECT count(*) FROM unrelated_dist_table; 297 count 298--------------------------------------------------------------------- 299 1001 300(1 row) 301 302 SELECT count(*) FROM reference_table; 303 count 304--------------------------------------------------------------------- 305 101 306(1 row) 307 308 ALTER TABLE on_update_fkey_table ADD COLUMN X INT; 309ERROR: cannot execute parallel DDL on table "on_update_fkey_table" after SELECT command on reference table "reference_table" because there is a foreign key between them and "reference_table" has been accessed in this transaction 310DETAIL: When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 311HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 312ROLLBACK; 313BEGIN; 314 SELECT count(*) FROM unrelated_dist_table; 315 count 316--------------------------------------------------------------------- 317 1001 318(1 row) 319 320 SELECT count(*) FROM transitive_reference_table; 321 count 322--------------------------------------------------------------------- 323 101 324(1 row) 325 326 ALTER TABLE on_update_fkey_table ADD COLUMN X INT; 327ERROR: cannot execute parallel DDL on table "on_update_fkey_table" after SELECT command on reference table "transitive_reference_table" because there is a foreign key between them and "transitive_reference_table" has been accessed in this transaction 328DETAIL: When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 329HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 330ROLLBACK; 331-- case 1.8: SELECT to a reference table is followed by a COPY 332BEGIN; 333 SELECT count(*) FROM reference_table; 334 count 335--------------------------------------------------------------------- 336 101 337(1 row) 338 339 COPY on_update_fkey_table FROM STDIN WITH CSV; 340ROLLBACK; 341BEGIN; 342 SELECT count(*) FROM transitive_reference_table; 343 count 344--------------------------------------------------------------------- 345 101 346(1 row) 347 348 COPY on_update_fkey_table FROM STDIN WITH CSV; 349ROLLBACK; 350-- case 2.1: UPDATE to a reference table is followed by a multi-shard SELECT 351BEGIN; 352 UPDATE reference_table SET id = 101 WHERE id = 99; 353DEBUG: switching to sequential query execution mode 354DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 355 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 356 count 357--------------------------------------------------------------------- 358 0 359(1 row) 360 361 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101; 362 count 363--------------------------------------------------------------------- 364 10 365(1 row) 366 367ROLLBACK; 368BEGIN; 369 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 370DEBUG: switching to sequential query execution mode 371DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 372 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 373 count 374--------------------------------------------------------------------- 375 10 376(1 row) 377 378 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101; 379 count 380--------------------------------------------------------------------- 381 0 382(1 row) 383 384ROLLBACK; 385-- case 2.2: UPDATE to a reference table is followed by multiple router SELECT 386BEGIN; 387 UPDATE reference_table SET id = 101 WHERE id = 99; 388DEBUG: switching to sequential query execution mode 389DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 390 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 99; 391 count 392--------------------------------------------------------------------- 393 1 394(1 row) 395 396 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 199; 397 count 398--------------------------------------------------------------------- 399 1 400(1 row) 401 402 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 299; 403 count 404--------------------------------------------------------------------- 405 1 406(1 row) 407 408 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 399; 409 count 410--------------------------------------------------------------------- 411 1 412(1 row) 413 414ROLLBACK; 415BEGIN; 416 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 417DEBUG: switching to sequential query execution mode 418DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 419 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 99; 420 count 421--------------------------------------------------------------------- 422 0 423(1 row) 424 425 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 199; 426 count 427--------------------------------------------------------------------- 428 0 429(1 row) 430 431 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 299; 432 count 433--------------------------------------------------------------------- 434 0 435(1 row) 436 437 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 399; 438 count 439--------------------------------------------------------------------- 440 0 441(1 row) 442 443ROLLBACK; 444-- case 2.3: UPDATE to a reference table is followed by a multi-shard UPDATE 445BEGIN; 446 UPDATE reference_table SET id = 101 WHERE id = 99; 447DEBUG: switching to sequential query execution mode 448DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 449 UPDATE on_update_fkey_table SET value_1 = 15; 450ROLLBACK; 451BEGIN; 452 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 453DEBUG: switching to sequential query execution mode 454DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 455 UPDATE on_update_fkey_table SET value_1 = 15; 456ROLLBACK; 457-- case 2.4: UPDATE to a reference table is followed by multiple router UPDATEs 458BEGIN; 459 UPDATE reference_table SET id = 101 WHERE id = 99; 460DEBUG: switching to sequential query execution mode 461DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 462 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 1; 463 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 2; 464 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 3; 465 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 4; 466ROLLBACK; 467BEGIN; 468 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 469DEBUG: switching to sequential query execution mode 470DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 471 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 1; 472ERROR: insert or update on table "on_update_fkey_table_xxxxxxx" violates foreign key constraint "fkey_xxxxxxx" 473DETAIL: Key (value_1)=(101) is not present in table "reference_table_2380001". 474CONTEXT: while executing command on localhost:xxxxx 475 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 2; 476ERROR: current transaction is aborted, commands ignored until end of transaction block 477 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 3; 478ERROR: current transaction is aborted, commands ignored until end of transaction block 479 UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 4; 480ERROR: current transaction is aborted, commands ignored until end of transaction block 481ROLLBACK; 482-- case 2.5: UPDATE to a reference table is followed by a DDL that touches fkey column 483BEGIN; 484 UPDATE reference_table SET id = 101 WHERE id = 99; 485DEBUG: switching to sequential query execution mode 486DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 487 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint; 488DEBUG: rewriting table "on_update_fkey_table" 489DEBUG: validating foreign key constraint "fkey" 490ROLLBACK; 491BEGIN; 492 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 493DEBUG: switching to sequential query execution mode 494DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 495 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint; 496DEBUG: rewriting table "on_update_fkey_table" 497DEBUG: validating foreign key constraint "fkey" 498ROLLBACK; 499-- case 2.6: UPDATE to a reference table is followed by an unrelated DDL 500BEGIN; 501 UPDATE reference_table SET id = 101 WHERE id = 99; 502DEBUG: switching to sequential query execution mode 503DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 504 ALTER TABLE on_update_fkey_table ADD COLUMN value_1_X INT; 505ROLLBACK; 506BEGIN; 507 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 508DEBUG: switching to sequential query execution mode 509DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 510 ALTER TABLE on_update_fkey_table ADD COLUMN value_1_X INT; 511ROLLBACK; 512-- case 2.7: UPDATE to a reference table is followed by COPY 513BEGIN; 514 UPDATE reference_table SET id = 101 WHERE id = 99; 515DEBUG: switching to sequential query execution mode 516DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 517 COPY on_update_fkey_table FROM STDIN WITH CSV; 518ROLLBACK; 519BEGIN; 520 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 521DEBUG: switching to sequential query execution mode 522DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 523 COPY on_update_fkey_table FROM STDIN WITH CSV; 524ERROR: insert or update on table "on_update_fkey_table_xxxxxxx" violates foreign key constraint "fkey_xxxxxxx" 525DETAIL: Key (value_1)=(101) is not present in table "reference_table_2380001". 526ROLLBACK; 527-- case 2.8: UPDATE to a reference table is followed by TRUNCATE 528BEGIN; 529 UPDATE reference_table SET id = 101 WHERE id = 99; 530DEBUG: switching to sequential query execution mode 531DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 532 TRUNCATE on_update_fkey_table; 533ROLLBACK; 534BEGIN; 535 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 536DEBUG: switching to sequential query execution mode 537DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 538 TRUNCATE on_update_fkey_table; 539ROLLBACK; 540-- case 3.1: an unrelated DDL to a reference table is followed by a real-time SELECT 541BEGIN; 542 ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001; 543DEBUG: switching to sequential query execution mode 544DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 545 SELECT count(*) FROM on_update_fkey_table; 546 count 547--------------------------------------------------------------------- 548 1001 549(1 row) 550 551ROLLBACK; 552BEGIN; 553 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001; 554DEBUG: switching to sequential query execution mode 555DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 556 SELECT count(*) FROM on_update_fkey_table; 557 count 558--------------------------------------------------------------------- 559 1001 560(1 row) 561 562ROLLBACK; 563-- case 3.2: DDL that touches fkey column to a reference table is followed by a real-time SELECT 564BEGIN; 565 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE int; 566 SELECT count(*) FROM on_update_fkey_table; 567 count 568--------------------------------------------------------------------- 569 1001 570(1 row) 571 572ROLLBACK; 573BEGIN; 574 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE int; 575 SELECT count(*) FROM on_update_fkey_table; 576 count 577--------------------------------------------------------------------- 578 1001 579(1 row) 580 581ROLLBACK; 582-- case 3.3: DDL to a reference table followed by a multi shard UPDATE 583BEGIN; 584 ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001; 585DEBUG: switching to sequential query execution mode 586DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 587 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 588ROLLBACK; 589BEGIN; 590 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001; 591DEBUG: switching to sequential query execution mode 592DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 593 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 594ROLLBACK; 595-- case 3.4: DDL to a reference table followed by multiple router UPDATEs 596BEGIN; 597 ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001; 598DEBUG: switching to sequential query execution mode 599DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 600 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 1; 601 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 2; 602 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 3; 603 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 4; 604ROLLBACK; 605BEGIN; 606 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001; 607DEBUG: switching to sequential query execution mode 608DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 609 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 1; 610 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 2; 611 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 3; 612 UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 4; 613ROLLBACK; 614-- case 3.5: DDL to reference table followed by a DDL to dist table 615BEGIN; 616 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 617DEBUG: rewriting table "reference_table" 618DEBUG: validating foreign key constraint "fkey" 619 CREATE INDEX fkey_test_index_1 ON on_update_fkey_table(value_1); 620ROLLBACK; 621BEGIN; 622 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 623DEBUG: rewriting table "transitive_reference_table" 624DEBUG: validating foreign key constraint "fkey" 625 CREATE INDEX fkey_test_index_1 ON on_update_fkey_table(value_1); 626ROLLBACK; 627-- case 4.6: DDL to reference table followed by a DDL to dist table, both touching fkey columns 628BEGIN; 629 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 630DEBUG: rewriting table "reference_table" 631DEBUG: validating foreign key constraint "fkey" 632 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 633DEBUG: rewriting table "on_update_fkey_table" 634DEBUG: validating foreign key constraint "fkey" 635ROLLBACK; 636BEGIN; 637 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 638DEBUG: rewriting table "transitive_reference_table" 639DEBUG: validating foreign key constraint "fkey" 640 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 641DEBUG: rewriting table "on_update_fkey_table" 642DEBUG: validating foreign key constraint "fkey" 643ROLLBACK; 644-- case 3.7: DDL to a reference table is followed by COPY 645BEGIN; 646 ALTER TABLE reference_table ADD COLUMN X int; 647DEBUG: switching to sequential query execution mode 648DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 649 COPY on_update_fkey_table FROM STDIN WITH CSV; 650ROLLBACK; 651BEGIN; 652 ALTER TABLE transitive_reference_table ADD COLUMN X int; 653DEBUG: switching to sequential query execution mode 654DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 655 COPY on_update_fkey_table FROM STDIN WITH CSV; 656ROLLBACK; 657-- case 3.8: DDL to a reference table is followed by TRUNCATE 658BEGIN; 659 ALTER TABLE reference_table ADD COLUMN X int; 660DEBUG: switching to sequential query execution mode 661DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 662 TRUNCATE on_update_fkey_table; 663ROLLBACK; 664BEGIN; 665 ALTER TABLE transitive_reference_table ADD COLUMN X int; 666DEBUG: switching to sequential query execution mode 667DETAIL: Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 668 TRUNCATE on_update_fkey_table; 669ROLLBACK; 670-- case 3.9: DDL to a reference table is followed by TRUNCATE 671BEGIN; 672 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 673DEBUG: rewriting table "reference_table" 674DEBUG: validating foreign key constraint "fkey" 675 TRUNCATE on_update_fkey_table; 676ROLLBACK; 677BEGIN; 678 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 679DEBUG: rewriting table "transitive_reference_table" 680DEBUG: validating foreign key constraint "fkey" 681 TRUNCATE on_update_fkey_table; 682ROLLBACK; 683--------------------------------------------------------------------- 684--- Now, start testing the other way araound 685--------------------------------------------------------------------- 686-- case 4.1: SELECT to a dist table is follwed by a SELECT to a reference table 687BEGIN; 688 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 689 count 690--------------------------------------------------------------------- 691 10 692(1 row) 693 694 SELECT count(*) FROM reference_table; 695 count 696--------------------------------------------------------------------- 697 101 698(1 row) 699 700ROLLBACK; 701BEGIN; 702 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 703 count 704--------------------------------------------------------------------- 705 10 706(1 row) 707 708 SELECT count(*) FROM transitive_reference_table; 709 count 710--------------------------------------------------------------------- 711 101 712(1 row) 713 714ROLLBACK; 715-- case 4.2: SELECT to a dist table is follwed by a DML to a reference table 716BEGIN; 717 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 718 count 719--------------------------------------------------------------------- 720 10 721(1 row) 722 723 UPDATE reference_table SET id = 101 WHERE id = 99; 724ERROR: cannot modify table "reference_table" because there was a parallel operation on a distributed table 725DETAIL: When there is a foreign key to a reference table or to a local table, Citus needs to perform all operations over a single connection per node to ensure consistency. 726HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 727ROLLBACK; 728BEGIN; 729 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 730 count 731--------------------------------------------------------------------- 732 10 733(1 row) 734 735 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 736ERROR: cannot modify table "transitive_reference_table" because there was a parallel operation on a distributed table 737DETAIL: When there is a foreign key to a reference table or to a local table, Citus needs to perform all operations over a single connection per node to ensure consistency. 738HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 739ROLLBACK; 740-- case 4.3: SELECT to a dist table is follwed by an unrelated DDL to a reference table 741BEGIN; 742 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 743 count 744--------------------------------------------------------------------- 745 10 746(1 row) 747 748 ALTER TABLE reference_table ADD COLUMN X INT; 749ERROR: cannot execute DDL on table "reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction 750HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 751ROLLBACK; 752BEGIN; 753 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 754 count 755--------------------------------------------------------------------- 756 10 757(1 row) 758 759 ALTER TABLE transitive_reference_table ADD COLUMN X INT; 760ERROR: cannot execute DDL on table "transitive_reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction 761HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 762ROLLBACK; 763-- case 4.4: SELECT to a dist table is follwed by a DDL to a reference table 764BEGIN; 765 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 766 count 767--------------------------------------------------------------------- 768 10 769(1 row) 770 771 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 772DEBUG: rewriting table "reference_table" 773DEBUG: validating foreign key constraint "fkey" 774ERROR: cannot execute DDL on table "reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction 775HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 776ROLLBACK; 777BEGIN; 778 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 779 count 780--------------------------------------------------------------------- 781 10 782(1 row) 783 784 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 785DEBUG: rewriting table "transitive_reference_table" 786DEBUG: validating foreign key constraint "fkey" 787ERROR: cannot execute DDL on table "transitive_reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction 788HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 789ROLLBACK; 790-- case 4.5: SELECT to a dist table is follwed by a TRUNCATE 791\set VERBOSITY terse 792SET client_min_messages to LOG; 793BEGIN; 794 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 795 count 796--------------------------------------------------------------------- 797 10 798(1 row) 799 800 TRUNCATE reference_table CASCADE; 801NOTICE: truncate cascades to table "on_update_fkey_table" 802ERROR: cannot execute DDL on table "reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction 803ROLLBACK; 804BEGIN; 805 SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99; 806 count 807--------------------------------------------------------------------- 808 10 809(1 row) 810 811 TRUNCATE transitive_reference_table CASCADE; 812NOTICE: truncate cascades to table "reference_table" 813NOTICE: truncate cascades to table "on_update_fkey_table" 814ERROR: cannot execute DDL on table "transitive_reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction 815ROLLBACK; 816-- case 4.6: Router SELECT to a dist table is followed by a TRUNCATE 817BEGIN; 818 SELECT count(*) FROM on_update_fkey_table WHERE id = 9; 819 count 820--------------------------------------------------------------------- 821 1 822(1 row) 823 824 TRUNCATE reference_table CASCADE; 825NOTICE: truncate cascades to table "on_update_fkey_table" 826ROLLBACK; 827BEGIN; 828 SELECT count(*) FROM on_update_fkey_table WHERE id = 9; 829 count 830--------------------------------------------------------------------- 831 1 832(1 row) 833 834 TRUNCATE transitive_reference_table CASCADE; 835NOTICE: truncate cascades to table "reference_table" 836NOTICE: truncate cascades to table "on_update_fkey_table" 837ROLLBACK; 838-- case 4.7: SELECT to a dist table is followed by a DROP 839-- DROP following SELECT is important as we error out after 840-- the standart process utility hook drops the table. 841-- That could cause SIGSEGV before the patch. 842-- Below block should "successfully" error out 843BEGIN; 844 SELECT count(*) FROM on_update_fkey_table; 845 count 846--------------------------------------------------------------------- 847 1001 848(1 row) 849 850 DROP TABLE reference_table CASCADE; 851NOTICE: drop cascades to constraint fkey on table on_update_fkey_table 852ERROR: cannot execute DDL on table because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction 853ROLLBACK; 854-- case 4.8: Router SELECT to a dist table is followed by a TRUNCATE 855-- No errors expected from below block as SELECT there is a router 856-- query 857BEGIN; 858 SELECT count(*) FROM on_update_fkey_table WHERE id = 9; 859 count 860--------------------------------------------------------------------- 861 1 862(1 row) 863 864 DROP TABLE reference_table CASCADE; 865NOTICE: drop cascades to constraint fkey on table on_update_fkey_table 866ROLLBACK; 867RESET client_min_messages; 868\set VERBOSITY default 869-- case 5.1: Parallel UPDATE on distributed table follow by a SELECT 870BEGIN; 871 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 872 SELECT count(*) FROM reference_table; 873 count 874--------------------------------------------------------------------- 875 101 876(1 row) 877 878ROLLBACK; 879BEGIN; 880 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 881 SELECT count(*) FROM transitive_reference_table; 882 count 883--------------------------------------------------------------------- 884 101 885(1 row) 886 887ROLLBACK; 888-- case 5.2: Parallel UPDATE on distributed table follow by a UPDATE 889BEGIN; 890 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 891 UPDATE reference_table SET id = 160 WHERE id = 15; 892ERROR: cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 893HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 894ROLLBACK; 895BEGIN; 896 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 897 UPDATE transitive_reference_table SET id = 160 WHERE id = 15; 898ERROR: cannot execute DML on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 899HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 900ROLLBACK; 901BEGIN; 902 WITH cte AS (UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15 RETURNING *) 903 SELECT * FROM cte ORDER BY 1, 2; 904 id | value_1 905--------------------------------------------------------------------- 906 15 | 16 907 115 | 16 908 215 | 16 909 315 | 16 910 415 | 16 911 515 | 16 912 615 | 16 913 715 | 16 914 815 | 16 915 915 | 16 916(10 rows) 917 918 UPDATE reference_table SET id = 160 WHERE id = 15; 919ERROR: cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 920HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 921ROLLBACK; 922BEGIN; 923 WITH cte AS (UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15 RETURNING *) 924 SELECT * FROM cte ORDER BY 1, 2; 925 id | value_1 926--------------------------------------------------------------------- 927 15 | 16 928 115 | 16 929 215 | 16 930 315 | 16 931 415 | 16 932 515 | 16 933 615 | 16 934 715 | 16 935 815 | 16 936 915 | 16 937(10 rows) 938 939 UPDATE transitive_reference_table SET id = 160 WHERE id = 15; 940ERROR: cannot execute DML on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 941HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 942ROLLBACK; 943-- case 5.3: Parallel UPDATE on distributed table follow by an unrelated DDL on reference table 944BEGIN; 945 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 946 ALTER TABLE reference_table ADD COLUMN X INT; 947ERROR: cannot execute DDL on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 948HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 949ROLLBACK; 950BEGIN; 951 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 952 ALTER TABLE transitive_reference_table ADD COLUMN X INT; 953ERROR: cannot execute DDL on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 954HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 955ROLLBACK; 956-- case 5.4: Parallel UPDATE on distributed table follow by a related DDL on reference table 957BEGIN; 958 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 959 ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint; 960ERROR: cannot execute DDL on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 961HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 962ROLLBACK; 963BEGIN; 964 UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15; 965 ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint; 966ERROR: cannot execute DDL on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 967HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 968ROLLBACK; 969-- case 6:1: Unrelated parallel DDL on distributed table followed by SELECT on ref. table 970BEGIN; 971 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 972 SELECT count(*) FROM reference_table; 973ERROR: cannot execute SELECT on table "reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction 974HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 975ROLLBACK; 976BEGIN; 977 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 978 SELECT count(*) FROM transitive_reference_table; 979ERROR: cannot execute SELECT on table "transitive_reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction 980HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 981ROLLBACK; 982-- case 6:2: Related parallel DDL on distributed table followed by SELECT on ref. table 983BEGIN; 984 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 985 UPDATE reference_table SET id = 160 WHERE id = 15; 986ROLLBACK; 987BEGIN; 988 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 989 UPDATE transitive_reference_table SET id = 160 WHERE id = 15; 990ROLLBACK; 991-- case 6:3: Unrelated parallel DDL on distributed table followed by UPDATE on ref. table 992BEGIN; 993 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 994 SELECT count(*) FROM reference_table; 995ERROR: cannot execute SELECT on table "reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction 996HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 997ROLLBACK; 998BEGIN; 999 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 1000 SELECT count(*) FROM transitive_reference_table; 1001ERROR: cannot execute SELECT on table "transitive_reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction 1002HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1003ROLLBACK; 1004-- case 6:4: Related parallel DDL on distributed table followed by SELECT on ref. table 1005BEGIN; 1006 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 1007 UPDATE reference_table SET id = 160 WHERE id = 15; 1008ERROR: cannot execute DML on table "reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction 1009HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1010ROLLBACK; 1011BEGIN; 1012 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 1013 UPDATE transitive_reference_table SET id = 160 WHERE id = 15; 1014ERROR: cannot execute DML on table "transitive_reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction 1015HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1016ROLLBACK; 1017-- case 6:5: Unrelated parallel DDL on distributed table followed by unrelated DDL on ref. table 1018BEGIN; 1019 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 1020 ALTER TABLE reference_table ADD COLUMN X int; 1021ERROR: cannot execute DDL on table "reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction 1022HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1023ROLLBACK; 1024BEGIN; 1025 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 1026 ALTER TABLE transitive_reference_table ADD COLUMN X int; 1027ERROR: cannot execute DDL on table "transitive_reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction 1028HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1029ROLLBACK; 1030-- case 6:6: Unrelated parallel DDL on distributed table followed by related DDL on ref. table 1031BEGIN; 1032 ALTER TABLE on_update_fkey_table ADD COLUMN X int; 1033 ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint; 1034ERROR: cannot modify table "on_update_fkey_table" because there was a parallel operation on a distributed table in the transaction 1035DETAIL: When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 1036HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1037ROLLBACK; 1038-- some more extensive tests 1039-- UPDATE on dist table is followed by DELETE to reference table 1040BEGIN; 1041 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 1042 DELETE FROM reference_table WHERE id = 99; 1043ERROR: cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 1044HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1045ROLLBACK; 1046BEGIN; 1047 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 1048 DELETE FROM transitive_reference_table WHERE id = 99; 1049ERROR: cannot execute DML on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 1050HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1051ROLLBACK; 1052-- an unrelated update followed by update on dist table and update 1053-- on reference table 1054BEGIN; 1055 UPDATE unrelated_dist_table SET value_1 = 15; 1056 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 1057 UPDATE reference_table SET id = 101 WHERE id = 99; 1058ERROR: cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 1059HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1060ROLLBACK; 1061BEGIN; 1062 UPDATE unrelated_dist_table SET value_1 = 15; 1063 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 1064 UPDATE transitive_reference_table SET id = 101 WHERE id = 99; 1065ERROR: cannot execute DML on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction 1066HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1067ROLLBACK; 1068-- an unrelated update followed by update on the reference table and update 1069-- on the cascading distributed table 1070-- note that the UPDATE on the reference table will try to set the execution 1071-- mode to sequential, which will fail since there is an already opened 1072-- parallel connections 1073BEGIN; 1074 UPDATE unrelated_dist_table SET value_1 = 15; 1075 UPDATE reference_table SET id = 101 WHERE id = 99; 1076ERROR: cannot modify table "reference_table" because there was a parallel operation on a distributed table 1077DETAIL: When there is a foreign key to a reference table or to a local table, Citus needs to perform all operations over a single connection per node to ensure consistency. 1078HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1079 UPDATE on_update_fkey_table SET value_1 = 5 WHERE id != 11; 1080ERROR: current transaction is aborted, commands ignored until end of transaction block 1081ROLLBACK; 1082BEGIN; 1083 CREATE TABLE test_table_1(id int PRIMARY KEY); 1084 SELECT create_reference_table('test_table_1'); 1085 create_reference_table 1086--------------------------------------------------------------------- 1087 1088(1 row) 1089 1090 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 1091 SELECT create_distributed_table('test_table_2', 'id'); 1092 create_distributed_table 1093--------------------------------------------------------------------- 1094 1095(1 row) 1096 1097 -- make sure that the output isn't too verbose 1098 SET LOCAL client_min_messages TO ERROR; 1099 DROP TABLE test_table_1 CASCADE; 1100ROLLBACK; 1101-- the fails since we're trying to switch sequential mode after 1102-- already executed a parallel query 1103BEGIN; 1104 CREATE TABLE test_table_1(id int PRIMARY KEY); 1105 SELECT create_reference_table('test_table_1'); 1106 create_reference_table 1107--------------------------------------------------------------------- 1108 1109(1 row) 1110 1111 CREATE TABLE tt4(id int PRIMARY KEY, value_1 int, FOREIGN KEY(id) REFERENCES tt4(id)); 1112 SELECT create_distributed_table('tt4', 'id'); 1113 create_distributed_table 1114--------------------------------------------------------------------- 1115 1116(1 row) 1117 1118 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)); 1119 SELECT create_distributed_table('test_table_2', 'id'); 1120ERROR: cannot distribute relation "test_table_2" in this transaction because it has a foreign key to a reference table 1121DETAIL: If a hash distributed table has a foreign key to a reference table, it has to be created in sequential mode before any parallel commands have been executed in the same transaction 1122HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1123 -- make sure that the output isn't too verbose 1124 SET LOCAL client_min_messages TO ERROR; 1125ERROR: current transaction is aborted, commands ignored until end of transaction block 1126 DROP TABLE test_table_1 CASCADE; 1127ERROR: current transaction is aborted, commands ignored until end of transaction block 1128ROLLBACK; 1129-- same test with the above, but this time using 1130-- sequential mode, succeeds 1131BEGIN; 1132 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 1133 CREATE TABLE test_table_1(id int PRIMARY KEY); 1134 SELECT create_reference_table('test_table_1'); 1135 create_reference_table 1136--------------------------------------------------------------------- 1137 1138(1 row) 1139 1140 CREATE TABLE tt4(id int PRIMARY KEY, value_1 int, FOREIGN KEY(id) REFERENCES tt4(id)); 1141 SELECT create_distributed_table('tt4', 'id'); 1142 create_distributed_table 1143--------------------------------------------------------------------- 1144 1145(1 row) 1146 1147 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)); 1148 SELECT create_distributed_table('test_table_2', 'id'); 1149 create_distributed_table 1150--------------------------------------------------------------------- 1151 1152(1 row) 1153 1154 -- make sure that the output isn't too verbose 1155 SET LOCAL client_min_messages TO ERROR; 1156 DROP TABLE test_table_1 CASCADE; 1157ROLLBACK; 1158-- another test with ALTER TABLE fails since we're already opened 1159-- parallel connection via create_distributed_table(), later 1160-- adding foreign key to reference table fails 1161BEGIN; 1162 CREATE TABLE test_table_1(id int PRIMARY KEY); 1163 SELECT create_reference_table('test_table_1'); 1164 create_reference_table 1165--------------------------------------------------------------------- 1166 1167(1 row) 1168 1169 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 1170 SELECT create_distributed_table('test_table_2', 'id'); 1171 create_distributed_table 1172--------------------------------------------------------------------- 1173 1174(1 row) 1175 1176 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 1177ERROR: cannot modify table "test_table_2" because there was a parallel operation on a distributed table in the transaction 1178DETAIL: When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 1179HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1180 -- make sure that the output isn't too verbose 1181 SET LOCAL client_min_messages TO ERROR; 1182ERROR: current transaction is aborted, commands ignored until end of transaction block 1183 DROP TABLE test_table_1, test_table_2; 1184ERROR: current transaction is aborted, commands ignored until end of transaction block 1185COMMIT; 1186-- same test with the above on sequential mode should work fine 1187BEGIN; 1188 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 1189 CREATE TABLE test_table_1(id int PRIMARY KEY); 1190 SELECT create_reference_table('test_table_1'); 1191 create_reference_table 1192--------------------------------------------------------------------- 1193 1194(1 row) 1195 1196 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 1197 SELECT create_distributed_table('test_table_2', 'id'); 1198 create_distributed_table 1199--------------------------------------------------------------------- 1200 1201(1 row) 1202 1203 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 1204 -- make sure that the output isn't too verbose 1205 SET LOCAL client_min_messages TO ERROR; 1206 DROP TABLE test_table_1, test_table_2; 1207COMMIT; 1208-- similar test with the above, but this time the order of 1209-- create_distributed_table and create_reference_table is 1210-- changed 1211BEGIN; 1212 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 1213 SELECT create_distributed_table('test_table_2', 'id'); 1214 create_distributed_table 1215--------------------------------------------------------------------- 1216 1217(1 row) 1218 1219 CREATE TABLE test_table_1(id int PRIMARY KEY); 1220 SELECT create_reference_table('test_table_1'); 1221 create_reference_table 1222--------------------------------------------------------------------- 1223 1224(1 row) 1225 1226 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 1227ERROR: cannot modify table "test_table_2" because there was a parallel operation on a distributed table in the transaction 1228DETAIL: When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 1229HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1230 -- make sure that the output isn't too verbose 1231 SET LOCAL client_min_messages TO ERROR; 1232ERROR: current transaction is aborted, commands ignored until end of transaction block 1233 DROP TABLE test_table_1 CASCADE; 1234ERROR: current transaction is aborted, commands ignored until end of transaction block 1235ROLLBACK; 1236-- same test in sequential mode should succeed 1237BEGIN; 1238 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 1239 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 1240 SELECT create_distributed_table('test_table_2', 'id'); 1241 create_distributed_table 1242--------------------------------------------------------------------- 1243 1244(1 row) 1245 1246 CREATE TABLE test_table_1(id int PRIMARY KEY); 1247 SELECT create_reference_table('test_table_1'); 1248 create_reference_table 1249--------------------------------------------------------------------- 1250 1251(1 row) 1252 1253 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 1254 -- make sure that the output isn't too verbose 1255 SET LOCAL client_min_messages TO ERROR; 1256 DROP TABLE test_table_1 CASCADE; 1257ROLLBACK; 1258-- again a very similar test, but this time 1259-- a parallel SELECT is already executed before 1260-- setting the mode to sequential should fail 1261BEGIN; 1262 SELECT count(*) FROM on_update_fkey_table; 1263 count 1264--------------------------------------------------------------------- 1265 1001 1266(1 row) 1267 1268 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 1269 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int); 1270 SELECT create_distributed_table('test_table_2', 'id'); 1271ERROR: cannot distribute "test_table_2" in sequential mode because a parallel query was executed in this transaction 1272HINT: If you have manually set citus.multi_shard_modify_mode to 'sequential', try with 'parallel' option. 1273 CREATE TABLE test_table_1(id int PRIMARY KEY); 1274ERROR: current transaction is aborted, commands ignored until end of transaction block 1275 SELECT create_reference_table('test_table_1'); 1276ERROR: current transaction is aborted, commands ignored until end of transaction block 1277 ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id); 1278ERROR: current transaction is aborted, commands ignored until end of transaction block 1279 -- make sure that the output isn't too verbose 1280 SET LOCAL client_min_messages TO ERROR; 1281ERROR: current transaction is aborted, commands ignored until end of transaction block 1282 DROP TABLE test_table_1 CASCADE; 1283ERROR: current transaction is aborted, commands ignored until end of transaction block 1284ROLLBACK; 1285-- make sure that we cannot create hash distributed tables with 1286-- foreign keys to reference tables when they have data in it 1287BEGIN; 1288 CREATE TABLE test_table_1(id int PRIMARY KEY); 1289 INSERT INTO test_table_1 SELECT i FROM generate_series(0,100) i; 1290 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 1291 INSERT INTO test_table_2 SELECT i, i FROM generate_series(0,100) i; 1292 SELECT create_reference_table('test_table_1'); 1293NOTICE: Copying data from local table... 1294NOTICE: copying the data has completed 1295DETAIL: The local data in the table is no longer visible, but is still on disk. 1296HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$test_fkey_to_ref_in_tx.test_table_1$$) 1297 create_reference_table 1298--------------------------------------------------------------------- 1299 1300(1 row) 1301 1302 SELECT create_distributed_table('test_table_2', 'id'); 1303ERROR: cannot distribute "test_table_2" in sequential mode because it is not empty 1304HINT: If you have manually set citus.multi_shard_modify_mode to 'sequential', try with 'parallel' option. If that is not the case, try distributing local tables when they are empty. 1305 -- make sure that the output isn't too verbose 1306 SET LOCAL client_min_messages TO ERROR; 1307ERROR: current transaction is aborted, commands ignored until end of transaction block 1308 DROP TABLE test_table_2, test_table_1; 1309ERROR: current transaction is aborted, commands ignored until end of transaction block 1310COMMIT; 1311-- the same test with above in sequential mode would still not work 1312-- since COPY cannot be executed in sequential mode 1313BEGIN; 1314 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 1315 CREATE TABLE test_table_1(id int PRIMARY KEY); 1316 INSERT INTO test_table_1 SELECT i FROM generate_series(0,100) i; 1317 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 1318 INSERT INTO test_table_2 SELECT i, i FROM generate_series(0,100) i; 1319 SELECT create_reference_table('test_table_1'); 1320NOTICE: Copying data from local table... 1321NOTICE: copying the data has completed 1322DETAIL: The local data in the table is no longer visible, but is still on disk. 1323HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$test_fkey_to_ref_in_tx.test_table_1$$) 1324 create_reference_table 1325--------------------------------------------------------------------- 1326 1327(1 row) 1328 1329 SELECT create_distributed_table('test_table_2', 'id'); 1330ERROR: cannot distribute "test_table_2" in sequential mode because it is not empty 1331HINT: If you have manually set citus.multi_shard_modify_mode to 'sequential', try with 'parallel' option. If that is not the case, try distributing local tables when they are empty. 1332 -- make sure that the output isn't too verbose 1333 SET LOCAL client_min_messages TO ERROR; 1334ERROR: current transaction is aborted, commands ignored until end of transaction block 1335 DROP TABLE test_table_2, test_table_1; 1336ERROR: current transaction is aborted, commands ignored until end of transaction block 1337COMMIT; 1338-- we should be able to execute and DML/DDL/SELECT after we've 1339-- switched to sequential via create_distributed_table 1340BEGIN; 1341 CREATE TABLE test_table_1(id int PRIMARY KEY); 1342 CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id)); 1343 SELECT create_reference_table('test_table_1'); 1344 create_reference_table 1345--------------------------------------------------------------------- 1346 1347(1 row) 1348 1349 SELECT create_distributed_table('test_table_2', 'id'); 1350 create_distributed_table 1351--------------------------------------------------------------------- 1352 1353(1 row) 1354 1355 -- and maybe some other test 1356 CREATE INDEX i1 ON test_table_1(id); 1357 ALTER TABLE test_table_2 ADD CONSTRAINT check_val CHECK (id > 0); 1358 SELECT count(*) FROM test_table_2; 1359 count 1360--------------------------------------------------------------------- 1361 0 1362(1 row) 1363 1364 SELECT count(*) FROM test_table_1; 1365 count 1366--------------------------------------------------------------------- 1367 0 1368(1 row) 1369 1370 UPDATE test_table_2 SET value_1 = 15; 1371 -- make sure that the output isn't too verbose 1372 SET LOCAL client_min_messages TO ERROR; 1373 DROP TABLE test_table_2, test_table_1; 1374COMMIT; 1375SET client_min_messages TO ERROR; 1376DROP TABLE reference_table CASCADE; 1377SET client_min_messages TO DEBUG1; 1378-- make sure that modifications to reference tables in a CTE can 1379-- set the mode to sequential for the next operations 1380CREATE TABLE reference_table(id int PRIMARY KEY); 1381DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "reference_table_pkey" for table "reference_table" 1382SELECT create_reference_table('reference_table'); 1383 create_reference_table 1384--------------------------------------------------------------------- 1385 1386(1 row) 1387 1388CREATE TABLE distributed_table(id int PRIMARY KEY, value_1 int); 1389DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "distributed_table_pkey" for table "distributed_table" 1390SELECT create_distributed_table('distributed_table', 'id'); 1391 create_distributed_table 1392--------------------------------------------------------------------- 1393 1394(1 row) 1395 1396ALTER TABLE 1397 distributed_table 1398ADD CONSTRAINT 1399 fkey_delete FOREIGN KEY(value_1) 1400REFERENCES 1401 reference_table(id) ON DELETE CASCADE; 1402INSERT INTO reference_table SELECT i FROM generate_series(0, 10) i; 1403DEBUG: distributed INSERT ... SELECT can only select from distributed tables 1404DEBUG: Collecting INSERT ... SELECT results on coordinator 1405DEBUG: switching to sequential query execution mode 1406DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 1407INSERT INTO distributed_table SELECT i, i % 10 FROM generate_series(0, 100) i; 1408DEBUG: distributed INSERT ... SELECT can only select from distributed tables 1409DEBUG: Collecting INSERT ... SELECT results on coordinator 1410-- this query returns 100 rows in Postgres, but not in Citus 1411-- see https://github.com/citusdata/citus_docs/issues/664 for the discussion 1412WITH t1 AS (DELETE FROM reference_table RETURNING id) 1413 DELETE FROM distributed_table USING t1 WHERE value_1 = t1.id RETURNING *; 1414DEBUG: generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id 1415DEBUG: Plan XXX query after replacing subqueries and CTEs: DELETE FROM test_fkey_to_ref_in_tx.distributed_table USING (SELECT intermediate_result.id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t1 WHERE (distributed_table.value_1 OPERATOR(pg_catalog.=) t1.id) RETURNING distributed_table.id, distributed_table.value_1, t1.id 1416DEBUG: switching to sequential query execution mode 1417DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 1418 id | value_1 | id 1419--------------------------------------------------------------------- 1420(0 rows) 1421 1422-- load some more data for one more test with real-time selects 1423INSERT INTO reference_table SELECT i FROM generate_series(0, 10) i; 1424DEBUG: distributed INSERT ... SELECT can only select from distributed tables 1425DEBUG: Collecting INSERT ... SELECT results on coordinator 1426DEBUG: switching to sequential query execution mode 1427DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 1428INSERT INTO distributed_table SELECT i, i % 10 FROM generate_series(0, 100) i; 1429DEBUG: distributed INSERT ... SELECT can only select from distributed tables 1430DEBUG: Collecting INSERT ... SELECT results on coordinator 1431-- this query returns 100 rows in Postgres, but not in Citus 1432-- see https://github.com/citusdata/citus_docs/issues/664 for the discussion 1433WITH t1 AS (DELETE FROM reference_table RETURNING id) 1434 SELECT count(*) FROM distributed_table, t1 WHERE value_1 = t1.id; 1435DEBUG: generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id 1436DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM test_fkey_to_ref_in_tx.distributed_table, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t1 WHERE (distributed_table.value_1 OPERATOR(pg_catalog.=) t1.id) 1437DEBUG: switching to sequential query execution mode 1438DETAIL: Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys. Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode 1439 count 1440--------------------------------------------------------------------- 1441 0 1442(1 row) 1443 1444-- this query should fail since we first to a parallel access to a distributed table 1445-- with t1, and then access to t2 1446WITH t1 AS (DELETE FROM distributed_table RETURNING id), 1447 t2 AS (DELETE FROM reference_table RETURNING id) 1448 SELECT count(*) FROM distributed_table, t1, t2 WHERE value_1 = t1.id AND value_1 = t2.id; 1449DEBUG: generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.distributed_table RETURNING id 1450DEBUG: generating subplan XXX_2 for CTE t2: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id 1451DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM test_fkey_to_ref_in_tx.distributed_table, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t1, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t2 WHERE ((distributed_table.value_1 OPERATOR(pg_catalog.=) t1.id) AND (distributed_table.value_1 OPERATOR(pg_catalog.=) t2.id)) 1452ERROR: cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "distributed_table" in the same transaction 1453HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1454-- similarly this should fail since we first access to a distributed 1455-- table via t1, and then access to the reference table in the main query 1456WITH t1 AS (DELETE FROM distributed_table RETURNING id) 1457 DELETE FROM reference_table RETURNING id; 1458DEBUG: generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.distributed_table RETURNING id 1459DEBUG: Plan XXX query after replacing subqueries and CTEs: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id 1460ERROR: cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "distributed_table" in the same transaction 1461HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" 1462-- finally, make sure that we can execute the same queries 1463-- in the sequential mode 1464BEGIN; 1465 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 1466 WITH t1 AS (DELETE FROM distributed_table RETURNING id), 1467 t2 AS (DELETE FROM reference_table RETURNING id) 1468 SELECT count(*) FROM distributed_table, t1, t2 WHERE value_1 = t1.id AND value_1 = t2.id; 1469DEBUG: generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.distributed_table RETURNING id 1470DEBUG: generating subplan XXX_2 for CTE t2: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id 1471DEBUG: Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM test_fkey_to_ref_in_tx.distributed_table, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t1, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t2 WHERE ((distributed_table.value_1 OPERATOR(pg_catalog.=) t1.id) AND (distributed_table.value_1 OPERATOR(pg_catalog.=) t2.id)) 1472 count 1473--------------------------------------------------------------------- 1474 0 1475(1 row) 1476 1477ROLLBACK; 1478BEGIN; 1479 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 1480 WITH t1 AS (DELETE FROM distributed_table RETURNING id) 1481 DELETE FROM reference_table RETURNING id; 1482DEBUG: generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.distributed_table RETURNING id 1483DEBUG: Plan XXX query after replacing subqueries and CTEs: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id 1484 id 1485--------------------------------------------------------------------- 1486(0 rows) 1487 1488ROLLBACK; 1489RESET client_min_messages; 1490\set VERBOSITY terse 1491DROP SCHEMA test_fkey_to_ref_in_tx CASCADE; 1492NOTICE: drop cascades to 5 other objects 1493\set VERBOSITY default 1494SET search_path TO public; 1495