Known Limitations in CockroachDB v23.1

​

New limitations

​

Limitations for EXPLAIN ANALYZE

• does not collect inverted statistics on columns that are indexed with both forward and inverted indexes; only forward statistics are collected for those columns.
• does not support the AS OF SYSTEM TIME syntax. Use instead.

​

Limitations for index recommendations

• recommendations are not aware of .
• CockroachDB does not support recommendations on .

​

Limitations for SELECT FOR UPDATE

• places locks on each key scanned by the base index scan. This means that even if some of those keys are later filtered out by a predicate which could not be pushed into the scan, they will still be locked.
• only places an unreplicated lock on the index being scanned by the query. This diverges from PostgreSQL, which aquires a lock on all indexes.

​

Limitations for composite types

• Changefeed types are not fully integrated with . Running changefeeds with user-defined composite types is in . Certain changefeed types do not support user-defined composite types. Refer to the change data capture for more detail. The following limitations apply:
  • A changefeed in will not be able to serialize .
  • A changefeed emitting will include AS labels in the message format when the changefeed serializes a .

​

Common table expressions are not supported in user-defined functions

​

Low estimated Request Units are rounded to zero

​

AS OF SYSTEM TIME does not support placeholders

​

null_ordered_last does not produce correct results with tuples

​

Inverted join for tsvector and tsquery types is not supported

​

No guaranteed state switch from DECOMMISSIONING to DECOMMISSIONED if node decommission is interrupted

• The cockroach node decommission --wait-all command was run and then interrupted
• The cockroach node decommission --wait=none command was run

​

Execution locality in changefeeds

• Changefeeds that use the set to a could create a plan that assigns most of the ranges to an on the coordinator node. This leads to an unbalanced plan and slow changefeed progress, particularly when the table is large and has many ranges.

​

Unresolved limitations

​

Limitations for user-defined functions (UDFs)

​

Limitations on use of UDFs

• Expressions (column, index, constraint) in tables.
• Views.
• Other user-defined functions.

​

Limitations on expressions allowed within UDFs

• Mutation statements such as INSERT, UPDATE, DELETE, and UPSERT.
• Common table expressions (CTEs).
• References to other user-defined functions.

​

Table-level restore will not restore user-defined functions

​

Incorrect query plans for partitions with NULL values

​

Limitations for DROP OWNED BY

​

DROP OWNED BY is not supported where role has system-level privileges

• If the for which you are trying to DROP OWNED BY was granted a (i.e., using the statement), the error shown below will be signalled. The workaround is to use and then use for each privilege in the result.
  ERROR: cannot perform drop owned by if role has synthetic privileges; foo has entries in system.privileges
  SQLSTATE: 0A000
  HINT: perform REVOKE SYSTEM ... for the relevant privileges foo has in system.privileges
  

​

Spatial features disabled for ARM Macs

​

Limited SQL cursor support

• DECLARE only supports forward cursors. Reverse cursors created with DECLARE SCROLL are not supported.
• FETCH supports forward, relative, and absolute variants, but only for forward cursors.
• BINARY CURSOR, which returns data in the Postgres binary format, is not supported.
• WITH HOLD, which allows keeping a cursor open for longer than a transaction by writing its results into a buffer, is accepted as valid syntax within a single transaction but is not supported. It acts as a no-op and does not actually perform the function of WITH HOLD, which is to make the cursor live outside its parent transaction. Instead, if you are using WITH HOLD, you will be forced to close that cursor within the transaction it was created in.
  • This syntax is accepted (but does not have any effect):
    BEGIN;
    DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM foo ORDER BY bar;
    CLOSE test_cur;
    COMMIT;
    
  • This syntax is not accepted, and will result in an error:
    BEGIN;
    DECLARE test_cur CURSOR WITH HOLD FOR SELECT * FROM foo ORDER BY bar;
    COMMIT; -- This will fail with an error because CLOSE test_cur was not called inside the transaction.
    
