github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/ccl/logictestccl/testdata/logic_test/zone (about) 1 # LogicTest: 5node 2 3 # Ensure that cost-based-optimizer uses an index with zone constraints that most 4 # closely matches the gateway's locality. Use "retry" option, since it can take 5 # a bit of time for gossip to refresh the zone. 6 7 statement ok 8 CREATE TABLE t ( 9 k INT PRIMARY KEY, 10 v STRING, 11 INDEX secondary (k) STORING (v), 12 INDEX tertiary (k) STORING (v), 13 FAMILY (k, v) 14 ); 15 16 # ------------------------------------------------------------------------------ 17 # Put table in dc2 and secondary index in dc1 so that the gateway matches the 18 # secondary index rather the primary index. 19 # ------------------------------------------------------------------------------ 20 21 statement ok 22 ALTER TABLE t CONFIGURE ZONE USING constraints='[+region=test,+dc=dc2]' 23 24 statement ok 25 ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]' 26 27 query TTT retry 28 EXPLAIN SELECT * FROM t WHERE k=10 29 ---- 30 · distributed true 31 · vectorized true 32 scan · · 33 · table t@secondary 34 · spans /10-/11 35 36 query T retry 37 EXPLAIN (OPT, CATALOG) SELECT * FROM t 38 ---- 39 TABLE t 40 ├── k int not null 41 ├── v string 42 ├── FAMILY fam_0_k_v (k, v) 43 ├── INDEX primary 44 │ ├── k int not null 45 │ └── ZONE 46 │ └── constraints: [+region=test,+dc=dc2] 47 ├── INDEX secondary 48 │ ├── k int not null 49 │ ├── v string (storing) 50 │ └── ZONE 51 │ └── constraints: [+region=test,+dc=dc1] 52 └── INDEX tertiary 53 ├── k int not null 54 ├── v string (storing) 55 └── ZONE 56 └── constraints: [+region=test,+dc=dc2] 57 scan t@secondary 58 59 # ------------------------------------------------------------------------------ 60 # Move secondary to dc3 and put tertiary in dc1 and ensure that gateway matches 61 # tertiary instead of secondary. Regression for #35546. 62 # ------------------------------------------------------------------------------ 63 64 statement ok 65 ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc3]' 66 67 statement ok 68 ALTER INDEX t@tertiary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]' 69 70 query TTT retry 71 EXPLAIN SELECT * FROM t WHERE k=10 72 ---- 73 · distributed true 74 · vectorized true 75 scan · · 76 · table t@tertiary 77 · spans /10-/11 78 79 query T retry 80 EXPLAIN (OPT, CATALOG) SELECT * FROM t 81 ---- 82 TABLE t 83 ├── k int not null 84 ├── v string 85 ├── FAMILY fam_0_k_v (k, v) 86 ├── INDEX primary 87 │ ├── k int not null 88 │ └── ZONE 89 │ └── constraints: [+region=test,+dc=dc2] 90 ├── INDEX secondary 91 │ ├── k int not null 92 │ ├── v string (storing) 93 │ └── ZONE 94 │ └── constraints: [+region=test,+dc=dc3] 95 └── INDEX tertiary 96 ├── k int not null 97 ├── v string (storing) 98 └── ZONE 99 └── constraints: [+region=test,+dc=dc1] 100 scan t@tertiary 101 102 # ------------------------------------------------------------------------------ 103 # Swap secondary and tertiary localities and ensure invalidation occurs. 104 # Regression for #35546. 105 # ------------------------------------------------------------------------------ 106 107 statement ok 108 ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]' 109 110 statement ok 111 ALTER INDEX t@tertiary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc3]' 112 113 query TTT retry 114 EXPLAIN SELECT * FROM t WHERE k=10 115 ---- 116 · distributed true 117 · vectorized true 118 scan · · 119 · table t@secondary 120 · spans /10-/11 121 122 # ------------------------------------------------------------------------------ 123 # Swap location of primary and secondary indexes and ensure that primary index 124 # is used instead. 125 # ------------------------------------------------------------------------------ 126 127 statement ok 128 ALTER TABLE t CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]' 129 130 statement ok 131 ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc2]' 132 133 query TTT retry 134 EXPLAIN SELECT * FROM t WHERE k=10 135 ---- 136 · distributed true 137 · vectorized true 138 scan · · 139 · table t@primary 140 · spans /10-/10/# 141 142 query T retry 143 EXPLAIN (OPT, CATALOG) SELECT * FROM t 144 ---- 145 TABLE t 146 ├── k int not null 147 ├── v string 148 ├── FAMILY fam_0_k_v (k, v) 149 ├── INDEX primary 150 │ ├── k int not null 151 │ └── ZONE 152 │ └── constraints: [+region=test,+dc=dc1] 153 ├── INDEX secondary 154 │ ├── k int not null 155 │ ├── v string (storing) 156 │ └── ZONE 157 │ └── constraints: [+region=test,+dc=dc2] 158 └── INDEX tertiary 159 ├── k int not null 160 ├── v string (storing) 161 └── ZONE 162 └── constraints: [+region=test,+dc=dc3] 163 scan t 164 165 # ------------------------------------------------------------------------------ 166 # Use PREPARE to make sure that the prepared plan is invalidated when the 167 # secondary index's constraints change. 168 # ------------------------------------------------------------------------------ 169 170 statement 171 PREPARE p AS SELECT tree, field, description FROM [EXPLAIN SELECT k, v FROM t WHERE k=10] 172 173 query TTT retry 174 EXECUTE p 175 ---- 176 · distributed true 177 · vectorized true 178 scan · · 179 · table t@primary 180 · spans /10-/10/# 181 182 statement ok 183 ALTER TABLE t CONFIGURE ZONE USING constraints='[+region=test,+dc=dc2]' 184 185 statement ok 186 ALTER INDEX t@secondary CONFIGURE ZONE USING constraints='[+region=test,+dc=dc1]' 187 188 query TTT retry 189 EXECUTE p 190 ---- 191 · distributed true 192 · vectorized true 193 scan · · 194 · table t@secondary 195 · spans /10-/11 196 197 statement ok 198 DEALLOCATE p 199 200 # ------------------------------------------------------------------------------ 201 # Put table lease preference in dc2 and secondary index lease preference in dc1 202 # so that the gateway matches the secondary index rather the primary index. 203 # ------------------------------------------------------------------------------ 204 205 statement ok 206 ALTER TABLE t CONFIGURE ZONE 207 USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc2]]' 208 209 statement ok 210 ALTER INDEX t@secondary CONFIGURE ZONE 211 USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]' 212 213 query TTT retry 214 EXPLAIN SELECT * FROM t WHERE k=10 215 ---- 216 · distributed true 217 · vectorized true 218 scan · · 219 · table t@secondary 220 · spans /10-/11 221 222 # ------------------------------------------------------------------------------ 223 # Move secondary lease preference to dc3 and put tertiary lease preference in 224 # dc1 and ensure that gateway matches tertiary. 225 # ------------------------------------------------------------------------------ 226 227 statement ok 228 ALTER INDEX t@secondary CONFIGURE ZONE 229 USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc3]]' 230 231 statement ok 232 ALTER INDEX t@tertiary CONFIGURE ZONE 233 USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]' 234 235 query TTT retry 236 EXPLAIN SELECT * FROM t WHERE k=10 237 ---- 238 · distributed true 239 · vectorized true 240 scan · · 241 · table t@tertiary 242 · spans /10-/11 243 244 query T retry 245 EXPLAIN (OPT, CATALOG) SELECT * FROM t 246 ---- 247 TABLE t 248 ├── k int not null 249 ├── v string 250 ├── FAMILY fam_0_k_v (k, v) 251 ├── INDEX primary 252 │ ├── k int not null 253 │ └── ZONE 254 │ ├── constraints: [+region=test] 255 │ └── lease preference: [+region=test,+dc=dc2] 256 ├── INDEX secondary 257 │ ├── k int not null 258 │ ├── v string (storing) 259 │ └── ZONE 260 │ ├── constraints: [+region=test] 261 │ └── lease preference: [+region=test,+dc=dc3] 262 └── INDEX tertiary 263 ├── k int not null 264 ├── v string (storing) 265 └── ZONE 266 ├── constraints: [+region=test] 267 └── lease preference: [+region=test,+dc=dc1] 268 scan t@tertiary 269 270 # ------------------------------------------------------------------------------ 271 # Ensure that an index constrained to a region is preferred over an index that 272 # merely has a lease preference in that region (since lease preferences can 273 # move, whereas constraints are fixed). 274 # ------------------------------------------------------------------------------ 275 276 statement ok 277 ALTER TABLE t CONFIGURE ZONE 278 USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]' 279 280 statement ok 281 ALTER INDEX t@secondary CONFIGURE ZONE 282 USING constraints='[+region=test,+dc=dc1]' 283 284 statement ok 285 ALTER INDEX t@tertiary CONFIGURE ZONE 286 USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]' 287 288 query TTT retry 289 EXPLAIN SELECT * FROM t WHERE k=10 290 ---- 291 · distributed true 292 · vectorized true 293 scan · · 294 · table t@secondary 295 · spans /10-/11 296 297 query T retry 298 EXPLAIN (OPT, CATALOG) SELECT * FROM t 299 ---- 300 TABLE t 301 ├── k int not null 302 ├── v string 303 ├── FAMILY fam_0_k_v (k, v) 304 ├── INDEX primary 305 │ ├── k int not null 306 │ └── ZONE 307 │ ├── constraints: [+region=test] 308 │ └── lease preference: [+region=test,+dc=dc1] 309 ├── INDEX secondary 310 │ ├── k int not null 311 │ ├── v string (storing) 312 │ └── ZONE 313 │ ├── constraints: [+region=test,+dc=dc1] 314 │ └── lease preference: [+region=test,+dc=dc3] 315 └── INDEX tertiary 316 ├── k int not null 317 ├── v string (storing) 318 └── ZONE 319 ├── constraints: [+region=test] 320 └── lease preference: [+region=test,+dc=dc1] 321 scan t@secondary 322 323 # ------------------------------------------------------------------------------ 324 # Use PREPARE to make sure that the prepared plan is invalidated when the 325 # secondary index's lease preferences change. 326 # ------------------------------------------------------------------------------ 327 328 statement ok 329 PREPARE p AS SELECT tree, field, description FROM [EXPLAIN SELECT k, v FROM t WHERE k=10] 330 331 query TTT retry 332 EXECUTE p 333 ---- 334 · distributed true 335 · vectorized true 336 scan · · 337 · table t@secondary 338 · spans /10-/11 339 340 statement ok 341 ALTER INDEX t@secondary CONFIGURE ZONE 342 USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc2]]' 343 344 query TTT retry 345 EXECUTE p 346 ---- 347 · distributed true 348 · vectorized true 349 scan · · 350 · table t@primary 351 · spans /10-/10/# 352 353 statement ok 354 DEALLOCATE p 355 356 357 # ------------------------------------------------------------------------------ 358 # Regression for issue #36642. Optimizer picked wrong index when the index had 359 # constraints / lease preferences, but the table had no zone config. 360 # ------------------------------------------------------------------------------ 361 362 statement ok 363 CREATE TABLE t36642 ( 364 k INT PRIMARY KEY, 365 v STRING, 366 INDEX secondary (k) STORING (v), 367 INDEX tertiary (k) STORING (v), 368 FAMILY (k, v) 369 ); 370 371 statement ok 372 ALTER INDEX t36642@secondary CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]' 373 374 query TTT retry 375 EXPLAIN SELECT * FROM t36642 WHERE k=10 376 ---- 377 · distributed true 378 · vectorized true 379 scan · · 380 · table t36642@secondary 381 · spans /10-/11 382 383 statement ok 384 ALTER INDEX t36642@tertiary CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc1]]' 385 386 statement ok 387 ALTER INDEX t36642@secondary CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+region=test,+dc=dc2]]' 388 389 query TTT retry 390 EXPLAIN SELECT * FROM t36642 WHERE k=10 391 ---- 392 · distributed true 393 · vectorized true 394 scan · · 395 · table t36642@tertiary 396 · spans /10-/11 397 398 query T retry 399 EXPLAIN (OPT, CATALOG) SELECT * FROM t 400 ---- 401 TABLE t 402 ├── k int not null 403 ├── v string 404 ├── FAMILY fam_0_k_v (k, v) 405 ├── INDEX primary 406 │ ├── k int not null 407 │ └── ZONE 408 │ ├── constraints: [+region=test] 409 │ └── lease preference: [+region=test,+dc=dc1] 410 ├── INDEX secondary 411 │ ├── k int not null 412 │ ├── v string (storing) 413 │ └── ZONE 414 │ ├── constraints: [+region=test] 415 │ └── lease preference: [+region=test,+dc=dc2] 416 └── INDEX tertiary 417 ├── k int not null 418 ├── v string (storing) 419 └── ZONE 420 ├── constraints: [+region=test] 421 └── lease preference: [+region=test,+dc=dc1] 422 scan t 423 424 425 # ------------------------------------------------------------------------------ 426 # Regression for issue #36644. Allow matching constraints for leading locality 427 # tiers to be omitted. 428 # ------------------------------------------------------------------------------ 429 430 statement ok 431 CREATE TABLE t36644 ( 432 k INT PRIMARY KEY, 433 v STRING, 434 INDEX secondary (k) STORING (v), 435 INDEX tertiary (k) STORING (v), 436 FAMILY (k, v) 437 ); 438 439 statement ok 440 ALTER INDEX t36644@secondary 441 CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+dc=dc1]]' 442 443 query TTT retry 444 EXPLAIN SELECT * FROM t36644 WHERE k=10 445 ---- 446 · distributed true 447 · vectorized true 448 scan · · 449 · table t36644@secondary 450 · spans /10-/11 451 452 statement ok 453 ALTER INDEX t36644@secondary CONFIGURE ZONE USING lease_preferences='[[+dc=dc3]]' 454 455 statement ok 456 ALTER INDEX t36644@tertiary 457 CONFIGURE ZONE USING constraints='[+region=test]', lease_preferences='[[+dc=dc1]]' 458 459 query TTT retry 460 EXPLAIN SELECT * FROM t36644 WHERE k=10 461 ---- 462 · distributed true 463 · vectorized true 464 scan · · 465 · table t36644@tertiary 466 · spans /10-/11 467 468 subtest regression_35756 469 470 statement ok 471 CREATE TABLE t35756 (x INT PRIMARY KEY) 472 PARTITION BY LIST (x) ( 473 PARTITION x1 VALUES IN (1), 474 PARTITION DEFAULT VALUES IN (DEFAULT) 475 ) 476 477 # When using partitions, if the partition has no zone config set, SHOW 478 # should display the default zone config and NOT display the "PARTITION" 479 # keyword. 480 481 query TT 482 SHOW ZONE CONFIGURATION FOR PARTITION x1 OF TABLE t35756 483 ---- 484 RANGE default ALTER RANGE default CONFIGURE ZONE USING 485 range_min_bytes = 134217728, 486 range_max_bytes = 536870912, 487 gc.ttlseconds = 90000, 488 num_replicas = 3, 489 constraints = '[]', 490 lease_preferences = '[]' 491 492 # Regression test for #38391: verify that altering an index's partition really 493 # modifies the partition. 494 495 statement ok 496 CREATE TABLE t38391 ( 497 x INT, y INT, z INT, 498 PRIMARY KEY(x, y), 499 INDEX foo (x, z) PARTITION BY LIST (x) ( 500 PARTITION x1_idx VALUES IN (1), 501 PARTITION DEFAULT VALUES IN (DEFAULT) 502 )) 503 PARTITION BY LIST (x) ( 504 PARTITION x1 VALUES IN (1), 505 PARTITION DEFAULT_idx VALUES IN (DEFAULT) 506 ) 507 508 statement ok 509 ALTER PARTITION x1_idx OF INDEX t38391@foo CONFIGURE ZONE USING gc.ttlseconds = 31337 510 511 query TT 512 SHOW ZONE CONFIGURATION FOR PARTITION x1_idx OF INDEX t38391@foo 513 ---- 514 PARTITION x1_idx OF INDEX t38391@foo ALTER PARTITION x1_idx OF INDEX t38391@foo CONFIGURE ZONE USING 515 range_min_bytes = 134217728, 516 range_max_bytes = 536870912, 517 gc.ttlseconds = 31337, 518 num_replicas = 3, 519 constraints = '[]', 520 lease_preferences = '[]' 521 522 statement ok 523 CREATE TABLE dup_constraint (x INT PRIMARY KEY); 524 525 statement ok 526 ALTER TABLE dup_constraint PARTITION BY LIST (x) ( 527 PARTITION p1 VALUES IN (1), 528 PARTITION p2 VALUES IN (2) 529 ) 530 531 statement error pq: incompatible zone constraints: "\+region=us-east1" and "\+region=us-west1" 532 ALTER PARTITION p1 OF TABLE dup_constraint CONFIGURE ZONE USING 533 CONSTRAINTS='[+region=us-east1, +region=us-west1]' 534 535 statement error pq: incompatible zone constraints: "\+region=us-east1" and "\-region=us-east1" 536 ALTER PARTITION p1 OF TABLE dup_constraint CONFIGURE ZONE USING 537 CONSTRAINTS='[+region=us-east1, -region=us-east1]' 538 539 # Create various identifiers with the space character to test quoting in target 540 # names. 541 statement ok 542 CREATE DATABASE "my database"; 543 USE "my database"; 544 CREATE TABLE "my table" (x INT PRIMARY KEY) PARTITION BY LIST (x) ( 545 PARTITION "my partition" VALUES IN (1) 546 ); 547 CREATE INDEX "my index" ON "my table" (x) PARTITION BY LIST (x) ( 548 PARTITION "my partition" VALUES IN (1) 549 ); 550 ALTER DATABASE "my database" CONFIGURE ZONE USING num_replicas = 1; 551 ALTER TABLE "my table" CONFIGURE ZONE USING num_replicas = 1; 552 ALTER INDEX "my table"@"my index" CONFIGURE ZONE USING num_replicas = 1; 553 ALTER PARTITION "my partition" OF INDEX "my table"@primary CONFIGURE ZONE USING num_replicas = 1; 554 ALTER PARTITION "my partition" OF INDEX "my table"@"my index" CONFIGURE ZONE USING num_replicas = 1 555 556 query TTTTTT 557 SELECT target, range_name, database_name, table_name, index_name, partition_name 558 FROM crdb_internal.zones 559 ---- 560 RANGE default default NULL NULL NULL NULL 561 DATABASE system NULL system NULL NULL NULL 562 RANGE meta meta NULL NULL NULL NULL 563 RANGE system system NULL NULL NULL NULL 564 RANGE liveness liveness NULL NULL NULL NULL 565 TABLE system.public.replication_constraint_stats NULL system replication_constraint_stats NULL NULL 566 TABLE system.public.replication_stats NULL system replication_stats NULL NULL 567 TABLE test.public.t NULL test t NULL NULL 568 INDEX test.public.t@secondary NULL test t secondary NULL 569 INDEX test.public.t@tertiary NULL test t tertiary NULL 570 INDEX test.public.t36642@secondary NULL test t36642 secondary NULL 571 INDEX test.public.t36642@tertiary NULL test t36642 tertiary NULL 572 INDEX test.public.t36644@secondary NULL test t36644 secondary NULL 573 INDEX test.public.t36644@tertiary NULL test t36644 tertiary NULL 574 PARTITION x1_idx OF INDEX test.public.t38391@foo NULL test t38391 foo x1_idx 575 DATABASE "my database" NULL my database NULL NULL NULL 576 TABLE "my database".public."my table" NULL my database my table NULL NULL 577 INDEX "my database".public."my table"@"my index" NULL my database my table my index NULL 578 PARTITION "my partition" OF INDEX "my database".public."my table"@primary NULL my database my table primary my partition 579 PARTITION "my partition" OF INDEX "my database".public."my table"@"my index" NULL my database my table my index my partition 580 581 # Test the zone information being displayed in SHOW CREATE 582 statement ok 583 CREATE TABLE show_test (x INT PRIMARY KEY) PARTITION BY LIST (x) ( 584 PARTITION p1 VALUES IN (1), 585 PARTITION p2 VALUES IN (2) 586 ) 587 588 statement ok 589 ALTER PARTITION p1 OF TABLE show_test CONFIGURE ZONE USING CONSTRAINTS='[+dc=dc1]' 590 591 statement ok 592 ALTER PARTITION p2 OF TABLE show_test CONFIGURE ZONE USING CONSTRAINTS='[+dc=dc2]' 593 594 query TT 595 SHOW CREATE TABLE show_test 596 ---- 597 show_test CREATE TABLE show_test ( 598 x INT8 NOT NULL, 599 CONSTRAINT "primary" PRIMARY KEY (x ASC), 600 FAMILY "primary" (x) 601 ) PARTITION BY LIST (x) ( 602 PARTITION p1 VALUES IN ((1)), 603 PARTITION p2 VALUES IN ((2)) 604 ); 605 ALTER PARTITION p1 OF INDEX "my database".public.show_test@primary CONFIGURE ZONE USING 606 constraints = '[+dc=dc1]'; 607 ALTER PARTITION p2 OF INDEX "my database".public.show_test@primary CONFIGURE ZONE USING 608 constraints = '[+dc=dc2]' 609 610 # test warnings on table creation 611 statement ok 612 CREATE TABLE warning (x INT PRIMARY KEY) 613 614 statement ok 615 ALTER TABLE warning PARTITION BY LIST (x) (PARTITION p1 VALUES IN (1)) 616 617 query TT 618 SHOW CREATE warning 619 ---- 620 warning CREATE TABLE warning ( 621 x INT8 NOT NULL, 622 CONSTRAINT "primary" PRIMARY KEY (x ASC), 623 FAMILY "primary" (x) 624 ) PARTITION BY LIST (x) ( 625 PARTITION p1 VALUES IN ((1)) 626 ) 627 -- Warning: Partitioned table with no zone configurations. 628 629 subtest alter_partition_across_all_indexes 630 631 statement ok 632 CREATE TABLE t2 (x INT PRIMARY KEY) PARTITION BY LIST (x) ( 633 PARTITION p1 VALUES IN (1), 634 PARTITION p2 VALUES IN (2) 635 ); 636 CREATE INDEX x1 ON t2 (x) PARTITION BY LIST (x) ( 637 PARTITION p1 VALUES IN (1), 638 PARTITION p2 VALUES IN (2) 639 ); 640 CREATE INDEX x2 ON t2 (x) PARTITION BY LIST (x) ( 641 PARTITION p1 VALUES IN (1), 642 PARTITION p2 VALUES IN (2), 643 PARTITION p3 VALUES IN (3) 644 ) 645 646 statement ok 647 ALTER PARTITION p1 OF INDEX t2@* CONFIGURE ZONE USING num_replicas = 1 648 649 query T 650 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.partitioning.alter-all-partitions' AND usage_count > 0 651 ---- 652 sql.partitioning.alter-all-partitions 653 654 statement error index "t2" does not exist\nHINT: try specifying the index as <tablename>@<indexname> 655 ALTER PARTITION p1 OF INDEX t2 CONFIGURE ZONE USING num_replicas = 1 656 657 query TT 658 SELECT * FROM [SHOW ALL ZONE CONFIGURATIONS] WHERE target LIKE '%t2@%' 659 ---- 660 PARTITION p1 OF INDEX "my database".public.t2@primary ALTER PARTITION p1 OF INDEX "my database".public.t2@primary CONFIGURE ZONE USING 661 num_replicas = 1 662 PARTITION p1 OF INDEX "my database".public.t2@x1 ALTER PARTITION p1 OF INDEX "my database".public.t2@x1 CONFIGURE ZONE USING 663 num_replicas = 1 664 PARTITION p1 OF INDEX "my database".public.t2@x2 ALTER PARTITION p1 OF INDEX "my database".public.t2@x2 CONFIGURE ZONE USING 665 num_replicas = 1 666 667 # ALTER PARTITION ... OF TABLE should only succeed if the partition name is 668 # unique across all indexes. 669 statement error pq: partition "p1" exists on multiple indexes of table "t2" 670 ALTER PARTITION p1 OF TABLE t2 CONFIGURE ZONE USING num_replicas = 1 671 672 statement ok 673 ALTER PARTITION p3 OF TABLE t2 CONFIGURE ZONE USING num_replicas = 1 674 675 query TT 676 SELECT * FROM [SHOW ALL ZONE CONFIGURATIONS] WHERE target LIKE '%t2@x2%' 677 ---- 678 PARTITION p1 OF INDEX "my database".public.t2@x2 ALTER PARTITION p1 OF INDEX "my database".public.t2@x2 CONFIGURE ZONE USING 679 num_replicas = 1 680 PARTITION p3 OF INDEX "my database".public.t2@x2 ALTER PARTITION p3 OF INDEX "my database".public.t2@x2 CONFIGURE ZONE USING 681 num_replicas = 1 682 683 statement error pq: partition "p4" does not exist on table "t2" 684 ALTER PARTITION p4 OF TABLE t2 CONFIGURE ZONE USING num_replicas = 1 685 686 # regression for #40417 687 statement ok 688 CREATE TABLE t40417 (x INT PRIMARY KEY) 689 690 statement ok 691 ALTER TABLE t40417 PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1)); 692 693 statement ok 694 ALTER PARTITION p1 OF TABLE t40417 CONFIGURE ZONE USING num_replicas = 1 695 696 query TT 697 SHOW CREATE TABLE t40417 698 ---- 699 t40417 CREATE TABLE t40417 ( 700 x INT8 NOT NULL, 701 CONSTRAINT "primary" PRIMARY KEY (x ASC), 702 FAMILY "primary" (x) 703 ) PARTITION BY LIST (x) ( 704 PARTITION p1 VALUES IN ((1)) 705 ); 706 ALTER PARTITION p1 OF INDEX "my database".public.t40417@primary CONFIGURE ZONE USING 707 num_replicas = 1 708 709 subtest authorization 710 711 statement ok 712 CREATE DATABASE auth; 713 CREATE TABLE auth.t (x INT PRIMARY KEY) PARTITION BY LIST (x) ( 714 PARTITION p VALUES IN (1) 715 ); 716 CREATE INDEX x ON auth.t (x) PARTITION BY LIST (x) ( 717 PARTITION p VALUES IN (1) 718 ) 719 720 user testuser 721 722 # User should have no CONFIGURE ZONE abilities by default. 723 statement error only users with the admin role are allowed to alter system ranges 724 ALTER RANGE default CONFIGURE ZONE USING num_replicas = 3 725 726 statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on database auth 727 ALTER DATABASE auth CONFIGURE ZONE USING num_replicas = 3 728 729 statement error pq: only users with the admin role are allowed to alter system tables 730 ALTER TABLE system.jobs CONFIGURE ZONE USING num_replicas = 3 731 732 statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t 733 ALTER TABLE auth.t CONFIGURE ZONE USING num_replicas = 3 734 735 statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t 736 ALTER PARTITION p OF TABLE auth.t CONFIGURE ZONE USING num_replicas = 3 737 738 statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t 739 ALTER PARTITION p OF INDEX auth.t@x CONFIGURE ZONE USING num_replicas = 3 740 741 # Granting CREATE on databases and tables should allow CONFIGURE ZONE on those 742 # objects. 743 user root 744 745 statement ok 746 GRANT CREATE ON DATABASE auth TO testuser 747 748 statement ok 749 GRANT CREATE ON TABLE auth.t TO testuser 750 751 user testuser 752 753 statement ok 754 ALTER DATABASE auth CONFIGURE ZONE USING num_replicas = 3 755 756 user root 757 758 statement ok 759 REVOKE CREATE ON DATABASE auth FROM testuser; 760 REVOKE CREATE ON TABLE auth.t FROM testuser; 761 762 user testuser 763 764 statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on database auth 765 ALTER DATABASE auth CONFIGURE ZONE USING num_replicas = 3 766 767 statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t 768 ALTER TABLE auth.t CONFIGURE ZONE USING num_replicas = 3 769 770 771 # Granting ZONECONFIG privilege should allow configuring zones on database and tables 772 user root 773 774 statement ok 775 CREATE TABLE auth.t2 (x INT PRIMARY KEY) PARTITION BY LIST (x) ( 776 PARTITION p VALUES IN (1) 777 ); 778 GRANT ZONECONFIG ON TABLE auth.t2 to testuser 779 780 user testuser 781 782 statement ok 783 ALTER TABLE auth.t2 CONFIGURE ZONE USING num_replicas = 3 784 785 user root 786 787 statement ok 788 REVOKE ZONECONFIG ON TABLE auth.t2 FROM testuser; 789 GRANT ZONECONFIG ON DATABASE auth TO testuser 790 791 user testuser 792 793 statement ok 794 ALTER DATABASE auth CONFIGURE ZONE USING num_replicas = 3 795 796 # Existing tables should not inherit ZONECONFIG privilege 797 statement error pq: user testuser does not have ZONECONFIG or CREATE privilege on relation t 798 ALTER TABLE auth.t CONFIGURE ZONE USING num_replicas = 3 799 800 # New tables should inherit ZONECONFIG privilege 801 user root 802 803 statement ok 804 CREATE TABLE auth.t3 (x INT PRIMARY KEY) PARTITION BY LIST (x) ( 805 PARTITION p VALUES IN (1) 806 ); 807 CREATE INDEX x ON auth.t3 (x) PARTITION BY LIST (x) ( 808 PARTITION p VALUES IN (1) 809 ); 810 811 user testuser 812 813 statement ok 814 ALTER TABLE auth.t3 CONFIGURE ZONE USING num_replicas = 3 815 816 # Index and rows (partitions) should inherit table permissions 817 statement ok 818 ALTER INDEX auth.t3@x CONFIGURE ZONE USING num_replicas=5; 819 ALTER PARTITION p OF INDEX auth.t3@x CONFIGURE ZONE USING num_replicas = 3 820 821 # Granting the admin role should allow configuring zones on system tables and 822 # ranges. 823 user root 824 825 statement ok 826 GRANT admin TO testuser 827 828 user testuser 829 830 statement ok 831 ALTER RANGE default CONFIGURE ZONE USING num_replicas = 3 832 833 statement ok 834 ALTER TABLE system.jobs CONFIGURE ZONE USING num_replicas = 3 835 836 # Test that index configurations don't infect partition configurations. 837 # Specifically we are testing that values written to infect@primary's 838 # zone configuration does not appear in partition p1 of infect@primary's zone config. 839 statement ok 840 CREATE TABLE infect (x INT PRIMARY KEY); 841 ALTER TABLE infect PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1)); 842 ALTER INDEX infect@primary CONFIGURE ZONE USING num_replicas=5; 843 ALTER PARTITION p1 OF TABLE infect CONFIGURE ZONE USING constraints='[+dc=dc1]' 844 845 query TT 846 SELECT partition_name, zone_config FROM [SHOW PARTITIONS FROM TABLE infect] 847 ---- 848 p1 constraints = '[+dc=dc1]' 849 850 # regression for #38074 851 statement ok 852 CREATE TABLE t38074 (x INT, index i(x)); 853 854 statement ok 855 ALTER INDEX t38074@i CONFIGURE ZONE USING gc.ttlseconds = 80000 856 857 statement ok 858 ALTER TABLE t38074 CONFIGURE ZONE USING gc.ttlseconds = 70000 859 860 # Ensure that the table-level zone configuration is no longer a placeholder. 861 query TTT 862 SELECT table_name, index_name, full_config_sql FROM crdb_internal.zones WHERE 863 table_name='t38074' 864 ---- 865 t38074 NULL ALTER TABLE test.public.t38074 CONFIGURE ZONE USING 866 range_min_bytes = 134217728, 867 range_max_bytes = 536870912, 868 gc.ttlseconds = 70000, 869 num_replicas = 3, 870 constraints = '[]', 871 lease_preferences = '[]' 872 t38074 i ALTER INDEX test.public.t38074@i CONFIGURE ZONE USING 873 range_min_bytes = 134217728, 874 range_max_bytes = 536870912, 875 gc.ttlseconds = 80000, 876 num_replicas = 3, 877 constraints = '[]', 878 lease_preferences = '[]' 879 880 # Regression test for #39994: verify that certain fields have to be set in tandem in indexes and partitions. 881 statement ok 882 CREATE TABLE validateTandemFields (a INT, b INT, c INT, PRIMARY KEY (a, b)) 883 PARTITION BY LIST (a, b) (PARTITION simple VALUES IN ((1, 1), (2, 2), (3, 3))) 884 885 statement error pq: could not validate zone config: range_min_bytes and range_max_bytes must be set together 886 ALTER PARTITION simple OF TABLE validateTandemFields CONFIGURE ZONE USING range_min_bytes = 66666 887 888 statement ok 889 CREATE INDEX secondary 890 ON validateTandemFields (b) 891 PARTITION BY LIST (b) 892 ( 893 PARTITION indexPartition VALUES IN (2, 3, 4) 894 ) 895 896 statement error pq: could not validate zone config: range_min_bytes and range_max_bytes must be set together 897 ALTER INDEX validateTandemFields@secondary CONFIGURE ZONE USING range_min_bytes = 66666 898 899 statement error pq: could not validate zone config: range_min_bytes and range_max_bytes must be set together 900 ALTER PARTITION indexPartition OF INDEX validateTandemFields@secondary CONFIGURE ZONE USING range_min_bytes = 66666 901 902 # Test that copy from parent works as expected. 903 statement ok 904 CREATE TABLE copy_from_parent (x INT PRIMARY KEY); 905 ALTER TABLE copy_from_parent PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1)) 906 907 statement ok 908 ALTER DATABASE test CONFIGURE ZONE USING num_replicas = 7 909 910 # Test that first inheriting from the parent database works correctly. 911 statement ok 912 ALTER TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = COPY FROM PARENT 913 914 query TT 915 SELECT table_name, raw_config_sql FROM crdb_internal.zones WHERE table_name = 'copy_from_parent' 916 ---- 917 copy_from_parent ALTER TABLE test.public.copy_from_parent CONFIGURE ZONE USING 918 num_replicas = 7 919 920 # Test that resetting the field manually works correctly. 921 statement ok 922 ALTER TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = 3 923 924 query TT 925 SELECT table_name, raw_config_sql FROM crdb_internal.zones WHERE table_name = 'copy_from_parent' 926 ---- 927 copy_from_parent ALTER TABLE test.public.copy_from_parent CONFIGURE ZONE USING 928 num_replicas = 3 929 930 # Test that trying to apply COPY FROM PARENT again picks up the parent's value. 931 statement ok 932 ALTER TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = COPY FROM PARENT 933 934 query TT 935 SELECT table_name, raw_config_sql FROM crdb_internal.zones WHERE table_name = 'copy_from_parent' 936 ---- 937 copy_from_parent ALTER TABLE test.public.copy_from_parent CONFIGURE ZONE USING 938 num_replicas = 7 939 940 # Ensure that the table has different zone configurations than its parent in 941 # order to avoid accidentally copying the parent value. 942 statement ok 943 ALTER TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = 6 944 945 # Test that the partition can inherit the table's configuration values. 946 statement ok 947 ALTER PARTITION p1 OF TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = 3 948 949 query TTTT 950 SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones 951 WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name = 'p1' 952 ---- 953 copy_from_parent primary p1 ALTER PARTITION p1 OF INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING 954 num_replicas = 3 955 956 statement ok 957 ALTER PARTITION p1 OF TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = COPY FROM PARENT 958 959 query TTTT 960 SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones 961 WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name = 'p1' 962 ---- 963 copy_from_parent primary p1 ALTER PARTITION p1 OF INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING 964 num_replicas = 6 965 966 statement ok 967 ALTER INDEX copy_from_parent@primary CONFIGURE ZONE USING num_replicas = 5 968 969 query TTTT 970 SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones 971 WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name IS NULL 972 ---- 973 copy_from_parent primary NULL ALTER INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING 974 num_replicas = 5 975 976 977 # Test that an index can inherit from its parent. 978 statement ok 979 ALTER INDEX copy_from_parent@primary CONFIGURE ZONE USING num_replicas = COPY FROM PARENT 980 981 query TTTT 982 SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones 983 WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name IS NULL 984 ---- 985 copy_from_parent primary NULL ALTER INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING 986 num_replicas = 6 987 988 # Test that a partition can inherit from its parent index configuration. 989 990 # First change the index's field value. 991 statement ok 992 ALTER INDEX copy_from_parent@primary CONFIGURE ZONE USING num_replicas = 9 993 994 query TTTT 995 SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones 996 WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name IS NULL 997 ---- 998 copy_from_parent primary NULL ALTER INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING 999 num_replicas = 9 1000 1001 statement ok 1002 ALTER PARTITION p1 OF TABLE copy_from_parent CONFIGURE ZONE USING num_replicas = COPY FROM PARENT 1003 1004 query TTTT 1005 SELECT table_name, index_name, partition_name, raw_config_sql FROM crdb_internal.zones 1006 WHERE table_name = 'copy_from_parent' AND index_name = 'primary' AND partition_name = 'p1' 1007 ---- 1008 copy_from_parent primary p1 ALTER PARTITION p1 OF INDEX test.public.copy_from_parent@primary CONFIGURE ZONE USING 1009 num_replicas = 9 1010 1011 # check that copy from parent on a subzone doesn't accidentally modify the parent zone. 1012 statement ok 1013 CREATE TABLE parent_modify (x INT, INDEX idx (x)); 1014 ALTER TABLE parent_modify CONFIGURE ZONE USING gc.ttlseconds = 700; 1015 ALTER INDEX parent_modify@idx CONFIGURE ZONE USING num_replicas = COPY FROM PARENT 1016 1017 query TTT 1018 SELECT table_name, index_name, raw_config_sql FROM crdb_internal.zones 1019 WHERE table_name = 'parent_modify' AND index_name = 'idx' 1020 ---- 1021 parent_modify idx ALTER INDEX test.public.parent_modify@idx CONFIGURE ZONE USING 1022 num_replicas = 7 1023 1024 query TTT 1025 SELECT table_name, index_name, raw_config_sql FROM crdb_internal.zones 1026 WHERE table_name = 'parent_modify' AND index_name IS NULL 1027 ---- 1028 parent_modify NULL ALTER TABLE test.public.parent_modify CONFIGURE ZONE USING 1029 gc.ttlseconds = 700 1030 1031 # Regression for #48254. Ensure that index rewrites in a primary key 1032 # change don't drop zone configs on rewritten indexes. 1033 statement ok 1034 DROP TABLE IF EXISTS t; 1035 CREATE TABLE t ( 1036 x INT PRIMARY KEY, 1037 y INT NOT NULL, 1038 z INT, 1039 w INT, 1040 INDEX i1 (z), 1041 INDEX i2 (w), 1042 FAMILY (x, y, z, w) 1043 ); 1044 ALTER INDEX t@i1 PARTITION BY LIST (z) ( 1045 PARTITION p1 VALUES IN (1, 2), 1046 PARTITION p2 VALUES IN (3, 4) 1047 ); 1048 ALTER INDEX t@i2 PARTITION BY LIST (w) ( 1049 PARTITION p3 VALUES IN (5, 6), 1050 PARTITION p4 VALUES IN (7, 8) 1051 ); 1052 ALTER PARTITION p1 OF INDEX t@i1 CONFIGURE ZONE USING gc.ttlseconds = 15210; 1053 ALTER PARTITION p2 OF INDEX t@i1 CONFIGURE ZONE USING gc.ttlseconds = 15213; 1054 ALTER PARTITION p3 OF INDEX t@i2 CONFIGURE ZONE USING gc.ttlseconds = 15411; 1055 ALTER PARTITION p4 OF INDEX t@i2 CONFIGURE ZONE USING gc.ttlseconds = 15418; 1056 ALTER TABLE t ALTER PRIMARY KEY USING COLUMNS (y) 1057 1058 # Ensure that all the partitions of i1 and i2 still have their zone configs. 1059 query TT 1060 SHOW CREATE t 1061 ---- 1062 t CREATE TABLE t ( 1063 x INT8 NOT NULL, 1064 y INT8 NOT NULL, 1065 z INT8 NULL, 1066 w INT8 NULL, 1067 CONSTRAINT "primary" PRIMARY KEY (y ASC), 1068 UNIQUE INDEX t_x_key (x ASC), 1069 INDEX i1 (z ASC) PARTITION BY LIST (z) ( 1070 PARTITION p1 VALUES IN ((1), (2)), 1071 PARTITION p2 VALUES IN ((3), (4)) 1072 ), 1073 INDEX i2 (w ASC) PARTITION BY LIST (w) ( 1074 PARTITION p3 VALUES IN ((5), (6)), 1075 PARTITION p4 VALUES IN ((7), (8)) 1076 ), 1077 FAMILY fam_0_x_y_z_w (x, y, z, w) 1078 ); 1079 ALTER PARTITION p1 OF INDEX test.public.t@i1 CONFIGURE ZONE USING 1080 gc.ttlseconds = 15210; 1081 ALTER PARTITION p2 OF INDEX test.public.t@i1 CONFIGURE ZONE USING 1082 gc.ttlseconds = 15213; 1083 ALTER PARTITION p3 OF INDEX test.public.t@i2 CONFIGURE ZONE USING 1084 gc.ttlseconds = 15411; 1085 ALTER PARTITION p4 OF INDEX test.public.t@i2 CONFIGURE ZONE USING 1086 gc.ttlseconds = 15418