Known Limitations in CockroachDB v24.1

​

New limitations in v24.1

​

PL/pgSQL

• It is not possible to use a variable as a target more than once in the same INTO clause. For example, SELECT 1, 2 INTO x, x;.
• PLpgSQL variable declarations cannot inherit the type of a table row or column using %TYPE or %ROWTYPE syntax.

​

UDFs and stored procedures

• Routines cannot be invoked with named arguments, e.g., SELECT foo(a => 1, b => 2); or SELECT foo(b := 1, a := 2);.
• Routines cannot be created if they reference temporary tables.
• Routines cannot be created with unnamed INOUT parameters. For example, CREATE PROCEDURE p(INOUT INT) AS $$ BEGIN NULL; END; $$ LANGUAGE PLpgSQL;.
• Routines cannot be created if they return fewer columns than declared. For example, CREATE FUNCTION f(OUT sum INT, INOUT a INT, INOUT b INT) LANGUAGE SQL AS $$ SELECT (a + b, b); $$;.
• A RECORD-returning UDF cannot be created without a RETURN statement in the root block, which would restrict the wildcard type to a concrete one.

​

Physical cluster replication fail back to primary cluster

​

Generic query plans

• Because use lookup joins instead of the scans and revscans used by custom query plans, generic query plans do not perform as well as custom query plans in some cases.
• are not included in the . This means a generic query plan built and optimized for a prepared statement in one session cannot be used by another session. To reuse generic query plans for maximum performance, a prepared statement should be executed multiple times instead of prepared and executed once.

​

Limitations from v23.2 and earlier

​

SQL statements

​

Syntax and behavior differences from PostgreSQL

​

AS OF SYSTEM TIME limitations

• CockroachDB does not support placeholders in . The time value must be a constant value embedded in the SQL string.
• The ANALYZE alias of does not support specifying an timestamp. ANALYZE statements use AS OF SYSTEM TIME '-0.001ms' automatically. For more control over the statistics interval, use the CREATE STATISTICS syntax instead.

​

COPY syntax not supported by CockroachDB

• COPY ... WITH FREEZE.
• COPY ... WITH QUOTE.
• COPY ... FROM ... WHERE <expr>.

​

IMPORT INTO limitations

• While importing into an existing table, the table is taken offline.
• After importing into an existing table, will be un-validated and need to be .
• Imported rows must not conflict with existing rows in the table or any unique secondary indexes.
• IMPORT INTO works for only a single existing table.
• IMPORT INTO can sometimes fail with a “context canceled” error, or can restart itself many times without ever finishing. If this is happening, it is likely due to a high amount of disk contention. This can be mitigated by setting the kv.bulk_io_write.max_rate to a value below your max disk write speed. For example, to set it to 10MB/s, execute:
  SET CLUSTER SETTING kv.bulk_io_write.max_rate = '10MB';
  

​

ALTER VIEW limitations

• Changing the statement executed by a view. Instead, you must drop the existing view and create a new view.
• Renaming a view that other views depend on. This feature may be added in the future.

​

Row-Level TTL limitations

• Any queries you run against tables with Row-Level TTL enabled (or against tables that have that reference TTL-enabled tables) 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 .
• Tables with Row-Level TTL can be referenced by . TTL deletes are issued as regular statements, so inbound foreign keys apply. If an inbound foreign key uses ON DELETE RESTRICT and referencing rows exist, the TTL job fails with a foreign key violation.
• 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.

​

CAST expressions containing a subquery with an ENUM target are not supported

​

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 .

​

Using default_int_size session variable in batch of statements

​

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}

​

Current sequence value not checked when updating min/max value

​

null_ordered_last does not produce correct results with tuples

​

Functions and procedures

​

PL/pgSQL support

• PL/pgSQL arguments cannot be referenced with ordinals (e.g., $1, $2).
• The following statements are not supported:
  • FOR loops, including FOR cursor loops, FOR query loops, and FOREACH loops.
  • RETURN NEXT and RETURN QUERY.
  • PERFORM, EXECUTE, GET DIAGNOSTICS, and CASE.
