github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/testdata/savepoints (about) 1 # This test exercises the savepoint state in the conn executor. 2 3 subtest implicit_release_at_end 4 5 # It's OK to leave savepoints open when the txn commits. 6 # This releases everything. 7 sql 8 BEGIN 9 SAVEPOINT foo 10 SAVEPOINT bar 11 SAVEPOINT baz 12 COMMIT 13 ---- 14 1: BEGIN -- 0 rows 15 -- NoTxn -> Open #.... (none) 16 2: SAVEPOINT foo -- 0 rows 17 -- Open -> Open ##... foo 18 3: SAVEPOINT bar -- 0 rows 19 -- Open -> Open ###.. foo>bar 20 4: SAVEPOINT baz -- 0 rows 21 -- Open -> Open ####. foo>bar>baz 22 5: COMMIT -- 0 rows 23 -- Open -> NoTxn ##### (none) 24 25 # Ditto rollbacks. 26 sql 27 BEGIN 28 SAVEPOINT foo 29 SAVEPOINT bar 30 SAVEPOINT baz 31 ROLLBACK 32 ---- 33 1: BEGIN -- 0 rows 34 -- NoTxn -> Open #.... (none) 35 2: SAVEPOINT foo -- 0 rows 36 -- Open -> Open ##... foo 37 3: SAVEPOINT bar -- 0 rows 38 -- Open -> Open ###.. foo>bar 39 4: SAVEPOINT baz -- 0 rows 40 -- Open -> Open ####. foo>bar>baz 41 5: ROLLBACK -- 0 rows 42 -- Open -> NoTxn #.... (none) 43 44 subtest end 45 46 subtest savepoint_stack 47 48 sql 49 BEGIN 50 SAVEPOINT foo 51 SAVEPOINT foo 52 SAVEPOINT bar 53 SAVEPOINT baz 54 ROLLBACK TO SAVEPOINT foo 55 SAVEPOINT baz 56 RELEASE SAVEPOINT foo 57 SAVEPOINT bar 58 RELEASE SAVEPOINT foo 59 COMMIT 60 ---- 61 1: BEGIN -- 0 rows 62 -- NoTxn -> Open #.......... (none) 63 2: SAVEPOINT foo -- 0 rows 64 -- Open -> Open ##......... foo 65 3: SAVEPOINT foo -- 0 rows 66 -- Open -> Open ###........ foo>foo 67 4: SAVEPOINT bar -- 0 rows 68 -- Open -> Open ####....... foo>foo>bar 69 5: SAVEPOINT baz -- 0 rows 70 -- Open -> Open #####...... foo>foo>bar>baz 71 6: ROLLBACK TO SAVEPOINT foo -- 0 rows 72 -- Open -> Open ###........ foo>foo 73 7: SAVEPOINT baz -- 0 rows 74 -- Open -> Open ###...#.... foo>foo>baz 75 8: RELEASE SAVEPOINT foo -- 0 rows 76 -- Open -> Open ###...##... foo 77 9: SAVEPOINT bar -- 0 rows 78 -- Open -> Open ###...###.. foo>bar 79 10: RELEASE SAVEPOINT foo -- 0 rows 80 -- Open -> Open ###...####. (none) 81 11: COMMIT -- 0 rows 82 -- Open -> NoTxn ###...##### (none) 83 84 85 subtest end 86 87 subtest savepoint_release_vs_rollback 88 89 # A rollback keeps the savepoint active. 90 sql 91 BEGIN 92 SAVEPOINT foo 93 ROLLBACK TO SAVEPOINT foo 94 ROLLBACK TO SAVEPOINT foo 95 COMMIT 96 ---- 97 1: BEGIN -- 0 rows 98 -- NoTxn -> Open #.... (none) 99 2: SAVEPOINT foo -- 0 rows 100 -- Open -> Open ##... foo 101 3: ROLLBACK TO SAVEPOINT foo -- 0 rows 102 -- Open -> Open ##... foo 103 4: ROLLBACK TO SAVEPOINT foo -- 0 rows 104 -- Open -> Open ##... foo 105 5: COMMIT -- 0 rows 106 -- Open -> NoTxn ##..# (none) 107 108 # A release does not. 109 sql 110 BEGIN 111 SAVEPOINT foo 112 RELEASE SAVEPOINT foo 113 RELEASE SAVEPOINT foo 114 COMMIT 115 ---- 116 1: BEGIN -- 0 rows 117 -- NoTxn -> Open #.... (none) 118 2: SAVEPOINT foo -- 0 rows 119 -- Open -> Open ##... foo 120 3: RELEASE SAVEPOINT foo -- 0 rows 121 -- Open -> Open ###.. (none) 122 4: RELEASE SAVEPOINT foo -- pq: savepoint "foo" does not exist 123 -- Open -> Aborted XXXXX (none) 124 5: COMMIT -- 0 rows 125 -- Aborted -> NoTxn #.... (none) 126 127 subtest end 128 129 130 subtest rollback_after_sql_error 131 132 sql 133 BEGIN 134 SAVEPOINT foo 135 SELECT nonexistent 136 ROLLBACK TO SAVEPOINT foo 137 SELECT 123 138 COMMIT 139 ---- 140 1: BEGIN -- 0 rows 141 -- NoTxn -> Open #..... (none) 142 2: SAVEPOINT foo -- 0 rows 143 -- Open -> Open ##.... foo 144 3: SELECT nonexistent -- pq: column "nonexistent" does not exist 145 -- Open -> Aborted XXXXXX foo 146 4: ROLLBACK TO SAVEPOINT foo -- 0 rows 147 -- Aborted -> Open ##.... foo 148 5: SELECT 123 -- 1 row 149 -- Open -> Open ##..#. foo 150 6: COMMIT -- 0 rows 151 -- Open -> NoTxn ##..## (none) 152 153 subtest end 154 155 subtest rollback_after_dup_error 156 157 sql 158 CREATE TABLE t(x INT UNIQUE) 159 INSERT INTO t(x) VALUES (1) 160 BEGIN 161 SAVEPOINT foo 162 INSERT INTO t(x) VALUES (1) 163 ROLLBACK TO SAVEPOINT foo 164 INSERT INTO t(x) VALUES (2) 165 COMMIT 166 ---- 167 1: CREATE TABLE t(x INT UNIQUE) -- 0 rows 168 -- NoTxn -> NoTxn #....... (none) 169 2: INSERT INTO t(x) VALUES (1) -- 1 row 170 -- NoTxn -> NoTxn ##...... (none) 171 3: BEGIN -- 0 rows 172 -- NoTxn -> Open ###..... (none) 173 4: SAVEPOINT foo -- 0 rows 174 -- Open -> Open ####.... foo 175 5: INSERT INTO t(x) VALUES (1) -- pq: duplicate key value (x)=(1) violates unique constraint "t_x_key" 176 -- Open -> Aborted XXXXXXXX foo 177 6: ROLLBACK TO SAVEPOINT foo -- 0 rows 178 -- Aborted -> Open ####.... foo 179 7: INSERT INTO t(x) VALUES (2) -- 1 row 180 -- Open -> Open ####..#. foo 181 8: COMMIT -- 0 rows 182 -- Open -> NoTxn ####..## (none) 183 184 sql 185 DROP TABLE t 186 ---- 187 1: DROP TABLE t -- 0 rows 188 -- NoTxn -> NoTxn # (none) 189 190 subtest end 191 192 subtest rollback_after_ddl 193 194 subtest rollback_after_ddl/release_normal_savepoint 195 196 # DDL under savepoints is fine as long as there is no rollback. 197 # Note: we do two DDL; the first one is there just to anchor 198 # the txn on the config range. The second DDL is the one 199 # exercised in the test. 200 sql 201 BEGIN; CREATE TABLE unused(x INT) 202 SAVEPOINT foo 203 CREATE TABLE t(x INT) 204 RELEASE SAVEPOINT foo 205 COMMIT 206 ---- 207 1: BEGIN; CREATE TABLE unused(x INT) -- 0 rows 208 -- NoTxn -> Open #.... (none) 209 2: SAVEPOINT foo -- 0 rows 210 -- Open -> Open ##... foo 211 3: CREATE TABLE t(x INT) -- 0 rows 212 -- Open -> Open ###.. foo 213 4: RELEASE SAVEPOINT foo -- 0 rows 214 -- Open -> Open ####. (none) 215 5: COMMIT -- 0 rows 216 -- Open -> NoTxn ##### (none) 217 218 sql 219 DROP TABLE unused; DROP TABLE t 220 ---- 221 1: DROP TABLE unused; DROP TABLE t -- 0 rows 222 -- NoTxn -> NoTxn # (none) 223 224 # Also fine at high priority. 225 226 sql 227 BEGIN TRANSACTION PRIORITY HIGH; CREATE TABLE unused(x INT) 228 SAVEPOINT foo 229 CREATE TABLE t(x INT) 230 RELEASE SAVEPOINT foo 231 COMMIT 232 ---- 233 1: BEGIN TRANSACTION PRIORITY HIGH; CREATE TABLE unused(x INT) -- 0 rows 234 -- NoTxn -> Open #.... (none) 235 2: SAVEPOINT foo -- 0 rows 236 -- Open -> Open ##... foo 237 3: CREATE TABLE t(x INT) -- 0 rows 238 -- Open -> Open ###.. foo 239 4: RELEASE SAVEPOINT foo -- 0 rows 240 -- Open -> Open ####. (none) 241 5: COMMIT -- 0 rows 242 -- Open -> NoTxn ##### (none) 243 244 sql 245 DROP TABLE unused; DROP TABLE t 246 ---- 247 1: DROP TABLE unused; DROP TABLE t -- 0 rows 248 -- NoTxn -> NoTxn # (none) 249 250 subtest end 251 252 subtest rollback_after_ddl/restart_savepoint 253 254 # DDL under a cockroach_restart savepoint can 255 # be rolled back. 256 sql 257 BEGIN; SAVEPOINT cockroach_restart; CREATE TABLE t(x INT) 258 INSERT INTO t(x) VALUES (1), (2) 259 ROLLBACK TO SAVEPOINT cockroach_restart 260 CREATE TABLE t(x INT) 261 INSERT INTO t(x) VALUES (3) 262 COMMIT 263 SELECT * FROM t 264 ---- 265 1: BEGIN; SAVEPOINT cockroach_restart; CREATE TABLE t(x INT) -- 0 rows 266 -- NoTxn -> Open #...... cockroach_restart(r) 267 2: INSERT INTO t(x) VALUES (1), (2) -- 2 rows 268 -- Open -> Open ##..... cockroach_restart(r) 269 3: ROLLBACK TO SAVEPOINT cockroach_restart -- 0 rows 270 -- Open -> Open #...... cockroach_restart(r) 271 4: CREATE TABLE t(x INT) -- 0 rows 272 -- Open -> Open #..#... cockroach_restart(r) 273 5: INSERT INTO t(x) VALUES (3) -- 1 row 274 -- Open -> Open #..##.. cockroach_restart(r) 275 6: COMMIT -- 0 rows 276 -- Open -> NoTxn #..###. (none) 277 7: SELECT * FROM t -- 1 row 278 -- NoTxn -> NoTxn #..#### (none) 279 280 sql 281 DROP TABLE t 282 ---- 283 1: DROP TABLE t -- 0 rows 284 -- NoTxn -> NoTxn # (none) 285 286 # DDL under a cockroach_restart savepoint cannot 287 # be rolled back at high priority, because of #46414. 288 sql 289 BEGIN TRANSACTION PRIORITY HIGH; SAVEPOINT cockroach_restart; CREATE TABLE t(x INT) 290 INSERT INTO t(x) VALUES (1), (2) 291 ROLLBACK TO SAVEPOINT cockroach_restart 292 ABORT 293 ---- 294 1: BEGIN TRANSACTION PRIORITY HIGH; SAVEPOINT cockroach_restart; CREATE TABLE t(x INT) -- 0 rows 295 -- NoTxn -> Open #... cockroach_restart(r) 296 2: INSERT INTO t(x) VALUES (1), (2) -- 2 rows 297 -- Open -> Open ##.. cockroach_restart(r) 298 3: ROLLBACK TO SAVEPOINT cockroach_restart -- pq: unimplemented: cannot use ROLLBACK TO SAVEPOINT in a HIGH PRIORITY transaction containing DDL 299 -- Open -> Aborted XXXX cockroach_restart(r) 300 4: ABORT -- 0 rows 301 -- Aborted -> NoTxn #... (none) 302 303 # Same error in the aborted state. 304 sql 305 BEGIN TRANSACTION PRIORITY HIGH; SAVEPOINT cockroach_restart; CREATE TABLE t(x INT) 306 INSERT INTO t(x) VALUES (1), (2) 307 SELECT undefined 308 ROLLBACK TO SAVEPOINT cockroach_restart 309 ABORT 310 ---- 311 1: BEGIN TRANSACTION PRIORITY HIGH; SAVEPOINT cockroach_restart; CREATE TABLE t(x INT) -- 0 rows 312 -- NoTxn -> Open #.... cockroach_restart(r) 313 2: INSERT INTO t(x) VALUES (1), (2) -- 2 rows 314 -- Open -> Open ##... cockroach_restart(r) 315 3: SELECT undefined -- pq: column "undefined" does not exist 316 -- Open -> Aborted XXXXX cockroach_restart(r) 317 4: ROLLBACK TO SAVEPOINT cockroach_restart -- pq: unimplemented: cannot use ROLLBACK TO SAVEPOINT in a HIGH PRIORITY transaction containing DDL 318 -- Aborted -> Aborted XXXXX cockroach_restart(r) 319 5: ABORT -- 0 rows 320 -- Aborted -> NoTxn #.... (none) 321 322 323 # However it's fine if there's just a release. 324 325 sql 326 BEGIN TRANSACTION PRIORITY HIGH; SAVEPOINT cockroach_restart; CREATE TABLE t(x INT) 327 INSERT INTO t(x) VALUES (1), (2) 328 RELEASE SAVEPOINT cockroach_restart 329 COMMIT 330 SELECT * FROM t 331 ---- 332 1: BEGIN TRANSACTION PRIORITY HIGH; SAVEPOINT cockroach_restart; CREATE TABLE t(x INT) -- 0 rows 333 -- NoTxn -> Open #.... cockroach_restart(r) 334 2: INSERT INTO t(x) VALUES (1), (2) -- 2 rows 335 -- Open -> Open ##... cockroach_restart(r) 336 3: RELEASE SAVEPOINT cockroach_restart -- 0 rows 337 -- Open -> CommitWait XXXXX (none) 338 4: COMMIT -- 0 rows 339 -- CommitWait -> NoTxn ###.. (none) 340 5: SELECT * FROM t -- 2 rows 341 -- NoTxn -> NoTxn ###.# (none) 342 343 sql 344 DROP TABLE t 345 ---- 346 1: DROP TABLE t -- 0 rows 347 -- NoTxn -> NoTxn # (none) 348 349 subtest end 350 351 subtest rollback_after_ddl/regular_savepoint 352 353 # Rollback of regular savepoint is unsupported after DDL for now. 354 # TODO(knz): Lift this limitation. 355 356 sql 357 BEGIN; CREATE TABLE unused(x INT) 358 SAVEPOINT foo 359 CREATE TABLE t(x INT) 360 ROLLBACK TO SAVEPOINT foo 361 ---- 362 1: BEGIN; CREATE TABLE unused(x INT) -- 0 rows 363 -- NoTxn -> Open #... (none) 364 2: SAVEPOINT foo -- 0 rows 365 -- Open -> Open ##.. foo 366 3: CREATE TABLE t(x INT) -- 0 rows 367 -- Open -> Open ###. foo 368 4: ROLLBACK TO SAVEPOINT foo -- pq: unimplemented: ROLLBACK TO SAVEPOINT not yet supported after DDL statements 369 -- Open -> Aborted XXXX foo 370 371 # Ditto in aborted state. 372 sql 373 BEGIN; CREATE TABLE unused(x INT) 374 SAVEPOINT foo 375 CREATE TABLE t(x INT) 376 SELECT undefined 377 ROLLBACK TO SAVEPOINT foo 378 ---- 379 1: BEGIN; CREATE TABLE unused(x INT) -- 0 rows 380 -- NoTxn -> Open #.... (none) 381 2: SAVEPOINT foo -- 0 rows 382 -- Open -> Open ##... foo 383 3: CREATE TABLE t(x INT) -- 0 rows 384 -- Open -> Open ###.. foo 385 4: SELECT undefined -- pq: column "undefined" does not exist 386 -- Open -> Aborted XXXXX foo 387 5: ROLLBACK TO SAVEPOINT foo -- pq: unimplemented: ROLLBACK TO SAVEPOINT not yet supported after DDL statements 388 -- Aborted -> Aborted XXXXX foo 389 390 391 subtest end 392 393 subtest end 394 395 subtest invalid_uses 396 397 sql 398 SAVEPOINT foo 399 ROLLBACK TO SAVEPOINT foo 400 RELEASE SAVEPOINT foo 401 ---- 402 1: SAVEPOINT foo -- pq: there is no transaction in progress 403 -- NoTxn -> NoTxn #.. (none) 404 2: ROLLBACK TO SAVEPOINT foo -- pq: savepoint "foo" does not exist 405 -- NoTxn -> NoTxn ##. (none) 406 3: RELEASE SAVEPOINT foo -- pq: there is no transaction in progress 407 -- NoTxn -> NoTxn ### (none) 408 409 sql 410 BEGIN 411 SAVEPOINT foo 412 RELEASE SAVEPOINT bar 413 ---- 414 1: BEGIN -- 0 rows 415 -- NoTxn -> Open #.. (none) 416 2: SAVEPOINT foo -- 0 rows 417 -- Open -> Open ##. foo 418 3: RELEASE SAVEPOINT bar -- pq: savepoint "bar" does not exist 419 -- Open -> Aborted XXX foo 420 421 sql 422 BEGIN 423 SAVEPOINT foo 424 ROLLBACK TO SAVEPOINT bar 425 ---- 426 1: BEGIN -- 0 rows 427 -- NoTxn -> Open #.. (none) 428 2: SAVEPOINT foo -- 0 rows 429 -- Open -> Open ##. foo 430 3: ROLLBACK TO SAVEPOINT bar -- pq: savepoint "bar" does not exist 431 -- Open -> Aborted XXX foo 432 433 subtest end 434 435 subtest rollback_after_error 436 437 # check that we can rollback after an error 438 sql 439 BEGIN; SAVEPOINT foo 440 SELECT * FROM bogus_name 441 ROLLBACK TO SAVEPOINT foo 442 ROLLBACK 443 ---- 444 1: BEGIN; SAVEPOINT foo -- 0 rows 445 -- NoTxn -> Open #... foo(r) 446 2: SELECT * FROM bogus_name -- pq: relation "bogus_name" does not exist 447 -- Open -> Aborted XXXX foo(r) 448 3: ROLLBACK TO SAVEPOINT foo -- 0 rows 449 -- Aborted -> Open #... foo(r) 450 4: ROLLBACK -- 0 rows 451 -- Open -> NoTxn #... (none) 452 453 # check that we can rollback after a retriable error to an initial savepoint 454 sql 455 CREATE TABLE t(x INT) 456 BEGIN; SAVEPOINT init; INSERT INTO t(x) VALUES (1) 457 SELECT crdb_internal.force_retry('1h') 458 ROLLBACK TO SAVEPOINT init 459 SELECT x from t 460 ROLLBACK; DROP TABLE t 461 ---- 462 1: CREATE TABLE t(x INT) -- 0 rows 463 -- NoTxn -> NoTxn #..... (none) 464 2: BEGIN; SAVEPOINT init; INSERT INTO t(x) VALUES (1) -- 1 row 465 -- NoTxn -> Open ##.... init(r) 466 3: SELECT crdb_internal.force_retry('1h') -- pq: restart transaction: crdb_internal.force_retry(): TransactionRetryWithProtoRefreshError: forced by crdb_internal.force_retry() 467 -- Open -> Aborted XXXXXX init(r) 468 4: ROLLBACK TO SAVEPOINT init -- 0 rows 469 -- Aborted -> Open ##.... init(r) 470 5: SELECT x from t -- 0 rows 471 -- Open -> Open ##..#. init(r) 472 6: ROLLBACK; DROP TABLE t -- 0 rows 473 -- Open -> NoTxn ##.... (none) 474 475 # Check that, after a retriable error, rolling back to anything other than an 476 # initial savepoint fails with a retriable error. 477 sql 478 CREATE TABLE t(x INT) 479 BEGIN; SAVEPOINT init; SELECT count(1) from t; SAVEPOINT inner_savepoint 480 SELECT crdb_internal.force_retry('1h') 481 ROLLBACK TO SAVEPOINT inner_savepoint 482 ROLLBACK TO SAVEPOINT init 483 ROLLBACK; DROP TABLE t 484 ---- 485 1: CREATE TABLE t(x INT) -- 0 rows 486 -- NoTxn -> NoTxn #..... (none) 487 2: BEGIN; SAVEPOINT init; SELECT count(1) from t; SAVEPOINT inner_savepoint -- 0 rows 488 -- NoTxn -> Open ##.... init(r)>inner_savepoint 489 3: SELECT crdb_internal.force_retry('1h') -- pq: restart transaction: crdb_internal.force_retry(): TransactionRetryWithProtoRefreshError: forced by crdb_internal.force_retry() 490 -- Open -> Aborted XXXXXX init(r)>inner_savepoint 491 4: ROLLBACK TO SAVEPOINT inner_savepoint -- pq: restart transaction: TransactionRetryWithProtoRefreshError: cannot rollback to savepoint after a transaction restart 492 -- Aborted -> Aborted XXXXXX init(r)>inner_savepoint 493 5: ROLLBACK TO SAVEPOINT init -- 0 rows 494 -- Aborted -> Open ##.... init(r) 495 6: ROLLBACK; DROP TABLE t -- 0 rows 496 -- Open -> NoTxn ##.... (none) 497 498 subtest end 499 500 501 subtest restart 502 503 subtest restart/must_be_first_in_txn 504 505 sql 506 CREATE TABLE t(x INT) 507 BEGIN 508 INSERT INTO t(x) VALUES (1) 509 SAVEPOINT cockroach_restart 510 ---- 511 1: CREATE TABLE t(x INT) -- 0 rows 512 -- NoTxn -> NoTxn #... (none) 513 2: BEGIN -- 0 rows 514 -- NoTxn -> Open ##.. (none) 515 3: INSERT INTO t(x) VALUES (1) -- 1 row 516 -- Open -> Open ###. (none) 517 4: SAVEPOINT cockroach_restart -- pq: SAVEPOINT "cockroach_restart" needs to be the first statement in a transaction 518 -- Open -> Aborted XXXX (none) 519 520 sql 521 DROP TABLE t 522 ---- 523 1: DROP TABLE t -- 0 rows 524 -- NoTxn -> NoTxn # (none) 525 526 subtest end 527 528 subtest restart/release_without_savepoint 529 530 sql 531 BEGIN 532 RELEASE SAVEPOINT cockroach_restart 533 ROLLBACK 534 ---- 535 1: BEGIN -- 0 rows 536 -- NoTxn -> Open #.. (none) 537 2: RELEASE SAVEPOINT cockroach_restart -- pq: savepoint "cockroach_restart" does not exist 538 -- Open -> Aborted XXX (none) 539 3: ROLLBACK -- 0 rows 540 -- Aborted -> NoTxn #.. (none) 541 542 subtest end 543 544 subtest restart/rollback_without_savepoint 545 546 # ROLLBACK TO SAVEPOINT in an open txn without a SAVEPOINT. 547 sql 548 BEGIN 549 ROLLBACK TO SAVEPOINT cockroach_restart 550 ---- 551 1: BEGIN -- 0 rows 552 -- NoTxn -> Open #. (none) 553 2: ROLLBACK TO SAVEPOINT cockroach_restart -- pq: savepoint "cockroach_restart" does not exist 554 -- Open -> Aborted XX (none) 555 556 # ROLLBACK TO SAVEPOINT in an aborted txn without a SAVEPOINT. 557 sql 558 BEGIN 559 SELECT * FROM bogus_name 560 ROLLBACK TO SAVEPOINT cockroach_restart 561 ---- 562 1: BEGIN -- 0 rows 563 -- NoTxn -> Open #.. (none) 564 2: SELECT * FROM bogus_name -- pq: relation "bogus_name" does not exist 565 -- Open -> Aborted XXX (none) 566 3: ROLLBACK TO SAVEPOINT cockroach_restart -- pq: savepoint "cockroach_restart" does not exist 567 -- Aborted -> Aborted XXX (none) 568 569 subtest end 570 571 subtest restart/rollbacks 572 573 sql 574 CREATE TABLE t(x INT); 575 BEGIN; SAVEPOINT cockroach_restart 576 ROLLBACK TO SAVEPOINT cockroach_restart 577 ROLLBACK TO SAVEPOINT cockroach_restart 578 INSERT INTO t(x) VALUES (1) 579 ROLLBACK TO SAVEPOINT cockroach_restart 580 COMMIT 581 ---- 582 1: CREATE TABLE t(x INT); -- 0 rows 583 -- NoTxn -> NoTxn #...... (none) 584 2: BEGIN; SAVEPOINT cockroach_restart -- 0 rows 585 -- NoTxn -> Open ##..... cockroach_restart(r) 586 3: ROLLBACK TO SAVEPOINT cockroach_restart -- 0 rows 587 -- Open -> Open ##..... cockroach_restart(r) 588 4: ROLLBACK TO SAVEPOINT cockroach_restart -- 0 rows 589 -- Open -> Open ##..... cockroach_restart(r) 590 5: INSERT INTO t(x) VALUES (1) -- 1 row 591 -- Open -> Open ##..#.. cockroach_restart(r) 592 6: ROLLBACK TO SAVEPOINT cockroach_restart -- 0 rows 593 -- Open -> Open ##..... cockroach_restart(r) 594 7: COMMIT -- 0 rows 595 -- Open -> NoTxn ##....# (none) 596 597 sql 598 DROP TABLE t 599 ---- 600 1: DROP TABLE t -- 0 rows 601 -- NoTxn -> NoTxn # (none) 602 603 subtest end 604 605 606 subtest restart/savepoint_under_restart 607 608 sql 609 BEGIN; SAVEPOINT cockroach_restart 610 SAVEPOINT foo 611 SAVEPOINT bar 612 ROLLBACK TO SAVEPOINT foo 613 SELECT crdb_internal.force_retry('1h') 614 ROLLBACK TO SAVEPOINT cockroach_restart 615 SELECT 123 616 COMMIT 617 ---- 618 1: BEGIN; SAVEPOINT cockroach_restart -- 0 rows 619 -- NoTxn -> Open #....... cockroach_restart(r) 620 2: SAVEPOINT foo -- 0 rows 621 -- Open -> Open ##...... cockroach_restart(r)>foo 622 3: SAVEPOINT bar -- 0 rows 623 -- Open -> Open ###..... cockroach_restart(r)>foo>bar 624 4: ROLLBACK TO SAVEPOINT foo -- 0 rows 625 -- Open -> Open ##...... cockroach_restart(r)>foo 626 5: SELECT crdb_internal.force_retry('1h') -- pq: restart transaction: crdb_internal.force_retry(): TransactionRetryWithProtoRefreshError: forced by crdb_internal.force_retry() 627 -- Open -> Aborted XXXXXXXX cockroach_restart(r)>foo 628 6: ROLLBACK TO SAVEPOINT cockroach_restart -- 0 rows 629 -- Aborted -> Open #....... cockroach_restart(r) 630 7: SELECT 123 -- 1 row 631 -- Open -> Open #.....#. cockroach_restart(r) 632 8: COMMIT -- 0 rows 633 -- Open -> NoTxn #.....## (none) 634 635 subtest end 636 637 subtest restart/all_savepoints_disabled 638 639 # Under "force_savepoint_restart", every savepoint 640 # is a restart savepoint. 641 642 sql 643 SET force_savepoint_restart = true 644 BEGIN; SAVEPOINT foo 645 SAVEPOINT bar 646 ---- 647 1: SET force_savepoint_restart = true -- 0 rows 648 -- NoTxn -> NoTxn #.. (none) 649 2: BEGIN; SAVEPOINT foo -- 0 rows 650 -- NoTxn -> Open ##. foo(r) 651 3: SAVEPOINT bar -- pq: SAVEPOINT "cockroach_restart" cannot be nested 652 -- Open -> Aborted XXX foo(r) 653 654 sql 655 SET force_savepoint_restart = false 656 ---- 657 1: SET force_savepoint_restart = false -- 0 rows 658 -- NoTxn -> NoTxn # (none) 659 660 subtest end 661 662 subtest restart/cockroach_restart_cant_be_nested 663 664 sql 665 BEGIN 666 SAVEPOINT foo 667 SAVEPOINT cockroach_restart 668 ROLLBACK 669 ---- 670 1: BEGIN -- 0 rows 671 -- NoTxn -> Open #... (none) 672 2: SAVEPOINT foo -- 0 rows 673 -- Open -> Open ##.. foo 674 3: SAVEPOINT cockroach_restart -- pq: SAVEPOINT "cockroach_restart" cannot be nested 675 -- Open -> Aborted XXXX foo 676 4: ROLLBACK -- 0 rows 677 -- Aborted -> NoTxn #... (none) 678 679 # Check the behavior of issuing "SAVEPOINT cockroach_restart". Multiple times. 680 # That is allowed (to facilitate SAVEPOINT cr; ROLLBACK TO cr; SAVEPOINT cr), 681 # but we're not actually creating multiple savepoints with the same name because 682 # the special release semantics don't allow us. 683 sql 684 BEGIN; SAVEPOINT cockroach_restart; SAVEPOINT cockroach_restart 685 RELEASE SAVEPOINT cockroach_restart 686 ROLLBACK TO SAVEPOINT cockroach_restart 687 ---- 688 1: BEGIN; SAVEPOINT cockroach_restart; SAVEPOINT cockroach_restart -- 0 rows 689 -- NoTxn -> Open #.. cockroach_restart(r) 690 2: RELEASE SAVEPOINT cockroach_restart -- 0 rows 691 -- Open -> CommitWait XXX (none) 692 3: ROLLBACK TO SAVEPOINT cockroach_restart -- pq: current transaction is committed, commands ignored until end of transaction block 693 -- CommitWait -> CommitWait XXX (none) 694 695 # Test that cockroach_restart doesn't nest in the same way that regular 696 # savepoints do. We allow the savepoint cockroach_restart to be redeclared after 697 # a rollback to cockroach_restart (or even immediately after declaring it the 698 # first time), and this redeclaration doesn't introduce a new savepoint. 699 sql 700 BEGIN; SAVEPOINT cockroach_restart; SAVEPOINT cockroach_restart; 701 ROLLBACK TO cockroach_restart; SAVEPOINT cockroach_restart; 702 COMMIT; 703 ---- 704 1: BEGIN; SAVEPOINT cockroach_restart; SAVEPOINT cockroach_restart; -- 0 rows 705 -- NoTxn -> Open #.. cockroach_restart(r) 706 2: ROLLBACK TO cockroach_restart; SAVEPOINT cockroach_restart; -- 0 rows 707 -- Open -> Open #.. cockroach_restart(r) 708 3: COMMIT; -- 0 rows 709 -- Open -> NoTxn #.# (none) 710 711 subtest end 712 713 # Test that the rewinding we do when performing an automatic retry restores the 714 # savepoint stack properly. 715 subtest restart/rewind_on_automatic_restarts 716 717 # We're going to generate a retriable error that will rewind us back to the 718 # SELECT statement (not to the original SAVEPOINT statement since that one is 719 # special and we advance the rewind position past it). The test checks that, 720 # after every restart, the RELEASE works because the savepoint has be 721 # re-instituted before we rewind. 722 sql 723 BEGIN; SAVEPOINT a; SELECT 42; RELEASE a; SELECT crdb_internal.force_retry('10ms'); COMMIT; 724 ---- 725 1: BEGIN; SAVEPOINT a; SELECT 42; RELEASE a; SELECT crdb_internal.force_retry('10ms'); COMMIT; -- 0 rows 726 -- NoTxn -> NoTxn # (none) 727 728 subtest end 729 730 subtest restart/txn_done_after_release_restart 731 732 sql 733 BEGIN; SAVEPOINT cockroach_restart 734 SELECT 1 735 RELEASE SAVEPOINT cockroach_restart 736 SELECT 2 737 ---- 738 1: BEGIN; SAVEPOINT cockroach_restart -- 0 rows 739 -- NoTxn -> Open #... cockroach_restart(r) 740 2: SELECT 1 -- 1 row 741 -- Open -> Open ##.. cockroach_restart(r) 742 3: RELEASE SAVEPOINT cockroach_restart -- 0 rows 743 -- Open -> CommitWait XXXX (none) 744 4: SELECT 2 -- pq: current transaction is committed, commands ignored until end of transaction block 745 -- CommitWait -> CommitWait XXXX (none) 746 747 # In contrast, it's OK to continue work after a RELEASE of a 748 # non-restart savepoint. 749 sql 750 BEGIN; SAVEPOINT some_other_restart 751 SELECT 1 752 RELEASE SAVEPOINT some_other_restart 753 SELECT 2 754 ---- 755 1: BEGIN; SAVEPOINT some_other_restart -- 0 rows 756 -- NoTxn -> Open #... some_other_restart(r) 757 2: SELECT 1 -- 1 row 758 -- Open -> Open ##.. some_other_restart(r) 759 3: RELEASE SAVEPOINT some_other_restart -- 0 rows 760 -- Open -> Open ###. (none) 761 4: SELECT 2 -- 1 row 762 -- Open -> Open #### (none) 763 764 765 subtest end 766 767 subtest end