What's New in v22.2

Docker image

$ docker pull cockroachdb/cockroach-unstable:v22.2.0-alpha.1

Backward-incompatible changes

• CockroachDB no longer performs environment variable expansion in the parameter --certs-dir. Uses like --certs-dir='$HOME/path' (expansion by CockroachDB) can be replaced by --certs-dir="$HOME/path" (expansion by the Unix shell). #81298
• In the Cockroach CLI, BOOL values are now formatted as t or f instead of True or False. #81943
• Removed the cockroach quit command. It has been deprecated since v20.1. To shut down a node gracefully, send a SIGTERM signal to it. #82988
• Added a cluster version to allow the Pebble storage engine to recombine certain SSTables (specifically, user keys that are split across multiple files in a level of the log-structured merge-tree). Recombining the split user keys is required for supporting the range keys feature. The migration to recombine the SSTables is expected to be short (split user keys are rare in practice), but will block subsequent migrations until all tables have been recombined. The storage.marked-for-compaction-files time series metric can show the progress of the migration. #84887
• Using a single TCP port listener for both RPC (node-node) and SQL client connections is now deprecated. This capability will be removed in the next version of CockroachDB. Instead, make one of the following configuration changes to your CockroachDB deployment:
  • Preferred: keep port 26257 for SQL, and allocate a new port, e.g., 36257, for node-node RPC connections. For example, you might configure a node with the flags --listen-addr=:36257 --sql-addr=:26257, where subsequent nodes seeking to join would then use the flag --join=othernode:36257,othernode:26257. This will become the default configuration in the next version of CockroachDB. When using this mode of operation, care should be taken to use a --join flag that includes both the previous and new port numbers for other nodes, so that no network partition occurs during the upgrade.
  • Optional: keep port 26257 for RPC, and allocate a new port, e.g., 36257, for SQL connections. For example, you might configure a node with the flags --listen-addr=:26257 --sql-addr=:36257. When using this mode of operation, the --join flags do not need to be modified. However, SQL client apps or the SQL load balancer configuration (when in use) must be updated to use the new SQL port number. #85671
• If no nullif option is specified while using IMPORT CSV, then a zero-length string in the input is now treated as NULL. The quoted empty string in the input is treated as an empty string. Similarly, if nullif is specified, then an unquoted value is treated as NULL, and a quoted value is treated as that string. These changes were made to make IMPORT CSV behave more similarly to COPY CSV. If the previous behavior (i.e., treating either quoted or unquoted values that match the nullif setting as NULL) is desired, you can use the new allow_quoted_null option in the IMPORT statement. #84487
• COPY FROM operations are now atomic by default instead of being segmented into 100 row transactions. Set the copy_from_atomic_enabled session setting to false for the previous behavior. #85986

Security updates

• HBA configuration can now restrict admin logins originating from localhost. This allows security conscious users to better restrict access to their instance. To restrict admins from logging in to localhost insert the following as the first line of your HBA configuration: host all root 127.0.0.1/32 cert-password. #77955
• Certain less-secure TLS 1.2 cipher suites are no longer supported. Clients more than five years old may fail to connect. CockroachDB now matches the "recommended" cipher list of the IETF defined in RFC 8447. #82362
• Changed access requirements to some observability features. Databases/tables/schema endpoints for admin UI require admin or VIEWACTIVITY. EXPERIMENTAL_AUDIT requires admin or MODIFYCLUSTERSETTING. SQL login requires that the NOSQLLOGIN or equivalent role are not set. #85769
• HTTP API endpoints under the /api/v2/ prefix now allow requests through when the cluster is running in insecure mode. When the cluster is running in insecure mode requests to these endpoints will have the username set to root. #86417

General changes

• When using Azure Cloud Storage for data operations, CockroachDB now calculates the storage account URL from the provided AZURE_ENVIRONMENT query parameter. This defaults to AzurePublicCloud if not specified, to maintain backward compatibility. #80511
• CockroachDB now collects schema info if phoning home is enabled. This schema info is added to the telemetry log by a built-in scheduled job which runs on a weekly basis by default. This recurrence can be changed via the sql.schema.telemetry.recurrence cluster setting. The schedule can also be paused via PAUSE SCHEDULE followed by its ID, which can be retrieved by querying SELECT * FROM [SHOW SCHEDULES] WHERE label = 'sql-schema-telemetry'. #84761
• Changefeeds without a specified sink will no longer terminate when schema changes occur. #85458
• Core changefeeds are now more resilient to transient errors (ex. network blips) by adding checkpointing. Previously, transient errors would result in a Core changefeed stopping and terminating the underlying SQL statement. This would require the user to restart the SQL statement. Furthermore, if the Core changefeed were restarted during an initial scan, the initial scan would start from the beginning. For large initial scans, transient errors are more likely, so restarting from the beginning would likely see more transient errors and restarts, which would not progress the changefeed. Now, a Core changefeed will automatically take frequent checkpoints and retry from the last checkpoint when a transient errors occurs. #86253

Enterprise edition changes

• Incremental backups with mismatched localities are now blocked. #79135
• Users can now authenticate to AWS by passing in the argument AUTH=assume and specifying an AWS_ROLE_ARN={role-ARN}. A user with AssumeRole can optionally be specified with AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY or left blank to use the default credentials chain. #79968
• Introduced the primary_key_filter option to restrict the span watched by a changefeed only to the portion that satisfies the filtering predicate. #80499
• Changefeed restarts and changefeeds started with the cursor option are now more efficient by using the catchup scan progress checkpoint. #77763
• Storage and KMS URIs for Google Cloud Storage in BACKUP and RESTORE now accept an ASSUME_ROLE parameter, which informs the current service account authenticated by either implicit or specified credentials to obtain temporary credentials for the service account specified by the ASSUME_ROLE parameter in order to access the resource specified by the URI. #80417
• The CREATE CHANGEFEED statement now supports general expressions: predicates and projections. Projections allow users to emit specific columnar data, including computed columns. While predicates (i.e., filters) allow users to restrict the data that emits to only those events that match the filter. For example: CREATE CHANGEFEED INTO 'kafka://' AS SELECT * FROM t WHERE NOT cdc_is_delete(). #82562
• Added replanning functionality for changefeeds when topology changes by adding a new replanning counter metric. This functionality is only supported for CockroachDB Serverless clusters.. #83143
• Allowed the ASSUME_ROLE parameter in Amazon S3 and Google Cloud Storage KMS URIs to specify a list of roles with a comma-separated string. The roles in the list can chain assume to access the resource specified by the URI. #83712
• The URI for Google Cloud Pub/Sub now accepts an ASSUME_ROLE parameter, which specifies a comma-separated list of service accounts to chain assume by the service account authenticated by the implicit or specified credentials. #84619
• Previously, if you dropped a column with the schema_change_policy='stop' option, the changefeed would stop. Dropping a column with a different policy would result in previous rows retransmitting with the dropped column omitted. In some cases, a changefeed may target specific columns (a column family) of a table. In these cases, if a non-target column is dropped, the changefeed should not stop or retransmit values, because the column was not visible to a consumer sink to begin with. With this change, dropping a non-target column from a table will not stop the changefeed when schema_change_policy is set to stop. With any other policy, dropping a non-target column will not trigger a backfill. #84674
• Implemented functionality to determine the number of column families that are referenced by a SELECT statement in changefeed expressions and handle appropriately. #84764
• BACKUP, RESTORE, and backup schedule creation now have corresponding events that emit to the telemetry channel. #82463
• Added the ALTER BACKUP SCHEDULE SQL statement to modify existing backup schedules. #85489
• CREATE CHANGEFEED statements with AS SELECT ... will require the option schema_change_policy='stop'. This means that the changefeed will stop if schema changes occur. #85896
• Introduced a new rangefeed RPC called MuxRangeFeed. Rangefeeds now use a common HTTP/2 stream per client for all range replicas on a node, instead of one per replica. This significantly reduces the amount of network buffer memory usage, which could cause nodes to run out of memory if a client was slow to consume events. The caller may opt in to use the mechanism by specifying WITH MuxRangefeed option when starting the rangefeed. However, a cluster wide COCKROACH_ENABLE_MULTIPLEXING_RANGEFEED environment variable may be set to false to inhibit the use of this new RPC. #75581
• ALTER BACKUP SCHEDULE now supports additional commands like SET WITH, SET SCHEDULE OPTION, SET LABEL, and SET INTO. #86190
• Changefeeds may opt in via changefeed.mux_rangefeed.enabled setting to use MuxRangeFeed RPC which multiplexes multiple rangefeed streams onto a single RPC stream per node. #86448
• Changefeeds now rate limit log messages related to resolved timestamps. #82838
• Adjusted per_changefeed_limit to 128MiB. The previous default of 1GiB could pressure garbage collection, which would potentially affect foreground traffic. #84686