• PL/pgSQL exception blocks cannot catch .
• RAISE statements cannot be annotated with names of schema objects related to the error (i.e., using COLUMN, CONSTRAINT, DATATYPE, TABLE, or SCHEMA).
• RAISE statements message the client directly, and do not produce log output.
• ASSERT debugging checks are not supported.
• RECORD parameters and variables are not supported in .
• Variable shadowing (e.g., declaring a variable with the same name in an inner block) is not supported in PL/pgSQL.
• Syntax for accessing members of composite types without parentheses is not supported.
• NOT NULL variable declarations are not supported.
• Cursors opened in PL/pgSQL execute their queries on opening, affecting performance and resource usage.
• Cursors in PL/pgSQL cannot be declared with arguments.
• OPEN FOR EXECUTE is not supported for opening cursors.
• The print_strict_params option is not supported in PL/pgSQL.
• The FOUND local variable, which checks whether a statement affected any rows, is not supported in PL/pgSQL.
• By default, when a PL/pgSQL variable conflicts with a column name, CockroachDB resolves the ambiguity by treating it as a column reference rather than a variable reference. This behavior differs from PostgreSQL, where an ambiguous column error is reported, and it is possible to change the plpgsql.variable_conflict setting in order to prefer either columns or variables.
• It is not possible to define a RECORD-returning PL/pgSQL function that returns different-typed expressions from different RETURN statements. CockroachDB requires a consistent return type for RECORD-returning functions.
• Variables cannot be declared with an associated collation using the COLLATE keyword.
• Variables cannot be accessed using the label.var_name pattern.

​

UDF and stored procedure support

• Routines cannot be created with an OUT parameter of type RECORD.
• DDL statements (e.g., CREATE TABLE, CREATE INDEX) are not allowed within UDFs or stored procedures.
• Polymorphic types cannot be cast to other types (e.g., TEXT) within routine parameters.
• COMMIT and ROLLBACK statements are not supported within nested procedures.
• User-defined functions are not currently supported in:
  • Expressions (column, index, constraint) in tables.
  • Views.
• User-defined functions cannot call themselves recursively.
• (CTE), recursive or non-recursive, are not supported in (UDF). That is, you cannot use a WITH clause in the body of a UDF.
• The setval function cannot be resolved when used inside UDF bodies.
• Casting subqueries to in UDFs is not supported.

​

Transactions

​

Read Committed features and performance

• Schema changes (e.g., , , ) cannot be performed within explicit READ COMMITTED transactions, and will cause transactions to abort. As a workaround, to SERIALIZABLE.
• READ COMMITTED transactions performing INSERT, UPDATE, or UPSERT cannot access tables in which and constraints exist, the region is not included in the constraint, and the region cannot be computed from the constraint columns.
• Multi-column-family checks during updates are not supported under READ COMMITTED isolation.
• Because locks acquired by checks, , and are fully replicated under READ COMMITTED isolation, some queries experience a delay for Raft replication.
• checks are not performed in parallel under READ COMMITTED isolation.
• statements are less optimized under READ COMMITTED isolation than under SERIALIZABLE isolation. Under READ COMMITTED isolation, SELECT FOR UPDATE and SELECT FOR SHARE usually perform an extra lookup join for every locked table when compared to the same queries under SERIALIZABLE. In addition, some optimization steps (such as de-correlation of correlated ) are not currently performed on these queries.
• Regardless of isolation level, statements in CockroachDB do not prevent insertion of new rows matching the search condition (i.e., ). This matches PostgreSQL behavior at all isolation levels.

​

Follower reads

Exact staleness reads and long-running writes

BEGIN PRIORITY HIGH AS OF SYSTEM TIME follower_read_timestamp();
SELECT ...
SELECT ...
COMMIT;

Exact staleness read timestamps must be far enough in the past

Bounded staleness read limitations

• They must be used in a .
• They must read from a single row.
• They must not require an . In other words, the index used by the read query must be either a , or some other index that covers the entire query by all columns.

cockroach demo

SELECT code FROM promo_codes AS OF SYSTEM TIME with_max_staleness('10s') LIMIT 1;
ERROR: unimplemented: cannot use bounded staleness for queries that may touch more than one row or require an index join
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/67562/v23.2

EXPLAIN SELECT code FROM promo_codes LIMIT 5;

info
-------------------------------------------------------------------------------
  distribution: full
  vectorized: true

  • scan
    estimated row count: 1 (0.10% of the table; stats collected 1 minute ago)
    table: promo_codes@primary
    spans: LIMITED SCAN
    limit: 1
(8 rows)

​

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