• Scrollable cursor (also known as reverse FETCH) is not supported.
• with a cursor is not supported.
• Respect for is not supported. Cursor definitions do not disappear properly if rolled back to a SAVEPOINT from before they were created.

​

SELECT FOR UPDATE locks are dropped on lease transfers and range splits/merges

• The desired ordering of concurrent accesses to one or more rows of a table expressed by your use of SELECT ... FOR UPDATE may not be preserved (that is, a transaction B against some table T that was supposed to wait behind another transaction A operating on T may not wait for transaction A).
• The transaction that acquired the (now dropped) unreplicated lock may fail to commit, leading to .

​

Unsupported trigram syntax

• word_similarity() built-in function.
• strict_word_similarity() built-in function.
• %> and <% comparisons and acceleration.
• <<% and %>> comparisons and acceleration.
• <->, <<->, <->>, <<<->, and <->>> comparisons.
• Acceleration on .
• % comparisons, show_trgm, and trigram index creation on .

​

A multi-region table cannot be restored into a non-multi-region table

​

Statements containing multiple modification subqueries of the same table are disallowed

• Set the sql.multiple_modifications_of_table.enabled to true.
• Use the enable_multiple_modifications_of_table .

​

transaction_rows_read_err and transaction_rows_written_err do not halt query execution

​

sql.guardrails.max_row_size_err misses indexed virtual computed columns

​

CockroachDB does not allow inverted indexes with STORING

​

CockroachDB does not properly optimize some left and anti joins with GIN indexes

CREATE DATABASE multi_region_test_db PRIMARY REGION "europe-west1" REGIONS "us-west1", "us-east1" SURVIVE REGION FAILURE;
USE multi_region_test_db;

CREATE TABLE t1 (
  k INT PRIMARY KEY,
  geom GEOMETRY
);

CREATE TABLE t2 (
  k INT PRIMARY KEY,
  geom GEOMETRY,
  INVERTED INDEX geom_idx (geom)
) LOCALITY REGIONAL BY ROW;

INSERT INTO t1 SELECT generate_series(1, 1000), 'POINT(1.0 1.0)';
INSERT INTO t2 (crdb_region, k, geom) SELECT 'us-east1', generate_series(1, 1000), 'POINT(1.0 1.0)';
INSERT INTO t2 (crdb_region, k, geom) SELECT 'us-west1', generate_series(1001, 2000), 'POINT(2.0 2.0)';
INSERT INTO t2 (crdb_region, k, geom) SELECT 'europe-west1', generate_series(2001, 3000), 'POINT(3.0 3.0)';

> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom);
                info
------------------------------------
  distribution: full
  vectorized: true

  • cross join (right outer)
  │ pred: st_contains(geom, geom)
  │
  ├── • scan
  │     estimated row count: 3,000
  │     table: t2@primary
  │     spans: FULL SCAN
  │
  └── • scan
        estimated row count: 1,000
        table: t1@primary
        spans: FULL SCAN
(15 rows)

> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom) AND t2.crdb_region = 'us-east1';
                       info
--------------------------------------------------
  distribution: full
  vectorized: true

  • lookup join (left outer)
  │ table: t2@primary
  │ equality: (crdb_region, k) = (crdb_region,k)
  │ equality cols are key
  │ pred: st_contains(geom, geom)
  │
  └── • inverted join (left outer)
      │ table: t2@geom_idx
      │
      └── • render
          │
          └── • scan
                estimated row count: 1,000
                table: t1@primary
                spans: FULL SCAN
(18 rows)

> EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom) AND t2.crdb_region = 'us-east1'
UNION ALL SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom) AND t2.crdb_region = 'us-west1'
UNION ALL SELECT * FROM t1 LEFT JOIN t2 ON st_contains(t1.geom, t2.geom) AND t2.crdb_region = 'europe-west1';
                           info