SQL language changes

• Core users that schedule a backup without the FULL BACKUP ALWAYS clause will receive a warning. #77506
• Implemented the pg_options_to_table built-in, which converts an options array format to a table. #77883
• COMMENT ON SCHEMA can now use qualified schema names. For example, COMMENT ON SCHEMA sc_name ... and COMMENT ON SCHEMA db_name.sc_name .... #79055
• Added the OVERLAPS syntax and overlaps() built-in function. The semantics is the same as the OVERLAPS syntax in PostgreSQL. This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or timestamp followed by an interval. When a pair of values is provided, either the start or the end can be written first. OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap. #77015
• Direction is now explicit for inverted indices in SHOW CREATE TABLE output. #78549
• Previously, the delimiters for PostgreSQL geography and geometry was inconsistent. It has been updated to use the : delimiter. #82304
• ALTER PRIMARY KEY will no longer create a secondary index on the previous PK columns if they're a strict prefix of an existing secondary index. #78046
• The to_regclass, to_regnamespace, to_regproc, to_regprocedure, to_regrole, and to_regtype built-in functions are now supported, improving compatibility with PostgreSQL. #78652
• Changefeed statements now detect duplicate targets and throw an error. #79465
• Previously, BACKUP allowed the user to specify a custom subdirectory name for their backups via BACKUP .. INTO {subdir} IN {collectionURI}. This is no longer supported. Users can only create a full backup via BACKUP ... INTO {collectionURI} or an incremental backup on the latest full backup in their collection via BACKUP ... INTO LATEST IN {collectionURI}. This deprecation also removes the need to address a bug in SHOW BACKUPS IN, which cannot display user-defined subdirectories. #79447
• Added a session variable, enable_multiple_modifications_of_table, which can be used instead of cluster variable sql.multiple_modifications_of_table.enabled to allow statements containing multiple INSERT ON CONFLICT, UPSERT, UPDATE, or DELETE subqueries to modify the same table. As with sql.multiple_modifications_of_table.enabled, with this session variable enabled there is nothing to prevent the table corruption seen in issue #70731 from occurring if the same row is modified multiple times by different subqueries of a single statement. We recommend rewriting these statements, but the session variable is provided as an aid if this is not possible. #79677
• Previously, if a column in a table has a comment, SHOW CREATE TABLE would fail after the column type is changed. This is now fixed. #79998
• Added the built-in functions: uuid_nil, uuid_ns_dns, uuid_ns_url, uuid_ns_oid, and uuid_ns_x500 provided by the uuid-ossp extension in PostgresSQL. #80204
• Added the built-in functions: uuid_generate_v1, uuid_generate_v1mc, uuid_generate_v3, and uuid_generate_v5. #80204
• The command CREATE EXTENSION "uuid-ossp" no longer fails, since CockroachDB now includes all the built-in functions from this extension. #80204
• Users can now pass locality-aware backup URIs to SHOW BACKUP. This change only affects SHOW BACKUP with the new syntax: e.g., SHOW BACKUP FROM LATEST IN ({collectionURI}, {localityURI1}, {localityURI2}). Users cannot run SHOW BACKUP for locality-aware backups created using the incremental_location parameter. #79121
• Table scans performed as a part of index joins, lookup joins, inverted joins, and zigzag joins now respect the row-level locking strength and wait policy specified by the optional FOR SHARE/UPDATE NOWAIT clause on SELECT statements. #60719
• The pg_cast table was populated in order to match PostgreSQL behavior. #79537
• Constraints that only include hidden columns are no longer excluded in SHOW CONSTRAINTS. You can enable the previous behavior using the show_primary_key_constraint_on_hidden_columns session variable. #80154
• Introduced the ST_XMin, ST_XMax, STYMin, and ST_YMax geospatial built-ins. #80363
• Introduced the st_makeenvelope built-in. #80408
• Added the pgcrypto gen_salt built-in with support for the des, xdes, md5, bf algorithms. #80318
• Added a new check_files option to SHOW BACKUP. This option checks that all SST files and metadata in a backup chain are in their expected location in external storage. If SHOW BACKUP cannot read from a file, an error message with the problematic file path returns. A successful SHOW BACKUP with check_files will also return the additional file_bytes column that indicates the estimated bytes on external storage storing a table object in the backup, analogous to the return pattern of the rows and size_bytes columns. #80491
• Previously, when a hash-sharded index was dropped, the accompanying shard column would also drop if no other index used this shard column. For hash-sharded indexes created in v21.2 and earlier, this shard column is a physical, STORED column. Dropping such a physical column can be very expensive since it requires a full table rewrite. For hash-sharded indexes created in v22.1 and later, this shard column is a virtual computed column. Dropping a virtual column is not as costly. Now, if the to-be-dropped sharded index has a physical shard column (and no other index uses that column), CockroachDB will drop only the index if not CASCADE, or will drop both the index and the column if CASCADE. #80806
• Allowed wildcards to SHOW GRANTS for all schemas in a database. #80861
• Added the pgcrypt crypt built-in with support for the md5 and bf algorithms. #80809
• Added a notice to the SET CLUSTER SETTINGS sql.defaults... statement that recommends using the ALTER ROLE syntax instead: the ALTER ROLE syntax allows users to set default values for session variables making SET CLUSTER SETTINGS sql.defaults... redundant.. #80548
• The JSON ? string operator is now index accelerated if there is an inverted index over the JSON column referred to on the left-hand side of the expression and a constant on the right. #81253
• The ?& and ?| operators are now index accelerated if the left-hand side is an inverted indexed JSON column and the right-hand side is a constant. #81253
• Added a new RESTART option to ALTER SEQUENCE and CREATE SEQUENCE that sets the nextval() to the given number, or back to the original START value. This implements the ALTER SEQUENCE PostgreSQL behavior. This is similar to calling the setval() function with is_called = false. That is, the specified value will return by the next call of nextval(). Writing RESTART with no restart value is equivalent to supplying the start value that was recorded by CREATE SEQUENCE or last set by ALTER SEQUENCE START WITH. In contrast to a setval() call, a RESTART operation on a sequence is transactional and blocks concurrent transactions from obtaining numbers from the same sequence. If this is not the desired mode of operation, setval() should be used. #81377
• Added syntax support for {GRANT|REVOKE} ... ON {SEQUENCE | ALL SEQUENCES IN SCHEMA}. #79862
• SHOW EXPERIMENTAL_FINGERPRINTS now supports tables with expression indexes. #81042
• Removed the ability to cast int, int2, and int8 to a 0 length BIT or VARBIT. #81266
• Expanded the capabilities of the EXPERIMENTAL SCRUB statement to include checking unique constraints for primary keys, unique indexes, and unique columns without indexes. The usage and output of SCRUB is unchanged, but if there is a unique constraint violation, users will see the error message unique_constraint_violation for all rows that violate the constraint, along with information about the row. #78297
• Added the pg_trgm.similarity_threshold session setting that controls the threshold at which the trigram similarity operator % returns true versus false. #81418
• Added support for the pg_trgm built-ins show_trgm, for showing the trigrams in a string and a measure of how similar two strings are based on their trigrams. #81418
• Added the % string trigram similarity overload. #81418
• The extra_float_digits session variable now defaults to 1. The meaning of the variable has also changed. Now, any value greater than 0 causes floats to be formatted in their shortest precise decimal representation. That is, the string representation produced is closer to the actual binary value than to any other value. (Previously, this was only the behavior when extra_float_digits was set to 3.) This change was made in accordance with an equivalent change that was part of the PostgreSQL 12.0 release. The behavior of a non-positive extra_float_digits value is unchanged: such a value will still reduce the number of float digits shown in the output string. The formula to compute the number of digits shown is max(1, (DIGITS + extra_float_digits)), where DIGITS=6 for FLOAT4 values, and DIGITS=15 for FLOAT8 values. #82022
• Added a new full_scan column to the crdb_internal.{cluster,node}_queries table, which describes whether a query contains a full table or index scan. This column is included in the SHOW QUERIES command. Note that this information is only valid when the query is in the executing phase. The ListSessions API includes this information under the field is_full_scan in the active query for a session. #81531
• STRING columns now support inverted indexes using trigrams. These indexes can be searched using the =, LIKE, ILIKE, and % (similarity) predicates. #79705
• Permitted usage of jsonb_ops, array_ops, gin_trgm_ops, and gist_trgm_ops as an operator class in inverted index creation. #79705
• Removed the deprecated GRANT privilege. #81310
• Casting from an INT to OID, or calling the oid built-in function, and using an integer that is larger than 32 bits now results in an error. Specifically, the range for valid inputs for these uses is [MinInt32, MaxUint32]. #82430
• SHOW BACKUP WITH check_files will display up to 10 missing SST files. #82274
• Index recommendations are now supported for spatial indexes. #82293
• Added the to_timestamp function that converts Unix epoch of FLOAT, INT, DECIMAL, and TEXT to TIMESTAMPTZ. #82523
• A column's DEFAULT/ON UPDATE clause can now have a type that differs from the column type, as long as that type can be assignment-cast into the column's type. This change increases compatibility with PostgreSQL. #81071
• COPY ... FROM CSV HEADER is now supported. #82457
• Added rowCount to TTL job progress. #81917
• Added logic for GRANT ... ON sequence names. #82458
• Introduced GLOBAL privileges, which live above the database level. Example: GRANT SYSTEM MODIFYCLUSTERSETTING TO foo. Currently MODIFYCLUSTERSETTING is the only global privilege, it allows users to query the crdb_internal.cluster_settings table. #82166
• Added a cluster.preserve-downgrade-option.last-updated metric that reports the Unix timestamp of the last updated time of the cluster.preserve_downgrade_option setting. This metric is now also emitted to Prometheus, and used to display a banner to the DB Console if cluster.preserve_downgrade_option has been set for greater than 48 hours. This change provides increased observability into upgrade finalization. #82633
• Added support for DROP OWNED BY. #82936
• Created two invariants for the stream_ingestion_stats built-in, for protobuf and JSON respectively, and extended them to return more details. #83066
• Added support for JSONB subscripting in SELECT-style cases, e.g., SELECT json_field['a'] ... WHERE json_field['b'] = .... #82877
• Added a new execution statistic that tracks the number of gRPC calls issued to perform read operations to the output of EXPLAIN ANALYZE, which exposes low-level details that might aid with debugging the performance of queries. #83365
• Added a new ttl_expiration_expression expression for CREATE TABLE and ALTER TABLE. ttl_expiration_expression accepts an expression that returns a TIMESTAMP to support custom TTL calculation. The following are supported:
  • CREATE TABLE ... WITH (ttl_expiration_expression='...')
  • ALTER TABLE ... SET (ttl_expiration_expression='...')
  • ALTER TABLE ... RESET (ttl_expiration_expression) #82686