• SKIP LOCKED cannot be used for tables with multiple .
• By default under SERIALIZABLE isolation, locks acquired using SELECT ... FOR UPDATE and SELECT ... FOR SHARE are implemented as fast, in-memory . If a or occurs on a range held by an unreplicated lock, the lock is dropped. The following behaviors can occur:
  • 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 and the .
  When running under SERIALIZABLE isolation, SELECT ... FOR UPDATE and SELECT ... FOR SHARE locks should be thought of as best-effort, and should not be relied upon for correctness. Note that is preserved despite this limitation. This limitation is fixed when the enable_durable_locking_for_serializable is set to true. This limitation does not apply to transactions.

​

SET does not ROLLBACK in a transaction

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

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

​

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.

​

CANCEL JOB limitations

• To avoid transaction states that cannot properly , the following statements cannot be cancelled with :
  • DROP statements (e.g., ).
  • ALTER ... RENAME statements (e.g., ).
  • statements.
  • statements, except for those that drop values.
• When an Enterprise is canceled, partially restored data is properly cleaned up. This can have a minor, temporary impact on cluster performance.

​

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.

​

Materialized views inside transactions

• CockroachDB cannot refresh 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
     

​

Schemas and indexes

​

Online schema change limitations

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)

No online schema changes if primary key change in progress

No online 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

​

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: failed to construct index entries during backfill: nextval(): unimplemented: cannot evaluate scalar expressions containing sequence operations in this context
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/42508/v24.1

​

Dropping a column referenced by a partial index

​

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

​

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.

​

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.

​

ALTER COLUMN limitations

• The column is part of an .
• The column has .
• The column owns a .
• The column has a . This will result in an ERROR: ... column ... cannot also have a DEFAULT expression with SQLSTATE: 42P16.
• The ALTER COLUMN TYPE statement is part of a combined ALTER TABLE statement.
• The ALTER COLUMN TYPE statement is inside an .

​

CREATE TABLE AS limitations

​

Remove a UNIQUE index created as part of CREATE TABLE

​

Max size of a single column family

​

Dropping a single partition

​

Placeholders in PARTITION BY

​

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 .

​

Unsupported full-text search features

• Aspects of other than the specified dictionary.
• websearch_to_tsquery() built-in function.
• tsquery_phrase() built-in function.
• ts_rank_cd() built-in function.
• setweight() built-in function.
• Inverted joins on TSVECTOR values.
• tsvector || tsvector comparisons.
• tsquery || tsquery comparisons.
• tsquery && tsquery comparisons.
• tsquery <-> tsquery comparisons.
• !! tsquery comparisons.
• tsquery @> tsquery and tsquery <@ tsquery comparisons.

​

CockroachDB does not allow inverted indexes with STORING

​

Multiple arbiter indexes for INSERT ON CONFLICT DO UPDATE

​

Expression index limitations

• The expression cannot reference columns outside the index’s table.
• Functional expression output must be determined by the input arguments. For example, you can’t use the now() to create an index because its output depends on more than just the function arguments.
• CockroachDB does not allow to reference .
• CockroachDB does not support expressions as ON CONFLICT targets. This means that unique cannot be selected as arbiters for statements. For example:
  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
  

​

Secondary regions and regional by row tables

​

Data types

​

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 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)).
• Defining a custom SRID by inserting rows into is not currently supported.

​

OID limitations

​

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 .
• Updating subfields of composite types using dot syntax results in a syntax error.
• Tuple elements cannot be accessed without enclosing the name in parentheses. For example, (v).x.

​

ALTER TYPE limitations

• When running the statement, you can only reference a user-defined type from the database that contains the type.
• You can only ALTER TYPE that drop values. This is because when you drop a value, CockroachDB searches through every row that could contain the type’s value, which could take a long time. All other ALTER TYPE schema change jobs are .

​

JSONB limitations

• You cannot use , , and on JSONB values.

​

Security and privileges

​

GRANT/REVOKE limitations

1. Set the session variable in all sessions by .
2. Apply the allow_role_memberships_to_change_during_transaction setting globally to an entire cluster using the statement:
   ALTER ROLE ALL SET allow_role_memberships_to_change_during_transaction = true;
   

​

DROP OWNED BY limitations

• types are not dropped.
• drops all owned objects as well as any on objects not owned by the .
• If the for which you are trying to DROP OWNED BY was granted a (i.e., using the statement), the following error will be signalled:
  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
  
  The phrase “synthetic privileges” in the error message refers to . The workaround is to use and then use for each privilege in the result.

​

Privileges for DELETE and UPDATE

​

Deployment and operations

​

Admission control

​

Data domiciling

