Show configuration of duplicate indexes pattern

[lin-crl]

Re: Duplicate Indexes Topology

Issue Description

According to Eng, SHOW CREATE TABLE is not expected to to include the lease preferences/constraints. You could find this information by running SHOW ZONE CONFIGURATION FROM INDEX however.

Suggested Resolution

use SHOW ZONE CONFIGURATION FROM INDEX to show the configuration of duplicate index was done correctly.

[jseldess]

@lin-crl, who did you speak to on eng about this? In 19.2, we announced:

There are now also several ways to view the details of partitions and confirm they are in effect, from the outputs of SHOW CREATE TABLE and the table schemas listed on the Databases page in the Admin UI to the output of SHOW PARTITIONS.

I'm confident that the commands on the dup indexes page and other topology pattern pages were accurate previously. Has this changed in 21.1?

@awoods187, do you have insights here?

[lin-crl]

HI Jesse, I was working w/ a customer and found show create table doesn't show zone config for indexes. and Eng suggested to use SHOW ZONE CONFIG FOR instead. details here https://github.com/cockroachlabs/support/issues/968

[jseldess]

Hmm, it works fine on 20.1. @awoods187, do you know when/why this changed since then?

~/Downloads/cockroach-v20.1.16.darwin-10.9-amd64$ ./cockroach demo --nodes=3 --demo-locality=region=us-east:region=us-west:region=us-central
#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB cluster of 3 nodes.
#
# This demo session will attempt to enable enterprise features
# by acquiring a temporary license from Cockroach Labs in the background.
# To disable this behavior, set the environment variable
# COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true.
#
# Beginning initialization of the movr dataset, please wait...
#
# The cluster has been preloaded with the "movr" dataset
# (MovR is a fictional vehicle sharing company).
#
# Reminder: your changes to data stored in the demo session will not be saved!
#
# Connection parameters:
#   (console) http://127.0.0.1:60430
#   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fj5%2Fjkvm_vyn47dfnpw4bb1_n8l00000gn%2FT%2Fdemo919017129&port=26257
#   (sql/tcp) postgres://root@127.0.0.1:60432?sslmode=disable
#
# To display connection parameters for other nodes, use \demo ls.
#
# Cockroach demo is running in insecure mode.
# Run with --insecure=false to use security related features.
# Note: Starting in secure mode will become the default in v20.2.
#
# Server version: CockroachDB CCL v20.1.16 (x86_64-apple-darwin14, built 2021/05/10 19:21:30, go1.13.9) (same version as client)
# Cluster ID: 18706620-487d-4b5b-8631-763b050f3d51
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
#
root@127.0.0.1:60432/movr> CREATE TABLE postal_codes (
    id INT PRIMARY KEY,
    code STRING
);
CREATE TABLE

Time: 7.544ms

root@127.0.0.1:60432/movr> ALTER TABLE postal_codes
    CONFIGURE ZONE USING
      num_replicas = 3,
      constraints = '{"+region=us-west":1}',
      lease_preferences = '[[+region=us-west]]';
CONFIGURE ZONE 1

Time: 7.82ms

root@127.0.0.1:60432/movr> CREATE INDEX idx_central ON postal_codes (id)
    STORING (code);
CREATE INDEX

Time: 104.273ms

root@127.0.0.1:60432/movr> CREATE INDEX idx_east ON postal_codes (id)
    STORING (code);
CREATE INDEX

Time: 188.729ms

root@127.0.0.1:60432/movr> ALTER INDEX postal_codes@idx_central
    CONFIGURE ZONE USING
      num_replicas = 3,
      constraints = '{"+region=us-central":1}',
      lease_preferences = '[[+region=us-central]]';
CONFIGURE ZONE 1

Time: 9.536ms

root@127.0.0.1:60432/movr> ALTER INDEX postal_codes@idx_east
    CONFIGURE ZONE USING
      num_replicas = 3,
      constraints = '{"+region=us-east":1}',
      lease_preferences = '[[+region=us-east]]';
CONFIGURE ZONE 1

Time: 11.526ms

root@127.0.0.1:60432/movr> SHOW CREATE TABLE postal_codes;
   table_name  |                           create_statement
---------------+------------------------------------------------------------------------
  postal_codes | CREATE TABLE postal_codes (
               |     id INT8 NOT NULL,
               |     code STRING NULL,
               |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
               |     INDEX idx_central (id ASC) STORING (code),
               |     INDEX idx_east (id ASC) STORING (code),
               |     FAMILY "primary" (id, code)
               | );
               | ALTER TABLE movr.public.postal_codes CONFIGURE ZONE USING
               |     num_replicas = 3,
               |     constraints = '{+region=us-west: 1}',
               |     lease_preferences = '[[+region=us-west]]';
               | ALTER INDEX movr.public.postal_codes@idx_central CONFIGURE ZONE USING
               |     num_replicas = 3,
               |     constraints = '{+region=us-central: 1}',
               |     lease_preferences = '[[+region=us-central]]';
               | ALTER INDEX movr.public.postal_codes@idx_east CONFIGURE ZONE USING
               |     num_replicas = 3,
               |     constraints = '{+region=us-east: 1}',
               |     lease_preferences = '[[+region=us-east]]'
(1 row)

Time: 106.278ms

[jseldess]

Turns out this is a bug in 20.2 and 21.1. Fix is in review: cockroachdb/cockroach#65167. Let's hold off on changing these docs.

[awoods187]

That SGTM

[lin-crl]

Thanks for following up @jseldess

[jseldess]

Fix has been merged and will be backported to 20.2. Closing this issue.