github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/select_for_update (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE t (a INT PRIMARY KEY, b INT, FAMILY (a, b)) 5 6 statement ok 7 CREATE TABLE u (a INT PRIMARY KEY, c INT, FAMILY (a, c)) 8 9 statement ok 10 CREATE VIEW v AS SELECT a FROM t AS t2 11 12 # ------------------------------------------------------------------------------ 13 # Basic tests. 14 # ------------------------------------------------------------------------------ 15 16 query TTT 17 EXPLAIN SELECT * FROM t FOR UPDATE 18 ---- 19 · distributed false 20 · vectorized true 21 scan · · 22 · table t@primary 23 · spans FULL SCAN 24 · locking strength for update 25 26 query TTT 27 EXPLAIN SELECT * FROM t FOR NO KEY UPDATE 28 ---- 29 · distributed false 30 · vectorized true 31 scan · · 32 · table t@primary 33 · spans FULL SCAN 34 · locking strength for no key update 35 36 query TTT 37 EXPLAIN SELECT * FROM t FOR SHARE 38 ---- 39 · distributed false 40 · vectorized true 41 scan · · 42 · table t@primary 43 · spans FULL SCAN 44 · locking strength for share 45 46 query TTT 47 EXPLAIN SELECT * FROM t FOR KEY SHARE 48 ---- 49 · distributed false 50 · vectorized true 51 scan · · 52 · table t@primary 53 · spans FULL SCAN 54 · locking strength for key share 55 56 query TTT 57 EXPLAIN SELECT * FROM t FOR KEY SHARE FOR SHARE 58 ---- 59 · distributed false 60 · vectorized true 61 scan · · 62 · table t@primary 63 · spans FULL SCAN 64 · locking strength for share 65 66 query TTT 67 EXPLAIN SELECT * FROM t FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE 68 ---- 69 · distributed false 70 · vectorized true 71 scan · · 72 · table t@primary 73 · spans FULL SCAN 74 · locking strength for no key update 75 76 query TTT 77 EXPLAIN SELECT * FROM t FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE FOR UPDATE 78 ---- 79 · distributed false 80 · vectorized true 81 scan · · 82 · table t@primary 83 · spans FULL SCAN 84 · locking strength for update 85 86 query TTT 87 EXPLAIN SELECT * FROM t FOR UPDATE OF t 88 ---- 89 · distributed false 90 · vectorized true 91 scan · · 92 · table t@primary 93 · spans FULL SCAN 94 · locking strength for update 95 96 query error pgcode 42P01 relation "t2" in FOR UPDATE clause not found in FROM clause 97 EXPLAIN SELECT * FROM t FOR UPDATE OF t2 98 99 query TTT 100 EXPLAIN SELECT 1 FROM t FOR UPDATE OF t 101 ---- 102 · distributed false 103 · vectorized true 104 render · · 105 └── scan · · 106 · table t@primary 107 · spans FULL SCAN 108 · locking strength for update 109 110 query TTT 111 EXPLAIN SELECT * FROM t WHERE a = 1 FOR UPDATE 112 ---- 113 · distributed false 114 · vectorized true 115 scan · · 116 · table t@primary 117 · spans /1-/1/# 118 · locking strength for update 119 120 query TTT 121 EXPLAIN SELECT * FROM t WHERE a = 1 FOR NO KEY UPDATE 122 ---- 123 · distributed false 124 · vectorized true 125 scan · · 126 · table t@primary 127 · spans /1-/1/# 128 · locking strength for no key update 129 130 query TTT 131 EXPLAIN SELECT * FROM t WHERE a = 1 FOR SHARE 132 ---- 133 · distributed false 134 · vectorized true 135 scan · · 136 · table t@primary 137 · spans /1-/1/# 138 · locking strength for share 139 140 query TTT 141 EXPLAIN SELECT * FROM t WHERE a = 1 FOR KEY SHARE 142 ---- 143 · distributed false 144 · vectorized true 145 scan · · 146 · table t@primary 147 · spans /1-/1/# 148 · locking strength for key share 149 150 query TTT 151 EXPLAIN SELECT * FROM t WHERE a = 1 FOR KEY SHARE FOR SHARE 152 ---- 153 · distributed false 154 · vectorized true 155 scan · · 156 · table t@primary 157 · spans /1-/1/# 158 · locking strength for share 159 160 query TTT 161 EXPLAIN SELECT * FROM t WHERE a = 1 FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE 162 ---- 163 · distributed false 164 · vectorized true 165 scan · · 166 · table t@primary 167 · spans /1-/1/# 168 · locking strength for no key update 169 170 query TTT 171 EXPLAIN SELECT * FROM t WHERE a = 1 FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE FOR UPDATE 172 ---- 173 · distributed false 174 · vectorized true 175 scan · · 176 · table t@primary 177 · spans /1-/1/# 178 · locking strength for update 179 180 query TTT 181 EXPLAIN SELECT * FROM t WHERE a = 1 FOR UPDATE OF t 182 ---- 183 · distributed false 184 · vectorized true 185 scan · · 186 · table t@primary 187 · spans /1-/1/# 188 · locking strength for update 189 190 query error pgcode 42P01 relation "t2" in FOR UPDATE clause not found in FROM clause 191 EXPLAIN SELECT * FROM t WHERE a = 1 FOR UPDATE OF t2 192 193 query TTT 194 EXPLAIN SELECT 1 FROM t WHERE a = 1 FOR UPDATE OF t 195 ---- 196 · distributed false 197 · vectorized true 198 render · · 199 └── scan · · 200 · table t@primary 201 · spans /1-/1/# 202 · locking strength for update 203 204 # ------------------------------------------------------------------------------ 205 # Tests with table aliases. 206 # ------------------------------------------------------------------------------ 207 208 query TTT 209 EXPLAIN SELECT * FROM t AS t2 FOR UPDATE 210 ---- 211 · distributed false 212 · vectorized true 213 scan · · 214 · table t@primary 215 · spans FULL SCAN 216 · locking strength for update 217 218 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 219 EXPLAIN SELECT * FROM t AS t2 FOR UPDATE OF t 220 221 query TTT 222 EXPLAIN SELECT * FROM t AS t2 FOR UPDATE OF t2 223 ---- 224 · distributed false 225 · vectorized true 226 scan · · 227 · table t@primary 228 · spans FULL SCAN 229 · locking strength for update 230 231 # ------------------------------------------------------------------------------ 232 # Tests with numeric table references. 233 # Cockroach numeric references start after 53 for user tables. 234 # ------------------------------------------------------------------------------ 235 236 query TTT 237 EXPLAIN SELECT * FROM [53 AS t] FOR UPDATE 238 ---- 239 · distributed false 240 · vectorized true 241 scan · · 242 · table t@primary 243 · spans FULL SCAN 244 · locking strength for update 245 246 query TTT 247 EXPLAIN SELECT * FROM [53 AS t] FOR UPDATE OF t 248 ---- 249 · distributed false 250 · vectorized true 251 scan · · 252 · table t@primary 253 · spans FULL SCAN 254 · locking strength for update 255 256 query error pgcode 42P01 relation "t2" in FOR UPDATE clause not found in FROM clause 257 EXPLAIN SELECT * FROM [53 AS t] FOR UPDATE OF t2 258 259 # ------------------------------------------------------------------------------ 260 # Tests with views. 261 # ------------------------------------------------------------------------------ 262 263 query TTT 264 EXPLAIN SELECT * FROM v FOR UPDATE 265 ---- 266 · distributed false 267 · vectorized true 268 scan · · 269 · table t@primary 270 · spans FULL SCAN 271 · locking strength for update 272 273 query TTT 274 EXPLAIN SELECT * FROM v FOR UPDATE OF v 275 ---- 276 · distributed false 277 · vectorized true 278 scan · · 279 · table t@primary 280 · spans FULL SCAN 281 · locking strength for update 282 283 query error pgcode 42P01 relation "v2" in FOR UPDATE clause not found in FROM clause 284 EXPLAIN SELECT * FROM v FOR UPDATE OF v2 285 286 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 287 EXPLAIN SELECT * FROM v FOR UPDATE OF t 288 289 query error pgcode 42P01 relation "t2" in FOR UPDATE clause not found in FROM clause 290 EXPLAIN SELECT * FROM v FOR UPDATE OF t2 291 292 # ------------------------------------------------------------------------------ 293 # Tests with aliased views. 294 # ------------------------------------------------------------------------------ 295 296 query TTT 297 EXPLAIN SELECT * FROM v AS v2 FOR UPDATE 298 ---- 299 · distributed false 300 · vectorized true 301 scan · · 302 · table t@primary 303 · spans FULL SCAN 304 · locking strength for update 305 306 query error pgcode 42P01 relation "v" in FOR UPDATE clause not found in FROM clause 307 EXPLAIN SELECT * FROM v AS v2 FOR UPDATE OF v 308 309 query TTT 310 EXPLAIN SELECT * FROM v AS v2 FOR UPDATE OF v2 311 ---- 312 · distributed false 313 · vectorized true 314 scan · · 315 · table t@primary 316 · spans FULL SCAN 317 · locking strength for update 318 319 # ------------------------------------------------------------------------------ 320 # Tests with subqueries. 321 # 322 # Row-level locking clauses only apply to subqueries in the FROM clause of a 323 # SELECT statement. They don't apply to subqueries in the projection or in 324 # the filter. 325 # ------------------------------------------------------------------------------ 326 327 query TTT 328 EXPLAIN SELECT * FROM (SELECT a FROM t) FOR UPDATE 329 ---- 330 · distributed false 331 · vectorized true 332 scan · · 333 · table t@primary 334 · spans FULL SCAN 335 · locking strength for update 336 337 query TTT 338 EXPLAIN SELECT * FROM (SELECT a FROM t FOR UPDATE) 339 ---- 340 · distributed false 341 · vectorized true 342 scan · · 343 · table t@primary 344 · spans FULL SCAN 345 · locking strength for update 346 347 query TTT 348 EXPLAIN SELECT * FROM (SELECT a FROM t FOR NO KEY UPDATE) FOR KEY SHARE 349 ---- 350 · distributed false 351 · vectorized true 352 scan · · 353 · table t@primary 354 · spans FULL SCAN 355 · locking strength for no key update 356 357 query TTT 358 EXPLAIN SELECT * FROM (SELECT a FROM t FOR KEY SHARE) FOR NO KEY UPDATE 359 ---- 360 · distributed false 361 · vectorized true 362 scan · · 363 · table t@primary 364 · spans FULL SCAN 365 · locking strength for no key update 366 367 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 368 EXPLAIN SELECT * FROM (SELECT a FROM t) FOR UPDATE OF t 369 370 query TTT 371 EXPLAIN SELECT * FROM (SELECT a FROM t FOR UPDATE OF t) 372 ---- 373 · distributed false 374 · vectorized true 375 scan · · 376 · table t@primary 377 · spans FULL SCAN 378 · locking strength for update 379 380 query TTT 381 EXPLAIN SELECT * FROM (SELECT a FROM t) AS r FOR UPDATE 382 ---- 383 · distributed false 384 · vectorized true 385 scan · · 386 · table t@primary 387 · spans FULL SCAN 388 · locking strength for update 389 390 query TTT 391 EXPLAIN SELECT * FROM (SELECT a FROM t FOR UPDATE) AS r 392 ---- 393 · distributed false 394 · vectorized true 395 scan · · 396 · table t@primary 397 · spans FULL SCAN 398 · locking strength for update 399 400 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 401 EXPLAIN SELECT * FROM (SELECT a FROM t) AS r FOR UPDATE OF t 402 403 query TTT 404 EXPLAIN SELECT * FROM (SELECT a FROM t FOR UPDATE OF t) AS r 405 ---- 406 · distributed false 407 · vectorized true 408 scan · · 409 · table t@primary 410 · spans FULL SCAN 411 · locking strength for update 412 413 query TTT 414 EXPLAIN SELECT (SELECT a FROM t) FOR UPDATE 415 ---- 416 · distributed false 417 · vectorized false 418 root · · 419 ├── values · · 420 │ size 1 column, 1 row 421 └── subquery · · 422 │ id @S1 423 │ original sql (SELECT a FROM t) 424 │ exec mode one row 425 └── max1row · · 426 └── scan · · 427 · table t@primary 428 · spans FULL SCAN 429 430 query TTT 431 EXPLAIN SELECT (SELECT a FROM t FOR UPDATE) 432 ---- 433 · distributed false 434 · vectorized false 435 root · · 436 ├── values · · 437 │ size 1 column, 1 row 438 └── subquery · · 439 │ id @S1 440 │ original sql (SELECT a FROM t FOR UPDATE) 441 │ exec mode one row 442 └── max1row · · 443 └── scan · · 444 · table t@primary 445 · spans FULL SCAN 446 · locking strength for update 447 448 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 449 EXPLAIN SELECT (SELECT a FROM t) FOR UPDATE OF t 450 451 query TTT 452 EXPLAIN SELECT (SELECT a FROM t FOR UPDATE OF t) 453 ---- 454 · distributed false 455 · vectorized false 456 root · · 457 ├── values · · 458 │ size 1 column, 1 row 459 └── subquery · · 460 │ id @S1 461 │ original sql (SELECT a FROM t FOR UPDATE OF t) 462 │ exec mode one row 463 └── max1row · · 464 └── scan · · 465 · table t@primary 466 · spans FULL SCAN 467 · locking strength for update 468 469 query TTT 470 EXPLAIN SELECT (SELECT a FROM t) AS r FOR UPDATE 471 ---- 472 · distributed false 473 · vectorized false 474 root · · 475 ├── values · · 476 │ size 1 column, 1 row 477 └── subquery · · 478 │ id @S1 479 │ original sql (SELECT a FROM t) 480 │ exec mode one row 481 └── max1row · · 482 └── scan · · 483 · table t@primary 484 · spans FULL SCAN 485 486 query TTT 487 EXPLAIN SELECT (SELECT a FROM t FOR UPDATE) AS r 488 ---- 489 · distributed false 490 · vectorized false 491 root · · 492 ├── values · · 493 │ size 1 column, 1 row 494 └── subquery · · 495 │ id @S1 496 │ original sql (SELECT a FROM t FOR UPDATE) 497 │ exec mode one row 498 └── max1row · · 499 └── scan · · 500 · table t@primary 501 · spans FULL SCAN 502 · locking strength for update 503 504 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 505 EXPLAIN SELECT (SELECT a FROM t) AS r FOR UPDATE OF t 506 507 query TTT 508 EXPLAIN SELECT (SELECT a FROM t FOR UPDATE OF t) AS r 509 ---- 510 · distributed false 511 · vectorized false 512 root · · 513 ├── values · · 514 │ size 1 column, 1 row 515 └── subquery · · 516 │ id @S1 517 │ original sql (SELECT a FROM t FOR UPDATE OF t) 518 │ exec mode one row 519 └── max1row · · 520 └── scan · · 521 · table t@primary 522 · spans FULL SCAN 523 · locking strength for update 524 525 query TTT 526 EXPLAIN SELECT * FROM t WHERE a IN (SELECT a FROM t) FOR UPDATE 527 ---- 528 · distributed false 529 · vectorized true 530 merge-join · · 531 │ type semi 532 │ equality (a) = (a) 533 │ left cols are key · 534 │ right cols are key · 535 │ mergeJoinOrder +"(a=a)" 536 ├── scan · · 537 │ table t@primary 538 │ spans FULL SCAN 539 │ locking strength for update 540 └── scan · · 541 · table t@primary 542 · spans FULL SCAN 543 544 query TTT 545 EXPLAIN SELECT * FROM t WHERE a IN (SELECT a FROM t FOR UPDATE) 546 ---- 547 · distributed false 548 · vectorized true 549 merge-join · · 550 │ type semi 551 │ equality (a) = (a) 552 │ left cols are key · 553 │ right cols are key · 554 │ mergeJoinOrder +"(a=a)" 555 ├── scan · · 556 │ table t@primary 557 │ spans FULL SCAN 558 └── scan · · 559 · table t@primary 560 · spans FULL SCAN 561 · locking strength for update 562 563 query TTT 564 EXPLAIN SELECT * FROM t WHERE a IN (SELECT a FROM t) FOR UPDATE OF t 565 ---- 566 · distributed false 567 · vectorized true 568 merge-join · · 569 │ type semi 570 │ equality (a) = (a) 571 │ left cols are key · 572 │ right cols are key · 573 │ mergeJoinOrder +"(a=a)" 574 ├── scan · · 575 │ table t@primary 576 │ spans FULL SCAN 577 │ locking strength for update 578 └── scan · · 579 · table t@primary 580 · spans FULL SCAN 581 582 query TTT 583 EXPLAIN SELECT * FROM t WHERE a IN (SELECT a FROM t FOR UPDATE OF t) 584 ---- 585 · distributed false 586 · vectorized true 587 merge-join · · 588 │ type semi 589 │ equality (a) = (a) 590 │ left cols are key · 591 │ right cols are key · 592 │ mergeJoinOrder +"(a=a)" 593 ├── scan · · 594 │ table t@primary 595 │ spans FULL SCAN 596 └── scan · · 597 · table t@primary 598 · spans FULL SCAN 599 · locking strength for update 600 601 # ------------------------------------------------------------------------------ 602 # Tests with common-table expressions. 603 # 604 # Unlike with FROM subqueries, row-level locking clauses do not apply to WITH 605 # queries referenced by the primary query. To achieve row locking within a WITH 606 # query, a locking clause should be specified within the WITH query. 607 # 608 # Note that scans with locking are considered to be side-effecting; CTEs that 609 # contain locking clauses are not inlined. 610 # ------------------------------------------------------------------------------ 611 612 query TTT 613 EXPLAIN SELECT * FROM [SELECT a FROM t] FOR UPDATE 614 ---- 615 · distributed false 616 · vectorized true 617 render · · 618 └── scan · · 619 · table t@primary 620 · spans FULL SCAN 621 622 query TTT 623 EXPLAIN WITH cte AS (SELECT a FROM t) SELECT * FROM cte FOR UPDATE 624 ---- 625 · distributed false 626 · vectorized true 627 render · · 628 └── scan · · 629 · table t@primary 630 · spans FULL SCAN 631 632 query TTT 633 EXPLAIN SELECT * FROM [SELECT a FROM t FOR UPDATE] 634 ---- 635 · distributed false 636 · vectorized false 637 root · · 638 ├── scan buffer node · · 639 │ label buffer 1 640 └── subquery · · 641 │ id @S1 642 │ original sql SELECT a FROM t FOR UPDATE 643 │ exec mode all rows 644 └── buffer node · · 645 │ label buffer 1 646 └── scan · · 647 · table t@primary 648 · spans FULL SCAN 649 · locking strength for update 650 651 query TTT 652 EXPLAIN WITH cte AS (SELECT a FROM t FOR UPDATE) SELECT * FROM cte 653 ---- 654 · distributed false 655 · vectorized false 656 root · · 657 ├── scan buffer node · · 658 │ label buffer 1 (cte) 659 └── subquery · · 660 │ id @S1 661 │ original sql SELECT a FROM t FOR UPDATE 662 │ exec mode all rows 663 └── buffer node · · 664 │ label buffer 1 (cte) 665 └── scan · · 666 · table t@primary 667 · spans FULL SCAN 668 · locking strength for update 669 670 # Verify that the unused CTE doesn't get eliminated. 671 # TODO(radu): we should at least not buffer the rows in this case. 672 query TTT 673 EXPLAIN WITH sfu AS (SELECT a FROM t FOR UPDATE) 674 SELECT c FROM u 675 ---- 676 · distributed false 677 · vectorized true 678 root · · 679 ├── scan · · 680 │ table u@primary 681 │ spans FULL SCAN 682 └── subquery · · 683 │ id @S1 684 │ original sql SELECT a FROM t FOR UPDATE 685 │ exec mode all rows 686 └── buffer node · · 687 │ label buffer 1 (sfu) 688 └── scan · · 689 · table t@primary 690 · spans FULL SCAN 691 · locking strength for update 692 693 # ------------------------------------------------------------------------------ 694 # Tests with joins. 695 # ------------------------------------------------------------------------------ 696 697 query TTT 698 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR UPDATE 699 ---- 700 · distributed false 701 · vectorized true 702 render · · 703 └── merge-join · · 704 │ type inner 705 │ equality (a) = (a) 706 │ left cols are key · 707 │ right cols are key · 708 │ mergeJoinOrder +"(a=a)" 709 ├── scan · · 710 │ table t@primary 711 │ spans FULL SCAN 712 │ locking strength for update 713 └── scan · · 714 · table u@primary 715 · spans FULL SCAN 716 · locking strength for update 717 718 query TTT 719 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR UPDATE OF t 720 ---- 721 · distributed false 722 · vectorized true 723 render · · 724 └── merge-join · · 725 │ type inner 726 │ equality (a) = (a) 727 │ left cols are key · 728 │ right cols are key · 729 │ mergeJoinOrder +"(a=a)" 730 ├── scan · · 731 │ table t@primary 732 │ spans FULL SCAN 733 │ locking strength for update 734 └── scan · · 735 · table u@primary 736 · spans FULL SCAN 737 738 query TTT 739 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR UPDATE OF u 740 ---- 741 · distributed false 742 · vectorized true 743 render · · 744 └── merge-join · · 745 │ type inner 746 │ equality (a) = (a) 747 │ left cols are key · 748 │ right cols are key · 749 │ mergeJoinOrder +"(a=a)" 750 ├── scan · · 751 │ table t@primary 752 │ spans FULL SCAN 753 └── scan · · 754 · table u@primary 755 · spans FULL SCAN 756 · locking strength for update 757 758 query TTT 759 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR UPDATE OF t, u 760 ---- 761 · distributed false 762 · vectorized true 763 render · · 764 └── merge-join · · 765 │ type inner 766 │ equality (a) = (a) 767 │ left cols are key · 768 │ right cols are key · 769 │ mergeJoinOrder +"(a=a)" 770 ├── scan · · 771 │ table t@primary 772 │ spans FULL SCAN 773 │ locking strength for update 774 └── scan · · 775 · table u@primary 776 · spans FULL SCAN 777 · locking strength for update 778 779 query TTT 780 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR UPDATE OF t FOR SHARE OF u 781 ---- 782 · distributed false 783 · vectorized true 784 render · · 785 └── merge-join · · 786 │ type inner 787 │ equality (a) = (a) 788 │ left cols are key · 789 │ right cols are key · 790 │ mergeJoinOrder +"(a=a)" 791 ├── scan · · 792 │ table t@primary 793 │ spans FULL SCAN 794 │ locking strength for update 795 └── scan · · 796 · table u@primary 797 · spans FULL SCAN 798 · locking strength for share 799 800 query error pgcode 42P01 relation "t2" in FOR UPDATE clause not found in FROM clause 801 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR UPDATE OF t2 FOR SHARE OF u2 802 803 query TTT 804 EXPLAIN SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t2 FOR SHARE OF u2 805 ---- 806 · distributed false 807 · vectorized true 808 render · · 809 └── merge-join · · 810 │ type inner 811 │ equality (a) = (a) 812 │ left cols are key · 813 │ right cols are key · 814 │ mergeJoinOrder +"(a=a)" 815 ├── scan · · 816 │ table t@primary 817 │ spans FULL SCAN 818 │ locking strength for update 819 └── scan · · 820 · table u@primary 821 · spans FULL SCAN 822 · locking strength for share 823 824 query TTT 825 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR KEY SHARE FOR UPDATE 826 ---- 827 · distributed false 828 · vectorized true 829 render · · 830 └── merge-join · · 831 │ type inner 832 │ equality (a) = (a) 833 │ left cols are key · 834 │ right cols are key · 835 │ mergeJoinOrder +"(a=a)" 836 ├── scan · · 837 │ table t@primary 838 │ spans FULL SCAN 839 │ locking strength for update 840 └── scan · · 841 · table u@primary 842 · spans FULL SCAN 843 · locking strength for update 844 845 query TTT 846 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR KEY SHARE FOR NO KEY UPDATE OF t 847 ---- 848 · distributed false 849 · vectorized true 850 render · · 851 └── merge-join · · 852 │ type inner 853 │ equality (a) = (a) 854 │ left cols are key · 855 │ right cols are key · 856 │ mergeJoinOrder +"(a=a)" 857 ├── scan · · 858 │ table t@primary 859 │ spans FULL SCAN 860 │ locking strength for no key update 861 └── scan · · 862 · table u@primary 863 · spans FULL SCAN 864 · locking strength for key share 865 866 query TTT 867 EXPLAIN SELECT * FROM t JOIN u USING (a) FOR SHARE FOR NO KEY UPDATE OF t FOR UPDATE OF u 868 ---- 869 · distributed false 870 · vectorized true 871 render · · 872 └── merge-join · · 873 │ type inner 874 │ equality (a) = (a) 875 │ left cols are key · 876 │ right cols are key · 877 │ mergeJoinOrder +"(a=a)" 878 ├── scan · · 879 │ table t@primary 880 │ spans FULL SCAN 881 │ locking strength for no key update 882 └── scan · · 883 · table u@primary 884 · spans FULL SCAN 885 · locking strength for update 886 887 # ------------------------------------------------------------------------------ 888 # Tests with joins of aliased tables and aliased joins. 889 # ------------------------------------------------------------------------------ 890 891 query TTT 892 EXPLAIN SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE 893 ---- 894 · distributed false 895 · vectorized true 896 render · · 897 └── merge-join · · 898 │ type inner 899 │ equality (a) = (a) 900 │ left cols are key · 901 │ right cols are key · 902 │ mergeJoinOrder +"(a=a)" 903 ├── scan · · 904 │ table t@primary 905 │ spans FULL SCAN 906 │ locking strength for update 907 └── scan · · 908 · table u@primary 909 · spans FULL SCAN 910 · locking strength for update 911 912 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 913 EXPLAIN SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t 914 915 query error pgcode 42P01 relation "u" in FOR UPDATE clause not found in FROM clause 916 EXPLAIN SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF u 917 918 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 919 EXPLAIN SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t, u 920 921 query TTT 922 EXPLAIN SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t2 923 ---- 924 · distributed false 925 · vectorized true 926 render · · 927 └── merge-join · · 928 │ type inner 929 │ equality (a) = (a) 930 │ left cols are key · 931 │ right cols are key · 932 │ mergeJoinOrder +"(a=a)" 933 ├── scan · · 934 │ table t@primary 935 │ spans FULL SCAN 936 │ locking strength for update 937 └── scan · · 938 · table u@primary 939 · spans FULL SCAN 940 941 query TTT 942 EXPLAIN SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF u2 943 ---- 944 · distributed false 945 · vectorized true 946 render · · 947 └── merge-join · · 948 │ type inner 949 │ equality (a) = (a) 950 │ left cols are key · 951 │ right cols are key · 952 │ mergeJoinOrder +"(a=a)" 953 ├── scan · · 954 │ table t@primary 955 │ spans FULL SCAN 956 └── scan · · 957 · table u@primary 958 · spans FULL SCAN 959 · locking strength for update 960 961 query TTT 962 EXPLAIN SELECT * FROM t AS t2 JOIN u AS u2 USING (a) FOR UPDATE OF t2, u2 963 ---- 964 · distributed false 965 · vectorized true 966 render · · 967 └── merge-join · · 968 │ type inner 969 │ equality (a) = (a) 970 │ left cols are key · 971 │ right cols are key · 972 │ mergeJoinOrder +"(a=a)" 973 ├── scan · · 974 │ table t@primary 975 │ spans FULL SCAN 976 │ locking strength for update 977 └── scan · · 978 · table u@primary 979 · spans FULL SCAN 980 · locking strength for update 981 982 # Postgres doesn't support applying locking clauses to joins. The following 983 # queries all return the error: "FOR UPDATE cannot be applied to a join". 984 # We could do the same, but it's not hard to support these, so we do. 985 986 query TTT 987 EXPLAIN SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE 988 ---- 989 · distributed false 990 · vectorized true 991 render · · 992 └── merge-join · · 993 │ type inner 994 │ equality (a) = (a) 995 │ left cols are key · 996 │ right cols are key · 997 │ mergeJoinOrder +"(a=a)" 998 ├── scan · · 999 │ table t@primary 1000 │ spans FULL SCAN 1001 │ locking strength for update 1002 └── scan · · 1003 · table u@primary 1004 · spans FULL SCAN 1005 · locking strength for update 1006 1007 query error pgcode 42P01 relation "t" in FOR UPDATE clause not found in FROM clause 1008 EXPLAIN SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE OF t 1009 1010 query error pgcode 42P01 relation "u" in FOR UPDATE clause not found in FROM clause 1011 EXPLAIN SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE OF u 1012 1013 query error pgcode 42P01 relation "u2" in FOR UPDATE clause not found in FROM clause 1014 EXPLAIN SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE OF u2 1015 1016 query TTT 1017 EXPLAIN SELECT * FROM (t JOIN u AS u2 USING (a)) j FOR UPDATE OF j 1018 ---- 1019 · distributed false 1020 · vectorized true 1021 render · · 1022 └── merge-join · · 1023 │ type inner 1024 │ equality (a) = (a) 1025 │ left cols are key · 1026 │ right cols are key · 1027 │ mergeJoinOrder +"(a=a)" 1028 ├── scan · · 1029 │ table t@primary 1030 │ spans FULL SCAN 1031 │ locking strength for update 1032 └── scan · · 1033 · table u@primary 1034 · spans FULL SCAN 1035 · locking strength for update 1036 1037 # ------------------------------------------------------------------------------ 1038 # Tests with lateral joins. 1039 # ------------------------------------------------------------------------------ 1040 1041 query TTT 1042 EXPLAIN SELECT * FROM t, u FOR UPDATE 1043 ---- 1044 · distributed false 1045 · vectorized true 1046 cross-join · · 1047 │ type cross 1048 ├── scan · · 1049 │ table t@primary 1050 │ spans FULL SCAN 1051 │ locking strength for update 1052 └── scan · · 1053 · table u@primary 1054 · spans FULL SCAN 1055 · locking strength for update 1056 1057 query TTT 1058 EXPLAIN SELECT * FROM t, u FOR UPDATE OF t 1059 ---- 1060 · distributed false 1061 · vectorized true 1062 cross-join · · 1063 │ type cross 1064 ├── scan · · 1065 │ table t@primary 1066 │ spans FULL SCAN 1067 │ locking strength for update 1068 └── scan · · 1069 · table u@primary 1070 · spans FULL SCAN 1071 1072 query TTT 1073 EXPLAIN SELECT * FROM t, u FOR SHARE OF t FOR UPDATE OF u 1074 ---- 1075 · distributed false 1076 · vectorized true 1077 cross-join · · 1078 │ type cross 1079 ├── scan · · 1080 │ table t@primary 1081 │ spans FULL SCAN 1082 │ locking strength for share 1083 └── scan · · 1084 · table u@primary 1085 · spans FULL SCAN 1086 · locking strength for update 1087 1088 query TTT 1089 EXPLAIN SELECT * FROM t, LATERAL (SELECT * FROM u) sub FOR UPDATE 1090 ---- 1091 · distributed false 1092 · vectorized true 1093 cross-join · · 1094 │ type cross 1095 ├── scan · · 1096 │ table t@primary 1097 │ spans FULL SCAN 1098 │ locking strength for update 1099 └── scan · · 1100 · table u@primary 1101 · spans FULL SCAN 1102 · locking strength for update 1103 1104 query error pgcode 42P01 relation "u" in FOR UPDATE clause not found in FROM clause 1105 EXPLAIN SELECT * FROM t, LATERAL (SELECT * FROM u) sub FOR UPDATE OF u 1106 1107 query TTT 1108 EXPLAIN SELECT * FROM t, LATERAL (SELECT * FROM u) sub FOR UPDATE OF sub 1109 ---- 1110 · distributed false 1111 · vectorized true 1112 cross-join · · 1113 │ type cross 1114 ├── scan · · 1115 │ table t@primary 1116 │ spans FULL SCAN 1117 └── scan · · 1118 · table u@primary 1119 · spans FULL SCAN 1120 · locking strength for update