• Added a new column, locality to the system.sql_instances table, which stores the locality of a SQL instance if it was provided when the instance was started. This exposes a SQL instance's locality to other instances in the cluster for query planning. #82915
• Implemented DROP INDEX under the declarative schema changer. #80133
• Removed the ttl_automatic_column storage parameter. The crdb_internal_expiration column is created when ttl_expire_after is set and removed when ttl_expire_after is reset. #83134
• Renamed oldest_query_start in the crdb_internal.cluster_sessions and crdb_internal.node_sessions tables to active_query_start, as this column contains the time at which the currently active query was started, not the time at which the session's first query was started. #83451
• The CREATE CHANGEFEED AS statement no longer requires WITH DIFF when using cdc_prev(). #83717
• CockroachDB can now parse the CREATE FUNCTION statement, but an unimplemented error will return since the statement is not fully implemented. #83891
• DROP statements performed by the declarative schema changer (which is the case by default) now transition descriptor states to OFFLINE in the initial schema change transaction before transitioning them to DROP in a subsequent transaction executed by the schema change job. Changefeeds watching tables that are dropped will now reflect this descriptor state in the returned error (i.e., either dropped or taken offline). Additionally, a concurrent backup will see the table as OFFLINE before it reaches DROP. potentially causing the offline table to be included in the backed-up data. #83915
• Extended the CREATE MATERIALIZED VIEW statement to support the WITH NO DATA clause, which allows the creation of materialized views with no data. Such views require a refresh at least once prior to access. #83347
• Added the sql.metrics.statement_details.index_recommendation_collection.enabled cluster setting that can be disabled if index recommendation generation is causing performance issues. #84282
• Added sequence option info for identity columns under information_schema. #84034
• The inet function has been added to support the conversion of a supplied type to that of the INET type family. If the conversion fails, a SQL error will be output. #83668
• The last column of an INVERTED INDEX can no longer have the DESC option. If DESC was used in prior versions, it could cause internal errors. #84516
• Introduced CREATE EXTERNAL CONNECTION syntax that can create an external connection representing a resource that resides outside of CockroachDB. The only supported resource at the moment is a nodelocal URI that can be represented as an external connection object using: CREATE EXTERNAL CONNECTION foo AS 'nodelocal://1/foo'. #84310
• Added DROP EXTERNAL CONNECTION to drop a previously created external connection object. #84751
• Cluster BACKUP and RESTORE no longer includes job records, which previously were usually only restored in a canceling state with the exception of schema changes, which restored to their initial running state. Instead, any schema change jobs required for restored tables are recreated after restoring the tables. #84886
• Introduced an EXTERNALCONNECTION system privilege that is required to create an external connection object to represent an underlying resource. #85007
• Added a new is_visible column to the crdb_internal.table_indexes and information_schema.statistics tables. Also, added a new visible column to the output of SHOW INDEX, SHOW INDEXES, and SHOW KEYS. The is_visible or visible columns indicates whether the index is visible to the optimizer. #84776
• Bulk operations and changefeeds will accept an external scheme URI that points to a previously created external connection object. These operations can then interact with the underlying resource represented by the object as they did before. #84931
• Introduced VIEWACTIVITY, VIEWACTIVITYREDACTED, VIEWCLUSTERSETTING, CANCELQUERY, and NOSQLLOGIN as system privileges. #84198
• Removed the byte string parameter in the crdb_internal.schedule_sql_stats_compaction function. #82560
• The SHOW DEFAULT PRIVILEGES command now has a column that indicates if the default privilege will give the GRANT option to the grantee. #85027
• Previously, ALTER DEFAULT PRIVILEGES would error out on functions. Now, the ALTER DEFAULT PRIVILEGES statement performs the GRANT/REVOKE with the newly added EXECUTE privilege from default privileges. #84471
• Added the explicit "true" and "false" values for detached and revision_history arguments in BACKUP and CREATE SCHEDULE FOR BACKUP. #85146
• CockroachDB now supports secondary regions. Secondary regions makes it possible to specify a failover region, which will receive the leaseholder if the primary region fails. #84450
• The parser now supports creating an index marked as invisible. However, this is not fully implemented and executing it returns an unimplemented error immediately. #84783
• Renamed statement to stmt and transaction to txn in columns in the crdb_internal.node_execution_insights table. Added txn_fingerprint_id, query, status, start_time, end_time, full_scan, user_name, app_name, database_name, plan_gist, rows_read, rows_written, priority, and retries columns. #85131
• The CREATE VIEW statement can now have a constant NULL column definition. The resulting column is of type TEXT. #85134
• A Google Cloud Storage KMS can be represented as an external connection object, which can be used during BACKUP or RESTORE using the external URI. #85075
• The IMPORT INTO statement now supports importing from CSV, AVRO, and delimited formats into a table with partial indexes. This was previously disallowed. #85244
• Introduced a new crdb_internal virtual table, cluster_execution_insights, offering a cluster-wide view of the same node-local information available in node_execution_insights. Currently, the insights subsystem is still under development and disabled by default. #85339
• Changed EXPLAIN output of full scans with soft limits to FULL SCAN (SOFT LIMIT) instead of FULL SCAN, to distinguish them from unlimited full scans. Unlimited full scans always scan the entire index. Full scans with soft limits could scan the entire index, but usually halt early once enough rows have been found to satisfy their parent operator. #85421
• Added support for privileges on virtual tables. Previously users were unable to GRANT on virtual tables, including crdb_internal, pg_catalog, and information_schema. Now users can GRANT/REVOKE SELECT privilege on virtual tables. SELECT is needed to query a virtual table. Note that virtual table privileges are not per database. Executing GRANT SELECT ON crdb_internal.tables TO foo allows foo to select on crdb_internal.tables across all databases. Though executing GRANT SELECT ON dbname.crdb_internal.tables TO foo completes without error, the database is ignored. #83604
• Added the crdb_internal.request_statement_bundle built-in, which allows the statement bundle to be requested from the SQL CLI. The new built-in takes three parameters: statement fingerprint text, minimum execution latency for the statement, and the duration the statement bundle request will stay valid for. The VIEWACTIVITY or admin role option is required to use this built-in. A user with the VIEWACTIVITYREDACTED role option is not allowed to use this built-in. #79693
• Added the column index_recommendations to crdb_internal.node_statement_statistics, crdb_internal.cluster_statement_statistics, system.statement_statistics, and crdb_internal.statement_statistics. #84618
• The pg_proc.proisstrict column is now correctly populated instead of always being false. If this column is true, it indicates that the function will not be called if any of its inputs are NULL. Instead, it will directly evaluate to NULL. #85676
• When statistics are refreshed for a table, CockroachDB now deletes any existing statistics on that table from columns or sets of columns that do not have their statistics refreshed by default. This ensures that stale statistics are removed and do not impact the ability of the optimizer to create a high quality query plan. The retention time for these statistics is controlled by a new cluster setting, sql.stats.non_default_columns.min_retention_period, which defaults to 24 hours. #85586
• Introduced the ALTER DATABASE database_name ALTER LOCALITY {GLOBAL|REGIONAL|REGIONAL IN} set_zone_config syntax, which allows setting the zone config extension. #83605
• Added last_retry_reason and exec_node_ids columns to the crdb_internal.node_execution_insights table. #85634
• The EXPLAIN output no longer annotates simple operations (like render and project) with the execution statistics or estimates since that information is redundant (it is copied from the child operations). #85649
• Users can now GRANT USAGE ON EXTERNAL CONNECTION and REVOKE USAGE ON EXTERNAL CONNECTION to grant and revoke the USAGE privilege. This privilege is required by all operations that interact with external connections. #85556
• Previously, the pg_proc table was only populated with built-in functions. With the added support for user-defined functions creation, the pg_proc table has now been extended to include user-defined function data as well. #85656
• Added a new SHOW CREATE FUNCTION statement, taking a function name as an argument. If the given function name is qualified, the explicit schema will be searched. If the function name is not qualified, the schemas on the search path are searched and functions from the most significant schema are returned. #85656
• Previously, ::regproc casting only supported built-in functions. Now it is extended to support user-defined functions as well. #85656
• Added a new virtual table crdb_internal.create_function_statements which can be used to query CREATE statements of user-defined functions, as well as parent db and schema ids. #85656
• Added the schema_only option to RESTORE, which enables you to run a regular restore without restoring any user table data. This can be used to quickly validate that a given backup is restorable. A schema_only restore takes a fraction of a regular restore's runtime. Note that during a cluster level, schema_only restore, the system tables are read from storage and written to disk, as this provides important validation coverage without much runtime cost (system tables should not be large). After running a successful schema_only restore, you can revert the cluster to its pre-restore state by dropping the descriptors added by the schema_only restore (e.g., if you restored a database, you can drop the database after the restore completes). #85231
• Added the VIEWDEBUG and VIEWCLUSTERMETADATA system privileges. #85280
• Added new index recommendations that are generated every hour and available from system.statement_statistics and crdb_internal.statement_statistics. Added a new sql.metrics.statement_details.max_mem_reported_idx_recommendations cluster setting with a default value of 100k. #85343
• SELECT ... FOR {UPDATE,SHARE} SKIP LOCKED is now supported. The option can be used to skip rows that cannot be immediately locked instead of blocking on contended row-level lock acquisition. #85720
• Implemented DROP FUNCTION in the legacy schema changer. Now users can drop a function with a function name or a function signature. #85718
• Users can now GRANT DROP ON EXTERNAL CONNECTION and REVOKE DROP ON EXTERNAL CONNECTION to grant and revoke the DROP privilege. This privilege is required by the user to DROP a particular external connection. #85770
• The CREATE EXTERNAL CONNECTION statement can be now used to represent a kafka sink. Subsequently, users can run CREATE CHANGEFEED with an external:///<external-connection-object-name URI as the sink to use the Kafka resource represented by the external connection object. #85410
• Added the strptime and strftime built-in functions as aliases for experimental_strptime and experimental_strftime. #85756
• The CREATE EXTERNAL CONNECTION statement can now be used to represent an Amazon S3 URI. #85680
• Added the format built-in function. format interpolates arguments into a string in the style of C's sprintf. For example, format('Hello, %s', 'world') returns 'Hello, world'. #84107
• The declarative schema changer now falls back to the legacy schema changer when a user-defined function is found in the dependency graph when encountering a DROP statement. This no longer throws an unimplemented error. #85981
• Arrays can now be imported in a CSV file using the {} format, similar to COPY FROM. Importing array expressions (e.g., ARRAY[1, 2, 3]) is still supported as well. #85850
• Creating a not visible index using CREATE TABLE …(INDEX … NOT VISIBLE) or CREATE INDEX … NOT VISIBLE is now supported. #85794
• The output from SHOW STATISTICS is now more deterministic. #77070
• Added a new WITH FORECAST option to the SHOW STATISTICS statement, which calculates and displays forecasted statistics along with the existing table statistics. #77070

