github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/ccl/logictestccl/testdata/logic_test/distsql_partitioning (about)

     1  # LogicTest: 5node
     2  
     3  # Tests for the show partitions command.
     4  
     5  statement ok
     6  CREATE TABLE t1 (x INT PRIMARY KEY)
     7  
     8  statement ok
     9  ALTER TABLE t1 PARTITION BY LIST (x) (
    10      PARTITION p1 VALUES IN (1),
    11      PARTITION p2 VALUES IN (2),
    12      PARTITION p3 VALUES IN (3)
    13  )
    14  
    15  query TTTTTTTTT colnames
    16  SHOW PARTITIONS FROM DATABASE test
    17  ----
    18  database_name  table_name  partition_name  parent_partition  column_names  index_name  partition_value  zone_config  full_zone_config
    19  test           t1          p1              NULL              x             t1@primary  (1)              NULL         range_min_bytes = 134217728,
    20  range_max_bytes = 536870912,
    21  gc.ttlseconds = 90000,
    22  num_replicas = 3,
    23  constraints = '[]',
    24  lease_preferences = '[]'
    25  test                      t1  p2  NULL  x  t1@primary  (2)  NULL  range_min_bytes = 134217728,
    26  range_max_bytes = 536870912,
    27  gc.ttlseconds = 90000,
    28  num_replicas = 3,
    29  constraints = '[]',
    30  lease_preferences = '[]'
    31  test                      t1  p3  NULL  x  t1@primary  (3)  NULL  range_min_bytes = 134217728,
    32  range_max_bytes = 536870912,
    33  gc.ttlseconds = 90000,
    34  num_replicas = 3,
    35  constraints = '[]',
    36  lease_preferences = '[]'
    37  
    38  statement ok
    39  ALTER PARTITION p1 OF TABLE t1 CONFIGURE ZONE USING constraints='[+dc=dc1]';
    40  ALTER PARTITION p2 OF TABLE t1 CONFIGURE ZONE USING constraints='[+dc=dc2]';
    41  ALTER PARTITION p3 OF TABLE t1 CONFIGURE ZONE USING constraints='[+dc=dc3]'
    42  
    43  query TTTTTTTTT colnames
    44  SHOW PARTITIONS FROM DATABASE test
    45  ----
    46  database_name  table_name  partition_name  parent_partition  column_names  index_name  partition_value  zone_config                full_zone_config
    47  test           t1          p1              NULL              x             t1@primary  (1)              constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
    48  range_max_bytes = 536870912,
    49  gc.ttlseconds = 90000,
    50  num_replicas = 3,
    51  constraints = '[+dc=dc1]',
    52  lease_preferences = '[]'
    53  test                      t1  p2  NULL  x  t1@primary  (2)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
    54  range_max_bytes = 536870912,
    55  gc.ttlseconds = 90000,
    56  num_replicas = 3,
    57  constraints = '[+dc=dc2]',
    58  lease_preferences = '[]'
    59  test                      t1  p3  NULL  x  t1@primary  (3)  constraints = '[+dc=dc3]'  range_min_bytes = 134217728,
    60  range_max_bytes = 536870912,
    61  gc.ttlseconds = 90000,
    62  num_replicas = 3,
    63  constraints = '[+dc=dc3]',
    64  lease_preferences = '[]'
    65  
    66  query TTTTTTTTT
    67  SHOW PARTITIONS FROM TABLE t1
    68  ----
    69  test  t1  p1  NULL  x  t1@primary  (1)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
    70  range_max_bytes = 536870912,
    71  gc.ttlseconds = 90000,
    72  num_replicas = 3,
    73  constraints = '[+dc=dc1]',
    74  lease_preferences = '[]'
    75  test                      t1  p2  NULL  x  t1@primary  (2)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
    76  range_max_bytes = 536870912,
    77  gc.ttlseconds = 90000,
    78  num_replicas = 3,
    79  constraints = '[+dc=dc2]',
    80  lease_preferences = '[]'
    81  test                      t1  p3  NULL  x  t1@primary  (3)  constraints = '[+dc=dc3]'  range_min_bytes = 134217728,
    82  range_max_bytes = 536870912,
    83  gc.ttlseconds = 90000,
    84  num_replicas = 3,
    85  constraints = '[+dc=dc3]',
    86  lease_preferences = '[]'
    87  
    88  query TTTTTTTTT
    89  SHOW PARTITIONS FROM INDEX t1@primary
    90  ----
    91  test  t1  p1  NULL  x  t1@primary  (1)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
    92  range_max_bytes = 536870912,
    93  gc.ttlseconds = 90000,
    94  num_replicas = 3,
    95  constraints = '[+dc=dc1]',
    96  lease_preferences = '[]'
    97  test                      t1  p2  NULL  x  t1@primary  (2)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
    98  range_max_bytes = 536870912,
    99  gc.ttlseconds = 90000,
   100  num_replicas = 3,
   101  constraints = '[+dc=dc2]',
   102  lease_preferences = '[]'
   103  test                      t1  p3  NULL  x  t1@primary  (3)  constraints = '[+dc=dc3]'  range_min_bytes = 134217728,
   104  range_max_bytes = 536870912,
   105  gc.ttlseconds = 90000,
   106  num_replicas = 3,
   107  constraints = '[+dc=dc3]',
   108  lease_preferences = '[]'
   109  
   110  statement ok
   111  CREATE TABLE t2 (x INT PRIMARY KEY)
   112  
   113  statement ok
   114  ALTER TABLE t2 PARTITION BY RANGE (x) (
   115      PARTITION p1 VALUES FROM (1) TO (2),
   116      PARTITION p2 VALUES FROM (2) TO (3)
   117  )
   118  
   119  statement ok
   120  ALTER PARTITION p1 OF TABLE t2 CONFIGURE ZONE USING constraints='[+dc=dc1]';
   121  ALTER PARTITION p2 OF TABLE t2 CONFIGURE ZONE USING constraints='[+dc=dc2]'
   122  
   123  query TTTTTTTTT
   124  SHOW PARTITIONS FROM DATABASE test
   125  ----
   126  test  t1  p1  NULL  x  t1@primary  (1)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
   127  range_max_bytes = 536870912,
   128  gc.ttlseconds = 90000,
   129  num_replicas = 3,
   130  constraints = '[+dc=dc1]',
   131  lease_preferences = '[]'
   132  test                      t1  p2  NULL  x  t1@primary  (2)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
   133  range_max_bytes = 536870912,
   134  gc.ttlseconds = 90000,
   135  num_replicas = 3,
   136  constraints = '[+dc=dc2]',
   137  lease_preferences = '[]'
   138  test                      t1  p3  NULL  x  t1@primary  (3)  constraints = '[+dc=dc3]'  range_min_bytes = 134217728,
   139  range_max_bytes = 536870912,
   140  gc.ttlseconds = 90000,
   141  num_replicas = 3,
   142  constraints = '[+dc=dc3]',
   143  lease_preferences = '[]'
   144  test                      t2  p1  NULL  x  t2@primary  (1) TO (2)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
   145  range_max_bytes = 536870912,
   146  gc.ttlseconds = 90000,
   147  num_replicas = 3,
   148  constraints = '[+dc=dc1]',
   149  lease_preferences = '[]'
   150  test                      t2  p2  NULL  x  t2@primary  (2) TO (3)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
   151  range_max_bytes = 536870912,
   152  gc.ttlseconds = 90000,
   153  num_replicas = 3,
   154  constraints = '[+dc=dc2]',
   155  lease_preferences = '[]'
   156  
   157  query TTTTTTTTT
   158  SHOW PARTITIONS FROM TABLE t2
   159  ----
   160  test  t2  p1  NULL  x  t2@primary  (1) TO (2)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
   161  range_max_bytes = 536870912,
   162  gc.ttlseconds = 90000,
   163  num_replicas = 3,
   164  constraints = '[+dc=dc1]',
   165  lease_preferences = '[]'
   166  test                      t2  p2  NULL  x  t2@primary  (2) TO (3)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
   167  range_max_bytes = 536870912,
   168  gc.ttlseconds = 90000,
   169  num_replicas = 3,
   170  constraints = '[+dc=dc2]',
   171  lease_preferences = '[]'
   172  
   173  query TTTTTTTTT
   174  SHOW PARTITIONS FROM INDEX t2@primary
   175  ----
   176  test  t2  p1  NULL  x  t2@primary  (1) TO (2)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
   177  range_max_bytes = 536870912,
   178  gc.ttlseconds = 90000,
   179  num_replicas = 3,
   180  constraints = '[+dc=dc1]',
   181  lease_preferences = '[]'
   182  test                      t2  p2  NULL  x  t2@primary  (2) TO (3)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
   183  range_max_bytes = 536870912,
   184  gc.ttlseconds = 90000,
   185  num_replicas = 3,
   186  constraints = '[+dc=dc2]',
   187  lease_preferences = '[]'
   188  
   189  statement ok
   190  CREATE TABLE t3 (x INT PRIMARY KEY, y INT, INDEX sec (y))
   191  
   192  statement ok
   193  ALTER TABLE t3 PARTITION BY LIST (x) (
   194      PARTITION p1 VALUES IN (1),
   195      PARTITION p2 VALUES IN (2)
   196  )
   197  
   198  statement ok
   199  ALTER INDEX sec PARTITION BY LIST (y) (
   200      PARTITION p3 VALUES IN (3),
   201      PARTITION p4 VALUES IN (4)
   202  )
   203  
   204  statement ok
   205  ALTER PARTITION p1 OF TABLE t3 CONFIGURE ZONE USING constraints='[+dc=dc1]';
   206  ALTER PARTITION p2 OF TABLE t3 CONFIGURE ZONE USING constraints='[+dc=dc2]';
   207  ALTER PARTITION p3 OF INDEX t3@sec CONFIGURE ZONE USING constraints='[+dc=dc3]';
   208  ALTER PARTITION p4 OF INDEX t3@sec CONFIGURE ZONE USING constraints='[+dc=dc4]'
   209  
   210  query TTTTTTTTT
   211  SHOW PARTITIONS FROM TABLE t3
   212  ----
   213  test  t3  p1  NULL  x  t3@primary  (1)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
   214  range_max_bytes = 536870912,
   215  gc.ttlseconds = 90000,
   216  num_replicas = 3,
   217  constraints = '[+dc=dc1]',
   218  lease_preferences = '[]'
   219  test                      t3  p2  NULL  x  t3@primary  (2)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
   220  range_max_bytes = 536870912,
   221  gc.ttlseconds = 90000,
   222  num_replicas = 3,
   223  constraints = '[+dc=dc2]',
   224  lease_preferences = '[]'
   225  test                      t3  p3  NULL  y  t3@sec  (3)  constraints = '[+dc=dc3]'  range_min_bytes = 134217728,
   226  range_max_bytes = 536870912,
   227  gc.ttlseconds = 90000,
   228  num_replicas = 3,
   229  constraints = '[+dc=dc3]',
   230  lease_preferences = '[]'
   231  test                      t3  p4  NULL  y  t3@sec  (4)  constraints = '[+dc=dc4]'  range_min_bytes = 134217728,
   232  range_max_bytes = 536870912,
   233  gc.ttlseconds = 90000,
   234  num_replicas = 3,
   235  constraints = '[+dc=dc4]',
   236  lease_preferences = '[]'
   237  
   238  query TTTTTTTTT
   239  SHOW PARTITIONS FROM INDEX t3@*
   240  ----
   241  test  t3  p1  NULL  x  t3@primary  (1)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
   242  range_max_bytes = 536870912,
   243  gc.ttlseconds = 90000,
   244  num_replicas = 3,
   245  constraints = '[+dc=dc1]',
   246  lease_preferences = '[]'
   247  test                      t3  p2  NULL  x  t3@primary  (2)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
   248  range_max_bytes = 536870912,
   249  gc.ttlseconds = 90000,
   250  num_replicas = 3,
   251  constraints = '[+dc=dc2]',
   252  lease_preferences = '[]'
   253  test                      t3  p3  NULL  y  t3@sec  (3)  constraints = '[+dc=dc3]'  range_min_bytes = 134217728,
   254  range_max_bytes = 536870912,
   255  gc.ttlseconds = 90000,
   256  num_replicas = 3,
   257  constraints = '[+dc=dc3]',
   258  lease_preferences = '[]'
   259  test                      t3  p4  NULL  y  t3@sec  (4)  constraints = '[+dc=dc4]'  range_min_bytes = 134217728,
   260  range_max_bytes = 536870912,
   261  gc.ttlseconds = 90000,
   262  num_replicas = 3,
   263  constraints = '[+dc=dc4]',
   264  lease_preferences = '[]'
   265  
   266  query TTTTTTTTT
   267  SHOW PARTITIONS FROM INDEX t3@sec
   268  ----
   269  test  t3  p3  NULL  y  t3@sec  (3)  constraints = '[+dc=dc3]'  range_min_bytes = 134217728,
   270  range_max_bytes = 536870912,
   271  gc.ttlseconds = 90000,
   272  num_replicas = 3,
   273  constraints = '[+dc=dc3]',
   274  lease_preferences = '[]'
   275  test                      t3  p4  NULL  y  t3@sec  (4)  constraints = '[+dc=dc4]'  range_min_bytes = 134217728,
   276  range_max_bytes = 536870912,
   277  gc.ttlseconds = 90000,
   278  num_replicas = 3,
   279  constraints = '[+dc=dc4]',
   280  lease_preferences = '[]'
   281  
   282  statement ok
   283  CREATE TABLE t4 (x INT, y INT, PRIMARY KEY (x, y))
   284  
   285  statement ok
   286  ALTER TABLE t4 PARTITION BY LIST (x) (
   287      PARTITION p1 VALUES IN (1) PARTITION BY LIST (y) (
   288          PARTITION p1_a VALUES in (2),
   289          PARTITION p1_b VALUES IN (3)
   290      ),
   291      PARTITION p2 VALUES IN (4) PARTITION BY LIST (y) (
   292          PARTITION p2_a VALUES IN (5)
   293      )
   294  )
   295  
   296  statement ok
   297  ALTER PARTITION p1 OF TABLE t4 CONFIGURE ZONE USING constraints='[+dc=dc1]';
   298  ALTER PARTITION p1_a OF TABLE t4 CONFIGURE ZONE USING constraints='[+dc=dc2]';
   299  ALTER PARTITION p1_b OF TABLE t4 CONFIGURE ZONE USING constraints='[+dc=dc3]';
   300  ALTER PARTITION p2 OF TABLE t4 CONFIGURE ZONE USING constraints='[+dc=dc4]';
   301  ALTER PARTITION p2_a OF TABLE t4 CONFIGURE ZONE USING constraints='[+dc=dc5]'
   302  
   303  query TTTTTTTTT
   304  SHOW PARTITIONS FROM TABLE t4
   305  ----
   306  test  t4  p1  NULL  x  t4@primary  (1)  constraints = '[+dc=dc1]'  range_min_bytes = 134217728,
   307  range_max_bytes = 536870912,
   308  gc.ttlseconds = 90000,
   309  num_replicas = 3,
   310  constraints = '[+dc=dc1]',
   311  lease_preferences = '[]'
   312  test                      t4  p1_a  p1  y  t4@primary  (2)  constraints = '[+dc=dc2]'  range_min_bytes = 134217728,
   313  range_max_bytes = 536870912,
   314  gc.ttlseconds = 90000,
   315  num_replicas = 3,
   316  constraints = '[+dc=dc2]',
   317  lease_preferences = '[]'
   318  test                      t4  p1_b  p1  y  t4@primary  (3)  constraints = '[+dc=dc3]'  range_min_bytes = 134217728,
   319  range_max_bytes = 536870912,
   320  gc.ttlseconds = 90000,
   321  num_replicas = 3,
   322  constraints = '[+dc=dc3]',
   323  lease_preferences = '[]'
   324  test                      t4  p2  NULL  x  t4@primary  (4)  constraints = '[+dc=dc4]'  range_min_bytes = 134217728,
   325  range_max_bytes = 536870912,
   326  gc.ttlseconds = 90000,
   327  num_replicas = 3,
   328  constraints = '[+dc=dc4]',
   329  lease_preferences = '[]'
   330  test                      t4  p2_a  p2  y  t4@primary  (5)  constraints = '[+dc=dc5]'  range_min_bytes = 134217728,
   331  range_max_bytes = 536870912,
   332  gc.ttlseconds = 90000,
   333  num_replicas = 3,
   334  constraints = '[+dc=dc5]',
   335  lease_preferences = '[]'
   336  
   337  # Partitioning inheritance test.
   338  statement ok
   339  CREATE DATABASE partitioning
   340  
   341  statement ok
   342  CREATE TABLE partitioning.inheritance (x INT PRIMARY KEY)
   343  
   344  statement ok
   345  ALTER INDEX partitioning.inheritance@primary PARTITION BY LIST (x) ( PARTITION p1 VALUES IN (1))
   346  
   347  statement ok
   348  ALTER DATABASE partitioning CONFIGURE ZONE USING range_min_bytes=64000, range_max_bytes=75000
   349  
   350  query TTTTTTTTT
   351  SHOW PARTITIONS FROM TABLE partitioning.inheritance
   352  ----
   353  partitioning  inheritance  p1  NULL  x  inheritance@primary  (1)  NULL  range_min_bytes = 64000,
   354  range_max_bytes = 75000,
   355  gc.ttlseconds = 90000,
   356  num_replicas = 3,
   357  constraints = '[]',
   358  lease_preferences = '[]'
   359  
   360  statement ok
   361  ALTER TABLE partitioning.inheritance CONFIGURE ZONE USING gc.ttlseconds=80000
   362  
   363  query TTTTTTTTT
   364  SHOW PARTITIONS FROM TABLE partitioning.inheritance
   365  ----
   366  partitioning  inheritance  p1  NULL  x  inheritance@primary  (1)  NULL  range_min_bytes = 64000,
   367  range_max_bytes = 75000,
   368  gc.ttlseconds = 80000,
   369  num_replicas = 3,
   370  constraints = '[]',
   371  lease_preferences = '[]'
   372  
   373  statement ok
   374  ALTER INDEX partitioning.inheritance@primary CONFIGURE ZONE USING num_replicas=5
   375  
   376  query TTTTTTTTT
   377  SHOW PARTITIONS FROM TABLE partitioning.inheritance
   378  ----
   379  partitioning  inheritance  p1  NULL  x  inheritance@primary  (1)  NULL  range_min_bytes = 64000,
   380  range_max_bytes = 75000,
   381  gc.ttlseconds = 80000,
   382  num_replicas = 5,
   383  constraints = '[]',
   384  lease_preferences = '[]'
   385  
   386  statement ok
   387  ALTER PARTITION p1 OF INDEX partitioning.inheritance@primary CONFIGURE ZONE USING constraints='[+dc=dc1]'
   388  
   389  query TTTTTTTTT
   390  SHOW PARTITIONS FROM TABLE partitioning.inheritance
   391  ----
   392  partitioning  inheritance  p1  NULL  x  inheritance@primary  (1)  constraints = '[+dc=dc1]'  range_min_bytes = 64000,
   393  range_max_bytes = 75000,
   394  gc.ttlseconds = 80000,
   395  num_replicas = 5,
   396  constraints = '[+dc=dc1]',
   397  lease_preferences = '[]'