----------------------------------------------------------
  distribution: full
  vectorized: true

  • union all
  │
  ├── • union all
  │   │
  │   ├── • lookup join (left outer)
  │   │   │ table: t2@primary
  │   │   │ equality: (crdb_region, k) = (crdb_region,k)
  │   │   │ equality cols are key
  │   │   │ pred: st_contains(geom, geom)
  │   │   │
  │   │   └── • inverted join (left outer)
  │   │       │ table: t2@geom_idx
  │   │       │
  │   │       └── • render
  │   │           │
  │   │           └── • scan
  │   │                 estimated row count: 1,000
  │   │                 table: t1@primary
  │   │                 spans: FULL SCAN
  │   │
  │   └── • lookup join (left outer)
  │       │ table: t2@primary
  │       │ equality: (crdb_region, k) = (crdb_region,k)
  │       │ equality cols are key
  │       │ pred: st_contains(geom, geom)
  │       │
  │       └── • inverted join (left outer)
  │           │ table: t2@geom_idx
  │           │
  │           └── • render
  │               │
  │               └── • scan
  │                     estimated row count: 1,000
  │                     table: t1@primary
  │                     spans: FULL SCAN
  │
  └── • lookup join (left outer)
      │ table: t2@primary
      │ equality: (crdb_region, k) = (crdb_region,k)
      │ equality cols are key
      │ pred: st_contains(geom, geom)
      │
      └── • inverted join (left outer)
          │ table: t2@geom_idx
          │
          └── • render
              │
              └── • scan
                    estimated row count: 1,000
                    table: t1@primary
                    spans: FULL SCAN
(54 rows)

​

Using RESTORE with multi-region table localities

• Restoring and tables into a non-multi-region database is not supported.
• and tables can be restored only if the regions of the backed-up table match those of the target database. All of the following must be true for RESTORE to be successful:
  • The of the source database and the regions of the destination database have the same set of regions.
  • The regions were added to each of the databases in the same order.
  • The databases have the same .
  The following example would be considered as having mismatched regions because the database regions were not added in the same order and the primary regions do not match. Running on the source database:
  ALTER DATABASE source_database SET PRIMARY REGION "us-east1";
  
  ALTER DATABASE source_database ADD region "us-west1";
  
  Running on the destination database:
  ALTER DATABASE destination_database SET PRIMARY REGION "us-west1";
  
  ALTER DATABASE destination_database ADD region "us-east1";
  
  In addition, the following scenario has mismatched regions between the databases since the regions were not added to the database in the same order. Running on the source database:
  ALTER DATABASE source_database SET PRIMARY REGION "us-east1";
  
  ALTER DATABASE source_database ADD region "us-west1";
  
  Running on the destination database:
  ALTER DATABASE destination_database SET PRIMARY REGION "us-west1";
  
  ALTER DATABASE destination_database ADD region "us-east1";
  
  ALTER DATABASE destination_database SET PRIMARY REGION "us-east1";
  

​

SET does not ROLLBACK in a transaction

SET TIME ZONE +2;
BEGIN;
SET TIME ZONE +3;
ROLLBACK;
SHOW TIME ZONE;

timezone
------------
3

​

JSONB/JSON comparison operators are not implemented

SELECT '{"a": 1}'::JSONB -> 'a' < '{"b": 2}'::JSONB -> 'b';

ERROR: unsupported comparison operator: <jsonb> < <jsonb>
SQLSTATE: 22023

​

Locality-optimized search works only for queries selecting a limited number of records

• Locality optimized search works only for queries selecting a limited number of records (up to 100,000 unique keys).

​

Expression indexes cannot reference computed columns

​

Materialized view limitations

• The optimizer may not select the most optimal query plan when querying materialized views because CockroachDB does not on materialized views.
• CockroachDB cannot refresh materialized views inside . Trying to refresh a materialized view inside an explicit transaction will result in an error.
  1. Start with the sample bank data set:
     cockroach demo bank
     
  2. Create the materialized view described in .
  3. Start a new multi-statement transaction with :
     BEGIN TRANSACTION;
     
  4. Inside the open transaction, attempt to . This will result in an error.
     REFRESH MATERIALIZED VIEW overdrawn_accounts;
     
     ERROR: cannot refresh view in an explicit transaction
     SQLSTATE: 25000
     

