Experimental Features

2020-02-16

This page lists the experimental features that are available in CockroachDB 2.1.

{{site.data.alerts.callout_danger}} This page describes experimental features. Their interfaces and outputs are subject to change, and there may be bugs.

If you encounter a bug, please file an issue. {{site.data.alerts.end}}

Session variables

The table below lists the experimental session settings available in CockroachDB 2.1. For a complete list of session variables, see SHOW (session settings).

| Variable | Default Value | Description | |-------------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | experimental_force_lookup_join | 'off' | Indicates whether the planner should try and plan a lookup join where the left side is scanned and index lookups are done on the right side. Will emit a warning if a lookup join cannot be planned. | | experimental_force_split_at | 'off' | Indicates whether checks to prevent incorrect usage of ALTER TABLE ... SPLIT AT should be skipped. | | experimental_force_zigzag_join | 'off' | Reserved for future use. Currently this setting has no effect. | | experimental_serial_normalization | 'rowid' | If set to 'virtual_sequence', make the SERIAL pseudo-type optionally auto-create a sequence for better compatibility with Hibernate sequences. |

SQL statements

Keep SQL audit logs

Log queries against a table to a file. For more information, see ALTER TABLE ... EXPERIMENTAL_AUDIT.

> ALTER TABLE t EXPERIMENTAL_AUDIT SET READ WRITE;

Relocate leases and replicas

You have the following options for controlling lease and replica location:

  1. Relocate leases and replicas using EXPERIMENTAL_RELOCATE
  2. Relocate just leases using EXPERIMENTAL_RELOCATE LEASE

For example, to distribute leases and ranges for N primary keys across N stores in the cluster, run a statement with the following structure:

> ALTER TABLE t EXPERIMENTAL_RELOCATE SELECT ARRAY[<storeid1>, <storeid2>, ..., <storeidN>], <primarykeycol1>, <primarykeycol2>, ..., <primarykeycolN>;

New in v2.1: To relocate just the lease without moving the replicas, run a statement like the one shown below, which moves the lease for the range containing primary key 'foo' to store 1.

> ALTER TABLE t EXPERIMENTAL_RELOCATE LEASE SELECT 1, 'foo';

Show statement fingerprints

If two expressions share the same fingerprint, then they are the identical expression. Fingerprints are used by the cost-based optimizer for plan caching.

Example:

> SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE t;
 index_name |     fingerprint     
------------+---------------------
 primary    | 1999042440040364641
(1 row)

Show a table's ranges

Show the ranges that make up a table or index. For more information, see SHOW EXPERIMENTAL_RANGES.

SHOW EXPERIMENTAL_RANGES FROM TABLE t;

Turn on KV event tracing

Use session tracing (via SHOW TRACE FOR SESSION) to report the replicas of all KV events that occur during its execution.

Example:

> SET tracing = on;
> SELECT * from t;
> SET tracing = off;
> SHOW EXPERIMENTAL_REPLICA TRACE FOR SESSION;
            timestamp             | node_id | store_id | replica_id
----------------------------------+---------+----------+------------
 2018-10-18 15:50:13.345879+00:00 |       3 |        3 |          7
 2018-10-18 15:50:20.628383+00:00 |       2 |        2 |         26

Check for constraint violations with SCRUB

Checks the consistency of UNIQUE indexes, CHECK constraints, and more. Partially implemented; see cockroachdb/cockroach#10425 for details.

{{site.data.alerts.callout_info}} This example uses the "users" table from our open-source, fictional peer-to-peer ride-sharing application,MovR. {{site.data.alerts.end}}

>  EXPERIMENTAL SCRUB table movr.users;
 job_uuid |        error_type        | database | table |                       primary_key                        |         timestamp         | repaired |                                                                                                                                                                         details                                                                                                                                                                         
----------+--------------------------+----------+-------+----------------------------------------------------------+---------------------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          | index_key_decoding_error | movr     | users | ('boston','0009eeb5-d779-4bf8-b1bd-8566533b105c')        | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'06484 Christine Villages\\nGrantport, TN 01572'", "city": "'boston'", "credit_card": "'4634253150884'", "id": "'0009eeb5-d779-4bf8-b1bd-8566533b105c'", "name": "'Jessica Webb'"}}
          | index_key_decoding_error | movr     | users | ('los angeles','0001252c-fc16-4006-b6dc-c6b1a0fd1f5b')   | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'91309 Warner Springs\\nLake Danielmouth, PR 33400'", "city": "'los angeles'", "credit_card": "'3584736360686445'", "id": "'0001252c-fc16-4006-b6dc-c6b1a0fd1f5b'", "name": "'Rebecca Gibson'"}}
          | index_key_decoding_error | movr     | users | ('new york','000169a5-e337-4441-b664-dae63e682980')      | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'0787 Christopher Highway Apt. 363\\nHamptonmouth, TX 91864-2620'", "city": "'new york'", "credit_card": "'4578562547256688'", "id": "'000169a5-e337-4441-b664-dae63e682980'", "name": "'Christopher Johnson'"}}
          | index_key_decoding_error | movr     | users | ('paris','00089fc4-e5b1-48f6-9f0b-409905f228c4')         | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'46735 Martin Summit\\nMichaelview, OH 10906-5889'", "city": "'paris'", "credit_card": "'5102207609888778'", "id": "'00089fc4-e5b1-48f6-9f0b-409905f228c4'", "name": "'Nicole Fuller'"}}
          | index_key_decoding_error | movr     | users | ('rome','000209fc-69a1-4dd5-8053-3b5e5769876d')          | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'473 Barrera Vista Apt. 890\\nYeseniaburgh, CO 78087'", "city": "'rome'", "credit_card": "'3534605564661093'", "id": "'000209fc-69a1-4dd5-8053-3b5e5769876d'", "name": "'Sheryl Shea'"}}
          | index_key_decoding_error | movr     | users | ('san francisco','00058767-1e83-4e18-999f-13b5a74d7225') | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'5664 Acevedo Drive Suite 829\\nHernandezview, MI 13516'", "city": "'san francisco'", "credit_card": "'376185496850202'", "id": "'00058767-1e83-4e18-999f-13b5a74d7225'", "name": "'Kevin Turner'"}}
          | index_key_decoding_error | movr     | users | ('seattle','0002e904-1256-4528-8b5f-abad16e695ff')       | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'81499 Samuel Crescent Suite 631\\nLake Christopherborough, PR 50401'", "city": "'seattle'", "credit_card": "'38743493725890'", "id": "'0002e904-1256-4528-8b5f-abad16e695ff'", "name": "'Mark Williams'"}}
          | index_key_decoding_error | movr     | users | ('washington dc','00007caf-2014-4696-85b0-840e7d8b6db9') | 2018-10-18 16:00:38.65916 | f        | {"error_message": "key ordering did not match datum ordering. IndexDescriptor=ASC", "index_name": "primary", "row_data": {"address": "e'4578 Holder Trafficway\\nReynoldsside, IL 23520-7418'", "city": "'washington dc'", "credit_card": "'30454993082943'", "id": "'00007caf-2014-4696-85b0-840e7d8b6db9'", "name": "'Marie Miller'"}}
(8 rows)

Functions and Operators

The table below lists the experimental SQL functions and operators available in CockroachDB 2.1. For more information, see each function's documentation at Functions and Operators.

| Function | Description | |----------------------------------------------------------------------------------+-------------------------------------------------| | experimental_strftime | Format time using standard strftime notation. | | experimental_strptime | Format time using standard strptime notation. | | experimental_uuid_v4() | Return a UUID. |

See Also