• Added the trunc(decimal, int) built-in function, which truncates the given decimal value to the specified number of decimal places. A negative value can be used for the scale parameter, which will truncate to the left of the decimal point. Example: #85890

  SELECT trunc(541.234, 2), trunc(541.234, 0), trunc(541.234, -1);
  trunc  | trunc | trunc
  -------+-------+---------
  541.23 |   541 | 5.4E+2.
  

• The CREATE EXTERNAL CONNECTION statement can be used to represent an underlying userfile resource. #86006

• Altering an index to visible or not visible using ALTER INDEX … VISIBLE or NOT VISIBLE is now supported. #86032

• When performed by the declarative schema changer (as is the case by default) the ALTER PRIMARY KEY statement now also drops the rowid column when no references are held to it anywhere. The rowid column is a hidden column which is implicitly added and serves as primary key on any table created without explicitly specifying a primary key. #86071

• Session setting optimizer_use_not_visible_indexes can be used to disable not visible index features. When this setting is enabled, the optimizer treats not visible indexes as if they were visible and can choose to use them for query planning. By default, this setting is disabled. #86033

• Google Cloud KMS will now accept the gcp-kms scheme along with the existing gs scheme. External Connections will only recognize the gcp-kms scheme when being created to represent a KMS resource. #85957

• The asynchronous garbage collection process has been changed such that quickly after dropping a table, index, or database, or after refreshing a materialized view, the system will issue range deletion tombstones over the dropped data. These tombstones will result in the KV statistics properly counting these bytes as garbage. Before this change, the asynchronous "gc job" would wait out the TTL and then issue a lower-level operation to clear out the data. That meant that while the job was waiting out the TTL, the data would appear in the statistics to still be live. #85878

• The CREATE EXTERNAL CONNECTION statement can be used to represent an underlying Google Cloud Storage resource. #85964

• When running ALTER TABLE ... ADD PRIMARY KEY or ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY in a single-statement, implicit transaction, where no primary key had previously been added to the table, the previous rowid column which had been automatically created as the table's PRIMARY KEY will now be dropped. #86195

• Added contention time to execution_insights. #85959

• Added a new alter_primary_region_super_region_override setting, which must be enabled to be able to move a secondary region either inside or outside of a super region. The primary region must be moved before moving the secondary region. #84999

• Added support for the IF EXIST syntax on the DROP SECONDARY REGIONstatement. Using it will avoid returning an error if a secondary region is not defined on a database. #84999

• Enabled a new subsystem, insights, for gathering slow statement executions in the crdb_internal.cluster_execution_insights table along with possible reasons for the slowness: full scans or missing indexes, contention, plan changes, retries, etc. This system may be tuned by a handful of new cluster settings and monitored with a handful of new metrics, all in the sql.insights namespace. #86216

• The CREATE EXTERNAL CONNECTION statement can be used to represent an Azure Storage URI. #86257

• Added the SHOW CREATE EXTERNAL CONNECTION and SHOW CREATE ALL EXTERNAL CONNECTIONS statements, which display the connection name and the unredacted query used to create the external connection. Currently, this can only be run by users of the admin role. #86161

