github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/autocommit (about) 1 # LogicTest: local 2 3 # This file tests against mutations that we expect to be handled with one-phase 4 # commit transactions. In addition to checking the planning part, we also check 5 # (using traces) that this is implemented correctly in terms of KV operations. 6 # Any change to the kv batches produced by these statements should be treated 7 # with care. 8 9 statement ok 10 CREATE TABLE ab (a INT PRIMARY KEY, b INT, FAMILY f1 (a, b)) 11 12 # Populate table descriptor cache. 13 query II 14 SELECT * FROM ab 15 ---- 16 17 # ------------ 18 # INSERT tests 19 # ------------ 20 21 # Single-row insert should auto-commit. 22 query B 23 SELECT count(*) > 0 FROM [ 24 EXPLAIN (VERBOSE) INSERT INTO ab VALUES (1, 1) 25 ] WHERE field = 'auto commit' 26 ---- 27 true 28 29 statement ok 30 SET TRACING=ON; 31 INSERT INTO ab VALUES (1, 1); 32 SET TRACING=OFF 33 34 query TT 35 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 36 WHERE message LIKE '%r32: sending batch%' 37 AND message NOT LIKE '%PushTxn%' 38 AND message NOT LIKE '%QueryTxn%' 39 ---- 40 dist sender send r32: sending batch 1 CPut, 1 EndTxn to (n1,s1):1 41 42 # Multi-row insert should auto-commit. 43 query B 44 SELECT count(*) > 0 FROM [ 45 EXPLAIN (VERBOSE) INSERT INTO ab VALUES (2, 2), (3, 3) 46 ] WHERE field = 'auto commit' 47 ---- 48 true 49 50 statement ok 51 SET TRACING=ON; 52 INSERT INTO ab VALUES (2, 2), (3, 3); 53 SET TRACING=OFF 54 55 query TT 56 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 57 WHERE message LIKE '%r32: sending batch%' 58 AND message NOT LIKE '%PushTxn%' 59 AND message NOT LIKE '%QueryTxn%' 60 ---- 61 dist sender send r32: sending batch 2 CPut, 1 EndTxn to (n1,s1):1 62 63 # No auto-commit inside a transaction. 64 statement ok 65 BEGIN 66 67 query B 68 SELECT count(*) > 0 FROM [ 69 EXPLAIN (VERBOSE) INSERT INTO ab VALUES (4, 4), (5, 5) 70 ] WHERE field = 'auto commit' 71 ---- 72 false 73 74 statement ok 75 SET TRACING=ON; 76 INSERT INTO ab VALUES (4, 4), (5, 5); 77 SET TRACING=OFF 78 79 query TT 80 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 81 WHERE message LIKE '%r32: sending batch%' 82 AND message NOT LIKE '%PushTxn%' 83 AND message NOT LIKE '%QueryTxn%' 84 ---- 85 dist sender send r32: sending batch 2 CPut to (n1,s1):1 86 87 statement ok 88 ROLLBACK 89 90 # Insert with simple RETURNING statement should auto-commit. 91 query B 92 SELECT count(*) > 0 FROM [ 93 EXPLAIN (VERBOSE) INSERT INTO ab VALUES (6, 6), (7, 7) RETURNING a, b 94 ] WHERE field = 'auto commit' 95 ---- 96 true 97 98 statement ok 99 SET TRACING=ON; 100 INSERT INTO ab VALUES (6, 6), (7, 7) RETURNING a, b; 101 SET TRACING=OFF 102 103 query TT 104 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 105 WHERE message LIKE '%r32: sending batch%' 106 AND message NOT LIKE '%PushTxn%' 107 AND message NOT LIKE '%QueryTxn%' 108 ---- 109 dist sender send r32: sending batch 2 CPut, 1 EndTxn to (n1,s1):1 110 111 # TODO(radu): allow non-side-effecting projections. 112 query B 113 SELECT count(*) > 0 FROM [ 114 EXPLAIN (VERBOSE) INSERT INTO ab VALUES (8, 8), (9, 9) RETURNING a + b 115 ] WHERE field = 'auto commit' 116 ---- 117 false 118 119 statement ok 120 SET TRACING=ON; 121 INSERT INTO ab VALUES (8, 8), (9, 9) RETURNING a + b; 122 SET TRACING=OFF 123 124 query TT 125 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 126 WHERE message LIKE '%r32: sending batch%' 127 AND message NOT LIKE '%PushTxn%' 128 AND message NOT LIKE '%QueryTxn%' 129 AND operation NOT LIKE '%async%' 130 ---- 131 dist sender send r32: sending batch 2 CPut to (n1,s1):1 132 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 133 134 # Insert with RETURNING statement with side-effects should not auto-commit. 135 # In this case division can (in principle) error out. 136 query B 137 SELECT count(*) > 0 FROM [ 138 EXPLAIN (VERBOSE) INSERT INTO ab VALUES (10, 10), (11, 11) RETURNING a / b 139 ] WHERE field = 'auto commit' 140 ---- 141 false 142 143 statement ok 144 SET TRACING=ON; 145 INSERT INTO ab VALUES (10, 10), (11, 11) RETURNING a / b; 146 SET TRACING=OFF 147 148 query TT 149 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 150 WHERE message LIKE '%r32: sending batch%' 151 AND message NOT LIKE '%PushTxn%' 152 AND message NOT LIKE '%QueryTxn%' 153 AND operation NOT LIKE '%async%' 154 ---- 155 dist sender send r32: sending batch 2 CPut to (n1,s1):1 156 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 157 158 # Another way to test the scenario above: generate an error and ensure that the 159 # mutation was not committed. 160 statement error division by zero 161 INSERT INTO ab VALUES (12, 0) RETURNING a / b 162 163 query I 164 SELECT count(*) FROM ab WHERE b=0 165 ---- 166 0 167 168 # ------------ 169 # UPSERT tests 170 # ------------ 171 172 # Single-row upsert should auto-commit. 173 query B 174 SELECT count(*) > 0 FROM [ 175 EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (1, 1) 176 ] WHERE field = 'auto commit' 177 ---- 178 true 179 180 statement ok 181 SET TRACING=ON; 182 UPSERT INTO ab VALUES (1, 1); 183 SET TRACING=OFF 184 185 query TT 186 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 187 WHERE message LIKE '%r32: sending batch%' 188 AND message NOT LIKE '%PushTxn%' 189 AND message NOT LIKE '%QueryTxn%' 190 ---- 191 dist sender send r32: sending batch 1 Put, 1 EndTxn to (n1,s1):1 192 193 # Multi-row upsert should auto-commit. 194 query B 195 SELECT count(*) > 0 FROM [ 196 EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (2, 2), (3, 3) 197 ] WHERE field = 'auto commit' 198 ---- 199 true 200 201 statement ok 202 SET TRACING=ON; 203 UPSERT INTO ab VALUES (2, 2), (3, 3); 204 SET TRACING=OFF 205 206 query TT 207 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 208 WHERE message LIKE '%r32: sending batch%' 209 AND message NOT LIKE '%PushTxn%' 210 AND message NOT LIKE '%QueryTxn%' 211 ---- 212 dist sender send r32: sending batch 2 Put, 1 EndTxn to (n1,s1):1 213 214 # No auto-commit inside a transaction. 215 statement ok 216 BEGIN 217 218 query B 219 SELECT count(*) > 0 FROM [ 220 EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (4, 4), (5, 5) 221 ] WHERE field = 'auto commit' 222 ---- 223 false 224 225 statement ok 226 SET TRACING=ON; 227 UPSERT INTO ab VALUES (4, 4), (5, 5); 228 SET TRACING=OFF 229 230 query TT 231 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 232 WHERE message LIKE '%r32: sending batch%' 233 AND message NOT LIKE '%PushTxn%' 234 AND message NOT LIKE '%QueryTxn%' 235 ---- 236 dist sender send r32: sending batch 2 Put to (n1,s1):1 237 238 statement ok 239 ROLLBACK 240 241 # Upsert with simple RETURNING statement should auto-commit. 242 query B 243 SELECT count(*) > 0 FROM [ 244 EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (6, 6), (7, 7) RETURNING a, b 245 ] WHERE field = 'auto commit' 246 ---- 247 true 248 249 statement ok 250 SET TRACING=ON; 251 UPSERT INTO ab VALUES (6, 6), (7, 7) RETURNING a, b; 252 SET TRACING=OFF 253 254 query TT 255 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 256 WHERE message LIKE '%r32: sending batch%' 257 AND message NOT LIKE '%PushTxn%' 258 AND message NOT LIKE '%QueryTxn%' 259 ---- 260 dist sender send r32: sending batch 2 Put, 1 EndTxn to (n1,s1):1 261 262 # TODO(radu): allow non-side-effecting projections. 263 query B 264 SELECT count(*) > 0 FROM [ 265 EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (8, 8), (9, 9) RETURNING a + b 266 ] WHERE field = 'auto commit' 267 ---- 268 false 269 270 statement ok 271 SET TRACING=ON; 272 UPSERT INTO ab VALUES (8, 8), (9, 9) RETURNING a + b; 273 SET TRACING=OFF 274 275 query TT 276 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 277 WHERE message LIKE '%r32: sending batch%' 278 AND message NOT LIKE '%PushTxn%' 279 AND message NOT LIKE '%QueryTxn%' 280 AND operation NOT LIKE '%async%' 281 ---- 282 dist sender send r32: sending batch 2 Put to (n1,s1):1 283 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 284 285 # Upsert with RETURNING statement with side-effects should not auto-commit. 286 # In this case division can (in principle) error out. 287 query B 288 SELECT count(*) > 0 FROM [ 289 EXPLAIN (VERBOSE) UPSERT INTO ab VALUES (10, 10), (11, 11) RETURNING a / b 290 ] WHERE field = 'auto commit' 291 ---- 292 false 293 294 statement ok 295 SET TRACING=ON; 296 UPSERT INTO ab VALUES (10, 10), (11, 11) RETURNING a / b; 297 SET TRACING=OFF 298 299 query TT 300 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 301 WHERE message LIKE '%r32: sending batch%' 302 AND message NOT LIKE '%PushTxn%' 303 AND message NOT LIKE '%QueryTxn%' 304 AND operation NOT LIKE '%async%' 305 ---- 306 dist sender send r32: sending batch 2 Put to (n1,s1):1 307 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 308 309 # Another way to test the scenario above: generate an error and ensure that the 310 # mutation was not committed. 311 statement error division by zero 312 UPSERT INTO ab VALUES (12, 0) RETURNING a / b 313 314 query I 315 SELECT count(*) FROM ab WHERE b=0 316 ---- 317 0 318 319 # ------------ 320 # UPDATE tests 321 # ------------ 322 323 # Simple update should auto-commit. 324 query B 325 SELECT count(*) > 0 FROM [ 326 EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3 327 ] WHERE field = 'auto commit' 328 ---- 329 true 330 331 statement ok 332 SET TRACING=ON; 333 UPDATE ab SET b=b+1 WHERE a < 3; 334 SET TRACING=OFF 335 336 query TT 337 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 338 WHERE message LIKE '%r32: sending batch%' 339 AND message NOT LIKE '%PushTxn%' 340 AND message NOT LIKE '%QueryTxn%' 341 ---- 342 dist sender send r32: sending batch 1 Scan to (n1,s1):1 343 dist sender send r32: sending batch 2 Put, 1 EndTxn to (n1,s1):1 344 345 # No auto-commit inside a transaction. 346 statement ok 347 BEGIN 348 349 query B 350 SELECT count(*) > 0 FROM [ 351 EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3 352 ] WHERE field = 'auto commit' 353 ---- 354 false 355 356 statement ok 357 SET TRACING=ON; 358 UPDATE ab SET b=b+1 WHERE a < 3; 359 SET TRACING=OFF 360 361 query TT 362 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 363 WHERE message LIKE '%r32: sending batch%' 364 AND message NOT LIKE '%PushTxn%' 365 AND message NOT LIKE '%QueryTxn%' 366 ---- 367 dist sender send r32: sending batch 1 Scan to (n1,s1):1 368 dist sender send r32: sending batch 2 Put to (n1,s1):1 369 370 statement ok 371 ROLLBACK 372 373 # Update with simple RETURNING statement should auto-commit. 374 query B 375 SELECT count(*) > 0 FROM [ 376 EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a, b 377 ] WHERE field = 'auto commit' 378 ---- 379 true 380 381 statement ok 382 SET TRACING=ON; 383 UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a, b; 384 SET TRACING=OFF 385 386 query TT 387 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 388 WHERE message LIKE '%r32: sending batch%' 389 AND message NOT LIKE '%PushTxn%' 390 AND message NOT LIKE '%QueryTxn%' 391 ---- 392 dist sender send r32: sending batch 1 Scan to (n1,s1):1 393 dist sender send r32: sending batch 2 Put, 1 EndTxn to (n1,s1):1 394 395 # TODO(radu): allow non-side-effecting projections. 396 query B 397 SELECT count(*) > 0 FROM [ 398 EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a + b 399 ] WHERE field = 'auto commit' 400 ---- 401 false 402 403 statement ok 404 SET TRACING=ON; 405 UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a + b; 406 SET TRACING=OFF 407 408 query TT 409 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 410 WHERE message LIKE '%r32: sending batch%' 411 AND message NOT LIKE '%PushTxn%' 412 AND message NOT LIKE '%QueryTxn%' 413 AND operation NOT LIKE '%async%' 414 ---- 415 dist sender send r32: sending batch 1 Scan to (n1,s1):1 416 dist sender send r32: sending batch 2 Put to (n1,s1):1 417 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 418 419 # Update with RETURNING statement with side-effects should not auto-commit. 420 # In this case division can (in principle) error out. 421 query B 422 SELECT count(*) > 0 FROM [ 423 EXPLAIN (VERBOSE) UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a / b 424 ] WHERE field = 'auto commit' 425 ---- 426 false 427 428 statement ok 429 SET TRACING=ON; 430 UPDATE ab SET b=b+1 WHERE a < 3 RETURNING a / b; 431 SET TRACING=OFF 432 433 query TT 434 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 435 WHERE message LIKE '%r32: sending batch%' 436 AND message NOT LIKE '%PushTxn%' 437 AND message NOT LIKE '%QueryTxn%' 438 AND operation NOT LIKE '%async%' 439 ---- 440 dist sender send r32: sending batch 1 Scan to (n1,s1):1 441 dist sender send r32: sending batch 2 Put to (n1,s1):1 442 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 443 444 # Another way to test the scenario above: generate an error and ensure that the 445 # mutation was not committed. 446 statement error division by zero 447 UPDATE ab SET b=0 WHERE a < 3 RETURNING a / b; 448 449 query I 450 SELECT count(*) FROM ab WHERE b=0 451 ---- 452 0 453 454 # ------------ 455 # DELETE tests 456 # ------------ 457 458 # Single-row delete should auto-commit. 459 query B 460 SELECT count(*) > 0 FROM [ 461 EXPLAIN (VERBOSE) DELETE FROM ab WHERE a = 1 462 ] WHERE field = 'auto commit' 463 ---- 464 true 465 466 statement ok 467 SET TRACING=ON; 468 DELETE FROM ab WHERE a = 1; 469 SET TRACING=OFF 470 471 query TT 472 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 473 WHERE message LIKE '%r32: sending batch%' 474 AND message NOT LIKE '%PushTxn%' 475 AND message NOT LIKE '%QueryTxn%' 476 ---- 477 dist sender send r32: sending batch 1 DelRng, 1 EndTxn to (n1,s1):1 478 479 # Multi-row delete should auto-commit. 480 query B 481 SELECT count(*) > 0 FROM [ 482 EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (2, 3) 483 ] WHERE field = 'auto commit' 484 ---- 485 true 486 487 statement ok 488 SET TRACING=ON; 489 DELETE FROM ab WHERE a IN (2, 3); 490 SET TRACING=OFF 491 492 query TT 493 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 494 WHERE message LIKE '%r32: sending batch%' 495 AND message NOT LIKE '%PushTxn%' 496 AND message NOT LIKE '%QueryTxn%' 497 ---- 498 dist sender send r32: sending batch 1 DelRng, 1 EndTxn to (n1,s1):1 499 500 # No auto-commit inside a transaction. 501 statement ok 502 BEGIN 503 504 query B 505 SELECT count(*) > 0 FROM [ 506 EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (4, 5) 507 ] WHERE field = 'auto commit' 508 ---- 509 false 510 511 statement ok 512 SET TRACING=ON; 513 DELETE FROM ab WHERE a IN (4, 5); 514 SET TRACING=OFF 515 516 query TT 517 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 518 WHERE message LIKE '%r32: sending batch%' 519 AND message NOT LIKE '%PushTxn%' 520 AND message NOT LIKE '%QueryTxn%' 521 ---- 522 dist sender send r32: sending batch 1 DelRng to (n1,s1):1 523 524 statement ok 525 ROLLBACK 526 527 # Delete with simple RETURNING statement should auto-commit. 528 query B 529 SELECT count(*) > 0 FROM [ 530 EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (6, 7) RETURNING a, b 531 ] WHERE field = 'auto commit' 532 ---- 533 true 534 535 statement ok 536 SET TRACING=ON; 537 DELETE FROM ab WHERE a IN (6, 7) RETURNING a, b; 538 SET TRACING=OFF 539 540 query TT 541 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 542 WHERE message LIKE '%r32: sending batch%' 543 AND message NOT LIKE '%PushTxn%' 544 AND message NOT LIKE '%QueryTxn%' 545 ---- 546 dist sender send r32: sending batch 1 Scan to (n1,s1):1 547 dist sender send r32: sending batch 2 Del, 1 EndTxn to (n1,s1):1 548 549 # TODO(radu): allow non-side-effecting projections. 550 query B 551 SELECT count(*) > 0 FROM [ 552 EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (8, 9) RETURNING a + b 553 ] WHERE field = 'auto commit' 554 ---- 555 false 556 557 statement ok 558 SET TRACING=ON; 559 DELETE FROM ab WHERE a IN (8, 9) RETURNING a + b; 560 SET TRACING=OFF 561 562 query TT 563 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 564 WHERE message LIKE '%r32: sending batch%' 565 AND message NOT LIKE '%PushTxn%' 566 AND message NOT LIKE '%QueryTxn%' 567 AND operation NOT LIKE '%async%' 568 ---- 569 dist sender send r32: sending batch 1 Scan to (n1,s1):1 570 dist sender send r32: sending batch 2 Del to (n1,s1):1 571 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 572 573 # Insert with RETURNING statement with side-effects should not auto-commit. 574 # In this case division can (in principle) error out. 575 query B 576 SELECT count(*) > 0 FROM [ 577 EXPLAIN (VERBOSE) DELETE FROM ab WHERE a IN (10, 11) RETURNING a / b 578 ] WHERE field = 'auto commit' 579 ---- 580 false 581 582 statement ok 583 SET TRACING=ON; 584 DELETE FROM ab WHERE a IN (10, 11) RETURNING a / b; 585 SET TRACING=OFF 586 587 query TT 588 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 589 WHERE message LIKE '%r32: sending batch%' 590 AND message NOT LIKE '%PushTxn%' 591 AND message NOT LIKE '%QueryTxn%' 592 AND operation NOT LIKE '%async%' 593 ---- 594 dist sender send r32: sending batch 1 Scan to (n1,s1):1 595 dist sender send r32: sending batch 2 Del to (n1,s1):1 596 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 597 598 statement ok 599 INSERT INTO ab VALUES (12, 0); 600 601 # Another way to test the scenario above: generate an error and ensure that the 602 # mutation was not committed. 603 statement error division by zero 604 DELETE FROM ab WHERE a = 12 RETURNING a / b 605 606 query I 607 SELECT count(*) FROM ab WHERE b=0 608 ---- 609 1 610 611 # ----------------------- 612 # Tests with foreign keys 613 # ----------------------- 614 615 statement ok 616 CREATE TABLE fk_parent (p INT PRIMARY KEY, q INT, FAMILY f1 (p, q)); 617 INSERT INTO fk_parent VALUES (1, 10), (2, 20), (3, 30); 618 CREATE TABLE fk_child (a INT, b INT REFERENCES fk_parent(p), FAMILY f1 (a, b)); 619 SET optimizer_foreign_keys = true; 620 SET experimental_optimizer_foreign_key_cascades = true 621 622 # Populate table descriptor cache. 623 statement ok 624 SELECT * FROM fk_parent JOIN fk_child ON p = b 625 626 query B 627 SELECT count(*) > 0 FROM [ 628 EXPLAIN (VERBOSE) INSERT INTO fk_child VALUES (1, 1), (2, 2) 629 ] WHERE field = 'auto commit' 630 ---- 631 false 632 633 statement ok 634 SET TRACING=ON; 635 INSERT INTO fk_child VALUES (1, 1), (2, 2); 636 SET TRACING=OFF 637 638 query TT 639 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 640 WHERE message LIKE '%r32: sending batch%' 641 AND message NOT LIKE '%PushTxn%' 642 AND message NOT LIKE '%QueryTxn%' 643 AND operation NOT LIKE '%async%' 644 ---- 645 dist sender send r32: sending batch 2 CPut, 2 InitPut to (n1,s1):1 646 dist sender send r32: sending batch 2 Scan to (n1,s1):1 647 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 648 649 query B 650 SELECT count(*) > 0 FROM [ 651 EXPLAIN (VERBOSE) UPDATE fk_child SET b=b+1 WHERE a < 2 652 ] WHERE field = 'auto commit' 653 ---- 654 false 655 656 statement ok 657 SET TRACING=ON; 658 UPDATE fk_child SET b=b+1 WHERE a < 2; 659 SET TRACING=OFF 660 661 query TT 662 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 663 WHERE message LIKE '%r32: sending batch%' 664 AND message NOT LIKE '%PushTxn%' 665 AND message NOT LIKE '%QueryTxn%' 666 AND operation NOT LIKE '%async%' 667 ---- 668 dist sender send r32: sending batch 1 Scan to (n1,s1):1 669 dist sender send r32: sending batch 1 Put, 1 CPut, 1 Del to (n1,s1):1 670 dist sender send r32: sending batch 1 Scan to (n1,s1):1 671 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 672 673 query B 674 SELECT count(*) > 0 FROM [ 675 EXPLAIN (VERBOSE) DELETE FROM fk_parent WHERE p = 3 676 ] WHERE field = 'auto commit' 677 ---- 678 false 679 680 681 statement ok 682 SET TRACING=ON; 683 DELETE FROM fk_parent WHERE p = 3; 684 SET TRACING=OFF 685 686 query TT 687 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 688 WHERE message LIKE '%r32: sending batch%' 689 AND message NOT LIKE '%PushTxn%' 690 AND message NOT LIKE '%QueryTxn%' 691 AND operation NOT LIKE '%async%' 692 ---- 693 dist sender send r32: sending batch 1 Scan to (n1,s1):1 694 dist sender send r32: sending batch 1 Del to (n1,s1):1 695 dist sender send r32: sending batch 1 Scan to (n1,s1):1 696 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 697 698 # Test with a single cascade, which should use autocommit. 699 statement ok 700 DROP TABLE fk_child; 701 CREATE TABLE fk_child (a INT, b INT REFERENCES fk_parent(p) ON DELETE CASCADE, FAMILY f1 (a, b)); 702 INSERT INTO fk_child VALUES (1, 1), (2, 2) 703 704 # Populate table descriptor cache. 705 statement ok 706 SELECT * FROM fk_parent JOIN fk_child ON p = b 707 708 statement ok 709 SET TRACING=ON; 710 DELETE FROM fk_parent WHERE p = 2; 711 SET TRACING=OFF 712 713 query TT 714 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 715 WHERE message LIKE '%r32: sending batch%' 716 AND message NOT LIKE '%PushTxn%' 717 AND message NOT LIKE '%QueryTxn%' 718 AND operation NOT LIKE '%async%' 719 ---- 720 dist sender send r32: sending batch 1 Scan to (n1,s1):1 721 dist sender send r32: sending batch 1 Del to (n1,s1):1 722 dist sender send r32: sending batch 1 Scan to (n1,s1):1 723 dist sender send r32: sending batch 2 Del, 1 EndTxn to (n1,s1):1 724 725 # ----------------------- 726 # Multiple mutation tests 727 # ----------------------- 728 query B 729 SELECT count(*) > 0 FROM [ 730 EXPLAIN (VERBOSE) INSERT INTO ab ( 731 SELECT a*10, b*10 FROM [ INSERT INTO ab VALUES (1, 1), (2, 2) RETURNING a, b ] 732 ) 733 ] WHERE field = 'auto commit' 734 ---- 735 false 736 737 statement ok 738 SET TRACING=ON; 739 INSERT INTO ab ( 740 SELECT a*10, b*10 FROM [ INSERT INTO ab VALUES (1, 1), (2, 2) RETURNING a, b ] 741 ); 742 SET TRACING=OFF 743 744 query TT 745 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 746 WHERE message LIKE '%r32: sending batch%' 747 AND message NOT LIKE '%PushTxn%' 748 AND message NOT LIKE '%QueryTxn%' 749 AND operation NOT LIKE '%async%' 750 ---- 751 dist sender send r32: sending batch 2 CPut to (n1,s1):1 752 dist sender send r32: sending batch 2 CPut to (n1,s1):1 753 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1 754 755 query B 756 SELECT count(*) > 0 FROM [ 757 EXPLAIN (VERBOSE) WITH cte AS (INSERT INTO ab VALUES (3, 3), (4, 4) RETURNING a, b) 758 INSERT INTO ab (SELECT a*10, b*10 FROM cte) 759 ] WHERE field = 'auto commit' 760 ---- 761 false 762 763 statement ok 764 SET TRACING=ON; 765 WITH cte AS (INSERT INTO ab VALUES (3, 3), (4, 4) RETURNING a, b) 766 INSERT INTO ab (SELECT a*10, b*10 FROM cte); 767 SET TRACING=OFF 768 769 query TT 770 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 771 WHERE message LIKE '%r32: sending batch%' 772 AND message NOT LIKE '%PushTxn%' 773 AND message NOT LIKE '%QueryTxn%' 774 AND operation NOT LIKE '%async%' 775 ---- 776 dist sender send r32: sending batch 2 CPut to (n1,s1):1 777 dist sender send r32: sending batch 2 CPut to (n1,s1):1 778 dist sender send r32: sending batch 1 EndTxn to (n1,s1):1