• When columns are , a subset of data from the indexed columns may appear in or other system tables. CockroachDB synchronizes these system ranges and system tables across nodes. This synchronization does not respect any multi-region settings applied via either the , or the low-level mechanism.
• can be used for data placement but these features were historically built for performance, not for domiciling. The replication system’s top priority is to prevent the loss of data and it may override the zone configurations if necessary to ensure data durability. For more information, see .
• If your are kept in the region where they were generated, there is some cross-region leakage (like the system tables described previously), but the majority of user data that makes it into the logs is going to be homed in that region. If that’s not strong enough, you can use the to strip all raw data from the logs. You can also limit your log retention entirely.
• If you start a node with a flag that says the node is in region A, but the node is actually running in some region B, data domiciling based on the inferred node placement will not work. A CockroachDB node only knows its locality based on the text supplied to the --locality flag; it can not ensure that it is actually running in that physical location.

​

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.

​

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

​

Simultaneous client connections and running queries on a single node

​

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.

​

Size limits on statement input from SQL clients

​

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

​

Spatial features disabled for ARM Macs

​

Logging system limitations

​

Per-replica circuit breaker limitations

• They cannot prevent requests from hanging when the node’s is unavailable. For more information about troubleshooting a cluster that’s having node liveness issues, see .
• They are not tripped if all replicas of a range , because the circuit breaker mechanism operates per-replica. This means at least one replica needs to be available to receive the request in order for the breaker to trip.

​

Kubernetes limitations

​

Observability

​

Datadog

​

DB Console may become inaccessible for secure clusters

​

Available capacity metric in the DB Console

​

Disaster recovery

​

Physical cluster replication

• Physical cluster replication is supported in CockroachDB self-hosted clusters on v23.2 or later. The primary cluster can be a or cluster. The standby cluster must be a .
• Read queries are not supported on the standby cluster before .
• In CockroachDB self-hosted, the primary and standby clusters must have the same in order to respect data placement configurations.
• After the for , will continue on the promoted cluster. You will need to manage or the schedule on the promoted standby cluster to avoid two clusters running the same changefeed to one sink.
• After a cutover, there is no mechanism to stop applications from connecting to the original primary cluster. It is necessary to redirect application traffic manually, such as by using a network load balancer or adjusting DNS records.

​

RESTORE limitations

• RESTORE will not restore a table that references a , unless you skip restoring the function with the option. Alternatively, take a to include everything needed to restore the table.
• 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 destination 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";
  

​

Enterprise BACKUP does not capture database/table/column comments

​

SHOW BACKUP does not support symlinks for nodelocal

​

Change data capture

• 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 .
• After the for , will continue on the promoted cluster. You will need to manage or the schedule on the promoted standby cluster to avoid two clusters running the same changefeed to one sink.
• 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 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.
• Creating a changefeed with on tables with more than one is not supported.
• When you create a changefeed on a table with more than one , 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.

​

ALTER CHANGEFEED limitations

• It is necessary to the changefeed before performing any statement.
• CockroachDB does not keep track of the option applied to tables when it is set to yes or only. For example:
  ALTER CHANGEFEED {job_ID} ADD table WITH initial_scan = 'yes';
  
  This will trigger an initial scan of the table and the changefeed will track table. The changefeed will not track initial_scan specified as an option, so it will not display in the output or after a SHOW CHANGEFEED JOB statement.
• is not fully supported with changefeeds that use . You can alter the options that a changefeed uses, but you cannot alter the changefeed target tables.

​

Performance optimization

​

Optimizer and locking behavior

• The new implementation of SELECT FOR UPDATE is not yet the default setting under SERIALIZABLE isolation. It can be used under SERIALIZABLE isolation by setting the optimizer_use_lock_op_for_serializable to true.
• SELECT FOR UPDATE does not lock completely-NULL column families in multi-column-family tables.

​

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.

​

Incorrect query plans for partitions with NULL values

​

Vectorized engine limitations

• The vectorized engine does not support queries containing a join filtered with an .
• The vectorized engine does not support . Queries with or will revert to the row-oriented execution engine.

​

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

​

Using LIKE...ESCAPE in WHERE and HAVING constraints

​

Import with a high amount of disk contention

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

​

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)

​

Locality optimized search limitations

• 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 .
• works only for queries selecting a limited number of records (up to 100,000 unique keys).

​

Query plans for materialized views

• The optimizer may not select the most optimal query plan when querying materialized views because CockroachDB does not on materialized views.

​

Inverted join for tsvector and tsquery types is not supported