• Added index recommendations to execution_insights. #86055

• Added support for the verify_backup_table_data option to the RESTORE statement. When using this option, along with the required schema_only option, a schema_only restore will run and all user data will be read from external storage, checksummed, and discarded before getting written to disk. This option provides two additional validation steps that a regular schema_only restore and SHOW BACKUP with check_files cannot provide:

  • RESTORE will verify that all data can be read and rekeyed to the restoring cluster
  • RESTORE will verify that all data passes a checksum check #86136

• The CREATE EXTERNAL CONNECTION statement can be used to represent an aws-kms scheme that represents an Amazon S3 KMS resource. #86402

• DROP OWNED BY can no longer be performed if the user has synthetic privileges (in system.privileges). #86619

• Added support for DISCARD SEQUENCES, which discards all sequence-related state data such as currval/lastval. DISCARD ALL now also discards sequence-related state. #86230

• EXPLAIN ANALYZE output now contains a warning when the estimated row count for scans is inaccurate and includes a hint to collect the table statistics manually. #86677

• The new sql.stats.response.show_internal cluster setting can be used to display information about internal stats on the SQL Activity page, with the fingerprint option. The setting defaults to false. #86679

Operational changes

• Introduced the kv.allocator.l0_sublevels_threshold and kv.allocator.L0_sublevels_threshold_enforce cluster settings, which enable excluding stores as targets for allocation and rebalancing of replicas when they have high-read amplification, indicated by the number of L0 sub-levels in level 0 of the store's LSM. By default, kv.allocator.l0_sublevels_threshold is set to 20 and kv.allocator.l0_sublevels_threshold_enforce is set to block_none_log. When both kv.allocator.l0_sublevels_threshold and the cluster average is exceeded, the action corresponding to kv.allocator.l0_sublevels_threshold_enforce is taken, as follows: #78608
  • block_none will exclude no candidate stores
  • block_none_log will exclude no candidates but log an event
  • block_rebalance_to will exclude candidates stores from being targets of rebalance actions
  • block_all will exclude candidate stores from being targets of both allocation and rebalancing.
• Added requests-per-second, exposed through the rebalancing.requestspersecond metric. requests-per-second tracks the average number of requests received per store, aggregated over the ranges it contains. Also added reads-per-second, exposed through the rebalanacing.readspersecond metric. reads-per-second tracks the count of keys read per second, on a replica basis. #76609
• HottestRanges will now report additional range statistics for the reported ranges. These statistics are:
  • requests per second: the number of requests received by this range recently per second.
  • writes per second: the number of keys written to in this range recently per second.
  • reads per second: the number of keys read from this range recently, per second.
  • write bytes per second: the number of bytes written to this range recently, per second.
  • read bytes per second: the number of bytes read from this range recently, per second. #76609
• Increased the default value of kv.transaction.max_refresh_span_bytes from 256KB to 4MB. #80115
• Added metrics range.snapshots.shapshots.(unknown|recovery|rebalancing).sent-bytes and range.snapshots.shapshots.(unknown|recovery|rebalancing).rcvd-bytes to the metrics dashboard. This allows tracking the number of bytes sent/received for each type of metric in addition to the total bytes sent/received. #81860
• httpSink and fluentSinks will now, by default, have buffered writes enabled. This means that writes to these sinks will be asynchronous. This will show in the output of debug check-log-config as well as impact the default behavior of these two types of network sinks. This is enabled via a new default buffering configuration for both the httpSink and fluentSink, where the default values are as follows: #82893
  • max-staleness: The maximum amount of time between flushes to the underlying http or fluent sink. Default: 5s
  • flush-trigger-size: The size in bytes of accumulated messages in the buffer that will trigger a flush. 0 disables this trigger. Default: 1MiB
  • max-buffer-size: Limits the size of the buffer. When a new message is causing the buffer to overflow beyond this limit, existing messages are dropped. Default: 50MiB
• I/O admission control now reduces the likelihood of storage layer write stalls, which can be caused when memtable flushes become a bottleneck. This is done by limiting write tokens based on flush throughput, so as to reduce storage layer write stalls. Consequently, write tokens are now limited both by flush throughput, and by compaction throughput out of L0. This behavior is enabled by default. The admission.min_flush_util_fraction cluster setting, defaulting to 0.5, can be used to disable or tune flush throughput-based admission tokens. Setting it to a value greater than 1, e.g., 10, will disable flush-based tokens. Tuning the behavior, without disabling it, should be done only on the recommendation of a domain expert. #82440
• The admission.kv.pause_replication_io_threshold cluster setting can be set to a nonzero value to reduce I/O throughput on followers that are driven toward an inverted LSM by replication traffic. The functionality is disabled by default. A suggested value is 0.8, meaning that replication traffic to non-essential followers is paused before these followers will begin throttling their foreground traffic. #83851
• Adjusted the way memory is tracked against kv.transaction.max_intents_bytes and kv.transaction.max_refresh_spans_bytes to be more precise. As a result, the stability of CockroachDB has improved. However, this change effectively reduces the budgets determined by those cluster settings. In practice, this means that:
  • the intents might be tracked more coarsely (due to span coalescing), which makes the intent resolution less efficient.
  • the refresh spans become more coarse too, making it more likely that ReadWithinUncertaintyIntervalErrors are returned to the user rather than retried transparently. #84230
• Added the storage metrics rangekeycount, rangekeybytes, rangevalcount, and rangevalbytes for MVCC range keys (i.e., MVCC range tombstones). These are analogous to the corresponding point key metrics (e.g., keycount). #85453
• Added new metrics range.snapshots.(send|recv)-queue and range.snapshots.(send|recv)-in-progress to track the number of queued and in-progress snapshots being sent or received on a store. #84947
• The cluster settings bulkio.restore_at_current_time.enabled and bulkio.import_at_current_time.enabled, which were introduced in v22.1 and defaulted to true, have been retired. They are now always enabled. #85757
• Added new metrics for tracking the successes/errors of a replica being processed by the replicate queue, using the allocator action as a method of categorizing these actions.
  • queue.replicate.addreplica.(success|error)
  • queue.replicate.removereplica.(success|error)
  • queue.replicate.replacedeadreplica.(success|error)
  • queue.replicate.removedeadreplica.(success|error)
  • queue.replicate.replacedecommissioningreplica.(success|error)
  • queue.replicate.removedecommissioningreplica.(success|error) #85844
• Clusters can now run nodes with different --max-offset settings at the same time. This enables operators to perform a rolling restart to change the value of each node's --max-offset flag. #85983
• Introduced a new server.secondary_tenants.redact_trace cluster setting that controls if traces should be redacted for operations run on behalf of secondary tenants. #85853
• The admission.kv.pause_replication_threshold cluster setting is now set to a default value of 0.8. On a fully migrated v22.2+ deployment, this will allow the KV layer to pause replication streams to followers located on stores that are close to activating their I/O admission control subsystem (thereby protecting these followers from additional overload). This cluster setting can be disabled by setting it to 0. #86147
• Added a sql.insights.execution_insights_capacity cluster setting, which limits the number of SQL execution insights retained in memory per node. #86272
• The new sql.insights.high_retry_count.threshold cluster setting may be used to configure how many times a slow statement (as identified by the execution insights system) must have been retried to be marked as having a high retry count. #86415
• Finalizing an upgrade to v22.2 requires that all in-flight schema changes enter a terminal state. This may mean that finalization takes as long as the longest-running schema change. #76154
• The option sql.mvcc_compliant_index_creation.enabled has been removed. #76154
• Added a new time series metric storage.keys.range-key-set.count for observing the count of internal range key set keys in the storage engine. In v22.2, these RangeKeySet keys are only used during DROP/TRUNCATE table operations, or when canceling an import. #86570
• The sql.insights.anomaly_detection.enabled cluster setting now defaults to true, and the sql.insights.anomaly_detection.latency_threshold cluster setting now defaults to 50ms, down from 100ms to complement the fixed-threshold detector's default of 100ms. #86673
• The disk bandwidth constraint can now be used to control admission of elastic writes. This requires configuration for each store, via the --store flag, that now contains an optional provisioned-rate field. The provisioned-rate field, if specified, needs to provide a disk-name for the store and optionally a disk bandwidth. If the disk bandwidth is not provided the cluster setting kv.store.admission.provisioned_bandwidth will be used. The cluster setting defaults to 0 (which means that the disk bandwidth constraint is disabled). If the effective disk bandwidth is 0 (including if using the possibly overridden cluster setting), the disk bandwidth constraint is disabled. Additionally, the admission control cluster setting admission.disk_bandwidth_tokens.elastic.enabled (which defaults to true) can be used to turn off enforcement even if other settings enable it. Turning off enforcement will still output all the relevant information about disk bandwidth usage, so can be used to observe part of the mechanism in action. To summarize, to enable this for a cluster with homogeneous disk, provide a disk-name in the provisioned-rate field in the store-spec, and set the kv.store.admission.provisioned_bandwidth cluster setting to the bandwidth limit. To only get information about disk bandwidth usage by elastic traffic (currently via logs, not metrics), perform the above actions and also set admission.disk_bandwidth_tokens.elastic.enabled to false. #86063
• The admission.kv.pause_replication_io_threshold cluster setting now defaults to 0 (off). #86776
• Clusters that are upgraded to an alpha or other manual build from the development branch will not be able to subsequently upgrade to a release build. #86345
• Added the rebalancing.writebytespersecond and rebalancing.readbytespersecond time series metrics. These metrics reflect the average number of bytes written and read across all replicas per store, over the last 30 minutes. #80245