​

CockroachDB cannot plan locality optimized searches that use partitioned unique indexes on virtual computed columns

• Locality optimized search does not work for queries that use on . A workaround for computed columns is to make the virtual computed column a . Locality optimized search does not work for queries that use partitioned unique .

​

Expressions as ON CONFLICT targets are not supported

CREATE TABLE t (a INT, b INT, UNIQUE INDEX ((a + b)));

CREATE TABLE

INSERT INTO t VALUES (1, 2) ON CONFLICT ((a + b)) DO NOTHING;

invalid syntax: statement ignored: at or near "(": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
INSERT INTO t VALUES (1, 2) ON CONFLICT ((a + b)) DO NOTHING
                                    ^
HINT: try \h INSERT

INSERT INTO t VALUES (1, 2) ON CONFLICT ((a + b)) DO UPDATE SET a = 10;

invalid syntax: statement ignored: at or near "(": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
INSERT INTO t VALUES (1, 2) ON CONFLICT ((a + b)) DO UPDATE SET a = 10
                                    ^
HINT: try \h INSERT

​

Statistics limitations

• The automatically checks whether it needs to refresh statistics for every table in the database upon startup of each node in the cluster. If statistics for a table have not been refreshed in a while, this will trigger collection of statistics for that table. If statistics have been refreshed recently, it will not force a refresh. As a result, the automatic statistics refresher does not necessarily perform a refresh of statistics after an . This could cause a problem, for example, if the upgrade moves from a version without to a version with histograms. To refresh statistics manually, use .
• The following do not immediately take effect, and instead only take effect when new statistics are collected for a table.
  Although shows the settings taking effect immediately, they do not actually take effect until new statistics are collected (as can be verified with ). As a workaround, disable and enable forecasting at the or level. This will invalidate the statistics cache and cause these settings to take effect immediately.
• When a table is dropped, the related rows in system.table_statistics are not deleted. CockroachDB does not delete historical statistics.
• CockroachDB does not collect statistics for . This can prevent the from accurately calculating the cost of scanning an index on a virtual column, and, transitively, the cost of scanning an .

​

Differences in syntax and behavior between CockroachDB and PostgreSQL

​

Multiple arbiter indexes for INSERT ON CONFLICT DO UPDATE

​

Spatial support limitations

• Not all PostGIS spatial functions are supported.
• The AddGeometryColumn only allows constant arguments.
• The AddGeometryColumn spatial function only allows the true value for its use_typmod parameter.
• CockroachDB does not support the @ operator. Instead of using @ in spatial expressions, we recommend using the inverse, with ~. For example, instead of a @ b, use b ~ a.
• CockroachDB does not yet support s into the . This limitation also blocks the ogr2ogr -f PostgreSQL file conversion command.
• CockroachDB does not yet support Triangle or TIN spatial shapes.
• CockroachDB does not yet support Curve, MultiCurve, or CircularString spatial shapes.
• CockroachDB does not yet support k-nearest neighbors.
• CockroachDB does not support using to refer to with type modifiers (e.g., public.geometry(linestring, 4326)). Instead, use fully-unqualified names to refer to data types with type modifiers (e.g., geometry(linestring,4326)). Note that, in output, data type names are prefixed by public.. If the type has a type modifier, you must remove the public. from the type name in order for the statements to work in CockroachDB.

​

Enterprise BACKUP does not capture database/table/column comments

​

SHOW BACKUP does not support symlinks for nodelocal

​

DB Console may become inaccessible for secure clusters

​

Large index keys can impair performance

​

Using LIKE...ESCAPE in WHERE and HAVING constraints

​

Current sequence value not checked when updating min/max value

