Docker image
[Multi-platform images](https://docs.docker.com/build/building/multi-platform/) include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.
Within the multi-platform image:
- The ARM image is **Experimental** and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is **Generally Available** for production use.
To download the Docker image:
docker pull cockroachdb/cockroach-unstable:v23.1.0-alpha.2
Source tag
To view or download the source code for CockroachDB v23.1.0-alpha.2 on Github, visit v23.1.0-alpha.2 source tag.
Changelog
View a detailed changelog on GitHub: v23.1.0-alpha.1...v23.1.0-alpha.2
Backward-incompatible changes
CockroachDB now supports sharing storage ranges across multiple indexes/tables. As a result, there is no longer a guarantee that there is at most one SQL object (e.g., table/index/sequence/materialized view) per storage range. Therefore, the columns table_id
, database_name
, schema_name
, table_name
and index_name
in crdb_internal.ranges
and .ranges_no_leases
have become nonsensical: a range cannot be attributed to a single table/index anymore. As a result:
- The aforementioned columns in the
crdb_internal
virtual tables have been removed. Existing code can use the SHOW RANGES
statement instead, optionally using WITH KEYS
to expose the raw start/end keys.
SHOW RANGES FROM DATABASE
continues to report one row per range, but stops returning the database / schema / table / index name.
SHOW RANGES FROM TABLE
continues to report one row per range, but stops returning the index name. Suggested replacements:
- Instead of:
SELECT range_id FROM crdb_internal.ranges WHERE table_name = 'x'
, use: SELECT range_id FROM [SHOW RANGES FROM TABLE x]
- Instead of
SELECT range_id FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2
(variable / unpredictable table name or ID), use: SELECT range_id FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES] WHERE table_name = $1 OR table_id = $2
- Instead of
SELECT start_key FROM crdb_internal.ranges WHERE table_name = 'x'
, use: SELECT raw_start_key FROM [SHOW RANGES FROM TABLE x WITH KEYS]
- Instead of
SELECT start_key FROM crdb_internal.ranges WHERE table_name = $1 OR table_id = $2
(unpredictable / variable table name or ID), use: SELECT raw_start_key FROM [SHOW RANGES FROM CURRENT_CATALOG WITH TABLES, KEYS] WHERE table_name = $1 OR table_id = $2
#93644
The format of the columns start_key
and end_key
for SHOW RANGES FROM DATABASE
and SHOW RANGES FROM TABLE
have been extended to include which table/index the key belongs to. This is necessary because a range can now contain data from more than one table/index. #93644
The format of the columns start_key
and end_key
for SHOW RANGE ... FOR ROW
has been changed to be consistent with the output of SHOW RANGES FROM INDEX
. #93644
The output of SHOW RANGES
no longer includes range_size
, range_size_mb
, lease_holder
, or lease_holder_localities
by default. This ensures that SHOW RANGES
remains fast in the common case. Use the new option WITH DETAILS
to include these columns. #93644
Changefeeds using "preview" expressions (released in v22.2.0) and that access the previous state of the row using the cdc_prev()
function will no longer work and will need to be recreated with new syntax. #94429
Fixed a bug where, when server.identity_map.configuration
was used, CockroachDB did not verify the client-provided username against the target mappings. Note that this means that the client must now provide a valid DB username. This requirement is compatible with PostgreSQL; it was not previously required by CockroachDB but it is now. This does not apply when identity maps are not in use. #94915
Previously, the type of the replicas
, voting_replicas
,non_voting_replicas
and learner_replicas
in crdb_internal.ranges
were overridden to INT2VECTOR
causing incompatible indexing between .ranges
and .ranges_no_leases
. Now the types of those columns in the two tables are set to INT[]
. #96287
Security updates
- Added an option to re-enable "old" cipher suites for use with very old clients. Fixes issue #1989. #95091
- Previously, the
ENCRYPTION_PASSPHRASE
option passed to RESTORE
would appear as 'redacted'. It now appears as '******' which is consistent with SHOW BACKUP
and BACKUP
. #95562
General changes
The garbage collection TTL previously defaulted to 25h
. This value was configurable using ALTER RANGE DEFAULT CONFIGURE ZONE USING gc.ttlseconds = <value>
, but it was also possible to scope to specific schema objects using ALTER {DATABASE,TABLE,INDEX} CONFIGURE ZONE USING ...
. The GC TTL value determined how long overwritten values were retained before being garbage collected.
With CockroachDB v23.1, the RANGE DEFAULT
value is lowered to 4h
but only for freshly created clusters. When existing clusters upgrade to this release, CockroachDB will respect whatever value was in use before the upgrade for all schema objects. This value will therefore be 25h
if the GC TTL was never altered, or some specific value if the GC TTL had been set explicitly. Full cluster backups taken on earlier version clusters, when restored to clusters that started off at v23.1, will use the GC TTL recorded in the backup image.
Cockroach Labs has found the 25h
value to translate to higher-than-necessary storage costs, especially for workloads where rows are deleted frequently. It can also make for costlier reads with respect to CPU since we currently have to scan over overwritten values to get to the one of interest. Finally, we've also observed cluster instability due to large unsplittable ranges that have accumulated an excessive amount of MVCC garbage.
We chose a default of 25h
originally to accommodate daily incremental backups with revision history. But with the introduction of scheduled backups introduced in v22.2, we no longer need a large GC TTL. Scheduled backups "chain together" and prevent garbage collection of relevant data to ensure coverage of revision history across backups, decoupling it from whatever value is used for GC TTL. The GC TTL determines how far back AS OF SYSTEM TIME
queries can go, which now if going past now()-4h
, will start failing informatively. To support larger windows for AS OF SYSTEM TIME
queries, users are encouraged to pick a more appropriate GC TTL and set it using ALTER ... CONFIGURE ZONE using gc.ttlseconds = <value>
. The earlier considerations around storage use, read costs, and stability still apply. #93836
Enterprise edition changes
- The
changefeed.active_protected_timestamps.enabled
cluster setting has been removed and is now always treated as if it was true
. #89975
- Improved changefeed expressions logic to rely on the optimizer to evaluate star expansion. #93979
- Changefeed expressions now support system columns. #93979
- Changefeed expressions now have access to the
cdc_prev
tuple which contains the previous state of the row. #94429
- Changefeed expressions now support non-volatile user defined functions (UDFs). #94429
- Changefeed transformations (e.g.,
CREATE CHANGEFEED ... AS SELECT ...
) no longer require the schema_change_policy=stop
option. #94653
- Changefeed transformations introduced in the v22.2 release in preview mode are no longer experimental. This feature can now be considered to be fully production-ready. #94806
- The
CREATE EXTERNAL CONNECTION
statement now supports URIs with the prefixes azure
, gs
, gcpubsub
, http
, https
, webhook-https
, nodelocal
, s3
, and kafka
for use by changefeeds. #86061
- The
CONTROLCHANGEFEED
role option will be deprecated in the future (see issue #94757). With this change, usages of the CONTROLCHANGEFEED
role option will come with a deprecation warning. Its existing behavior remains the same. The SELECT
and CHANGEFEED
privileges will be used for changefeeds henceforth:
- The
SELECT
privilege on a set of tables allows a user to run core changefeeds against them.
- The
CHANGEFEED
privilege on a set of tables allows a user to run enterprise changefeeds on them, and also manage the underlying changefeed job (ie. view, pause, cancel, and resume the job).
Notably, a new cluster setting changefeed.permissions.require_external_connection_sink.enabled
is added and set to false
by default. Enabling this setting restricts users with CHANGEFEED
on a set of tables to create enterprise changefeeds into external connections only. To use a given external connection, a user typically needs the USAGE
privilege on it. Note that ALTER DEFAULT PRIVILEGES
can be used with both the CHANGEFEED
and SELECT
privileges to assign coarse-grained permissions (i.e., assign permissions to all tables in a schema rather than manually assign them for each table). #94796
- Changefeeds created/altered with a
metrics_label
set while server.child_metrics.enabled
is set to false
will now provide the user a notice upon creation. #94948
- Fix a bug in
ALTER CHANGEFEED
that would panic when altering changefeeds to remove a table that has already been dropped. #95739
- Reduced the default size of scan RPC replies to improve cluster stability during changefeed accounting for transient memory. #95798
- Changefeed expressions now support the
changefeed_created_timestamp
function. #95179
- Increased the default
changefeed.memory.per_changefeed_limit
cluster setting to 1/2GiB
. This should result in changefeeds being able to produce larger files. #96340
- The
confluent_schema_registry
URI for avro changefeeds now supports client_cert
and client_key
parameters. #96510
SQL language changes
- Added the
pg_get_function_arguments
built-in function. This returns the argument list (with defaults) necessary to identify the function with a given OID. #93675
- Added
voting_replicas
and non_voting_replicas
columns to the output of SHOW RANGE
and SHOW RANGES
statements. #93513
- It is now possible to create and alter
NOT VISIBLE
indexes using the alias INVISIBLE
. The alias can be used anywhere NOT VISIBLE
is used when creating or altering indexes. NOTE: the INVISIBLE
alias is not supported for NOT VISIBLE
columns. #93750
- The
system.table_statistics
table now contains a column called fullStatisticsID
to store an id referencing the full table statistic the partial statistic was derived from. #93751
- Ordinal column references (e.g.,
SELECT @1, @2 FROM t
) are now deprecated. By default, statements using this syntax will now result in an error. If desired, such statements can be allowed using the session setting SET allow_ordinal_column_references=true
. Support for ordinal column references is scheduled to be removed in upcoming version v23.2. #93754
- The optimizer will now use table statistics that are merged combinations of the newest partial statistic and latest full statistic collection. And, if forecasting is enabled, the merged statistic will be used in the forecast. #91933
- Added the column
indexes_usage
and the index indexes_usage_idx
on value on the table system.statement_statistics
. #93089
- Add the
log_timezone
session variable, which is read-only and always UTC
. #94123
- Added Two new virtual tables
crdb_internal.index_spans
and .table_spans
, which list the logical keyspace used by each index/table. #93644
- The following new statements are introduced:
SHOW RANGES FROM CURRENT_CATALOG
and SHOW RANGES
without a parameter: functions as an alias for SHOW RANGES FROM DATABASE
on the session's current database.
SHOW RANGES FROM DATABASE ... WITH TABLES
: reports at least one row per table. It is possible for the same range ID to be repeated across multiple rows, when a range spans multiple tables.
SHOW RANGES FROM DATABASE ... WITH INDEXES
: reports at least one row per index. It is possible for the same range ID to be repeated across multiple rows, when a range spans multiple indexes.
SHOW RANGES FROM TABLE ... WITH INDEXES
: reports at least one row per index. It is possible for the same range ID to be repeated across multiple rows, when a range spans multiple indexes.
SHOW CLUSTER RANGES [ WITH { INDEXES | TABLES } ]
: reports ranges across the entire cluster, including ranges that do not contain table data. The behavior of WITH INDEXES
and WITH TABLES
is the same as SHOW RANGES FROM DATABASE
.
Additionally, the following new options have been added to the SHOW RANGES
statement: - WITH KEYS
: produces the raw bytes of the start/end key boundaries.
WITH DETAILS
: produces more details, using computations that require extra network roundtrips. This option will make the operation slower overall.
WITH EXPLAIN
: produces the text of the SQL query used to run the statement. #93644
- Implemented the
pg_timezone_names
pg_catalog
table, which lists all supported timezones. #94122
- Improved the performance of trigram operations. #93757
- Previously, CockroachDB would crash if a user creates a user-defined function (UDF) whose function signature includes a implicit record type (essentially a table) which has a column using a user defined enum type. The root cause was a hydration deadloop when looking up descriptors during hydration. This fix adds a new flag to avoid hydration in order to avoid the deadloop. #94106
- Previously, error messages for missing users sometimes had different forms. This is now unified in the form
role/user "user" does not exist
. #94677
- User-defined functions (UDFs) with subqueries in the body of the function are now supported. #94962
- Previously, setting a table's locality was not allowed if the table contained any hash sharded index. This restriction is now removed. #94436
- Users can now add a super region when creating a database. #93939
COPY
now logs an error during the insert phase on the SQL_EXEC
logging channel. #95038
- Added a new
REDACT
flag to EXPLAIN
which causes constants, literal values, parameter values, and any other user data to be redacted in explain output. Redacted statement diagnostics bundles can now be collected with EXPLAIN ANALYZE (DEBUG, REDACT)
. #94950
- Added two new virtual tables displaying execution insights for transactions:
crdb_internal.cluster_txn_execution_insights
crdb_internal.node_txn_execution_insights
#94720
- Some queries which previously resulted in the error "could not decorrelate subquery" now succeed. #95234
- If
copy_from_retries_enabled
is set, COPY
is now able to retry certain safe circumstances: namely when copy_from_atomic_enabled
is false
, there is no transaction running COPY
and the error returned is retriable. This prevents users who keep running into TransactionProtoWithRefreshError
from having issues. #95275
- Fixed the databases list API when the database name has special characters. #95209
- The pgwire protocol implementation can now accept arguments of the
JSON[]
type (oid=199). Previously, it could only accept JSONB[]
(oid=3804). Internally, JSON[]
and JSONB[]
values are still identical, so this change only affects how the values are received over the wire protocol. #94705
- CPU time spent during SQL execution is now visible in the output of queries run with
EXPLAIN ANALYZE
. This measure does not include CPU time spent while serving KV requests, and CPU time is not shown for queries that perform mutations or for plans that aren't vectorized. This can be useful for diagnosing performance issues and optimizing SQL queries. #93952
- The
SHOW GRANTS ON EXTERNAL CONNECTION
and SHOW SYSTEM GRANTS
statements now use a column name of privilege_type
rather than privilege
. For external connections, the name
column has been changed to connection_name
. This makes the commands consistent with other SHOW GRANTS
commands. #95532
- The
SHOW INDEXES
statement will now show the expression used to define an index, if one was used. #95413
- It is now possible to run efficient
tsvector @@ tsquery
searches when there is an inverted index on the tsvector
column being searched. #93769
- Added a
cpuNanos
field to the statistics column of the crdb_internal.statement_statistics
and system.statement_statistics
tables that reports the amount of CPU time in nanoseconds during SQL execution for queries that track CPU time. #95639
- Previously,
SHOW BACKUP
options would get parsed as kv_options
, which meant that a user could not pass multiple values to a SHOW BACKUP
option, causing feature gaps in SHOW BACKUP
relative to BACKUP
and RESTORE
. This patch rewrites the show backup option parser, closing the following feature gaps:
- A user can now pass and check multiple KMS URIs in
SHOW BACKUP
- A user can pass locality-aware
incremental_locations
, allowing a user to also pass the check_files
parameter to a locality-aware backup chain that also specifies the backup incremental location. #95562
- Updated the name of the
cpuNanos
column to cpuSQLNanos
on crdb_internal.statement_statistics
and system.statement_statistics
#96278
- Some queries with
EXISTS
subqueries which previously resulted in the error "could not decorrelate subquery" now succeed. #95883
- Users can query the
crdb_internal.kv_dropped_relation
table to see which tables, materialized views and sequences are currently already dropped but have not yet been garbage collected, along with the garbage collection TTL setting that is currently in force. This setting originates from the table's own zone configuration, or from its parent database which it inherits, or in turn from the default zone configuration. These settings are typically set using ALTER TABLE ... CONFIGURE ZONE USING gc.ttlseconds = ...;
. #96217
- Administrators may now call a new built-in function
crdb_internal.upsert_dropped_relation_gc_ttl
to retroactively set the garbage collection TTL on a table, materialized view, or sequence which has already been dropped. Effectively, this retroactively performs ALTER TABLE ... CONFIGURE ZONE USING gc.ttlseconds = ...;
. Note that this statement is prevented from being executed on dropped tables because they can no longer be referenced by name at that point. Usage of this built-in is typically in conjunction with the recently-added crdb_internal.kv_dropped_relations
virtual table. For example, garbage collection can be triggered ASAP for all dropped relations by querying: SELECT crdb_internal.upsert_dropped_relation_gc_ttl(id, '1 second') FROM crdb_internal.kv_dropped_relations;
. Doing so for all tables in a dropped database requires filtering on the parent_id
column, the database name being lost at that point. #96217
- Allow
*
expressions in user-defined functions (UDFs). #95710
- Previously, user-defined functions (UDFs) could be created with any volatility no matter if the function body statements contained any expression which would violate the target volatility. For example, an immutable function might use
random()
in it. This change added validations to guarantee that all statements in the function body should be as strict as the expected UDF volatility. #96476
Operational changes
- Updated the metrics
queue.gc.info.clearrangesuccess
and queue.gc.info.clearrangefailed
to include statistics about GC operations that perform ClearRange
on parts of the range keyspace. Previously those metrics only included requests to remove range data completely when performing a schema change. #90830
- The load-based splitter has been redesigned to be more consistent with CPU-based rebalancing rather than QPS-based rebalancing to improve range splits. This redesign is disabled by default currently. #93838
- The cluster setting
storage.value_blocks.enabled
, when set to true
, writes the values of older versions of the same key to separate value blocks in the same sstable. For workloads that create many versions, this can improve the performance of reads by increasing locality. It can also help scan performance with single versions due to an optimization that avoids a key comparison (useful if the key are not very short). The default value of this setting is currently false
. #94634
- The bytes read from SQL clients prior to authentication are now reported via the metric
sql.pre_serve.bytesin
. Previously, this was reported via the sql.bytesin
metric. #92580
- Transaction errors will contain more detailed information in redacted logs. #93760
- Added the
COCKROACH_RAFT_MAX_INFLIGHT_BYTES
environment variable, which helps strictly limit inflight traffic from a Raft leader to its followers, particularly in situations when many large messages are sent and significantly exceed COCKROACH_RAFT_MAX_SIZE_PER_MSG * COCKROACH_RAFT_MAX_INFLIGHT_MSGS
which is a softer limit. #94692
- Log messages for write stalls have been adjusted slightly. #95436
- Histogram metrics can now optionally use the legacy
HdrHistogram
model by setting a new environment variable COCKROACH_ENABLE_HDR_HISTOGRAMS
to true
on CockroachDB nodes. Note that this is not recommended unless users are having difficulties with the newer Prometheus-backed histogram model. Enabling legacy historgrams can cause performance issues with timeseries databases like Prometheus, as processing and storing the increased number of buckets is taxing on both CPU and storage. Note that the legacy HdrHistogram
model is slated for full deprecation in upcoming releases. #96029
- Prometheus histograms will now export more buckets across the board to improve precision & fidelity of information reported by histogram metrics, such as quantiles. This will lead to an increase in storage requirements to process these histogram metrics in downstream systems like Prometheus, but should still be a marked improvement when compared to the legacy HdrHistogram model. If users have issues with the precision of these bucket boundaries, they can set the environment variable
COCKROACH_ENABLE_HDR_HISTOGRAMS=true
to revert to using the legacy HdrHistogram model instead, although this is not recommended otherwise as the HdrHistogram strains systems like Prometheus with excessive numbers of histogram buckets. Note that HdrHistograms are slated for full deprecation in upcoming releases. #96029
- In the rare event of a range inconsistency, the consistency checker now saves a storage checkpoint on each storage the range belongs to. Previously, this was a full checkpoint, so its cost could quickly escalate on the nodes that went on running. This change makes the checkpoints partial, i.e., they now only contain the relevant range and its neighbors. This eliminates the time pressure on the cluster operator to remove the checkpoints. #95963
- The count of new SQL connections is now also reported on
sql.pre_serve.new_conns
. #92580
- The count of network bytes sent to report re-authentication errors to a SQL client is now reported via the metric
sql.pre_serve.bytesout
(instead of sql.bytesout
previously). The count of pre-authentication errors is now reported via the metric sql.pre_serve.conn.failures
(instead of sql.conn.failures
previously). #92580
Command-line changes
- The TLS parameters to connect to a
cockroach demo
cluster from SQL clients have been simplified. #94421
- The password assigned to the
demo
user in cockroach demo
can now be overridden by the env var COCKROACH_DEMO_PASSWORD
. This is meant for use in automated tests, when tests cannot be configured to use TLS client certificate authentication using the client certificates in $HOME/.cockroach-demo
. #94421
cockroach sql
and cockroach demo
now support an \info
client-side command to print the server details again. #94421
cockroach demo
is now able to preserve open web sessions across restarts of the cockroach demo
command. The sessions are saved in the directory ~/.cockroach-demo
alongside the TLS certificates and keys. #94455
- The file format used for transient loss of quorum recovery files has changed. It is not possible to use replica info files generated by earlier versions to be used with current and future versions. #93157
- Workloads that take a
--seed
argument previously defaulted to 1
. Now, they use a randomly generated seed in each run. Users can still pass a custom seed with the --seed
flag. #95326
- Added
cpu_time_per_second
to the hot-ranges.sh
utility debug zip
script. #96213
DB Console changes
- Secure clusters now show correct login information in the top right corner. #94021
- The databases filter dropdown in the Statements page now uses the
getDatabasesList()
API call, resulting in all cluster databases showing up. #93657
- Added information about the selected plan to the Explain Plan tab under Statement Details. #94719
- The Databases page and the Databases Details pages now each contain search and filter components, allowing the ability to search and filter through databases and their tables. #92589
- Graphs can now be clicked on to toggle legend "stickiness" and make the points stop following the mouse. This makes it easier to read dense graphs with many series plotted together. #92949
- The Statement Fingerprint Details page in the DB Console no longer infinitely loads after 5 minutes. #92596
- Added a time picker to the Workload Insights Overview pages in the DB Console. #92285
- Added all three session status options (
Active
, Closed
, and Idle
) and an empty results placeholder. #95005
- Added a list of all statement fingerprints that use a given index to the Index Details page. #94863
- Removed
reset sql stats
and reset index stats
from the DB Console when the user is a non-admin. #95303
- Remove
reset sql stats
from the Transactions page for non-admins. #95461
- Added an insights type filter for the Workload Insights page, and added an execution status filter for the Active Execution pages. #94002
- Hid the apply option for index recommendation when the user is not an admin. #95516
- The Database list filter now shows all databases in the cluster on CC console. #95450
- Hid the filter for the Databases and Database Details pages if both the node and regions dropdowns are also hidden. #95376
- The introduction of the Key Visualizer makes it easy to identify historical hotspots. To support this new feature, three new cluster settings are introduced:
keyvisualizer.enabled
: enables the Key Visualizer.
keyvisualizer.sample_interval
: controls the frequency at which the Key Visualizer collects samples.
keyvisualizer.max_buckets
controls the maximum number of buckets in a sample. #88353
- Added a CPU Time chart to the Statement Details page. #95796
- Added CPU time as a column on the Statement and Transaction tables. #95795
- Hid the list of used fingerprints per index on the Index Details page for non-admin users. #95997
- Added write bytes, write keys, read bytes, read keys and cpu statistics to the
/hotranges
DB Console page. These statistics are the rated average over the last 30 minutes. #95190
- The loading spinner on the Insights Overview page now only shows when a request is pending and either the data is nullish or invalid (i.e., a new time range selected). #93219
- Previously, transaction start time for the Transaction Insights page was only available for transaction insights with high contention as the issue. This information is now available for all transaction insights: start time, end time, and time elapsed. #93219
- Added search, filter and time picker for the list of most-used statement fingerprints on the Index Details page. #96112
- Added CPU Time to the Statement and Transaction Insights page. #96279
- Previously, the stale node metrics displayed in the Cluster Overview Nodes table may mislead users in to thinking that they are current values when in fact they are stale. Now these metrics are displayed with a stale tag, allowing for users to be informed about the staleness of the data displayed to them regarding dead nodes. #95868
Bug fixes
- Fixed a bug where the
session_id
session variable would not be properly set if used from a subquery. #93748
- Fixed a bug where CockroachDB could, in rare cases, encounter an internal error when evaluating the
crdb_internal.range_stats
built-in function (which powers the SHOW RANGES
command among other things). The bug was introduced in v22.2.0 and is now fixed. #93868
- Fixed a bug that could prevent
CASE
expressions that used placeholder return values from type-checking correctly. #93855
- Updated the volatility of the
hmac
, digest
, and crypt
built-ins to be immutable. #93628
- Server logs are now correctly fsynced at every syncInterval. #93956
- The
stxnamespace
, stxkind
and stxstattarget
columns are now defined in pg_statistics_ext
. #93274
- Fixed a bug where, when experimental MVCC range tombstones are enabled (they are disabled by default), a bulk ingestion (e.g., an
IMPORT
) could fail to take a committed-but-unresolved write intent into account during conflict checks when written above an MVCC range tombstone. It was therefore possible in very rare circumstances for the ingestion to write a value below the timestamp of the committed intent, causing the ingested value to disappear. #93938
- This patch fixes
JOIN
queries involving tables with unique constraints using LIMIT
, GROUP BY
, and ORDER BY
clauses to ensure that the optimizer considers streaming group-by with no TopK
operation, when possible. This is often the most efficient query plan. #93858
- Secure clusters now show correct login information in the top right corner. #94021
- Previously, CockroachDB could crash in rare circumstances when evaluating lookup and index joins. The bug has been present since the v22.2.0 release. You can also employ a temporary workaround without upgrading to the release with this fix by changing the value of the undocumented cluster setting
sql.distsql.use_streamer.enabled
to false
. #94031
- The
CREATE ROLE
, DELETE ROLE
, GRANT
, and REVOKE
statements no longer function when the transaction is in read-only mode. #93991
- Fixed a bug where, when experimental MVCC range tombstones are enabled (they are disabled by default), a bulk ingestion (e.g., an
IMPORT
) could, in some situations, fail to properly check for conflicts with existing MVCC range tombstones. This could cause the ingestion to write below a recently-written MVCC range tombstone, in turn losing the ingested data. This could only happen in rare circumstances where a bulk ingestion was applied concurrently with an import cancellation. #94045
- Fixed a bug where, in some cases, the start/end key columns of the output of
SHOW RANGES
was missing. #93644
- Fixed a bug where trigrams ignored unicode (multi-byte) characters from input strings. #93757
- Fixed a bug that could happen when type-checking an array expression that only contains NULLs and placeholder values. The bug was only present in v22.2.1. #94207
- Fixed a bug where tables which receive writes concurrently with portions of an
ALTER TABLE ... SET LOCALITY REGIONAL BY ROW
may fail with an error: duplicate key value violates unique constraint "new_primary_key"
. This bug was introduced in 22.1. #94151
- Fixed a bug where CockroachDB could encounter an internal error when evaluating window functions with
RANGE
window frame mode with an OFFSET PRECEDING
or OFFSET FOLLOWING
boundary set when an ORDER BY
clause has the NULLS LAST
option set. This will now result in a regular error since the feature is marked as unsupported. #94342
- Previously, CockroachDB could delay the release of the locks acquired when evaluating
SELECT FOR UPDATE
statements in some cases. This delay (up to 5s) could then block future readers. The bug was introduced in 22.2.0, and the temporary workaround without upgrading to a release with this fix is to set the undocumented cluster setting sql.distsql.use_streamer.enabled
to false
. #94399
- Record types can now be encoded with the binary encoding of the Postgres wire protocol. Previously, trying to use this encoding could case a panic. #94405
- Fixed a panic that could be caused when using a SQL cursor to access tables in the
crdb_internal
schema. #94425
- Fixed a bug in which
RESTORE SYSTEM USERS
would fail to restore role options. #94134
- Fixed a crash that could occur on the gateway node when collecting a statement diagnostics bundle (e.g.,
EXPLAIN ANALYZE (DEBUG)
) on a statement that fails with certain errors. This crash has existed in various forms since the introduction of statement bundles in v20.1.0. #94440
- Fixed a recursive CTE expressions which cause internal errors when explicit CASTs of initial expressions to output types are missing. #94581
- Previously, certain
GRANT
or REVOKE
commands on a user which does not exist would error with the correct PG code. This is now fixed. #94677
- The
pg_proc
and pg_type
virtual OID indexes no longer incorrectly show cross-db objects. #94339
- It is now possible to run
cockroach version
and cockroach start
(and possibly other sub-commands) when the user running the command does not have permission to access the current working directory. #94894
- It is now possible to use a directory whose name is
"disabled"
via --external-io-dir
. To actually disable external I/O, use --external-io-dir=""
. #94985
- This patch fixes an internal error occurring in CASE expressions when a column present in a
THEN
or ELSE
expression is of an inequivalent type compared to that of a constant this column is compared to in an equality predicate, e.g., (CASE WHEN false THEN int_col ELSE 1 END) IN (int_col) AND int_col=3/2
. #94791
- The
pronamespace
column of the pg_proc
table now correctly reports the crdb_internal
schema for built-in functions that have the "crdb_internal" prefix. #95029
- In the SQL shell (
cockroach sql
/ cockroach demo
), when using \c
/\connect
to connect to a different server, CockroachDB would previously report an error if the new server had a different cluster ID. This has been fixed: this situation is merely a warning. #95219
- When using identity maps (via
server.identity_map.configuration
), authentication now correctly verifies that the client-provided username matches at least one of the mappings for the system identity. Previously, the client-provided username was incorrectly ignored and authentication could fail if the first candidate mapping did not result in a valid DB username. #94915
- The
prokind
column of pg_catalog.pg_proc
is now populated properly. #95289
pg_catalog.pg_description
and pg_catalog.obj_description()
are now able to retrieve the descriptive help for built-in functions. #95294
- Fixed a crash that could occur when formatting a tuple with an unknown type. #95009
- Fixed a bug where a DNS lookup was performed during gossip remote forwarding while holding the gossip mutex. This could cause processing stalls if the DNS server was slow to respond. #95426
- Fixed a bug where
CLOSE ALL
would not respect the "ALL" flag and would instead attempt to close a cursor with no name. #95414
- Fixed a bug where default expressions could have a type which differs from the type of the column as long as the expression's type could be cast in an assignment context, which could lead to a panic during a backfill if such a default expression was added to a new column. This bug was introduced in v22.2.0. #95398
- DB Console features that check for the
VIEWACTIVITYREDACTED
privilege now also account for system-level privileges. #95258
- CockroachDB now supports receiving
regXXX
-type values in binary extended protocol. #94355
- Operations like
BACKUP
can now reuse a previously created AWS KMS client if the client was created with the same parameters. This addresses the NoCredentialProviders
errors on EC2 for backups with long incremental chains. #95026
- The syntax
EXPLAIN (DDL) COMMENT ON
is now possible. #95467
- Fixed a bug where a database
RESTORE
would not grant CREATE
and USAGE
on the public schema to the public role. #95466
- Fixed the formatting of JSON values inside of a SQL array so they no longer have improper quoting. #94705
- Fixed a bug which could cause expressions with side-effects (e.g., volatile expressions or data-modifying statements like
INSERT
) in NOT MATERIALIZED
CTEs to be executed more than once. This bug was present since NOT MATERIALIZED
was first supported in version 20.2.0. #95454
- Fixed
pg_get_indexdef
so that it shows the expression used to define an expression-based index. In addition, the function was previously including columns stored by the index, which was incorrect and has now also been fixed. #95413
- Reduced register session, deregister session, and session cancel query contention. #95553
- Fixed a bug that could cause arguments of a
COALESCE
statement to be evaluated when previous arguments always evaluated to non-NULL values. This bug could cause query errors to originate from arguments of a COALESCE
that should have never been evaluated. #95599
- Fixed the
array_to_string
built-in function so that nested arrays are traversed without printing 'ARRAY' at each nesting level. #95802
- Fixed a bug where using the
COPY
statement to copy data into a column with collated strings would result in an error similar to internal error: unknown type collatedstring
. #95894
- The content of
column_default
in information_schema.columns
no longer has type annotations. #94153
- Fixed a long-standing bug that caused incorrect parsing of double-quoted identifiers separated by one or more newlines. #96019
- Fixed a rare internal errors in
LATERAL
queries with redundant function calls. #96048
- Fixed an internal error which may occur in the
SHOW RANGE FROM TABLE
statement when the FOR ROW
clause specifies a BYTE
literal and the corresponding column data type is BIT
. #96002
- Fixed an internal errors which may occur on some
AS OF SYSTEM TIME
expressions. #96113
- Fixed a bug where a node with a disk stall would continue to accept new connections and preserve existing connections until the disk stall abated. #96371
- Fixed a bug where a
RESTORE
flow could hang indefinitely in the face of a context cancellation, manifesting as a stuck restore job. #96302
- Fixed a bug where the
NOSQLLOGIN
system-level privilege had a bug that made CockroachDB ignore it entirely, so it had no effect. The bug was introduced in v22.2.0-alpha.1. The NOSQLLOGIN
role option is unaffected by this bug. #96466
- The compatibility scalar functions in
information_schema
are now listed in the proper namespace in pg_catalog.pg_proc
. #96562
- Fixed a bug in which the
CREATE SCHEDULE
statement would not properly handle a placeholder for the revision_history
option. #95675
- The optimizer can now avoid planning a sort in more cases with joins that perform lookups into an index with one or more columns sorted in descending order. This can significantly decrease the number of rows that have to be scanned in order to satisfy a
LIMIT
clause. #93673
- Significantly reduced CPU usage of the underlying gossip network in large clusters. #89613
- Refactored the query logic when fetching database index recommendations for the
DatabaseDetails
API endpoint, greatly reducing the query time and cost, particularly for large schemas. #93937
- Improved performance when populating
crdb_internal.default_privileges
. #94247
- Some types of user-defined functions (UDFs) are now inlined in query plans as relation expressions, which speeds up their evaluation. UDFs must be non-volatile and have a single statement in the function body to be inlined. #92955
- Improved the performance of
pg_{function,table,type}_is_visible
. #94339
- Long chains of incremental backups and restore of such chains will now allocate less memory during the unmarshaling of metadata. #93997
- Extended the RPC compression encoding with a length prefixing format, allowing more efficient decompression on receivers. #93871
- When planning lookup joins with a
LIMIT
clause from a REGIONAL BY ROW
input table, the optimizer will now explore a locality-optimized-search plan of two lookup joins to possibly avoid high latency of reading rows in a remote region. #93377
- Improve the performance of the
SHOW FUNCTIONS
statement. #94771
- Improved the performance of several PostgreSQL compatibility built-ins. #94771
- In some cases, when planning an inverted zigzag join, the optimizer can now detect whether it is necessary to re-apply the filter after the zigzag join. If it is not necessary, the optimizer can produce a more efficient plan. #95638
- Fixed a bug which could lead to very slow drop when tables or views have a very large number of columns (greater than 1000). #95850
- In 22.2, CockroachDB introduced support for
DISCARD TEMP
and made DISCARD ALL
actually discard temp tables. This implementation ran expensive logic to discover temporary schemas rather than consulting in-memory data structures. As a result, DISCARD ALL
, which is issued regularly by connection pools, became an expensive operation when it should be cheap. This problem is now resolved. #95876
- In 22.2, logic was added to make
SET SESSION AUTHORIZATION DEFAULT
not a no-op. This implementation used more general code for setting the role for a session which made sure that the role exists. This improves the performance of the DISCARD ALL
and SET SESSION AUTHORIZATION DEFAULT
statements. #95876
- The optimizer now plans inverted index scans for queries that filter by JSON fetch value operators (
->
) with integer indices, e.g, json_col->0 = '{"b": "c"}'
. #95848
- The optimizer now plans inverted index scans for queries that filter by JSON fetch value operators (
->
) with integer indices alongside the "contains" or the "contained by" operators, e.g, json_col->0 @> '{"b": "c"}' or json_col->0 <@ '{"b": "c"}'
. #96202
- Added delegated snapshots which can reduce WAN traffic for snapshot movement. If there is another replica for this range with a closer locality than the delegate, the leaseholder will attempt to have that delegate send the snapshot. This is particularly useful in the case of a decommission of a node where most snapshots are transferred to another replica in the same locality. #83991
- The Raft proposal pipeline has been optimized to reduce interference between Raft proposals. This improves average and tail write latency at high concurrency. #94165
Build changes
- The native binary for Cypress is now only downloaded and installed when UI end-to-end tests are run, instead of eagerly downloading it on all platforms at build-time. This restores the ability for non-{Windows, Darwin, Linux} platforms like FreeBSD and illumos to build CRDB without modifications, which broke in the initial 22.2 release. #93800
- Required NPM dependencies are now fetched from a Google Cloud Storage bucket managed by Cockroach Labs instead of from a git submodule committed in-tree. #94152
Contributors
This release includes 820 merged PRs by 95 authors.
We would like to thank the following contributors from the CockroachDB community:
- HighPon (first-time contributor)
- Vivian Kong
v23.1.0-alpha.1
Release Date: December 19, 2022
Downloads
Warning:
CockroachDB v23.1.0-alpha.1 is a testing release. Testing releases are intended for testing and experimentation only, and are not qualified for production environments and not eligible for support or uptime SLA commitments.
Note:
Experimental downloads are not qualified for production use and not eligible for support or uptime SLA commitments, whether they are for testing releases or production releases.
Docker image
[Multi-platform images](https://docs.docker.com/build/building/multi-platform/) include support for both Intel and ARM. Multi-platform images do not take up additional space on your Docker host.
Within the multi-platform image:
- The ARM image is **Experimental** and not yet qualified for production use and not eligible for support or uptime SLA commitments.
- The Intel image is **Generally Available** for production use.
To download the Docker image:
docker pull cockroachdb/cockroach-unstable:v23.1.0-alpha.1
Source tag
To view or download the source code for CockroachDB v23.1.0-alpha.1 on Github, visit v23.1.0-alpha.1 source tag.
Backward-incompatible changes
- Replaced the
cdc_prev()
function in favor of a cdc_prev
tuple. This is an incompatible change that may break changefeeds that use the previous cdc_prev()
function. #85177
SHOW RANGES FOR TABLE
now includes rows for all indexes that support the table. Prior to this change, SHOW RANGES FOR TABLE foo
was an alias for SHOW RANGES FOR INDEX foo@primary
. This was causing confusion, as it would miss data for secondary indexes. It is still possible to filter to just the primary index using SHOW RANGES FOR INDEX foo@primary
. The statement output now also includes the index name. #93545
Security updates
- It is now possible to create TLS client certificates for identity principals that are not a valid SQL username using
cockroach cert create-client
and the new flag --disable-username-validation
. This is meant to be used in combination with the cluster setting server.identity_map.configuration
and the map
option in HBA rules (server.host_based_authentication.configuration
). To test this feature, use cockroach sql
. Cockroach Labs recommends passing the username separately from the connection URL. #90439
- There is a new SQL session variable
system_identity
defined to contain the identity principal presented by the SQL clients during the initial connection. This may be different from session_user
when the identity was mapped, either using GSSAPI and include_realm=0
, or when using an identity map. #90439
General changes
- Upgraded gRPC to v1.49.0 #88562
- The connection timeout for cluster-internal connections between nodes has been reduced to 5s to potentially reduce the impact of network issues. Previously, CockroachDB employed a 20s connection timeout. #88625
- CockroachDB no longer shares a TCP connection for the KV and Gossip subsystems. Each subsystem now uses their own connection, so the total number of outgoing and incoming TCP connections at each node in the cluster will increase by 30 to 50 percent. #88625
- Bulk operations now log the (sanitized) destinations they are connecting to. For example:
backup planning to connect to destination gs://test/backupadhoc?AUTH=specified&CREDENTIALS=redacted
. #89531
Enterprise edition changes
SHOW CHANGEFEED JOBS
no longer reveals Confluent schema registry user information, including a user's secret key. This information is now redacted, meaning it will not be stored in CockroachDB internal tables at all. #86603
- Changefeeds JSON encoder performance is improved by 50%. #88064
- Changefeeds, using cloud storage sink, now have better throughput. #88395
- Added the cluster setting
changefeed.event_consumer_workers
which allows changefeeds to process events concurrently. #87994
- Changefeed) exports are up to 25% faster due to uniform work assignment. #88672
- Changefeeds can emit files compressed with the ZSTD algorithm, which provides good compression and is much faster than GZIP. In addition, a new, faster implementation of GZIP is used by default. #88635
- When a changefeed is run with the option
virtual_columns = "null"
, the virtual column will be ordered last in each row. #89329
- For Kafka sinks, you can now add the optional JSON field
"Compression"
to the kafka_sink_config
option. This field can be set to "none"
(default), "GZIP"
, "SNAPPY"
, "LZ4"
, or "ZSTD"
. Setting this field will result in the specified compression protocol to be used when emitting events. #90270
- Fixed a bug that could cause changefeeds to fail during a rolling restart. #89913
- Changefeeds will now treat all errors, unless otherwise indicated, as retryable errors. #90810
- CockroachDB now supports passing in the optional external ID when assuming a role. This is done by extending the values of the comma-separated string value of the
ASSUME_ROLE
parameter to the format <role>;external_id=<id>
. Users can still use the previous format of just <role>
to specify a role without any external ID. When using role chaining, each role in the chain can be associated with a different external ID. #91040
- JWT authentication cluster settings can now be modified from within tenants to better support serverless use cases. #92406
- CDC queries are now planned and evaluated using the SQL optimizer and distSQL execution. The state of the previous row is now exposed as the
cdc_prev
tuple. #85177
- Changefeeds no longer require the
COCKROACH_EXPERIMENTAL_ENABLE_PER_CHANGEFEED_METRICS
environment variable to be set in order to use the metrics_label
option. #93423
- Changefeeds can now be scheduled at intervals specified in crontab notation. #92232
SQL language changes
- Added the
crdb_internal.to_json_as_changefeed_with_flags
function to help debug JSON changefeeds. #84509
- Added the
regions
column to the SHOW BACKUP
command which will output a string of ALTER DATABASE
commands if the database is a multi-region database and NULL
for everything else. Previously, the user did not have an easy way to see if a backed up database is multi-region. #88136
- Types with length modifiers can now be used to prefix literals. #82028
- A new column
plan_gist
was added to crdb_internal.{node,cluster}_queries
representing the compressed logical plan. #88770
- You can generate easy-to-read
CREATE INDEX
statements for a table's (secondary) indexes using the SHOW CREATE INDEXES FROM <table_name>
and SHOW CREATE SECONDARY INDEXES FROM <table_name>
statements. #88861
enum_first
, enum_last
, and enum_range
may now take NULL
arguments as long as their type can be inferred from the expression. #89124
- Declarative schema changer support for
ALTER PRIMARY KEY
statements now extends to tables which have secondary indexes. #86176
- The
backup.restore_span.target_size
cluster setting now defaults to 384 MiB
. This should reduce the number of ranges created during restore and thereby reduce the merging of ranges that needs to occur after the restore. #89333
- The
transaction_timeout
session variable was added. transaction_timeout
aborts an explicit transaction when it runs longer than the configured duration. When the timer times out, the current statement is cancelled and the transaction enters an aborted state. This timeout does not have any effect when no statement is being executed, so it should be used with idle_in_transaction_timeout
for the best results. #89033
- The
crdb_internal.check_consistency
function now does not include the diff between inconsistent replicas, should they occur. If an inconsistency occurs, the storage engine checkpoints should be inspected. This change is made because the maximum range size limit has been increased from 64 MiB to 512 MiB, so inlining diffs in consistency checks does not scale. #89502
- CockroachDB now shows a hash-sharded check constraint in
SHOW CREATE TABLE
if it is set to NOT VALID
. #89517
- Added the
SHOW FUNCTIONS
and SHOW FUNCTIONS FROM <schema>
statements, which list user-defined functions. #89557
- The default value of
sql.metrics.statement_details.plan_collection.enabled
is now false
. #89847
- CockroachDB now supports executing statements of the form
DELETE FROM ... USING
. #88974
- The cluster setting
sql.ttl.default_range_concurrency
and table storage parameter ttl_range_concurrency
are no longer configurable. #89392
- CockroachDB drops the associated scheduled incremental backup when
DROP SCHEDULE
or DROP SCHEDULES
is called. Previously, whenever a user dropped a scheduled full backup, the corresponding scheduled incremental backup would not be dropped. #89768
- Added the
sql.auth.change_own_password.enabled
cluster setting. It defaults to false
. When set to true
, any user is allowed to change their own password to a non-null value. Changing other role options still has the same privilege requirements as before (either CREATEROLE
or CREATELOGIN
, depending on the option). #90485
- The
sql.distsql.max_running_flows
cluster setting has been removed. #84888
- The query field in the
crdb_internal.node_queries
, crdb_internal.cluster_queries
, and SHOW QUERIES
commands now includes the original comments in the queries. #86968
- Added a new
descriptor_validation
session variable which can be set to read_only
or off
to disable descriptor validation, which may be useful when mitigating or recovering from catalog corruption. #90488
- CockroachDB now supports using
DROP CONSTRAINT, ADD CONSTRAINT
to add a new primary key without moving the existing primary key to a secondary index if the primary key name is a reserved SQL keyword. Previously, a constraint already exists
error was returned. #90865
- Added the
contention_events
column to the cluster_execution_insights
table to see which transaction is blocking the specific statement. #90660
crdb_internal.scan
and crdb_internal.list_sql_keys_in_range
return the timestamp for the time at which the value for a key was written, in addition to the raw key and value. #90956
- Previously, the
AS OF SYSTEM TIME
value was set at the start of the TTL job (with a TTL cutoff - 30s), but this results in an error for TTL jobs that run longer than gc.ttlseconds
: error selecting rows to delete: ttl select defaultdb.public.events: batch timestamp 1666883527.780656000,0 must be after replica GC threshold 1666883574.542825089,0
. CockroachDB now makes the AS OF SYSTEM TIME
value relative to when each SELECT
query is run (query time - 30s) to prevent this error from happening, but each SELECT
query will run against a different table state. This should be ok because if records are missed during one job invocation they should still be picked up the next. #90981
- The
system.sql_instances
table now includes pre-allocated ID entries, where all the fields except id
will be NULL
. #90427
- The
to_char(timestamp, string)
function has been added. #91382
- The
to_char(interval, string)
built-in function has been added. #91382
- The
system.table_statistics
table now contains a column called partialPredicate
to store a predicate for a partial statistic collection. #91248
- A new
NumericStat
, idleLat
, was introduced to the statistics column of crdb_internal.statement_statistics
. This reports the time spent waiting for the client to send the statement while holding a transaction open. Developers may use this stat to identify opportunities for restructuring their apps to reduce contention. #91098
- The set of supported compression algorithms in compress/decompress built-in functions is expanded to include
lz4
, snappy
, and zstd
. #91162
- A new column
database
was added to crdb_internal.{node,cluster}_queries
and list sessions endpoint. #91629
- Fixed a bug in the legacy schema changer where comments were not dropped together with the database. #91689
- The
round(decimal
) built-in function no longer returns negative 0 for any input. #86106
- Added an estimate for the number of request units consumed by a query to the output of
EXPLAIN ANALYZE
for tenant sessions. #89256
- Enabled forward indexes on arrays. #91762
- Users can now manually create partial single-column statistics at the extreme values on columns that are prefixes of their index. The output of
SHOW STATISTICS
now includes a column indicating the partial predicate for a partial statistic, or NULL
for a full statistic. #91228
- A new SQL statement
SHOW COMMIT TIMESTAMP
has been added. This statement can be used to retrieve the commit timestamp of the current explicit transaction, current multi-statement implicit transaction, or previous transaction. The statement may be used in a variety of settings to maximize its utility in the face of connection pooling. When used as a part of an explicit transaction, the statement implicitly commits the transaction internally before being able to return a causality token. #80848
- Added support for the
pg_blocking_pids
built-in function. It is hardcoded to return an empty array because CockroachDB has no equivalent concept of PIDs as in PostgreSQL. #92253
- Added a list of indexes used by the query on the statistics column on the
system.statement_statistics
and crdb_internal.statement_statistics
tables. The format is tableID@indexID
. #92351
- Added a list of used indexes (with the format
tableID@indexID
) to the sampled query telemetry log. #92464
- A new
NumericStat
, idleLat
, was introduced to the statistics column of crdb_internal.transaction_statistics
. It reports the time spent waiting for the client to send statements while holding a transaction open. #92695
- Added an in-memory-only evaluation of
tsvector
and tsquery
datatypes and the @@
matches operator. #90842
- Implemented the
ALTER TABLE ... ADD CHECK
statement in the declarative schema changer. #91153
to_char
now has caching for parse formats, marking a speedup when running to_char
with the same format between sessions. #91564
- Casts from index name to
REGCLASS
are now supported. Previously, only table names could be cast to REGCLASS
. #90649
- Added user-defined composite column types. #90491
- SQL queries running on remote nodes now show up in CPU profiles with
distsql.*
labels. Currently this includes appname
, gateway
, txn
, and stmt. #92775
- CockroachDB now permits non-indexed storage of
tsvector
and tsquery
datatypes #92957
- Implemented the
parse_ident
built-in function, which splits a qualified identifier into an array of identifiers, removing any quoting of individual identifiers. By default, extra characters after the last identifier are considered an error; but if the second parameter is false
, then such extra characters are ignored. #93300
Operational changes
- Reduced the length of the
raft.process.handleready.latency
metric help text to avoid it being rejected by certain Prometheus services. #87166
- Logs produced by increasing the vmodule setting for
s3_storage
are now directed to the DEV
channel rather than STDOUT
. #88798
- Added observability for when load-based splitting cannot find a key to indicate the reasons why the load splitter could not find a split key, which provides more insight into why a range is not splitting easier. #88720
- Added five new fields to the
sampled_query
telemetry events:
ScanCount
: Number of scans in the query plan.
ScanWithStatsCount
: Number of scans using statistics (including forecasted statistics) in the query plan.
ScanWithStatsForecastCount
: Number of scans using forecasted statistics in the query plan.
TotalScanRowsWithoutForecastsEstimate
: Total number of rows read by all scans in the query, as estimated by the optimizer without using forecasts.
NanosSinceStatsForecasted
: The greatest quantity of nanoseconds that have passed since the forecast time (or until the forecast time, if it is in the future, in which case it will be negative) for any table with forecasted stats scanned by this query. #88539
- Added a new debug tool to allow for decrypting files in a store using encryption-at-rest. This tool is intended for use while debugging, or for providing debug artifacts to Cockroach Labs to aid with support investigations. It is intended to be run "in-situ" (i.e., on site), as it prevents having to move sensitive key material. #89668
- Added a new command that can be used by an operator to list the files present in the encryption-at-rest file registry. #89873
- Release version binaries can now be instructed via the enviroment variable
COCKROACH_FORCE_DEV_VERSION
to override their cluster version support to match that of development builds, which can allow a release binary to be started in a cluster that is run (or has previously run) a development build. #90002
- The consistency check failure message is now more informative, and suggests a few actions that operators should perform in the unlikely event a failure occurs. #89899
- Updated metric descriptions of
rebalancing.*
to include the recording period. #90619
- CockroachDB now prioritizes non-voters in voter additions, meaning that when selecting a store to add a voter on (in the allocator), CockroachDB will prioritize candidate stores that contain a non-voter replica higher. This reduces the number of snapshots that need to be sent over the WAN. #89650
- CockroachDB now uses response data rather than just the request span in the load-based splitter to pass more accurate data about the keys iterated over to the load splitter to find a suitable split key, enabling the load splitter to find a split key under heavy range query workloads. #89217
- Added the
replicas.leaders_invalid_lease
metric, which indicates how many replicas are Raft group leaders but holding invalid leases. #91179
- The cluster settings
server.web_session.purge.period
and server.web_session.purge.max_deletions_per_cycle
, which were specific to the cleanup function for system.web_sessions
, have been replaced by server.log_gc.period
and server.log_gc.max_deletions_per_cycle
which apply to the cleanup function for system.eventlog
, system.rangelog
and system.web_sessions
equally. #90789
- The cluster setting
server.web_session.auto_logout.timeout
has been removed. #90789
- Splunk dashboard templates are available in the public repository under
/monitoring/splunk-dashboard/
. #92330
- The network timeout for RPC connections between cluster nodes has been reduced from 3 seconds to 2 seconds, with a connection timeout of 4 seconds, in order to reduce unavailability and tail latencies during infrastructure outages. This can now be changed by setting the
COCKROACH_NETWORK_TIMEOUT
environment variable, which defaults to 2s
. #92542
- The Raft election timeout has been reduced from 3 seconds to 2 seconds, and the lease interval from 9 seconds to 6 seconds, with a corresponding reduction in the node heartbeat interval from 4.5 seconds to 3 seconds. This reduces the period of unavailability following leaseholder loss, but places tighter restrictions on network latencies (no more than 500ms roundtrip time). This can be adjusted by setting the
COCKROACH_RAFT_ELECTION_TIMEOUT_TICKS
environment variable, which now defaults to 10 and will scale all of these intervals proportionally. #91947
- The RPC heartbeat and gRPC keepalive ping intervals have been reduced to 1 second to detect failures faster. This is adjustable via the new
COCKROACH_PING_INTERVAL
environment variable. The timeouts remain unchanged. #93399
Command-line changes
- The interactive SQL shell now retains a maximum of 1000 entries. There was no limit previously. #88173
- The deprecated CLI command
debug unsafe-remove-dead-replicas
has been removed. Use debug recover
instead. #89150
- The
\df
metacommand was added to the SQL shell, which will list all user-defined functions in the current database. #89557
- In the
kv
workload, you can now enable --splits
with the --sequential
flag and adjust splitting to uniformly partition the keyspace. #90000
- Added the
--insert-count
flag to insert rows before the kv
workload begins. #90055
- CockroachDB will now include recommended remediation actions alongside log messages for some errors. #82891
- The input syntax of
\set
is now more flexible: it is now more accepting of space characters in various positions of the syntax and it supports quoted values, e.g., via \set prompt1 "a b c"
. #90520
cockroach demo --global
will now start up more quickly. The latency that will be injected will not be injected until after the initial cluster is set up internally. #92231
- The engine used as line editor in the interactive shell (
cockroach sql
and cockroach demo
) has been updated. It includes numerous bug fixes and new features. The previous engine can still be accessed by setting the COCKROACH_SQL_FORCE_LIBEDIT
environment variable to true. This support will be removed in a later version. #86457
- The interactive SQL shell now supports an advanced debug mode for troubleshooting when
--debug-sql-cli
is specified on the command line. The debug mode can be enabled with Ctrl+@ or Ctrl+_ (Ctrl+space on macOS). #86457
The following fields have been redacted and were added to the redacted debug zip:
crdb_internal.create_statements
:
create_statement
alter_statements
(each element is redacted)
crdb_internal.create_function_statements
:
crdb_internal.{node,cluster}_distsql_flows
:
crdb_internal.{cluster,node}_sessions
:
last_active
active_queries
crdb_internal.{cluster,node}_queries
:
The interactive SQL shell now supports a rudimentary form of tab completion to input the name of SQL objects and functions. #87606
The command-line flag --empty
to cockroach demo
is not marked as deprecated anymore; it is more convenient than --no-example-database
. However, the latter remains supported as an alias. #93255
The command-line flags --logtostderr
, --log-file-verbosity
, --no-color
, --redactable-logs
, --log-file-max-size
, --log-group-max-size
, --log-dir
, --sql-audit-dir
are not marked as deprecated anymore; instead, they are defined as convenience aliases for various --log
specifications. #93255
cockroach demo
now supports --pid-file
for symmetry with cockroach start
. #93343
The debug utility script hot-ranges.sh
partitions output by statistics: queries_per_second
, writes_per_second
, read_bytes_per_second
, write_bytes_per_second
. It also decreased the number of ranges shown under each heading from 20 to 10. #93528
DB Console changes
- The High Contention Time insight description now accurately reflects the event's contention duration in the DB Console. #89035
- Overview and Explain Plan tabs were added to the Active Statement Details page. #89021
- The Apply button was added on the Table Details page (DB Console only) when there is a recommendation to drop an unused index. #90070
- Overview and Explain Plan tabs were added to the Statement Insight Details page. #90250
- The Jobs Page now includes a column picker. #89678
- The fingerprint ID values for statements and transactions on the Insights pages are links that open the respective details page on the time period of the execution of that statement or transaction. #90403
- Requests to fetch table and database statistics now have limited concurrency. This may make loading the Databases page slower, but in return should result in making those pages less disruptive. #90210
- The Transaction filter label on the SQL Activity page was fixed. #91255
- The metric graph tooltip styling was fixed to prevent content collapse. #91290
- Index recommendations were added to the Statement Active Execution Details page, and the plan gist was added as the first line of the explain plan. #91629
- Transaction insights pages now show insights about slow execution with unknown causes, index recommendations, and failed executions. The following fields have also been added on the Details page, but are not available for transactions where the insight is "High Contention" (i.e., user name, session ID, rows processed, rows read, rows written, retries, last retry reason, full scan, and transaction priority). #91698
- The fingerprint ID in hex format was added to the Statement Details page and Transaction Details page. #91885
- The contention time, schema, database, table, and index info was added to the Insights Statement Details page. #91668
- The query column in the insight recommendations table was removed. Instead, the statement is included in the description if the transaction being reported has multiple statements. #91955
- Graphs on the Metrics page now downsample using max value instead of average. Previously, zooming out on a graph would cause any spikes in the graph to smooth out, potentially hiding anomalies. These anomalies are now visible even when looking at a zoomed out interval. #92017
- The Statement Execution and Planning Time chart on the Statement Fingerprint page now includes a third value ("Idle") representing the time spent by the application waiting to execute this statement while holding a transaction open. #92284
- A list of used index per explain plan was added, under the Explain Plan tab on the Statement Details page, with links to the table or index details pages. #92463
- The Insights pages in the DB Console now show the seconds and milliseconds for all timestamp values. #92571
- Links were added on the fingerprint ID in the High Contention table on the Transaction Insights Details page. #92612
- The following new charts were added to the Metrics page, under SQL view: Service Latency: SQL Statements, 99.9th percentile and Service Latency: SQL Statements, 99.99th percentile. #92591
- Renamed the chart on the Statement Details page from Statement Execution and Planning Time to Statement Times. #92765
- The Transaction resource usage card on the Transaction Fingerprint page now includes an "Idle latency" row, representing the time spent by the application performing other work while holding this transaction open. #92951
- The Databases table page now displays all the grants in a single row per user. #92871
- Added a goroutine scheduling latency graph to the Overload dashboard in the DB Console. It shows what the per-node p99 scheduling latency is for goroutines. #93217
- CockroachDB now prevents polling
/settings
, /nodes_ui,
and /cluster
endpoints on incorrect login. #93211
- The Statement and Transaction pages for tenant clusters gained region columns and filters for multi-region tenants. #92357
Bug fixes
- The flag
--sql-advertise-addr
now properly works even when the SQL and RPC ports are shared (because --sql-addr
was not specified). Note that this port sharing is a deprecated feature in v22.2. #87412
- Fixed a bug introduced in v21.2 that could cause an internal error in rare cases when a query required a constrained index scan to return results in order. #87562
- Fixed a bug that existed from before v21.1 that could cause an internal error when executing a query with a limit ordering on the output of a window function. #87320
- Fixed an incorrect default value of
cloudstorage.gs.chunking.retry_timeout
to 60 seconds #87817
- Fixed a bug in
pg_catalog
tables that could result in an internal error if a schema is concurrently dropped. #88568
- Fixed a bug that caused
ALTER CHANGEFEED
to fail if the changefeed was created with a cursor option and had been running for more than gc.ttlseconds
. #88402
- Fixed a bug that could cause a panic when running a query with
EXPLAIN
that attempts to order on a non-output column. #88441
- Fixed missing automatic statistics collection at cluster startup when the
sql.stats.automatic_collection.enabled
cluster setting is false, but there are tables with the storage parameter sql_stats_automatic_collection_enabled
set to true. #88673
- Fixes an issue where when a statement bundle was collected for a query that results in an error due to a
statement_timeout
, the bundle would not be saved. #88080
- CockroachDB now excludes check constraints of hash-shared indexes from being invalidated when executing
IMPORT INTO
. #89231
- CockroachDB now flushes WAL when writing storage checkpoints on consistency checker failures. #89369
- Fixed optimizer selectivity and cost estimates of zigzag joins in order to prevent query plans from using it when it would perform poorly (e.g., when many rows are qualified). #89261
- Changefeeds will now never permanently error on a "failed to send RPC" error. #87763
- Fixed a bug that could occur when dropping a role that owned two schemas with the same name in different databases. The bug was introduced in v22.1.0. #89504
- CockroachDB now avoids a source of internal connectivity problems that would resolve after restarting the affected node. #89539
- CockroachDB now shows the correct value on table stats on UI, when there are no values to show. #89867
- Charts on the Statement Details page in the DB Console are no longer overlapping. #90014
- It is now possible to create tables, views, columns, etc. with the name
nothing
(e.g., CREATE TABLE nothing...
) without having to quote the name, like in PostgreSQL. This bug was introduced in CockroachDB v2.0. #89903
- Fixed detection and erroring out of queries using locality-optimized joins when the session setting
enforce_home_region
is true and the input table to the join has no home region or its home region does not match the gateway region. #90107
- Fixed an issue with the
enforce_home_region
session setting which may cause SHOW CREATE TABLE
or other non-DML statements to error out if the optimizer plan for the statement involves accessing a multi-region table. #90007
- Fixed a bug in
changefeed.batch_reduction_retry
which resulted in only a single level of retry being able to occur. #90153
- During JWT based auth, CockroachDB now infers the algorithm type if it is not specified by the JWKS. This enables support for a wider range of keys. #89989
- Fixed an extremely rare out of bounds crash in the protected timestamp subsystem. #90357
- Fixed the calculation of the
pg_attribute.attnum
column for indexes so that the attnum
is always based on the order the column appears in the index. Also fixed the pg_attribute
table so that it includes stored columns in secondary indexes. #90287
- Fixed a bug in the DB Console where when the height of the filter was big, you had to scroll to get to the Apply button. #90457
- Fixed a bug in the DB Console to now send the proper start and end values to the endpoint used on the SQL Activity page so it returns the full hour as described on the UI. #90403
- Fixed a rare bug where concurrent follower read/split operations could lead to invalid read results. #89886
- Fixed a bug that could cause
UPDATE .. FROM
clauses to update the same row multiple times, resulting in incorrect UPDATED
row counts and duplicate output rows for statements with a RETURNING
clause. The bug only appeared when the target table had a hidden primary key column (e.g., an implicit rowid
primary key column). The bug has been present since support for UPDATE .. FROM
was added in v19.0. #89780
- Protected timestamps are now created during index validation. Before, index validation could be starved if it took longer than any GC jobs for a given table. #89540
- Fixed a bug where
SELECT *
operations on tables with virtual computed columns undergoing schema changes could potentially fail. #90670
- Fixed a bug where in large, multi-region clusters it was possible for the leasing mechanism used for jobs to get caught in a live-lock scenario whereby jobs could not be adopted. #90875
- CockroachDB now ensures changefeeds shut down when one of the aggregator nodes returns an error. #90767
- Fixed a bug the occurred when attempting to reduce the size of a fixed-size
VARCHAR
column. #91078
- Fixed a bug that caused ranges to remain without a leaseholder in cases of asymmetric network partitions. #87244
- Fixed a bug that would prevent data from a failed restore from being cleaned up quickly. #88342
- Fixed a bug which, in rare cases, could result in a changefeed missing rows which occur around the time of a split in writing transactions which take longer than the closed timestamp target duration (defaults to 3s). #91116
- Fixed a bug where point lookups on the
pg_catalog.pg_type
table would fail to find the implicit record type that gets created for tables in the pg_catalog
, information_schema
, and crdb_internal
schemas. #90924
- Fixed a bug that prevented the usage of implicit record types for tables in the
pg_catalog
, information_schema
, and crdb_internal
schemas. #90924
- Fixed a bug that could result in transient errors when dropping a database and immediately recreating a database with the same name and connecting to it for use. #91174
- Fixes a bug that resulted in the regions listed for databases and tables including an incorrect list of regions due to the logic including information about tables which are adjacent in the keyspace. #91130
- Fixed a bug where the experimental
scrub
command did not handle type descriptors in the database. #91085
- Fixed a panic that could occur when calling
st_distancespheroid
or st_distancesphere
with a spatial object containing an NaN
coordinate. This now produces an error, with the message "input is out of range". #90218
- Fixed a bug that could result in infrequent progress updates for very large backup or restore jobs. #89971
- Added leading zeros to fingerprint IDs with less than 16 characters. #91885
- Fixed a bug causing changefeeds to fail when a value is deleted while running on a non-primary column family with multiple columns. #91870
- Fixed a bug that existed since before v21.1 where the
cgroup
memory limit was undetected when using systemd
. #91789
- Fixed a bug that existed since v20.2 that could cause incorrect results in rare cases for queries with inner joins and left joins. #91425
- Fixed an unhandled error that could happen if
ALTER DEFAULT PRIVILEGES
was run on the system database. #92075
- CockroachDB now prevents schema changes on the
crdb_internal_expiration
table. #91720
- When configured to true, the
sql.metrics.statement_details.dump_to_logs
cluster setting no longer causes a mutex deadlock. #92272
- Fixed a bug that could lead to errors when running multiple schema change statements in a single command using a driver that uses the extended pgwire protocol internally (for example the Npgsql C# driver). The error messages was "attempted to update job for mutation 2, but job already exists with mutation 1". #92300
- Fixed the Statement Activity page so that it no longer shows multi-statement implicit transactions as "explicit." #92408
- Server crashes that occur during startup are now more clearly reported in logs and the standard error output. #91823
- Fixed incorrect cancellation logic when attempting to detect stuck rangefeeds. #92582
- Fixed an internal error when comparing a tuple type with a non-tuple type. #92635
- Fixed incorrect selectivity estimation for queries with
OR
predicates all referencing a common single table. #89358
- Added sort setting to tables on the Transaction and Statement Insights Details pages. #92573
- Fixed an issue where
changefeed.emitted_messages
would be increased twice per message for changefeed cloud storage sinks. #92685
- Fixed a bug where
attidentity
in pg_attribute
for the GENERATED BY DEFAULT AS IDENTITY
column should be d
. #92545
- CockroachDB previously could incorrectly evaluate queries that performed left semi and left anti "virtual lookup" joins on tables in
pg_catalog
or information_schema
. These join types can be planned when a subquery is used inside of a filter condition. The bug was introduced in v20.2.0 and is now fixed. #92713
- Fixed a link to index details on the Drop Index Insights in the Cloud Console. #92953
- Fixed a bug where encoding of
ARRAY
type to Parquet format would fail in some cases during the EXPORT
command. #92948
- Fixed a rare panic only present in v22.2.0 that occurs when using particular forms of existing statistics in table statistics forecasting. #92707
- In the presence of several backup files, CockroachDB now speeds up slow listing calls that could manifest as restore queries hanging during execution. #93072
- Prepared statements that use type hints can now succeed type-checking in more cases when the placeholder type is ambiguous. #92834
- Fixed a bug where glob patterns that matched no tables in
GRANT
or REVOKE
statements would return an internal error with a confusing message as opposed to the appropriate "no objects matched" error. #93173
- Fixed a bug where empty
COPY
commands would not escape after an EOF character or error if encountering a \.
with no input. #93100
- Fixed a bug where in PostgreSQL extended protocol mode it was possible for auto-commits to not execute certain logic for DDL, when certain DML (insert/update/delete) and DDL were combined in an implicit transaction. #93283
- Fixed the
pg_table_is_visible
built-in function so it correctly reports visibility of indexes based on the current search_path
. #90649
- Fixed a bug that would result in incomplete backups when non-default, non-public resource limiting settings (
kv.bulk_sst.max_request_time
or admission.elastic_cpu.enabled
) were enabled. #92825
- The
pg_function_is_visible
function now correctly reports visibility based on the functions that are visible on the current search_path
. #90657
- Fixed a rare bug that could cause upgrades from v22.1 to v22.2 to fail if the job coordinator node crashes in the middle of a specific upgrade migration. #93487
- Fixed a bug for queries with disjunctions (i.e., contains
OR
) where all the columns referenced in the disjunctions are known to have a single value. #93480
- Fixed a bug introduced in v22.1.0 in which the non-default nulls ordering,
NULLS LAST
, was ignored in window and aggregate functions. This bug would cause incorrect query results when NULLS LAST
was used. #93426
- Fixed a bug that caused an internal error when trying to execute a UDF with an empty function body. This bug was present since UDFs were introduced in v22.2.0. #93331
- Fixed an issue where
DISTINCT ON
queries would fail with the error "SELECT DISTINCT ON expressions must match initial ORDER BY expressions" when the query included an ORDER BY
clause containing ASC NULLS LAST
or DESC NULLS FIRST
. #93567
- Fixed a bug where selecting a small timeframe in the past in a timeseries query resulted in no data in the graphs. #93293
- Fixed a bug where CockroachDB would error when receiving Geometry/Geography using binary parameters. #93563
- Fixed an internal error that could occur when comparing a column of type void to
NULL
using col IS NULL
or col IS NOT NULL
. #93652
- Fixed a bug where a query would incorrectly pass if a given interval for
AS OF SYSTEM TIME
interval was a small positive duration. #93146
- The optimizer will now plan inverted index scans for queries with JSON subscripting filters, like
json_col['field'] = '"value"
. #87957
- CockroachDB now avoids wasteful contention on the gossip mutex caused by checking if the network needs tightening hundreds of times per second. #88472
- Some types of queries with comparisons with constant values now execute faster. #88638
- The optimizer now explores plans with a single lookup join expressions in rare cases where it previously planned two lookup join expressions. #88491
- Consistency checks are now properly cancelled on timeout, preventing them from piling up. #86591
- Raft ticks now adapt to scheduling delays. This helps preventing re-elections, and the corresponding performance effects, in the event of relatively short (sub-second) processing delays. #86240
- HTTP requests with
Accept-encoding: gzip
previously resulted in valid GZIP-encoded, but uncompressed, responses. This resulted in inefficient HTTP transfer times, as far more bytes were transferred than necessary. Those responses are now properly compressed, resulting in smaller network responses. #88950
pg_catalog.col_description
is now much faster when resolving columns for tables in the pg_catalog
, crdb_internal
, or information_schema
namespaces. #89465
- The optimizer now does less copying of histograms while planning queries, which will reduce memory pressure a little. #88526
- Added early inlining of
VALUES
clauses and unnested arrays in WITH
queries in order to eliminate unnecessary joins. #87790
- Added significantly faster JSON parsing, which should help with any workloads that insert large amounts of JSON data, including
IMPORT
workloads. #89884
- Loading the Database Details page in the DB Console is now somewhat less expensive when there are a large number of databases and a large number of tables in each database and a large number of ranges in the cluster. #90198
- Tables in
pg_catalog
and information_schema
(when not explicitly referenced as "".information_schema
) may now be much faster if the current database has a small number of relations relative to the total number in the cluster. #90116
- The overhead of running
EXPLAIN ANALYZE
and EXPLAIN ANALYZE (DISTSQL)
has been significantly reduced. The overhead of EXPLAIN ANALYZE (DEBUG)
did not change. #91117
- Enabled more efficient lookup joins by deriving new join constraints when equijoin predicates exist on the column(s) of a unique constraint on one table which are a proper subset of the referencing columns of a foreign key constraint on the other table. If an index exists on those foreign key constraint referencing columns, equijoin predicates are derived between the primary key and foreign key columns not currently bound by
ON
clause predicates. #90599
- The setup of the distributed query execution is now fully parallelized which should reduce the query latencies, especially in multi-region setups. #89649
- Performance of the
LIKE
and ILIKE
operators using patterns without any wildcards has been improved. #91895
- The optimizer can now better calculate the properties of recursive common table expressions in the presence of a
LIMIT
. #90725
- Certain types of reads will now have a far smaller contention footprint with conflicting concurrent writers. #85993
- In some cases, the key-value store client now needs to look up where to send requests. Prior to this change, such lookup requests were always routed to the leaseholder of the metadata range storing that information. Now the client can attempt to look up this metadata from followers. This can improve tail latency in multi-region clusters in cases where the range addressing cache is cold. #91638
- The garbage collection score triggering a MVCC GC run has been lowered. The GC Score is a ratio computed from MVCC stats and uses the ratio of live objects and estimated garbage age to estimate collectability of existing data. The reduced score will trigger garbage collection earlier, lowering the interval between runs 3 times, giving 2 times reduced peak garbage usage at the expense of 30% increase of wasteful data scanning on constantly updated data. #92118
- CockroachDB in some cases now correctly incorporates the value of the
OFFSET
clause when determining the number of rows that need to be read when the LIMIT
clause is also present. Note that there was no correctness issue here, only that extra unnecessary rows could be read. #92779
- In 22.2, privileges on virtual tables (system catalogs like
pg_catalog
, information_schema
, and crdb_internal
) were introduced. A problem with this new feature is that we now must fetch those privileges into a cache before we can use those tables or determine their visibility in other system catalogs. This process used to occur on-demand, when the privilege was needed. Now, CockroachDB will fetch these privileges eagerly during startup to mitigate latency when accessing pg_catalog
right after the server boots up. #93557
Contributors
This release includes 1655 merged PRs by 101 authors.
We would like to thank the following contributors from the CockroachDB community:
- Alex (first-time contributor)
- Ambuj Kumar (first-time contributor)
- Faizaan Madhani (first-time contributor)
- GirishV (first-time contributor)
- Klaus Rettinghaus (first-time contributor)
- Vivian Kong (first-time contributor)
- e-mbrown
- likzn
- nnaka2992
- quanuw (first-time contributor)
- rharding6373
- shralex
Was this helpful?
On this page
Was this helpful?