Command-line changes

• Added support for the \password CLI command that enables secure alteration of the password for a user. The given password will always be pre-hashed with the password hash method obtained via the session variable password-encryption, e.g., scram-sha-256 as the default hashing algorithm. #77975
• Changed the default debug compact maximum compaction concurrency to the number of processors, and added a --max-concurrency flag for overriding the new default. #78987
• The standalone cockroach-sql executable now has more compatibility with cockroach sql, so it can be used as a drop-in replacement. For example, it supports running without a URL, using connection defaults. It also supports overriding --certs-dir and other client-side options also supported by cockroach sql. #82020
• BYTEA values are now formatted according to the bytea_output session setting. #81943
• The statement tag displayed for INSERT statements now has the full information returned by the server: the string "INSERT", followed by the OID of the row that was inserted (which is currently always 0 in CockroachDB), followed by the number of rows inserted. #81943

• CLI commands that use a SQL connection (e.g., cockroach sql and cockroach node status) now support connecting with PGPASSFILE and PGSERVICEFILE. The behavior is compatible with how libpq (the psql C library) behaves. The PGPASSFILE file defaults to the filepath ~/.pgpass, and has the format hostname:port:database:username:password, where the password field from the first line that matches the current connection parameters will be used to connect to the database. The PGSERVICEFILE file defaults to the filepath ~/.pg_service.conf, and has the format:

  [myservice]
  host=somehost
  port=26257
  user=someuser
  

  • Any connection parameters (including passfile or password) can be specified in this file as well. Then, a connection string that specifies the service=myservice connection parameter will use the values in PGSERVICEFILE to connect. #82389

• CLI commands that use a SQL connection (e.g., cockroach sql, cockroach node status) now default to using the file in ~/.postgresql/root.crt for the sslrootcert when connecting. The file can still be configured using the PGSSLROOTCERT environment variable or the sslrootcert URL parameter. #82389

• Using COPY in the SQL shell is now supported while inside an explicit transaction. #82101

• CTRL+C (the interrupt signal) can now be used in the CLI to attempt to cancel the currently executing SQL query. #82101

• cockroach sql (and thus cockroach demo too) now support the client-side commands \o and \qecho, like psql:

  • The \o command can redirect the output of SQL queries to a file.
  • The \qecho command adds arbitrary text to the current query output file. #83118

• CockroachDB now produces a clearer error when the path specified via --socket-dir is too long. #84532

• When the --background flag is specified, CockroachDB now makes three attempts to find a suitable directory to create the notification socket: the value of --socket-dir if specified, the value of $TMPDIR (or /tmp if the environment variable is empty), and the current working directory. If none of these directories has a name short enough, an explanatory error is printed. #84532

API endpoint changes

• Added logic to support dropping unused index recommendations. #77642
• ListSessions now returns closed sessions in addition to open sessions. ListSessionsRequest now has a exclude_closed_sessions flag, which is a BOOL to exclude closed sessions. serverpb.Session now has end and status fields, which specify the time the session ended and the status (opened, closed) of the session, respectively. #78650
• Updated the api/v2/rules endpoint to include additional rules for events to alert on. #80274
• Added a new last_auto_retry_reason field under the active_txn field for a session to the ListSessions API. This field contains the string describing the retry reason or nil if none exists. This is also surfaced in the crdb_internal.{cluster,node}_transactions tables and in the output of the SHOW TRANSACTIONS statement under the last_auto_retry_reason column. #81531
• serverpb.Session now has three new fields: number of transactions executed, transaction fingerprint IDs, and total active time. #82352
• Added information about total bytes, live (non-MVCC) bytes and live (non-MVCC) percentage to the table details endpoint. #83677
• Added support for index recommendations to be returned on the statement details API. #85863

DB Console changes

• Added index created time as an option on the DB Console Databases page. #78283
• Users can now see actively running queries and transactions in the SQL Activity page. The transactions and statements tabs in SQL activity now have a menu to show either active or historical transactions and statements data. #76753
• Added the last modified timestamp and coordinator ID to the Jobs page to aid in debugging jobs issues. #78501
• Added index recommendations to the Databases page for the Databases, Database Details, Database Table, and Index Details graphs. #79365
• Fixed resizing of tables on the Hot Ranges page. #80481
• Sessions Overview and Session Details pages now display closed sessions. The Sessions Overview Page now has username and session status filters. #80410
• The Learn more link on an empty transactions link now mentions transactions. #81530
• The Circuit Breaker Tripped events chart now displays the rate of events per interval instead of accumulated number of events. #81438
• The Jobs page now shows the oldest time (in UTC) that jobs are shown for. #81148
• The Cluster Overview page now displays a banner containing the previous versions of the cluster with a message cluster_version - Mixed Versions when a cluster runs nodes with different versions. #82118
• Fixed grammar on the mixed-version banner alert. #83150
• On the SQL Activity page, the selection to view historical or active executions will now persist between tabs. #83903
• The Active Statements and Active Transactions pages now have a single filter option for internal apps. These pages no longer display internal statements and transactions by default. #83014
• Added MVCC information to the tables list on the Databases page and on the Tables Details page. #84037
• Updated the Jobs Details page to a new design and added information about last execution time, next execution time, and execution count. #84498
• Updated the style in the Statement Details, Active Statement Details, Transaction Details, and Active Transaction Details summary component to be consistent with other existing styles. #84500
• Updated the time picker options to remove "1" on the hour and day options. #84510
• Added the Last Execution Time column to the SQL Activity overview, which allow users to sort by when queries were executed. This column is hidden by default. #84501
• Added Range Key Bytes and Range Value Bytes stats on the Node Details page. #85599
• A new section, Wait Time Insights has been added to the Active Statement and Transaction Details pages. The section is included if the transaction being viewed is experiencing contention and includes information on the blocked schema, table, index name, time spent blocking, and the transactions blocking or waiting for the viewed transaction. Only users having VIEWACTIVITY or higher can view this feature. The column Time Spent Waiting has been added to the active executions tables that shows the total amount of time an execution has been waiting for a lock. #85081
• The Explain Plans tab on Statement Details page now displays insights of index recommendations. #85863
• Added new Insights page to the DB Console. #84612
• Added the following fields to the Active Statement and Transaction Details pages:
  • Full Scan: indicates if the execution contains a full scan.
  • Last Retry Reason (Transactions page only): the last recorded reason the transaction was retried.
  • Priority (Transactions page only): the transaction priority. #85974
• The following fields have been added to the Sessions Overview page:
  • Transaction Count: the number of transactions executed by the session.
  • Session Active Duration: the time a session spent executing transactions.
  • Most recent Session fingerprint ids. #85974