​

Using default_int_size session variable in batch of statements

​

COPY syntax not supported by CockroachDB

• Various COPY options (FORMAT, FREEZE, QUOTE, etc.).
• COPY ... FROM ... WHERE <expr>.

​

Import with a high amount of disk contention

> SET CLUSTER SETTING kv.bulk_io_write.max_rate = '10MB';

​

Placeholders in PARTITION BY

​

Dropping a single partition

​

Adding a column with sequence-based DEFAULT values

> CREATE TABLE t (x INT);

> INSERT INTO t(x) VALUES (1), (2), (3);

> CREATE SEQUENCE s;

> ALTER TABLE t ADD COLUMN y INT DEFAULT nextval('s');

ERROR: nextval(): unimplemented: cannot evaluate scalar expressions containing sequence operations in this context
SQLSTATE: 0A000

​

Available capacity metric in the DB Console

​

Schema changes within transactions

• You can run schema changes inside the same transaction as a statement. For more information, see . However, a CREATE TABLE statement containing clauses cannot be followed by statements that reference the new table.
• Schema change DDL statements inside a multi-statement transaction can fail while other statements succeed.
• can result in data loss if one of the other schema changes in the transaction fails or is canceled. To work around this, move the DROP COLUMN statement to its own explicit transaction or run it in a single statement outside the existing transaction.

​

Schema change DDL statements inside a multi-statement transaction can fail while other statements succeed

transaction committed but schema change aborted with error: <description of error>
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.

• Creating a unique index fails because values aren’t unique.
• The evaluation of a computed value fails.
• Adding a constraint (or a column with a constraint) fails because the constraint is violated for the default/computed values in the column.

CREATE TABLE T(x INT);
INSERT INTO T(x) VALUES (1), (2), (3);

BEGIN;
ALTER TABLE t ADD CONSTRAINT unique_x UNIQUE(x);
INSERT INTO T(x) VALUES (3);
COMMIT;

pq: transaction committed but schema change aborted with error: (23505): duplicate key value (x)=(3) violates unique constraint "unique_x"
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.

SELECT * FROM t;

  x
+---+
  1
  2
  3
  3
(4 rows)

​

Schema changes between executions of prepared statements

CREATE TABLE users (id INT PRIMARY KEY);

PREPARE prep1 AS SELECT * FROM users;

ALTER TABLE users ADD COLUMN name STRING;

INSERT INTO users VALUES (1, 'Max Roach');

EXECUTE prep1;

ERROR: cached plan must not change result type
SQLSTATE: 0A000

​

Size limits on statement input from SQL clients

​

Using \| to perform a large input in the SQL shell

​

New values generated by DEFAULT expressions during ALTER TABLE ADD COLUMN

• use the default regardless of the search path configured in the current session via SET SEARCH_PATH.
• use the UTC time zone regardless of the time zone configured in the current session via .
• have no default database regardless of the default database configured in the current session via , so you must specify the database of any tables they reference.
• use the transaction timestamp for the statement_timestamp() function regardless of the time at which the ALTER statement was issued.

​

Load-based lease rebalancing in uneven latency deployments

• Your cluster runs in datacenters which are very different distances away from each other.
• Each node was started with a single tier of --locality, e.g., --locality=datacenter=a.
• Most client requests get sent to a single datacenter because that’s where all your application traffic is.

​

Overload resolution for collated strings

> SELECT 'string1' || 'string2';

     ?column?
------------------
  string1string2
(1 row)

> SELECT ('string1' collate en) || ('string2' collate en);

pq: unsupported binary operator: <collatedstring{en} || <collatedstring{en}

​

Max size of a single column family

​

Simultaneous client connections and running queries on a single node

​

Privileges for DELETE and UPDATE

​

ROLLBACK TO SAVEPOINT in high-priority transactions containing DDL

> BEGIN PRIORITY HIGH; SAVEPOINT s; CREATE TABLE t(x INT); ROLLBACK TO SAVEPOINT s;

