github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/schema_change_in_txn (about) 1 # LogicTest: !3node-tenant 2 # Disable automatic stats to avoid flakiness (sometimes causes retry errors). 3 statement ok 4 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false 5 6 subtest create_and_add_fk_in_same_txn 7 8 statement ok 9 BEGIN 10 11 statement ok 12 CREATE TABLE test.parent (id int primary key) 13 14 statement ok 15 INSERT INTO test.parent values (1) 16 17 statement ok 18 CREATE TABLE test.child (id int primary key, parent_id int) 19 20 # The index on parent_id is added automatically because test.child is empty 21 statement ok 22 ALTER TABLE test.child ADD CONSTRAINT fk_child_parent_id FOREIGN KEY (parent_id) REFERENCES test.parent (id); 23 24 statement ok 25 INSERT INTO test.child VALUES (1, 1) 26 27 # Check that the auto-created index is visible 28 query II rowsort 29 SELECT * FROM test.child@child_auto_index_fk_child_parent_id 30 ---- 31 1 1 32 33 statement ok 34 COMMIT 35 36 # Verify that the constraint is unvalidated, which is a limitation of adding the 37 # constraint in the same transaction as CREATE TABLE. 38 # TODO (lucy): Add a job to validate the table in this situation. 39 query TTTTB 40 SHOW CONSTRAINTS FROM test.child 41 ---- 42 child fk_child_parent_id FOREIGN KEY FOREIGN KEY (parent_id) REFERENCES parent(id) false 43 child primary PRIMARY KEY PRIMARY KEY (id ASC) true 44 45 statement ok 46 ALTER TABLE test.child VALIDATE CONSTRAINT fk_child_parent_id 47 48 query TTTTB 49 SHOW CONSTRAINTS FROM test.child 50 ---- 51 child fk_child_parent_id FOREIGN KEY FOREIGN KEY (parent_id) REFERENCES parent(id) true 52 child primary PRIMARY KEY PRIMARY KEY (id ASC) true 53 54 statement ok 55 DROP TABLE test.child, test.parent 56 57 subtest create_and_add_fk_in_separate_txns 58 59 statement ok 60 CREATE TABLE test.parent (id int primary key) 61 62 statement ok 63 INSERT INTO test.parent values (1) 64 65 statement ok 66 CREATE TABLE test.child (id int primary key, parent_id int) 67 68 statement ok 69 BEGIN 70 71 # The index on parent_id is added automatically because test.child is empty 72 statement ok 73 ALTER TABLE test.child ADD CONSTRAINT fk_child_parent_id FOREIGN KEY (parent_id) REFERENCES test.parent (id); 74 75 statement ok 76 INSERT INTO test.child VALUES (1, 1) 77 78 statement ok 79 COMMIT 80 81 # Check that the auto-created index is visible 82 query II rowsort 83 SELECT * FROM test.child@child_auto_index_fk_child_parent_id 84 ---- 85 1 1 86 87 # Verify that the constraint is validated. 88 query TTTTB 89 SHOW CONSTRAINTS FROM test.child 90 ---- 91 child fk_child_parent_id FOREIGN KEY FOREIGN KEY (parent_id) REFERENCES parent(id) true 92 child primary PRIMARY KEY PRIMARY KEY (id ASC) true 93 94 statement ok 95 DROP TABLE test.child, test.parent 96 97 subtest auto_add_fk_with_composite_index_to_empty_table 98 99 statement ok 100 BEGIN 101 102 statement ok 103 CREATE TABLE parent_composite_index (a_id INT NOT NULL, b_id INT NOT NULL, PRIMARY KEY (a_id, b_id)) 104 105 statement ok 106 CREATE TABLE child_composite_index (id SERIAL NOT NULL, parent_a_id INT, parent_b_id INT, PRIMARY KEY (id)) 107 108 # The (composite) index needed for the fk constraint is automatically added because the table is empty 109 statement ok 110 ALTER TABLE child_composite_index ADD CONSTRAINT fk_id FOREIGN KEY (parent_a_id, parent_b_id) REFERENCES parent_composite_index; 111 112 statement ok 113 INSERT INTO parent_composite_index VALUES (100, 200) 114 115 statement ok 116 INSERT INTO child_composite_index VALUES (1, 100, 200) 117 118 # Check that the auto-created index is visible 119 query III rowsort 120 SELECT * FROM child_composite_index@child_composite_index_auto_index_fk_id 121 ---- 122 1 100 200 123 124 statement ok 125 COMMIT 126 127 statement ok 128 DROP TABLE parent_composite_index, child_composite_index 129 130 subtest auto_add_fk_to_nonempty_table_error 131 132 statement ok 133 BEGIN 134 135 statement ok 136 CREATE TABLE nonempty_a (id SERIAL NOT NULL, self_id INT, b_id INT NOT NULL, PRIMARY KEY (id)) 137 138 statement ok 139 CREATE TABLE nonempty_b (id SERIAL NOT NULL, PRIMARY KEY (id)) 140 141 statement ok 142 INSERT INTO nonempty_b VALUES (1), (2), (3); 143 144 statement ok 145 INSERT INTO nonempty_a VALUES (1, NULL, 1) 146 147 # Fails because self_id is not indexed, and an index will not be automatically created because the table is nonempty 148 statement error foreign key requires an existing index on columns \("self_id"\) 149 ALTER TABLE nonempty_a ADD CONSTRAINT fk_self_id FOREIGN KEY (self_id) REFERENCES nonempty_a; 150 151 statement ok 152 COMMIT 153 154 subtest auto_add_fk_index_name_collision 155 156 statement ok 157 BEGIN 158 159 statement ok 160 CREATE TABLE parent_name_collision (id SERIAL NOT NULL, PRIMARY KEY (id)) 161 162 statement ok 163 CREATE TABLE child_name_collision (id SERIAL NOT NULL, parent_id INT, other_col INT) 164 165 statement ok 166 CREATE INDEX child_name_collision_auto_index_fk_id ON child_name_collision (other_col) 167 168 # Testing the unusual case where an index already exists that has the same name 169 # as the index to be auto-generated when adding a fk constraint to an empty 170 # table (but the existing index is not on the referencing column), in which 171 # case the ALTER TABLE generate another unique name for the index. 172 statement ok 173 ALTER TABLE child_name_collision ADD CONSTRAINT fk_id FOREIGN KEY (parent_id) references parent_name_collision 174 175 statement ok 176 COMMIT 177 178 subtest auto_add_fk_duplicate_cols_error 179 180 statement ok 181 BEGIN 182 183 statement ok 184 CREATE TABLE parent (a_id INT, b_id INT, PRIMARY KEY (a_id, b_id)) 185 186 statement ok 187 CREATE TABLE child_duplicate_cols (id INT, parent_id INT, PRIMARY KEY (id)) 188 189 # The fk constraint is invalid because it has duplicate columns, so automatically adding the index fails 190 statement error index \"child_duplicate_cols_auto_index_fk\" contains duplicate column \"parent_id\" 191 ALTER TABLE child_duplicate_cols ADD CONSTRAINT fk FOREIGN KEY (parent_id, parent_id) references parent 192 193 statement ok 194 COMMIT 195 196 subtest create_with_other_commands_in_txn 197 198 statement ok 199 CREATE TABLE kv (item, quantity) AS VALUES ('cups', 10), ('plates', 30), ('forks', 15) 200 201 statement count 3 202 SELECT * FROM kv 203 204 statement ok 205 BEGIN 206 207 statement ok 208 CREATE TABLE test.parent (id int primary key) 209 210 statement ok 211 INSERT into test.parent values (1) 212 213 statement ok 214 CREATE TABLE test.chill (id int primary key, parent_id int) 215 216 # random schema change that doesn't require a backfill. 217 statement ok 218 ALTER TABLE test.chill RENAME TO test.child 219 220 statement ok 221 INSERT INTO test.child VALUES (1, 1) 222 223 # index is over data added in the transaction so the backfill runs 224 # within the trasaction. 225 statement ok 226 CREATE INDEX idx_child_parent_id ON test.child (parent_id) 227 228 # FK can be added because the index is visible. 229 statement ok 230 ALTER TABLE test.child ADD CONSTRAINT fk_child_parent_id FOREIGN KEY (parent_id) REFERENCES test.parent (id); 231 232 statement ok 233 INSERT INTO test.child VALUES (2, 1) 234 235 # check that the index is indeed visible. 236 query II rowsort 237 SELECT * FROM test.child@idx_child_parent_id 238 ---- 239 1 1 240 2 1 241 242 # create index on a table that was created outside of the transaction 243 statement ok 244 CREATE INDEX foo ON test.kv (quantity) 245 246 statement ok 247 COMMIT 248 249 # foo is visible 250 query TI rowsort 251 SELECT * FROM test.kv@foo 252 ---- 253 cups 10 254 forks 15 255 plates 30 256 257 subtest create_index_references_create_table_outside_txn 258 259 statement ok 260 BEGIN 261 262 # create index on a table that was created outside of the transaction 263 statement ok 264 CREATE INDEX bar ON test.kv (quantity) 265 266 # bar is invisible 267 statement error index "bar" not found 268 SELECT * FROM test.kv@bar 269 270 statement ok 271 COMMIT 272 273 # bar is still invisible because the error above prevents the 274 # transaction from committing. 275 statement error index "bar" not found 276 SELECT * FROM test.kv@bar 277 278 subtest create_reference_to_create_outside_txn_17949 279 280 statement ok 281 BEGIN 282 283 statement ok 284 CREATE TABLE b (parent_id INT REFERENCES parent(id)); 285 286 # schema changes are permitted on the table even though it's in the ADD state. 287 statement ok 288 CREATE INDEX foo ON b (parent_id) 289 290 statement ok 291 ALTER TABLE b ADD COLUMN d INT DEFAULT 23, ADD CONSTRAINT bar UNIQUE (parent_id) 292 293 query TT 294 SHOW CREATE TABLE b 295 ---- 296 b CREATE TABLE b ( 297 parent_id INT8 NULL, 298 d INT8 NULL DEFAULT 23:::INT8, 299 CONSTRAINT fk_parent_id_ref_parent FOREIGN KEY (parent_id) REFERENCES parent(id), 300 INDEX b_auto_index_fk_parent_id_ref_parent (parent_id ASC), 301 INDEX foo (parent_id ASC), 302 UNIQUE INDEX bar (parent_id ASC), 303 FAMILY "primary" (parent_id, rowid, d) 304 ) 305 306 # table b is not visible to the transaction #17949 307 statement error pgcode 42P01 relation "b" does not exist 308 INSERT INTO b VALUES (1); 309 310 statement ok 311 COMMIT 312 313 subtest create_as_with_add_column_index_in_txn 314 315 statement ok 316 BEGIN 317 318 statement ok 319 CREATE TABLE stock (item, quantity) AS VALUES ('cups', 10), ('plates', 30), ('forks', 15) 320 321 statement count 3 322 SELECT * FROM stock 323 324 # index is only over data added in the transaction so the backfill occurs 325 # within the trasaction. 326 statement ok 327 CREATE INDEX idx_quantity ON stock (quantity) 328 329 # Add two columns and a constraint in the same statement. 330 statement ok 331 ALTER TABLE stock ADD COLUMN c INT AS (quantity + 4) STORED, ADD COLUMN d INT DEFAULT 23, ADD CONSTRAINT bar UNIQUE (c) 332 333 # check that the index and columns are indeed visible. 334 query TIII rowsort 335 SELECT * FROM test.stock@idx_quantity 336 ---- 337 cups 10 14 23 338 forks 15 19 23 339 plates 30 34 23 340 341 # check that the constraint bar is indeed visible. 342 query TIII rowsort 343 SELECT * FROM test.stock@bar 344 ---- 345 cups 10 14 23 346 forks 15 19 23 347 plates 30 34 23 348 349 statement ok 350 COMMIT 351 352 subtest create_as_with_reuse_column_index_name_in_txn 353 354 statement ok 355 BEGIN 356 357 statement ok 358 CREATE TABLE warehouse (item STRING PRIMARY KEY, quantity INT, UNIQUE (quantity), INDEX bar (quantity)) 359 360 statement ok 361 INSERT INTO warehouse VALUES ('cups', 10), ('plates', 30), ('forks', 15) 362 363 statement ok 364 DROP INDEX warehouse@bar 365 366 statement ok 367 ALTER TABLE warehouse DROP quantity 368 369 # See if the column and index names can be reused. 370 statement ok 371 ALTER TABLE warehouse ADD COLUMN quantity INT DEFAULT 23 372 373 statement ok 374 CREATE INDEX bar ON warehouse (item) 375 376 # check that the index is indeed visible. 377 query TI rowsort 378 SELECT * FROM warehouse@bar 379 ---- 380 cups 23 381 forks 23 382 plates 23 383 384 # drop a column created in the same transaction 385 statement ok 386 ALTER TABLE warehouse DROP COLUMN quantity 387 388 query T rowsort 389 SELECT * FROM warehouse@bar 390 ---- 391 cups 392 forks 393 plates 394 395 statement ok 396 COMMIT 397 398 subtest create_as_drop_and_create_in_txn 399 400 statement ok 401 BEGIN 402 403 statement ok 404 CREATE TABLE hood (item, quantity) AS VALUES ('cups', 10), ('plates', 30), ('forks', 15) 405 406 statement count 3 407 SELECT * FROM hood 408 409 statement ok 410 DROP TABLE hood 411 412 statement ok 413 CREATE TABLE hood (item, quantity) AS VALUES ('plates', 10), ('knives', 30), ('spoons', 12) 414 415 statement count 3 416 SELECT * FROM hood 417 418 query TI rowsort 419 SELECT * FROM hood 420 ---- 421 plates 10 422 knives 30 423 spoons 12 424 425 statement ok 426 COMMIT 427 428 subtest create_as_rename_and_create_in_txn 429 430 statement ok 431 BEGIN 432 433 statement ok 434 CREATE TABLE shop (item, quantity) AS VALUES ('cups', 10), ('plates', 30), ('forks', 15) 435 436 statement count 3 437 SELECT * FROM shop 438 439 statement ok 440 ALTER TABLE shop RENAME TO ship 441 442 statement ok 443 CREATE TABLE shop (item, quantity) AS VALUES ('spoons', 11), ('plates', 34), ('knives', 22) 444 445 statement count 3 446 SELECT * FROM shop 447 448 query TI rowsort 449 SELECT * FROM shop 450 ---- 451 spoons 11 452 plates 34 453 knives 22 454 455 query TI rowsort 456 SELECT * FROM ship 457 ---- 458 cups 10 459 plates 30 460 forks 15 461 462 statement ok 463 COMMIT 464 465 subtest create_as_fail_unique_index 466 467 statement ok 468 BEGIN 469 470 statement ok 471 CREATE TABLE shopping (item, quantity) AS VALUES ('cups', 10), ('plates', 30), ('forks', 10) 472 473 statement count 3 474 SELECT * FROM shopping 475 476 statement error pgcode 23505 violates unique constraint "bar" 477 CREATE UNIQUE INDEX bar ON shopping (quantity) 478 479 statement ok 480 COMMIT 481 482 # Ensure the above transaction didn't commit. 483 query error pgcode 42P01 relation \"shopping\" does not exist 484 SELECT * FROM shopping 485 486 subtest add_column_not_null_violation 487 488 statement ok 489 BEGIN 490 491 statement ok 492 CREATE TABLE shopping (item, quantity) AS VALUES ('cups', 10), ('plates', 30), ('forks', 10) 493 494 statement count 3 495 SELECT * FROM shopping 496 497 statement error pgcode 23502 null value in column \"q\" violates not-null constraint 498 ALTER TABLE shopping ADD COLUMN q DECIMAL NOT NULL 499 500 statement ok 501 COMMIT 502 503 # Ensure the above transaction didn't commit. 504 statement error pgcode 42P01 relation \"shopping\" does not exist 505 SELECT * FROM shopping 506 507 subtest add_column_computed_column_failure 508 509 statement ok 510 BEGIN 511 512 statement ok 513 CREATE TABLE shopping (item, quantity) AS VALUES ('cups', 10), ('plates', 30), ('forks', 10) 514 515 statement count 3 516 SELECT * FROM shopping 517 518 statement error pgcode 22012 division by zero 519 ALTER TABLE shopping ADD COLUMN c int AS (quantity::int // 0) STORED 520 521 statement ok 522 COMMIT 523 524 subtest create_as_add_multiple_columns 525 526 statement ok 527 BEGIN 528 529 statement ok 530 CREATE TABLE cutlery (item, quantity) AS VALUES ('cups', 10), ('plates', 30), ('forks', 15) 531 532 statement count 3 533 SELECT * FROM cutlery 534 535 # Add two columns, one with a computed and the other without any default. 536 statement ok 537 ALTER TABLE cutlery ADD COLUMN c INT AS (quantity + 4) STORED, ADD COLUMN d INT 538 539 query TIII rowsort 540 SELECT * FROM test.cutlery 541 ---- 542 cups 10 14 NULL 543 plates 30 34 NULL 544 forks 15 19 NULL 545 546 statement ok 547 COMMIT 548 549 subtest table_rename_within_txn 550 551 statement ok 552 BEGIN 553 554 statement ok 555 CREATE TABLE dontwant (k CHAR PRIMARY KEY, v CHAR) 556 557 statement ok 558 CREATE TABLE want (k CHAR PRIMARY KEY, v CHAR) 559 560 statement ok 561 INSERT INTO dontwant (k,v) VALUES ('a', 'b') 562 563 statement ok 564 INSERT INTO want (k,v) VALUES ('c', 'd') 565 566 statement ok 567 ALTER TABLE want RENAME TO forlater 568 569 statement ok 570 ALTER TABLE dontwant RENAME TO want 571 572 statement ok 573 INSERT INTO want (k,v) VALUES ('e', 'f') 574 575 statement ok 576 COMMIT 577 578 query TT rowsort 579 SELECT * FROM want 580 ---- 581 a b 582 e f 583 584 subtest fk_in_same_txn 585 586 statement ok 587 BEGIN 588 589 statement ok 590 CREATE TABLE parents (k CHAR PRIMARY KEY) 591 592 statement ok 593 INSERT INTO parents (k) VALUES ('b') 594 595 statement ok 596 CREATE TABLE children (k CHAR PRIMARY KEY, v CHAR REFERENCES parents) 597 598 statement ok 599 INSERT INTO children (k,v) VALUES ('a', 'b') 600 601 # Add a column to test a column backfill in the midst of FK checks. 602 statement ok 603 ALTER TABLE children ADD COLUMN d INT DEFAULT 23 604 605 query TTI 606 SELECT * FROM children 607 ---- 608 a b 23 609 610 statement ok 611 COMMIT 612 613 subtest add_drop_add_constraint 614 615 statement ok 616 BEGIN 617 618 statement ok 619 CREATE TABLE class (k CHAR PRIMARY KEY) 620 621 statement ok 622 INSERT INTO class (k) VALUES ('b') 623 624 statement ok 625 CREATE TABLE student (k CHAR PRIMARY KEY, v CHAR REFERENCES class) 626 627 statement ok 628 INSERT INTO student (k,v) VALUES ('a', 'b') 629 630 statement ok 631 ALTER TABLE student DROP CONSTRAINT fk_v_ref_class 632 633 statement ok 634 ALTER TABLE student ADD FOREIGN KEY (v) REFERENCES class 635 636 query TT 637 SELECT * FROM student 638 ---- 639 a b 640 641 statement ok 642 COMMIT 643 644 subtest interleaved_in_same_txn 645 646 statement ok 647 BEGIN 648 649 statement ok 650 CREATE TABLE customers (k CHAR PRIMARY KEY) 651 652 statement ok 653 INSERT INTO customers (k) VALUES ('b') 654 655 statement ok 656 CREATE TABLE orders (k CHAR PRIMARY KEY, v CHAR) INTERLEAVE IN PARENT customers (k) 657 658 statement ok 659 INSERT INTO orders (k,v) VALUES ('a', 'b') 660 661 # Add a column to test a column backfill over an interleaved child. 662 statement ok 663 ALTER TABLE orders ADD COLUMN d INT DEFAULT 23 664 665 query TTI 666 SELECT * FROM orders 667 ---- 668 a b 23 669 670 statement ok 671 COMMIT 672 673 subtest truncate_and_insert 674 675 statement ok 676 BEGIN 677 678 statement ok 679 TRUNCATE want 680 681 statement ok 682 INSERT INTO want (k,v) VALUES ('a', 'b') 683 684 statement ok 685 CREATE INDEX foo on want (v) 686 687 query TT 688 SELECT * FROM want@foo 689 ---- 690 a b 691 692 statement ok 693 COMMIT 694 695 query TT 696 SELECT * FROM want 697 ---- 698 a b 699 700 statement ok 701 BEGIN 702 703 statement ok 704 TRUNCATE orders 705 706 # table orders is not visible to the transaction #17949 707 statement error pgcode 42P01 relation "orders" does not exist 708 INSERT INTO orders (k,v) VALUES ('a', 'b') 709 710 statement ok 711 COMMIT; 712 713 statement ok 714 BEGIN 715 716 statement ok 717 TRUNCATE customers CASCADE 718 719 # table customers is not visible to the transaction #17949 720 statement error pgcode 42P01 relation "customers" does not exist 721 INSERT INTO customers (k) VALUES ('b') 722 723 statement ok 724 COMMIT; 725 726 subtest rollback_mutations 727 728 statement ok 729 INSERT INTO customers (k) VALUES ('z'), ('x') 730 731 statement ok 732 BEGIN 733 734 statement ok 735 ALTER TABLE customers ADD i INT DEFAULT 5 736 737 statement ok 738 ALTER TABLE customers ADD j INT DEFAULT 4 739 740 statement ok 741 ALTER TABLE customers ADD l INT DEFAULT 3 742 743 statement ok 744 ALTER TABLE customers ADD m CHAR 745 746 statement ok 747 ALTER TABLE customers ADD n CHAR DEFAULT 'a' 748 749 statement ok 750 CREATE INDEX j_idx ON customers (j) 751 752 statement ok 753 CREATE INDEX l_idx ON customers (l) 754 755 statement ok 756 CREATE INDEX m_idx ON customers (m) 757 758 statement ok 759 CREATE UNIQUE INDEX i_idx ON customers (i) 760 761 statement ok 762 CREATE UNIQUE INDEX n_idx ON customers (n) 763 764 statement error pgcode XXA00 violates unique constraint 765 COMMIT 766 767 query TTBTTTB 768 SHOW COLUMNS FROM customers 769 ---- 770 k CHAR false NULL · {primary} false 771 772 query error pq: index "j_idx" not found 773 SELECT * FROM customers@j_idx 774 775 query TT 776 SELECT status, 777 regexp_replace(description, 'ROLL BACK JOB \d+.*', 'ROLL BACK JOB') as description 778 FROM [SHOW JOBS] WHERE job_type = 'SCHEMA CHANGE' ORDER BY job_id DESC LIMIT 1 779 ---- 780 failed ALTER TABLE test.public.customers ADD COLUMN i INT8 DEFAULT 5;ALTER TABLE test.public.customers ADD COLUMN j INT8 DEFAULT 4;ALTER TABLE test.public.customers ADD COLUMN l INT8 DEFAULT 3;ALTER TABLE test.public.customers ADD COLUMN m CHAR;ALTER TABLE test.public.customers ADD COLUMN n CHAR DEFAULT 'a';CREATE INDEX j_idx ON test.public.customers (j);CREATE INDEX l_idx ON test.public.customers (l);CREATE INDEX m_idx ON test.public.customers (m);CREATE UNIQUE INDEX i_idx ON test.public.customers (i);CREATE UNIQUE INDEX n_idx ON test.public.customers (n) 781 782 query TT 783 SELECT status, 784 regexp_replace(description, 'ROLL BACK JOB \d+.*', 'ROLL BACK JOB') as description 785 FROM [SHOW JOBS] WHERE job_type = 'SCHEMA CHANGE GC' ORDER BY job_id DESC LIMIT 1 786 ---- 787 running GC for ROLLBACK of ALTER TABLE test.public.customers ADD COLUMN i INT8 DEFAULT 5;ALTER TABLE test.public.customers ADD COLUMN j INT8 DEFAULT 4;ALTER TABLE test.public.customers ADD COLUMN l INT8 DEFAULT 3;ALTER TABLE test.public.customers ADD COLUMN m CHAR;ALTER TABLE test.public.customers ADD COLUMN n CHAR DEFAULT 'a';CREATE INDEX j_idx ON test.public.customers (j);CREATE INDEX l_idx ON test.public.customers (l);CREATE INDEX m_idx ON test.public.customers (m);CREATE UNIQUE INDEX i_idx ON test.public.customers (i);CREATE UNIQUE INDEX n_idx ON test.public.customers (n) 788 789 subtest add_multiple_computed_elements 790 791 statement ok 792 BEGIN 793 794 statement ok 795 ALTER TABLE customers ADD i INT DEFAULT 5 796 797 statement ok 798 ALTER TABLE customers ADD j INT AS (i-1) STORED 799 800 statement ok 801 ALTER TABLE customers ADD COLUMN d INT DEFAULT 15, ADD COLUMN e INT AS (d + (i-1)) STORED 802 803 statement ok 804 COMMIT 805 806 query TIIII rowsort 807 SELECT * FROM customers 808 ---- 809 b 5 4 15 19 810 x 5 4 15 19 811 z 5 4 15 19 812 813 query TT 814 SELECT status, description FROM [SHOW JOBS] 815 WHERE job_type = 'SCHEMA CHANGE' ORDER BY job_id DESC LIMIT 1 816 ---- 817 succeeded ALTER TABLE test.public.customers ADD COLUMN i INT8 DEFAULT 5;ALTER TABLE test.public.customers ADD COLUMN j INT8 AS (i - 1) STORED;ALTER TABLE test.public.customers ADD COLUMN d INT8 DEFAULT 15, ADD COLUMN e INT8 AS (d + (i - 1)) STORED 818 819 # VALIDATE CONSTRAINT will not hang when executed in the same txn as 820 # a schema change in the same txn #32118 821 subtest validate_in_schema_change_txn 822 823 # To get an unvalidated foreign key for testing, use the loophole that we 824 # currently don't support adding a validated FK in the same transaction as 825 # CREATE TABLE 826 statement ok 827 BEGIN 828 829 statement ok 830 CREATE TABLE products (sku STRING PRIMARY KEY, upc STRING UNIQUE, vendor STRING) 831 832 statement ok 833 CREATE TABLE orders2 ( 834 id INT8 PRIMARY KEY, 835 product STRING DEFAULT 'sprockets', 836 INDEX (product) 837 ) 838 839 statement ok 840 ALTER TABLE orders2 ADD FOREIGN KEY (product) REFERENCES products 841 842 statement ok 843 COMMIT 844 845 statement ok 846 BEGIN 847 848 # Perform an unrelated schema change 849 statement ok 850 ALTER TABLE orders2 ADD CHECK (id > 0) 851 852 statement ok 853 ALTER TABLE orders2 VALIDATE CONSTRAINT fk_product_ref_products 854 855 statement ok 856 COMMIT 857 858 statement ok 859 DROP TABLE products, orders2 860 861 subtest fk_constraint_being_added 862 863 statement ok 864 CREATE TABLE products (sku STRING PRIMARY KEY, upc STRING UNIQUE, vendor STRING) 865 866 statement ok 867 CREATE TABLE orders2 ( 868 id INT8 PRIMARY KEY, 869 product STRING DEFAULT 'sprockets', 870 INDEX (product) 871 ) 872 873 # The constraint can't be validated with VALIDATE CONSTRAINT in the same transaction 874 statement ok 875 BEGIN 876 877 statement ok 878 ALTER TABLE orders2 ADD FOREIGN KEY (product) REFERENCES products 879 880 statement error constraint "fk_product_ref_products" in the middle of being added, try again later 881 ALTER TABLE orders2 VALIDATE CONSTRAINT fk_product_ref_products 882 883 statement ok 884 COMMIT 885 886 # Dependent columns can't be dropped 887 statement ok 888 BEGIN 889 890 statement ok 891 ALTER TABLE orders2 ADD FOREIGN KEY (product) REFERENCES products 892 893 statement error constraint "fk_product_ref_products" in the middle of being added, try again later 894 ALTER TABLE orders2 DROP COLUMN product 895 896 statement ok 897 COMMIT 898 899 # Dependent indexes can't be dropped 900 statement ok 901 BEGIN 902 903 statement ok 904 ALTER TABLE orders2 ADD FOREIGN KEY (product) REFERENCES products 905 906 statement error constraint "fk_product_ref_products" in the middle of being added, try again later 907 DROP INDEX orders2@orders2_product_idx 908 909 statement ok 910 COMMIT 911 912 # The constraint can't be renamed 913 statement ok 914 BEGIN 915 916 statement ok 917 ALTER TABLE orders2 ADD CONSTRAINT c FOREIGN KEY (product) REFERENCES products 918 919 statement error constraint "c" in the middle of being added, try again later 920 ALTER TABLE orders2 RENAME CONSTRAINT c to d 921 922 statement ok 923 COMMIT 924 925 # Verify that check constraints can be added on columns being added in the same transaction 926 subtest check_on_add_col 927 928 statement ok 929 CREATE TABLE check_table (k INT PRIMARY KEY) 930 931 statement ok 932 INSERT INTO check_table VALUES (1) 933 934 statement ok 935 BEGIN 936 937 statement ok 938 ALTER TABLE check_table ADD c INT 939 940 statement ok 941 ALTER TABLE check_table ADD CONSTRAINT c_0 CHECK (c > 0) NOT VALID 942 943 statement ok 944 ALTER TABLE check_table ADD d INT DEFAULT 1 945 946 statement ok 947 ALTER TABLE check_table ADD CONSTRAINT d_0 CHECK (d > 0) 948 949 statement ok 950 COMMIT 951 952 query TTTTB 953 SHOW CONSTRAINTS FROM check_table 954 ---- 955 check_table c_0 CHECK CHECK ((c > 0:::INT8)) false 956 check_table d_0 CHECK CHECK ((d > 0:::INT8)) true 957 check_table primary PRIMARY KEY PRIMARY KEY (k ASC) true 958 959 statement ok 960 BEGIN 961 962 statement ok 963 ALTER TABLE check_table ADD e INT DEFAULT 0 964 965 statement ok 966 ALTER TABLE check_table ADD CONSTRAINT e_0 CHECK (e > 0) 967 968 statement error pgcode XXA00 validation of CHECK "e > 0:::INT8" failed on row: k=1, c=NULL, d=1, e=0 969 COMMIT 970 971 # Test rollbacks after error in expression evaluation 972 statement ok 973 BEGIN 974 975 statement ok 976 ALTER TABLE check_table ADD e STRING DEFAULT 'a' 977 978 statement ok 979 ALTER TABLE check_table ADD CONSTRAINT e_0 CHECK (e::INT > 0) 980 981 statement error pgcode XXA00 validate check constraint: could not parse "a" as type int 982 COMMIT 983 984 # Constraint e_0 was not added 985 query TTTTB 986 SHOW CONSTRAINTS FROM check_table 987 ---- 988 check_table c_0 CHECK CHECK ((c > 0:::INT8)) false 989 check_table d_0 CHECK CHECK ((d > 0:::INT8)) true 990 check_table primary PRIMARY KEY PRIMARY KEY (k ASC) true 991 992 # Adding column e was rolled back 993 query TTBTTTB 994 SHOW COLUMNS FROM check_table 995 ---- 996 k INT8 false NULL · {primary} false 997 c INT8 true NULL · {} false 998 d INT8 true 1:::INT8 · {} false 999 1000 statement ok 1001 DROP TABLE check_table 1002 1003 # Test that a check constraint is rolled back if adding other schema elements in the same transaction fails 1004 subtest rollback_check 1005 1006 statement ok 1007 CREATE TABLE check_table (k INT PRIMARY KEY, a INT) 1008 1009 statement ok 1010 INSERT INTO check_table VALUES (0, 0), (1, 0) 1011 1012 statement ok 1013 BEGIN 1014 1015 statement ok 1016 CREATE UNIQUE INDEX idx ON check_table (a) 1017 1018 statement ok 1019 ALTER TABLE check_table ADD CHECK (a >= 0) 1020 1021 statement error pgcode XXA00 violates unique constraint "idx" 1022 COMMIT 1023 1024 query TTTTB 1025 SHOW CONSTRAINTS FROM check_table 1026 ---- 1027 check_table primary PRIMARY KEY PRIMARY KEY (k ASC) true 1028 1029 statement ok 1030 BEGIN 1031 1032 statement ok 1033 ALTER TABLE check_table ADD CHECK (a >= 0) 1034 1035 statement ok 1036 ALTER TABLE check_table ADD CHECK (a < 0) 1037 1038 statement error pgcode XXA00 validation of CHECK \"a < 0:::INT8\" failed on row: k=0, a=0 1039 COMMIT 1040 1041 query TTTTB 1042 SHOW CONSTRAINTS FROM check_table 1043 ---- 1044 check_table primary PRIMARY KEY PRIMARY KEY (k ASC) true 1045 1046 statement ok 1047 DROP TABLE check_table 1048 1049 subtest check_constraint_being_added 1050 1051 statement ok 1052 CREATE TABLE check_table (k INT PRIMARY KEY) 1053 1054 statement ok 1055 BEGIN 1056 1057 statement ok 1058 ALTER TABLE check_table ADD f INT 1059 1060 statement ok 1061 ALTER TABLE check_table ADD CONSTRAINT f_0 CHECK (f > 0) 1062 1063 statement error constraint "f_0" in the middle of being added 1064 ALTER TABLE check_table DROP CONSTRAINT f_0 1065 1066 statement ok 1067 COMMIT 1068 1069 statement ok 1070 BEGIN 1071 1072 statement ok 1073 ALTER TABLE check_table ADD g INT 1074 1075 statement ok 1076 ALTER TABLE check_table ADD CONSTRAINT g_0 CHECK (g > 0) 1077 1078 statement error referencing constraint "g_0" in the middle of being added 1079 ALTER TABLE check_table DROP COLUMN g 1080 1081 statement ok 1082 COMMIT 1083 1084 statement ok 1085 BEGIN 1086 1087 statement ok 1088 ALTER TABLE check_table ADD h INT 1089 1090 statement ok 1091 ALTER TABLE check_table ADD CONSTRAINT h_0 CHECK (h > 0) 1092 1093 statement error constraint "h_0" in the middle of being added 1094 ALTER TABLE check_table VALIDATE CONSTRAINT h_0 1095 1096 statement ok 1097 COMMIT 1098 1099 statement ok 1100 DROP TABLE check_table 1101 1102 subtest check_rename 1103 1104 statement ok 1105 CREATE TABLE check_table (k INT PRIMARY KEY) 1106 1107 statement ok 1108 BEGIN 1109 1110 statement ok 1111 ALTER TABLE check_table ADD f INT 1112 1113 statement ok 1114 ALTER TABLE check_table ADD CONSTRAINT f_0 CHECK (f > 0) 1115 1116 statement error constraint "f_0" in the middle of being added 1117 ALTER TABLE check_table RENAME CONSTRAINT f_0 to f_1 1118 1119 statement ok 1120 COMMIT 1121 1122 statement ok 1123 BEGIN 1124 1125 statement ok 1126 ALTER TABLE check_table ADD f INT 1127 1128 statement error constraint "f_0" in the middle of being added 1129 ALTER TABLE check_table ADD CONSTRAINT f_0 CHECK (f > 0), 1130 RENAME CONSTRAINT f_0 to f_1 1131 1132 statement ok 1133 COMMIT 1134 1135 statement ok 1136 DROP TABLE check_table 1137 1138 # Test adding a check constraint to a table that was created in the same transaction 1139 subtest check_on_new_table 1140 1141 # Test multiple successful constraint adds in the same transaction 1142 statement ok 1143 BEGIN 1144 1145 statement ok 1146 CREATE TABLE check_table (a INT) 1147 1148 statement ok 1149 INSERT INTO check_table VALUES (0) 1150 1151 # This validates the constraint for existing rows, because it's in the same txn as CREATE TABLE 1152 statement ok 1153 ALTER TABLE check_table ADD CONSTRAINT ck_a CHECK (a = 0) 1154 1155 statement ok 1156 ALTER TABLE check_table ADD COLUMN b INT DEFAULT 1 1157 1158 # This validates the constraint for existing rows, because it's in the same txn as CREATE TABLE 1159 statement ok 1160 ALTER TABLE check_table ADD CONSTRAINT ck_b CHECK (b > 0) 1161 1162 # Test ADD COLUMN and ADD CONSTRAINT in the same ALTER TABLE statement 1163 statement ok 1164 ALTER TABLE check_table ADD COLUMN c INT DEFAULT 2, ADD CONSTRAINT ck_c CHECK (c > b) 1165 1166 statement ok 1167 COMMIT 1168 1169 # Verify that the constraints had been validated in the above txn 1170 query TTTTB 1171 SHOW CONSTRAINTS FROM check_table 1172 ---- 1173 check_table ck_a CHECK CHECK ((a = 0:::INT8)) true 1174 check_table ck_b CHECK CHECK ((b > 0:::INT8)) true 1175 check_table ck_c CHECK CHECK ((c > b)) true 1176 1177 # Also test insert/update to ensure constraint was added in a valid state (with correct column IDs, etc.) 1178 1179 statement ok 1180 INSERT INTO check_table VALUES (0, 1, 2) 1181 1182 statement ok 1183 UPDATE check_table SET b = 1 WHERE b IS NULL 1184 1185 statement ok 1186 DROP TABLE check_table 1187 1188 # Test when check validation fails 1189 1190 statement ok 1191 BEGIN 1192 1193 statement ok 1194 CREATE TABLE check_table (a INT) 1195 1196 statement ok 1197 INSERT INTO check_table VALUES (0) 1198 1199 # This validates the constraint for existing rows, because it's in the same txn as CREATE TABLE 1200 statement error validation of CHECK "a > 0:::INT8" failed on row: a=0 1201 ALTER TABLE check_table ADD CONSTRAINT ck CHECK (a > 0) 1202 1203 statement ok 1204 COMMIT 1205 1206 statement ok 1207 BEGIN 1208 1209 statement ok 1210 CREATE TABLE check_table (a INT PRIMARY KEY) 1211 1212 statement ok 1213 INSERT INTO check_table VALUES (0) 1214 1215 statement ok 1216 ALTER TABLE check_table ADD COLUMN b INT DEFAULT 0 1217 1218 # This validates the constraint for existing rows, because it's in the same txn as CREATE TABLE 1219 statement error validation of CHECK "b > 0:::INT8" failed on row: a=0, b=0 1220 ALTER TABLE check_table ADD CONSTRAINT ck CHECK (b > 0) 1221 1222 statement ok 1223 COMMIT 1224 1225 statement ok 1226 BEGIN 1227 1228 statement ok 1229 CREATE TABLE check_table (a INT PRIMARY KEY) 1230 1231 statement ok 1232 INSERT INTO check_table VALUES (0) 1233 1234 # Test ADD COLUMN and ADD CONSTRAINT in the same ALTER TABLE statement 1235 statement error validation of CHECK "c > 0:::INT8" failed on row: a=0, c=0 1236 ALTER TABLE check_table ADD COLUMN c INT DEFAULT 0, ADD CONSTRAINT ck CHECK (c > 0) 1237 1238 statement ok 1239 COMMIT 1240 1241 # Test that if a new column has a check that references a public column, writes to the public column ignore the check (until a later state in the schema change process) 1242 subtest 35193_column_with_default_value 1243 1244 statement ok 1245 CREATE TABLE t (a INT) 1246 1247 # Insert a pre-existing row to test updates 1248 statement ok 1249 INSERT INTO t VALUES (2) 1250 1251 statement ok 1252 BEGIN 1253 1254 statement ok 1255 ALTER TABLE t ADD COLUMN b INT DEFAULT 1 1256 1257 statement ok 1258 ALTER TABLE t ADD CHECK (a > b) 1259 1260 statement ok 1261 INSERT INTO t (a) VALUES (3) 1262 1263 statement ok 1264 UPDATE t SET a = 4 WHERE a < 4 1265 1266 statement ok 1267 COMMIT 1268 1269 statement ok 1270 DROP TABLE t 1271 1272 # Perform some writes that would violate the constraint, which shouldn't cause an error until the entire transaction is done 1273 1274 statement ok 1275 CREATE TABLE t (a INT) 1276 1277 statement ok 1278 BEGIN 1279 1280 statement ok 1281 ALTER TABLE t ADD COLUMN c INT DEFAULT 10 1282 1283 statement ok 1284 ALTER TABLE t ADD CHECK (a < c) 1285 1286 statement ok 1287 INSERT INTO t (a) VALUES (11) 1288 1289 statement error pgcode XXA00 validation of CHECK \"a < c\" failed on row: a=11, .* c=10 1290 COMMIT 1291 1292 # Insert a pre-existing row to test updates 1293 statement ok 1294 INSERT INTO t VALUES (2) 1295 1296 statement ok 1297 BEGIN 1298 1299 statement ok 1300 ALTER TABLE t ADD COLUMN c INT DEFAULT 10 1301 1302 statement ok 1303 ALTER TABLE t ADD CHECK (a < c) 1304 1305 statement ok 1306 UPDATE t SET a = 12 WHERE a < 12 1307 1308 statement error pgcode XXA00 validation of CHECK \"a < c\" failed on row: a=12, .*, c=10 1309 COMMIT 1310 1311 statement ok 1312 DROP TABLE t 1313 1314 # Test that we're not picking up NULL values for the new column that just haven't been backfilled 1315 statement ok 1316 CREATE TABLE t (a INT) 1317 1318 statement ok 1319 INSERT INTO t VALUES (2) 1320 1321 statement ok 1322 BEGIN 1323 1324 statement ok 1325 ALTER TABLE t ADD COLUMN d INT DEFAULT 1 1326 1327 statement ok 1328 ALTER TABLE t ADD CHECK (a > d AND d IS NOT NULL) 1329 1330 statement ok 1331 INSERT INTO t (a) VALUES (3) 1332 1333 statement ok 1334 UPDATE t SET a = 4 WHERE a < 4 1335 1336 statement ok 1337 COMMIT 1338 1339 statement ok 1340 DROP TABLE t 1341 1342 # Test that if a new column has a check that references a public column, writes to the public column ignore the check (until a later state in the schema change process) 1343 subtest 35193_computed_column 1344 1345 statement ok 1346 CREATE TABLE t (a INT) 1347 1348 # Insert a pre-existing row to test updates 1349 statement ok 1350 INSERT INTO t VALUES (2) 1351 1352 statement ok 1353 BEGIN 1354 1355 statement ok 1356 ALTER TABLE t ADD COLUMN b INT AS (a - 1) STORED 1357 1358 statement ok 1359 ALTER TABLE t ADD CHECK (a > b) 1360 1361 statement ok 1362 INSERT INTO t (a) VALUES (3) 1363 1364 statement ok 1365 UPDATE t SET a = 4 WHERE a < 4 1366 1367 statement ok 1368 COMMIT 1369 1370 statement ok 1371 DROP TABLE t 1372 1373 # Perform some writes that would violate the constraint, which shouldn't cause an error until the entire transaction is done 1374 1375 statement ok 1376 CREATE TABLE t (a INT) 1377 1378 statement ok 1379 BEGIN 1380 1381 statement ok 1382 ALTER TABLE t ADD COLUMN c INT AS (a - 1) STORED 1383 1384 statement ok 1385 ALTER TABLE t ADD CHECK (a < c) 1386 1387 statement ok 1388 INSERT INTO t (a) VALUES (11) 1389 1390 statement error pgcode XXA00 validation of CHECK \"a < c\" failed on row: a=11, .* c=10 1391 COMMIT 1392 1393 # Insert a pre-existing row to test updates 1394 statement ok 1395 INSERT INTO t VALUES (2) 1396 1397 statement ok 1398 BEGIN 1399 1400 statement ok 1401 ALTER TABLE t ADD COLUMN c INT AS (a - 1) STORED 1402 1403 statement ok 1404 ALTER TABLE t ADD CHECK (a < c) 1405 1406 statement ok 1407 UPDATE t SET a = 12 WHERE a < 12 1408 1409 statement error pgcode XXA00 validation of CHECK \"a < c\" failed on row: a=12, .*, c=11 1410 COMMIT 1411 1412 statement ok 1413 DROP TABLE t 1414 1415 # Test that we're not picking up NULL values for the new column that just haven't been backfilled 1416 statement ok 1417 CREATE TABLE t (a INT) 1418 1419 statement ok 1420 INSERT INTO t VALUES (2) 1421 1422 statement ok 1423 BEGIN 1424 1425 statement ok 1426 ALTER TABLE t ADD COLUMN d INT AS (a - 1) STORED 1427 1428 statement ok 1429 ALTER TABLE t ADD CHECK (a > d AND d IS NOT NULL) 1430 1431 statement ok 1432 INSERT INTO t (a) VALUES (3) 1433 1434 statement ok 1435 UPDATE t SET a = 4 WHERE a < 4 1436 1437 statement ok 1438 COMMIT 1439 1440 statement ok 1441 DROP TABLE t 1442 1443 # Test adding NOT NULL constraints on a new column. 1444 subtest not_null_new_column 1445 1446 statement ok 1447 CREATE TABLE t (a INT) 1448 1449 statement ok 1450 INSERT INTO t VALUES (1) 1451 1452 statement ok 1453 BEGIN 1454 1455 statement ok 1456 ALTER TABLE t ADD COLUMN b INT AS (a) STORED 1457 1458 statement ok 1459 ALTER TABLE t ALTER COLUMN b SET NOT NULL 1460 1461 statement ok 1462 COMMIT 1463 1464 statement ok 1465 BEGIN 1466 1467 statement ok 1468 ALTER TABLE t ADD COLUMN c INT 1469 1470 statement ok 1471 ALTER TABLE t ALTER COLUMN c SET NOT NULL 1472 1473 statement error pgcode XXA00 validation of NOT NULL constraint failed: validation of CHECK "c IS NOT NULL" failed 1474 COMMIT 1475 1476 statement ok 1477 DROP TABLE t 1478 1479 # Test adding CHECK and NOT NULL constraints in the same transaction. 1480 subtest check_and_not_null 1481 1482 statement ok 1483 CREATE TABLE t (a INT) 1484 1485 statement ok 1486 INSERT INTO t VALUES (1) 1487 1488 statement ok 1489 BEGIN 1490 1491 statement ok 1492 ALTER TABLE t ADD CHECK (a > 0) 1493 1494 # Check for name collisions with the auto-generated NOT NULL check constraint name 1495 statement ok 1496 ALTER TABLE t ADD CONSTRAINT a_auto_not_null CHECK (a IS NOT NULL) 1497 1498 statement ok 1499 ALTER TABLE t ALTER COLUMN a SET NOT NULL 1500 1501 statement ok 1502 COMMIT 1503 1504 statement ok 1505 DROP TABLE t 1506 1507 # Test that DROP INDEX on an index with dependent foreign keys is correctly 1508 # rolled back when there is an error 1509 subtest 38733 1510 1511 statement ok 1512 CREATE TABLE x (a INT PRIMARY KEY, b INT, UNIQUE INDEX (b), c INT) 1513 1514 statement ok 1515 CREATE TABLE y (a INT PRIMARY KEY, b INT, INDEX (b)) 1516 1517 statement ok 1518 INSERT INTO x VALUES (1, 1, 1), (2, 2, 1); 1519 1520 statement ok 1521 INSERT INTO y VALUES (1, 1), (2, 1); 1522 1523 # First, test dropping the index on the referencing side 1524 statement ok 1525 ALTER TABLE y ADD FOREIGN KEY (b) REFERENCES x (b) 1526 1527 statement ok 1528 BEGIN 1529 1530 # Drop the index that the FK reference depends on 1531 statement ok 1532 DROP INDEX y_b_idx CASCADE; 1533 1534 # This will fail, causing the previous DROP INDEX to also be rolled back 1535 statement ok 1536 CREATE UNIQUE INDEX ON y (b); 1537 1538 statement error pgcode XXA00 violates unique constraint 1539 COMMIT 1540 1541 # Verify that table y is in a consistent state (otherwise, SHOW CONSTRAINTS 1542 # would fail with an error) 1543 query TTTTB 1544 SHOW CONSTRAINTS FROM y 1545 ---- 1546 y primary PRIMARY KEY PRIMARY KEY (a ASC) true 1547 1548 # Also test dropping the index on the referenced side 1549 statement ok 1550 ALTER TABLE y ADD FOREIGN KEY (b) REFERENCES x (b) 1551 1552 statement ok 1553 BEGIN 1554 1555 # Drop the index that the FK reference depends on 1556 statement ok 1557 DROP INDEX x_b_key CASCADE; 1558 1559 # This will fail, causing the previous DROP INDEX to also be rolled back 1560 statement ok 1561 CREATE UNIQUE INDEX ON x (c); 1562 1563 statement error pgcode XXA00 violates unique constraint 1564 COMMIT 1565 1566 # Verify that table x is in a consistent state (otherwise, SHOW CONSTRAINTS 1567 # would fail with an error). 1568 query TTTTB 1569 SHOW CONSTRAINTS FROM x 1570 ---- 1571 x primary PRIMARY KEY PRIMARY KEY (a ASC) true 1572 x x_b_key UNIQUE UNIQUE (b ASC) true 1573 1574 statement ok 1575 DROP TABLE x, y 1576 1577 subtest drop_constraint_in_txn 1578 1579 statement ok 1580 CREATE TABLE t (a INT) 1581 1582 statement ok 1583 ALTER TABLE t ADD CONSTRAINT c CHECK (a > 0) 1584 1585 statement ok 1586 BEGIN 1587 1588 statement ok 1589 ALTER TABLE t DROP CONSTRAINT c 1590 1591 # Since the check constraint is dropped in the schema changer after the 1592 # transaction commits, it's still enforced during the rest of the transaction. 1593 statement error pq: failed to satisfy CHECK constraint \(a > 0:::INT8\) 1594 INSERT INTO t VALUES (0) 1595 1596 statement ok 1597 ROLLBACK 1598 1599 statement ok 1600 ALTER TABLE t DROP CONSTRAINT c 1601 1602 statement ok 1603 ALTER TABLE t ADD CONSTRAINT c_not_valid CHECK (a > 0) NOT VALID 1604 1605 statement ok 1606 BEGIN 1607 1608 statement ok 1609 ALTER TABLE t DROP CONSTRAINT c_not_valid 1610 1611 # The constraint was unvalidated, so it doesn't need to go through the schema 1612 # changer and is dropped immediately. 1613 statement ok 1614 INSERT INTO t VALUES (0) 1615 1616 statement ok 1617 COMMIT 1618 1619 statement ok 1620 DROP TABLE t 1621 1622 statement ok 1623 CREATE TABLE t (a INT) 1624 1625 statement ok 1626 ALTER TABLE t ALTER COLUMN a SET NOT NULL 1627 1628 statement ok 1629 BEGIN 1630 1631 statement ok 1632 ALTER TABLE t ALTER COLUMN a DROP NOT NULL 1633 1634 # Since the non-null constraint is dropped in the schema changer after the 1635 # transaction commits, it's still enforced during the rest of the transaction. 1636 # The error is about a check constraint because we generate a check constraint 1637 # when dropping not-null constraints in the schema changer. 1638 statement error failed to satisfy CHECK constraint \(a IS NOT NULL\) 1639 INSERT INTO t VALUES (NULL) 1640 1641 statement ok 1642 ROLLBACK 1643 1644 statement ok 1645 DROP TABLE t 1646 1647 statement ok 1648 CREATE TABLE t (a INT) 1649 1650 statement ok 1651 CREATE TABLE t2 (b INT PRIMARY KEY) 1652 1653 statement ok 1654 ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (a) REFERENCES t2 1655 1656 statement ok 1657 BEGIN 1658 1659 statement ok 1660 ALTER TABLE t DROP CONSTRAINT fk 1661 1662 # Since the foreign key constraint is dropped in the schema changer after the 1663 # transaction commits, it's still enforced during the rest of the transaction. 1664 statement error pgcode 23503 foreign key 1665 INSERT INTO t VALUES (1) 1666 1667 statement ok 1668 ROLLBACK 1669 1670 statement ok 1671 ALTER TABLE t DROP CONSTRAINT fk 1672 1673 # TODO(lucy): This part of the test is flaky and sometimes fails with a 1674 # TransactionRetryWithProtoRefreshError (see #40200), so it's being skipped 1675 # and investigated. 1676 1677 # statement ok 1678 # ALTER TABLE t ADD CONSTRAINT fk_not_valid FOREIGN KEY (a) REFERENCES t2 NOT VALID 1679 # 1680 # statement ok 1681 # BEGIN 1682 # 1683 # statement ok 1684 # ALTER TABLE t DROP CONSTRAINT fk_not_valid 1685 # 1686 # # The constraint was unvalidated, so it doesn't need to go through the schema 1687 # # changer and is dropped immediately. 1688 # statement ok 1689 # INSERT INTO t VALUES (1) 1690 # 1691 # statement ok 1692 # COMMIT 1693 1694 statement ok 1695 DROP TABLE t, t2 1696 1697 subtest delete_index_in_other_table 1698 1699 # Test setup 1700 statement ok 1701 BEGIN; 1702 1703 statement ok 1704 CREATE TABLE a (); 1705 1706 statement ok 1707 CREATE TABLE b ( key INT ); 1708 1709 statement ok 1710 CREATE INDEX b_idx ON b (key); 1711 1712 statement ok 1713 COMMIT; 1714 1715 # Try to delete an index in the same transaction 1716 statement ok 1717 BEGIN; 1718 1719 statement ok 1720 DROP TABLE a; 1721 1722 statement ok 1723 DROP INDEX b_idx CASCADE; 1724 1725 statement ok 1726 COMMIT; 1727 1728 # Test that deleting an index on a table that gets dropped in the same 1729 # transaction is allowed. 1730 subtest delete_index_and_table_in_txn 1731 1732 statement ok 1733 CREATE TABLE people (id INT PRIMARY KEY, name STRING); 1734 1735 statement ok 1736 CREATE INDEX people_name_index ON people (name); 1737 1738 statement ok 1739 BEGIN; 1740 1741 statement ok 1742 DROP INDEX people@people_name_index; 1743 1744 statement ok 1745 DROP TABLE people; 1746 1747 statement ok 1748 COMMIT; 1749 1750 subtest add_column_default_sequence_op 1751 1752 # This is a current known limitation (#42508). This test ensures that 1753 # the error message is properly reported, with issue hint. 1754 # Once the limitation is lifted, this entire test can be removed 1755 # (and replaced by test for the feature). 1756 1757 statement ok 1758 CREATE TABLE t42508(x INT); INSERT INTO t42508(x) VALUES (1); 1759 1760 statement ok 1761 CREATE SEQUENCE s42508 1762 1763 statement error pgcode 0A000 unimplemented: cannot evaluate scalar expressions containing sequence operations.*\nHINT.*\n.*42508 1764 ALTER TABLE t42508 ADD COLUMN y INT DEFAULT nextval('s42508') 1765 1766 statement ok 1767 BEGIN 1768 1769 statement ok 1770 ALTER TABLE t42508 ADD COLUMN y INT DEFAULT nextval('s42508') 1771 1772 statement error pgcode XXA00 unimplemented: cannot evaluate scalar expressions containing sequence operations.*\nHINT.*\n.*42508 1773 COMMIT 1774 1775 # Test that rolling back to a savepoint past a schema change does not result in 1776 # a deadlock. This is a regression test for #24885. Rolling back past a schema 1777 # change used to have a problem because leaving locks behind on descriptors or 1778 # namespace entries could block the schema resolution after the rollback (schema 1779 # resolution uses different transactions to do its reads). We've fixed it by having those 1780 # other transactions run at high priority, thus pushing the intents out of their way. 1781 subtest no_table_schemachange_deadlock_after_savepoint_rollback 1782 1783 statement ok 1784 begin; savepoint s; create table t(x int); rollback to savepoint s; 1785 1786 query error relation "t" does not exist 1787 select * from t; 1788 1789 statement ok 1790 commit; 1791 1792 subtest no_database_schemachange_deadlock_after_savepoint_rollback 1793 1794 statement ok 1795 begin; savepoint s; create database d46224; rollback to savepoint s; 1796 1797 query error relation "d46224.t" does not exist 1798 select * from d46224.t; 1799 1800 statement ok 1801 commit; 1802 1803 # Test that adding a self-referencing foreign key to a table in the same 1804 # transaction which creates the table is okay. In the past this created an 1805 # infinite loop. 1806 subtest create_and_add_self_referencing_fk_in_same_txn 1807 1808 statement ok 1809 BEGIN; 1810 1811 statement ok 1812 CREATE TABLE self_ref_fk (id INT8 PRIMARY KEY, parent_id INT8); 1813 1814 statement ok 1815 ALTER TABLE "self_ref_fk" ADD CONSTRAINT fk_self_ref_fk__parent_id FOREIGN KEY (parent_id) REFERENCES self_ref_fk (id) ON DELETE CASCADE; 1816 1817 # Test that the constraint is enforced in this transaction. Create a savepoint 1818 # so that we can rollback the error and commit the transaction. 1819 1820 statement ok 1821 SAVEPOINT fk_violation; 1822 1823 statement error insert on table "self_ref_fk" violates foreign key constraint "fk_self_ref_fk__parent_id" 1824 INSERT INTO self_ref_fk VALUES (2, 1); 1825 1826 statement ok 1827 ROLLBACK TO SAVEPOINT fk_violation; 1828 1829 statement ok 1830 COMMIT; 1831 1832 # Ensure that the constraint is enforced after the transaction commits. 1833 1834 query error insert on table "self_ref_fk" violates foreign key constraint "fk_self_ref_fk__parent_id" 1835 INSERT INTO self_ref_fk VALUES (2, 1); 1836 1837 # Add some data and ensure the constraint is applied. 1838 1839 statement ok 1840 INSERT INTO self_ref_fk VALUES (1, NULL), (2, 1), (3, 2); 1841 1842 query II rowsort 1843 SELECT * FROM self_ref_fk 1844 ---- 1845 1 NULL 1846 2 1 1847 3 2 1848 1849 # Check that the cascade delete takes effect and there are now no rows. 1850 1851 statement ok 1852 DELETE FROM self_ref_fk WHERE id = 1; 1853 1854 query II rowsort 1855 SELECT * FROM self_ref_fk; 1856 ---- 1857 1858 statement ok 1859 DROP TABLE self_ref_fk;