• Removed the back to sessions link on Session Details "not found" page. #86050
• The statements and transaction fingerprint now refreshes data every 5 minutes for non-custom time ranges. #85772
• The time spent waiting columns for active execution tables has been hidden in CockroachDB Cloud. #86264
• Transactions and statements in active execution pages that are waiting for a lock will now have the status Waiting. #86329
• Added new Workload Insight Details page to the DB Console. #86325
• Added a button on the Statement Details page under the Explain Plan tab to perform the index recommendation directly from the DB Console. #86382
• Removed the Next Planned Execution Time label, when the job doesn't have a next planned execution scheduled. #86486
• Added a filter for live nodes based on MembershipStatus to resolve an issue where decommissioned nodes would in rare cases display as live in the DB Console. #86252
• Added new styles of summary cards on Session Details page to align with other details pages. #86572
• Added a link to the Explain Plan table linking to EXPLAIN documentation. #86581
• Surfaced paused replicas to Range Report, Problem Ranges, and Replication Metrics pages. #86407
• Changed the Plans table within the Explain Plan tab of the Statement Details page to use a plan gist instead of the plan ID. Also added the plan gist as the first line on the Explain Plan display. #86653
• Added clarification of the compression used to the tooltip of table size. #86821
• Changed the height of the SQL Box on Session Details, Active Transaction Details, Job Details, and Active Statement Details pages. #86812
• Added the new Schema Insights page to the DB Console, which displays a table of schema insights including different types of index recommendations (i.e., DROP/CREATE/REPLACE index recommendations). Each schema insight row offers the user the ability to execute the corresponding SQL query that realizes their schema insight via a clickable button. Filters are available to filter the surfaced schema insights by database and insight type, as well as search. #86317

Bug fixes

• Fixed the insight execution priority to display the correct value instead of always being default. Changed the column to string to avoid converting it in the UI. #86901
• Fixed the has_sequence_privilege() built-in function checking on the USAGE privilege. #82458
• Fixed a bug where backups in the base directory of a Google Storage bucket would not be discovered by SHOW BACKUPS. These backups will now appear correctly. #80182
• Fixed an optimizer bug that prevented expressions of the form (NULL::STRING[] <@ ARRAY['x']) from being folded to NULL. #77995
• Fixed the implementation of the function substr() in the vectorized execution engine for UTF-8 encodings. #77308
• A lookup join on pg_type.oid no longer results in an error. Example: SELECT pg_type.oid FROM (SELECT null::OID AS b) AS a INNER LOOKUP JOIN pg_type ON pg_type.oid=a.b. #78960
• Previously, queries reading from an index or primary key on FLOAT or REAL columns DESC would read -0 for every +0 value stored in the index. Fixed this to correctly read +0 for +0 and -0 for -0. #79473
• Previously, queries with many joins and projections of multi-column expressions, e.g., col1 + col2, either present in the query or within a virtual column definition, could experience very long optimization times or hangs, where the query is never sent for execution. This has now been fixed. #80212
• Previously, queries which involve an ORDER BY clause, a DISTINCT ON clause and a GROUP BY clause could sometimes error out depending on the columns referenced in those clauses. This is now fixed. #80447
• Updated the type reported in the wire protocol for STRING(n) types to match VARCHAR(n). #80414
• Previously, creating a table with a locality of REGIONAL BY ROW could intermittently fail with a missing type error. This is now fixed. #80590
• SHOW EXPERIMENTAL_FINGERPRINTS FROM TABLE now works on tables with partial indexes. #80539
• Fixed a rare race condition that could allow for a transaction to serve a stale read and violate real-time ordering under moderate clock skew. #80706
• The hex encoding for BYTEA values now works properly when used in COPY FROM ... CSV statements. #81120
• Constants in SQL query fields are now correctly removed for VIEWACTIVITYREDACTED users. #80707
• Fixed a gap in disk-stall detection. Previously, disk stalls during filesystem metadata operations could go undetected, inducing deadlocks. Now stalls during these types of operations will correctly fail the process. #81389
• Fixed a bug that caused duplicated schema change job description messages. #81268
• Fixed false negatives produced by the JSON ? operator when invoked on a JSON array with the vectorized engine set explicitly to off. #81648
• Fixed a bug where sequences could return values that are out-of-bounds in some cases. #81123
• Fixed the formatting of floats in arrays and tuples sent over the client-server pgwire protocol so that they respect the extra_float_digits parameter, and correctly format infinity values. #82022
• The DateStyle session setting is no longer ignored using the CLI when set in the options URL parameter. #82101
• Previously, dropping tables with foreign key dependencies would generate the wrong pgcode (Uncategorized versus DependentObjectsStillExist). This is now fixed. #80142
• Previously, if a foreign key was concurrently added while the referenced table was dropped before validation was completed, CockroachDB could potentially hang on the rollback. Now, CockroachDB will generate appropriate errors when the referenced table is dropped and gracefully rollback the change. #80142
• Views are no longer allowed to reference types that are defined in different databases. Even though this was allowed at view-creation time previously, it would cause errors, since cross-database type references are not supported. #82763
• CREATE TABLE AS in explicit transactions would fail with an error if the size of the source table exceeded the Raft command size limit. #82951
• Range lease transfers are no longer permitted to follower replicas that may require a Raft snapshot. This ensures that lease transfers are never delayed behind snapshots, which could previously create range unavailability until the snapshot completed. Lease transfers are now only allowed when the outgoing leaseholder can guarantee that the incoming leaseholder does not need a snapshot. #82758
• Fixed a bug where creating a unique, expression index on a REGIONAL BY TABLE could result in an error. #83125
• Fixed a bug where in rare cases a stale read could be returned. This is fixed by introducing a new in-memory field to a LeaseStatus, which is when it most recently acquired data to a different store. Any uncertain observed timestamps before this time are ignored. #83345
• Previously, the querySummary metadata field in the crdb_internal.statement_statistics table was inconsistent with the query metadata field for executed prepared statements. These fields are now consistent for prepared statements. #83673
• Fixed a bug where BACKUP may be missing data when the cluster was configured with very low values for kv.bulk_sst.max_allowed_overage and kv.bulk_sst.target_size cluster settings. #83102
• Fixed an issue where some exports would receive "unexpected closure of consumer" rather than the actual error the export encountered. #77938
• Fixed a bug causing a graceful node shutdown to stall forever. #83824
• The PASSWORD option of the CREATE/ALTER ROLE commands now requires the password to be surrounded with single quotes. This fixes confusion that could arise when a mixed-case string is used, since previously that would cause the password to be normalized to lowercase. #83924
• Fixed a bug causing the row_to_json SQL function to error out when used with input having the VOID data type. #83876
• The Active Transactions page no longer shows transactions from closed sessions. #83896
• Fixed a bug that could cause an optimizer panic in rare cases when a query had a left join in the input of an inner join. #83875
• DROP SCHEMA ... CASCADE in the legacy schema changer now correctly fails when a backup, restore, or an import of an underlying table or type is concurrently underway. #84189
• DROP ... CASCADE of a database or a schema in the declarative schema changer now correctly fails when a when a backup, restore, or an import of an underlying table or type is concurrently underway. #84189
• Fixed a bug that caused internal errors in rare cases when performing DELETEs on a table that had foreign key references to it with the ON DELETE CASCADE option. For example, imagine tables a and b already exist, and b has a foreign key ON DELETE CASCADE column referencing a. If table c is added with a foreign key ON DELETE CASCADE column referencing table b and a DELETE statement is performed on table a in the same transaction, an internal error could occur. This bug has been present since v21.1.0. #84219
• Fixed a bug that could corrupt indexes and cause incorrect query results with INTERVAL values greater than about 290 years or less than about -290 years. #84045
• Fixed a bug that led to the querySummary field in the crdb_internal.statements_statistics metadata column being empty. #84170
• Previously, CockroachDB could deadlock when evaluating analytical queries if multiple queries had to spill to disk at the same time. This is now fixed by making some of the queries error out instead. #84398
• Fixed a bug where an ephemeral I/O error could crash a node. #84449
• Fixed a bug where, in an ALTER PRIMARY KEY statement, if the new primary key columns is a subset of the previous primary key columns, CockroachDB would not rewrite existing secondary indexes, and hence those secondary indexes continue to have some of the previous primary key columns in their suffixColumns. But the user might, reasonably think those columns are not used anymore and proceed to drop them. The bug then caused those dependent secondary indexes to be dropped, unexpectedly for the user. #84303
• Fixed a bug where the CLI cockroach commands could produce spurious "latency jump" warnings when connecting to a remote server. This bug had been introduced in CockroachDB v21.2. #84031
• Fixed vectorized evaluation of COALESCE when involving expressions of type VOID, and enhances type checking of NULLIF expressions with VOID, so incompatible comparisons can be caught during query compilation instead of during query execution. #83868
• In the DB Console, changing the time window using arrow buttons and the Now button will now properly turn the timeframe into a moving window when endTime = now. #84649
• The cockroach process no longer announces that it is shutting down to stdout when running with the --background flag. #84532
• The public role can no longer be granted default privileges with the grant option. This was a bug because the public role already cannot have the grant option on regular privileges. #85027
• Fixed a bug where CockroachDB should initialize a schemaChangerState of connExecutor from the corresponding session variable (use_declarative_schema_changer), which can cause DDL statements to be executed under the legacy schema changer unknowingly. #85344
• When a CockroachDB node is being drained, all queries that are still running on that node are now forcefully canceled after waiting the server.shutdown.query_wait period. #82752
• The SQL Unix socket, when requested, now contains a port number compatible with the connection URL when --listen-addr is configured to auto-allocate a port number. This bug had existed since CockroachDB v1.0. #84910
• Previously, if a Unix socket was requested but it already existed on disk, CockroachDB would exit with an error even if the original owner process was not running. This limitation would, for example, prevent reuse of a Unix socket after an abnormal shutdown. It had been present since CockroachDB v1.0. This is now fixed. #84910
• Fixed a panic when loading tenant HTTP endpoints for statement statistics. #85407
• The crdb_internal.range_statistics function now uses a vectorized implementation that allows the lookup of range metadata to occur in parallel. #85442
• Fixed a bug where EXECUTE did not accept placeholder arguments if the type did not exactly match. #85861
• Fixed a bug where, in a stage of validation operations in the declarative schema changer, only the first validation operation is properly handled and the rest are skipped. #85781
• Fixed a bug internal to drawing dependency graph of a DDL statement under the declarative schema changer. #85773
• Fixed a rare bug where errors could occur related to the use of arrays of type ENUM. #85940
• CockroachDB now more precisely respects the distsql_workmem setting, which improves the stability of each node and makes out-of-memory issues less likely. #85440
• Fixed a bug in post deserialization changes where CockroachDB might incorrectly change constraint ID of a constraint that lives in the mutation slice of a table descriptor. #85778
• Active Execution pages will no longer crash if there are no filters set in local settings. #86139
• Fixed a bug where an incorrect parameter name for database was used in the SQL API. The correct parameter name database is now used. #86169
• The statements and transaction fingerprint will no longer get stuck on the loading page in the CockroachDB Cloud Console after 5 minutes idling on the page. #85772
• Intersection spatial operations could previously return incorrect results on the ARM processor. This is now resolved. #86126
• Sequence integer bounds are now consistent with the cluster setting default_int_size. #84555
• Users that create an external connection are now granted ALL privileges on the object. #86336
• Fixed a vulnerability in the optimizer that could cause a panic in rare cases when planning complex queries with ORDER BY. #86193
• Fixed a bug in backup where spans for views were being backed up. Because ranges are not split at view boundaries, this can cause the backup to send export requests to ranges that do not belong to any backup target. #85158
• Previously, SET SESSION AUTHORIZATION DEFAULT would have no effect. Now, it causes the current role to be reset to the original user who logged into the session. #86485
• Fixed a bug with Search in the Active Execution Overview pages, where providing a search string did not properly filter out statements and transactions that do not contain the search string. #86764
• Fixed a longstanding bug that could cause the optimizer to produce an incorrect plan when aggregate functions st_makeline or st_extent were called with invalid-type and empty inputs respectively. #86722
• Fixed a crash that could occur when formatting queries that have placeholder BitArray arguments. #86607
• Fixed a crash/panic that could occur if placeholder arguments were used with the with_min_timestamp() or with_max_staleness() functions. #86605
• Fixed a bug that caused some special characters to be misread if COPY ... FROM into a TEXT[] column was reading them. #86712
• Previously, CockroachDB would return an internal error when evaluating the json_build_object() built-in when an ENUM or VOID data type was passed as the first argument. This is now fixed. #86675
• Rollback of materialized view creation left references inside dependent objects. This fix adds clean up to the back/forward references for materialized views. #82087
• User-defined functions are disallowed in any expressions (column, index, constraint) in tables. #85718