ERROR: unimplemented: cannot use ROLLBACK TO SAVEPOINT in a HIGH PRIORITY transaction containing DDL
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.

​

CockroachDB does not test for all connection failure scenarios

• When migrating a node to a new machine, keep the server listening at the previous IP address until the cluster has completed the migration.
• Configure any active network firewalls to allow node-to-node traffic.
• Verify that orchestration tools (e.g., Kubernetes) are configured to use the correct network connection information.

​

Some column-dropping schema changes do not roll back properly

• Perform column drops in transactions separate from other schema changes. This ensures that other schema change failures will not cause the column drop to be rolled back.
• Drop all (including ) on the column in a separate transaction, before dropping the column.
• Drop any or on a column before attempting to drop the column. This prevents conflicts between constraints and default/computed values during a column drop rollback.

​

Remove a UNIQUE index created as part of CREATE TABLE

​

Secondary regions and regional by row tables

​

Row-Level TTL limitations

• Any queries you run against tables with Row-Level TTL enabled do not filter out expired rows from the result set (this includes and ). This feature may be added in a future release. For now, follow the instructions in .
• Enabling Row-Level TTL on a table with multiple can have negative performance impacts on a cluster, including increased and . This is particularly true for large tables with terabytes of data and billions of rows that are split up into multiple ranges across separate nodes.
  • Increased latency may occur because secondary indexes aren’t necessarily stored on the same underlying as a table’s . Further, the secondary indexes’ ranges may have located on different nodes than the primary index.
  • Increased contention may occur because must be written as part of performing the deletions.
  • Finally, secondary indexes can also have a negative impact on the overall performance of . According to internal testing, the is worse on tables with secondary indexes. If you encounter this situation, decreasing the may help by decreasing the number of ranges that need to be accessed by the job.

​

Change data capture limitations

• Changefeed target options are limited to tables and .
• and in CockroachDB Advanced clusters do not support connecting to a sink’s internal IP addresses for . To connect to a Kafka sink from CockroachDB Advanced, it is necessary to expose the Kafka cluster’s external IP address and open ports with firewall rules to allow access from a CockroachDB Advanced cluster.
• Webhook sinks only support HTTPS. Use the parameter when testing to disable certificate verification; however, this still requires HTTPS and certificates.
• Formats for changefeed messages are not supported by all changefeed sinks. Refer to the page for details on compatible formats with each sink and the option to specify a changefeed message format.
• Using the and options on the same changefeed will cause an error when using the following : Kafka and Google Cloud Pub/Sub. Instead, use the individual FAMILY keyword to specify column families when creating a changefeed.
• Changefeed types are not fully integrated with . Running changefeeds with user-defined composite types is in . Certain changefeed types do not support user-defined composite types. Refer to the change data capture for more detail. The following limitations apply:
  • A changefeed in will not be able to serialize .
  • A changefeed emitting will include AS labels in the message format when the changefeed serializes a .
• You can only apply CDC queries on a single table in each statement.
• Some , notably functions that return MVCC timestamps, are overridden to return the MVCC timestamp of the event, e.g., transaction_timestamp or statement_timestamp. Additionally, some , such as now() are not supported. We recommend using the transaction_timestamp() function or the crdb_internal_mvcc_timestamp column instead.
• The following are not permitted in CDC queries:
  • .
  • Sub-select queries.
  • and (i.e., functions operating over many rows).
• delete changefeed events will only contain the . All other columns will emit as NULL. See for detail on running a CDC query that emits the deleted values.
• ALTER CHANGEFEED is not fully supported with changefeeds that use CDC queries. . You can alter the options that a changefeed uses, but you cannot alter the changefeed target tables.
• Creating a changefeed with CDC queries on tables with more than one column family is not supported.
• When you create a changefeed on a table with more than one column family , the changefeed will emit messages per column family in separate streams. As a result, for different column families will arrive at the under separate topics.