Performance improvements

• Performance of inner, semi, or anti joins between two tables with ORed equi-join predicates is improved by enabling the optimizer to select a join plan in which each equi-join predicate is evaluated by a separate join, with the results of the joins as a union or intersected together. #74303
• Expressions using the overlaps (&&) operator for arrays now support index-acceleration for faster execution in some cases. #77418
• Improved the ability of the optimizer to detect contradictions in filter conditions of the form x IS NULL when x can never be NULL. This enables the optimizer to simplify query plans. #80211
• Added per-span checkpointing to cases when the high-water mark lags excessively behind the leading edge of the frontier in order to avoid re-emitting the majority of spans due to a small minority that is experiencing issues progressing. This helps to enable changefeeds to operate on very large tables when performing large catchup scan. #77763
• The optimizer cost model is now more aware of the cost of executing expensive functions (such as spatial functions) in filter conditions. This may lead to improved query plans. #81924
• Changefeed catchup scans now use time-bound iterators, which improves their performance by avoiding accessing data that is outside the catchup scan time interval. Previously, this was controlled by the kv.rangefeed.catchup_scan_iterator_optimization.enabled cluster setting, which defaulted to off. This change removes this cluster setting, as its functionality is in effect now always enabled. #82450
• The optimizer now explores more efficient query plans when indexing computed columns and expressions that have IS NULL expressions. #83619
• The optimizer can now return the results of a join in sorted order in more cases. This can allow the optimizer to avoid expensive sorts that need to buffer all input rows. #84689
• The optimizer is now less likely to take an excessive amount of time to plan queries with many joins. #85100
• The optimizer can detect contradictory filters in more cases, leading to more efficient query plans. #85351
• The row-level TTL job has been modified to distribute work using DistSQL. This usually results in the leaseholder nodes managing deletes of the spans they own. #84728
• The execution engine can now short-circuit execution of lookup joins in more cases, which can decrease latency for queries with limits. #85731
• ILIKE and NOT ILIKE filters can now be evaluated more efficiently in some cases. #85695
• MVCC garbage collection should now be much less disruptive to foreground traffic than previously. #83213
• The execution engine can now perform lookup joins in more cases. This can significantly improve join performance when there is a large table with an index that conforms to the join ON conditions, as well as allow joins to halt early in the presence of a limit. #85597
• Point deletes in SQL are now more efficient during concurrent workloads. #63416
• Enabled table statistics forecasts, which predict future statistics based on historical collected statistics. Forecasts help the optimizer produce better plans for queries that read data modified after the latest statistics collection. CockroachDB only uses the forecasts that fit the historical collected statistics very well, meaning it has high confidence in their accuracy. Forecasts can be viewed using SHOW STATISTICS FOR TABLE ... WITH FORECAST. #86078
• Optimized the execution of COPY FROM. #83840
• Long-running SQL sessions are now less likely to maintain large allocations for long periods of time, which decreases the risk of OOM and improves memory utilization. #85949
• SQL statements that cause events to be logged to system.eventlog are now able to complete faster. #86174
• Planning time has been reduced for queries over tables with a large number of columns and/or indexes. #86606
• Introduced the kv.log_range_and_node_events.enabled cluster setting to disable transactionally logging range events (e.g., merges, splits, and rebalancing) and node join and restart events to system tables, to remove the dependency on such tables and improve performance. #85593
• The default L0 sub-level enforcement for rebalancing and allocation decisions is now set to block_rebalance_to. This has the effect of stopping rebalancing to stores that have high read amplification. #79794
• Changed the MVCC garbage collection queue to recompute MVCC statistics on a range, if after doing a garbage collection run it still thinks there is garbage in the range. #83194

Build changes

• Upgraded to Go 1.18.4. #84590
• Build experimental Linux ARM64 binary. #86043

Contributors

This release includes 2637 merged PRs by 141 authors. We would like to thank the following contributors from the CockroachDB community:

• Eng Zer Jun (first-time contributor)
• Farye Nwede (first-time contributor)
• Frediano Ziglio (first-time contributor)
• Frédéric BIDON (first-time contributor)
• Nathan Lowe (first-time contributor)
• Prashant Khoje (first-time contributor)
• Rajiv Sharma (first-time contributor)
• Tim Graham
• changhan (first-time contributor)
• dandotimujahid (first-time contributor)
• likzn (first-time contributor)
• lyubomirkyuchukov (first-time contributor)
• mosquito2333
• nnaka2992 (first-time contributor)

Was this helpful?