10.1.x Release Notes

Vertica
Software Version: 10.1.x

 

IMPORTANT: Vertica for SQL on Hadoop Storage Limit

Vertica for SQL on Hadoop is licensed per node, on an unlimited number of central processing units or CPUs and an unlimited number of users. Vertica for SQL on Hadoop is for deployment on Hadoop nodes. Includes 1 TB of Vertica ROS formatted data on HDFS.

This 1 TB of ROS limit enforcement is currently only contractual, but it will begin to be technically enforced in Vertica 10.1. Starting with Vertica 10.1, if you are using Vertica for SQL on Hadoop, you will not be able to load more than 1 TB of ROS data into your Vertica database. If you were unaware of this limitation and already have more than 1 TB of ROS data in your database at this time, please make any necessary adjustments to stay below the limit, or contact our sales team to explore other licensing options.

IMPORTANT: Before Upgrading: Identify and Remove Unsupported Projections

With version 9.2, Vertica has removed support for pre-join and range segmentation projections. If a table's only superprojection is one of these projection types, the projection is regarded as unsafe.

Before upgrading to 9.2 or higher, you are strongly urged to check your database for unsupported projections. If the upgrade encounters these projections, it is liable to fail. You must then revert to the previous installation.

Solution: Run the pre-upgrade script

Vertica has provided a pre-upgrade script that examines your current database and sends to standard output its analysis and recommendations. The script identifies and lists any unsupported projections. If the script finds projection buddies with different SELECT and ORDER BY clauses, it generates a deploy script. Run this script to remedy projections so they comply with system K-safety.

https://www.vertica.com/pre-upgrade-script/

For more information, see Fixing Unsafe Buddy Projections in the Vertica documentation.

Updated: 8/24/2021

About Vertica Release Notes

What's New in Vertica 10.1.1

What's Deprecated in Vertica 10.1.1

Vertica 10.1.1-7: Resolved Issues

Vertica 10.1.1-6: Resolved Issues

Vertica 10.1.1-5: Resolved Issues

Vertica 10.1.1-4: Resolved Issues

Vertica 10.1.1-3: Resolved Issues

Vertica 10.1.1-2: Resolved Issues

Vertica 10.1.1-1: Resolved Issues

Vertica 10.1.1: Resolved Issues

Vertica 10.1.1: Known Issues

What's New in Vertica 10.1.0

What's Deprecated in Vertica 10.1.0

Vertica 10.1.0-3: Resolved Issues

Vertica 10.1.0-2: Resolved Issues

Vertica 10.1.0-1: Resolved Issues

Vertica 10.1.0: Resolved Issues

Vertica 10.1.0: Known Issues

About Vertica Release Notes

The Release Notes contain the latest information on new features, changes, fixes, and known issues in Vertica 10.1.x.

They also contain information about issues resolved in:

Downloading Major and Minor Releases, and Service Packs

The Premium Edition of Vertica is available for download at https://support.microfocus.com/downloads/swgrp.html.

The Community Edition of Vertica is available for download at https://www.vertica.com/download/vertica/community-edition.

The documentation is available at https://www.vertica.com/docs/10.1.x/HTML/index.htm.

Downloading Hotfixes

Hotfixes are available to Premium Edition customers only. Contact Vertica support for information on downloading hotfixes.

 

What's New in Vertica 10.1.1

Take a look at the Vertica 10.1.1 New Features Guide for a complete list of additions and changes introduced in this release.

admintools

New restart_node Option

The node IP addresses of an Eon Mode database on Kubernetes must occasionally be updated—for example, a pod fails, or is added to the cluster or rescheduled. When this happens, you must update the catalog with the new IP addresses of affected nodes. Vertica's restart_node tool addresses this requirement with its new option ‑‑new-host-ips, which lets you change the node IP addresses of an Eon Mode database that is running on Kubernetes.

This option is only supported for Eon databases on Kubernetes.

For details, see Restarting a Node with New Host IPs.

Backup, Restore, Recovery, and Replication

New vbr Configuration File Option for Foreign Key Constraints

The new drop_foreign_constraints vbr configuration file option enables you to restore individual objects independent of their foreign key constraints from a full or object-level backup.

For details, see Restoring Individual Objects from a Full or Object-Level Backup and [Misc] Miscellaneous Settings.

Data Types

One-Dimensional Arrays in ORC Files

The ORC parser now supports reading one-dimensional arrays of primitive types for both external and internal tables. For details, see Reading Arrays in ORC Data.

Documentation Updates

New File Systems Section in SQL Reference

To make information about interacting with external file systems easier to find, the SQL Reference now has a new File Systems and Object Stores section. The reference pages in this section describe URI formats and authentication requirements, summarize applicable configuration parameters, and provide examples. These reference pages supplement the topics about performing specific file-system actions such as loading data and creating communal storage locations.

Eon Mode

Fetching Pinned Objects to a Warming Depot

When depot warming is enabled, on startup the node now checks the new configuration parameter PreFetchPinnedObjectsToDepotAtStartup. If this parameter is enabled, the node gets a list of objects that are pinned on this node's subcluster and queues them for fetching to the depot.

For details, see Depot Warming.

Eon Mode Communal Storage on Azure Blob Storage

You can now create Eon Mode databases on Azure using Azure's blobs for communal storage, with the following restrictions:

For details, see Eon Mode Databases on Azure.

Loading Data

Load from Azure Blob Storage

You can now load data and create external tables from data stored in Azure Blob Storage. Use the azb URI scheme. For more information, see Azure Blob Storage Object Store.

Machine Learning

PMML 4.4 support

Vertica now supports PMML models of version 4.4 and below.

Management Console

Changes to MC Email Configuration

For more information on configuring email notifications for MC, see Set Up Email.

New Local SSD Options for Google Cloud Platform (GCP) in Eon Mode

In Eon Mode, Management Console supports new local SSD options (ephemeral storage) for GCP. Vertica uses the new local SSD options to provide new volume configuration defaults, described in the following table:

 

Total Local SSDs Depot Temp
5 4 x 375 GB 1 x 375 GB
6 5 x 375 GB 1 x 375 GB
16 12 x 375 GB 4 x 375 GB
24 20 x 375 GB 4 x 375 GB

For details about persistent storage and instance types available for each new default configuration, see Eon Mode Volume Configuration Defaults for GCP.

New Feedback Button

There is a new Feedback button that enables you to submit a star rating and free-text comments to Vertica. The button displays vertically at the bottom-right of your browser.

Vertica collects the following metadata along with your feedback submission:

New Option to Provide User Analytics and Tracking Information

You can provide Vertica with analytic information about your MC usage, for use when making improvements to the MC in future releases. Your information is stored anonymously and securely, and never shared with third-party organizations.

Choose whether to participate when you accept the End-User License Agreement Terms for the MC during your first login, or go to the User Analytics and Tracking section in MC settings > Configuration. For details, see Managing MC Settings.

Parquet Export

Additional Compression Support

EXPORT TO PARQUET now supports compression with GZIP, Brotli, and ZSTD, in addition to Snappy which was already supported.

Export to Azure Blob Storage

You can use EXPORT TO PARQUET to export data to Azure Blob Storage. If you are using Azure managed identities, you do not need to configure credentials in Vertica. Alternatively, you can specify credentials in session or database parameters.

As with other object stores, data is exported directly to the target directory instead of being written to a temporary location first, and you must wait for the export to finish before reading the data.

SDK Updates

Polymorphic Outputs

User-defined transform functions and user-defined analytic functions now support polymorphic output types. You can declare, in the factory, that the function returns any type, and determine the return type at runtime. For details, see Creating a Polymorphic UDx.

Security and Authentication

GRANT TRUSTED for Access Policies

By default, Vertica only allows users to edit data that they can see. By specifying GRANT TRUSTED for a new or existing access policy, you can now override this behavior and defer entirely to explicit GRANT statements when assessing whether a user can perform a DML operation on the target table.

For more information on this option and DML operations as they relate to access policies, see Access Policies and DML Operations.

SQL Functions and Statements

ARGMAX and ARGMIN

Vertica now supports the analytic functions ARGMAX and ARGMIN. These functions are patterned after the mathematical functions argmax(f(x)) and argmin(f(x)), which return the value of x that maximizes and minimizes f(x), respectively. Similarly, the new Vertica functions ARGMAX and ARGMIN take two arguments, target and arg, where both arguments are columns or column expressions in the queried dataset. ARGMAX and ARGMIN respectively find the row with largest and smallest non-null value in target, and return the value of arg in that row.

TO_JSON

The new TO_JSON function returns the JSON representation of a complex-type value, including mixed and nested complex types.

STRING_TO_ARRAY Improvements

The STRING_TO_ARRAY function now allows you to specify several more special characters for collections beyond the element delimiter that was already supported. You can now also specify the characters that mark the beginning and end of the array, the null element, and an enclosing character for elements. These parameters behave the same way as the corresponding options when loading delimited data.

To support these additional optional parameters, the syntax has changed. The previous syntax is deprecated.

APPROXIMATE_QUANTILES

The new APPROXIMATE_QUANTILES function computes an array of weighted, approximate percentiles of a column within some user-specified error.

LISTAGG Separators

The LISTAGG function now supports multi-character separators.

MAKEUTF8

The new MAKEUTF8 function takes a string and removes or replaces characters that are not UTF-8. You can use this function when loading data as an alternative to cleaning input data before loading.

System Tables

ACCESS_POLICY

The ACCESS_POLICY system table now contains a new column called TRUST_GRANTS, which indicates whether a given access policy includes the GRANT TRUSTED option. This option determines how access policies handle DML operations. For more information, see Access Policies and DML Operations.

What's Deprecated in Vertica 10.1.1

This section describes the two phases Vertica follows to retire Vertica functionality:

Deprecated

The following Vertica functionality was deprecated and will be retired in future versions:

Release Functionality Notes
10.1.1 STRING_TO_ARRAY(array,delimiter) syntax Instead, use USING PARAMETERS syntax for collection_delimiter and other new parameters. See STRING_TO_ARRAY.
Support for Red Hat Enterprise Linux / CentOS 6.x  

Removed

No functionality was removed in this release.

For more information see Deprecated and Retired Functionality in the Vertica documentation.

Vertica 10.1.1-7: Resolved Issues

Release Date: 8/19/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-78347 Data Networking In rare circumstances, the socket on which Vertica accepts internal connections could erroneously close and send a large number of socket-related error messages to vertica.log. This issue has been fixed.
VER-78350 S3, Storage and Access Layer Vertica now stores S3 storage location Location_path values in the same upper and lower case format that they were created in.
VER-78351 UI - Management Console After an automatic upgrade in AWS, the Management Console Overview page now displays properly.
VER-78354 Catalog Engine Standby nodes did not get changes to the GENERAL resource pool when it replaced a down node. This problem has been resolved.
VER-78357 Optimizer Updating the result set of a query that called the volatile function LISTAGG resulted in unequal row counts among projections of the updated table. This issue has been resolved.
VER-78362 UI - Management Console COPY type of queries have been added to the list of queries displayed for Completed Queries on Query Monitoring Activity page.
VER-78364 ILM, Tuple Mover

Several Vertica partition management operations support a force-split option: copy_partitions_to_table, drop_partitions, move_partitions_to_table, and swap_partitions_between_tables. In all cases, if force-split is true, and the operation specifies a range of partition keys that span multiple containers or part of a single container, Vertica executes the operation in two steps:

1. Breaks open the ROS containers

2. Reorganizes/merges the ROS containers as per operation requirements

Occasionally, the Tuple Mover would attempt to merge the split ROS containers before step 2 was complete, and the operation would fail. This issue has been resolved: the two stages are now part of a single transaction. Until the transaction is complete, the Tuple Mover does not queue merge requests on the split containers.

VER-78441 Admin Tools If admintools called create_db and the database creation process was lengthy, admintools sometimes prompted users to confirm whether to continue waiting. If the user did not answer the prompt--for example, when create_db was called by a script--create_db completed execution without creating all database nodes and properly updating the configuration file admintools.conf. In this case, the database was incomplete and unusable. Now, the prompt times out after 120 seconds. If the user doesn't respond within that time period, create_db exits.
VER-78500 Installation Program Vertica now properly handles nofile and nproc values in environments with >1TB RAM.
VER-78523 Depot Vertica now properly detects and handles corrupted depot index files.

Vertica 10.1.1-6: Resolved Issues

Release Date: 7/21/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-77828 Backup/DR When restoring objects in coexist mode, the STDOUT now contains the correct schema name prefix.
VER-77871 Kafka Integration The Kafka scheduler no longer hangs in rare cases.
VER-77875 Backup/DR Vertica no longer checks for access permissions check for the root of the S3 bucket during backup/restore.
VER-78124 Catalog Engine Vertica now restarts properly for nodes that have very large checkpoint files.
VER-78129 Optimizer Queries that included the TABLESAMPLE option were not supported for views. This issue has been resolved: you can now query views with the TABLESAMPLE option.
VER-78130 Execution Engine

It was unsafe to reorder query predicates when the following conditions were true:

* The query contained a predicate on a projection's leading sort order columns that restricted the leading columns to constant values, where the leading columns also were not run-length encoded.

* A SIPS predicate from a merge join was applied to non-leading sort order columns of that projection.

This issue has been resolved: query predicates can no longer be reordered when it contains a predicate on a projection's leading sort order columns that restrict leading columns to constant values, where the leading columns are not run-length encoded.

VER-78132 Installation Program The Vertica installer now optimizes and caches the information obtained in get_disks and reuses it instead of calling get_disks repeatedly.
VER-78147 Data Removal - Delete, Purge, Partitioning If you created a local storage location for USER data on a cluster that included standby nodes, attempts to drop the storage location returned with an error that Vertica was unable to drop the storage location from standby nodes. This issue has been resolved.
VER-78201 Spread Spread now responds properly to network changes.
VER-78211 Catalog Engine If you called close_session() while running analyze_statistics() on a local temporary table, Vertica sometimes crashed. This issue has been resolved.

Vertica 10.1.1-5: Resolved Issues

Release Date: 7/13/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-77844 Kafka Integration The vkconfig scheduler now properly supports an offset of -3.
VER-78065 Execution Engine A change in Vertica 10.1 prevented volatile functions from being called multiple times in an SQL macro. This change affected the throw_error function. The throw_error function is now marked immutable, so SQL macros can call it multiple times.
VER-78092 SDK-Python Python users can now define scalar functions as immutable.

Vertica 10.1.1-4: Resolved Issues

Release Date: 7/7/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-76256 Hadoop Vertica now properly handles queries of external tables of Parquet files with ZSTD compression.
VER-77571 Data Collector Vertica now properly handles custom data collector settings when upgrading to version 10.x.
VER-77634 Data Export Vertica now displays a more helpful warning message when using the deprecated s3export function on Minio storage.
VER-77928 Hadoop Exporting data to Parquet now properly respects resource pool limits.
VER-77946 Execution Engine Before evaluating a query predicate on rows, Vertica gets the min/max of the expression to determine what rows it can first prune from the queried dataset. An incorrect check on a block's null count caused Vertica to use the maximum value of an all-null block, and mistakenly prune rows that otherwise would have passed the predicate. This issue has been resolved.

Vertica 10.1.1-3: Resolved Issues

Release Date: 6/15/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-77181 Spread Vertica now properly detects duplicate tokens with the same arq ID.
VER-77389 Installation Program The Vertica pre-upgrade script now displays more helpful error messages for users running it against version 10 and later databases.
VER-77391 Data load / COPY, Security The COPY option REJECTED DATA to TABLE now properly distributes data between tables with identical names belonging to different schemas.
VER-77458 Optimizer SELECT clause CASE expressions with constant conditions and string results that were evaluated to shorter strings sometimes produced an internal error when participating in joins with aggregation. This issue has been resolved.
VER-77490 Execution Engine If a query includes 'count(s.*)' where s is a subquery, Vertica expects multiple outputs for s.*. Because Vertica does not support multi-valued expressions in this context, the expression tree represents s.* as a single record-type variable. The mismatch in the number of outputs can result in database failure.
VER-77523 Security Vertica now automatically creates needed default key projections for a user with DML access when that user performs an INSERT into a table with a primary key and no projections.
VER-77573

Monitoring

Startup.log now contains a section that identifies nodes that were slow to join a cluster.

Vertica 10.1.1-2: Resolved Issues

Release Date: 6/9/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-77369 Hadoop Vertica now properly connects to an HDFS nameservice regardless of the upper- or lowercase spelling of the nameservice.
VER-77295 Backup/DR Vertica vbr can now delete restore points that are already partially deleted.
VER-77246 Data Removal - Delete, Purge, Partitioning Vertica DML performance is now improved when loading data into partitioned tables.
VER-77180 Execution Engine

Vertica was unable to optimize queries on v_internal tables, where equality predicates (with operators = or <=>) filtered on columns relname or nspname, in the following cases:

  • The predicate specified expressions with embedded characters such as underscore (_) or percentage (%). For example: SELECT * FROM v_internal.vs_columns WHERE nspname = 'x_yz';
  • The query contained multiple predicates separated by AND operators, and more than one predicate queried the same column, either nspname or relname. For example: SELECT * FROM v_internal.vs_columns WHERE nspname = 'xyz' AND nspname <> 'vs_internal'

In this case, Vertica was unable to optimize equality predicate nspname = 'xyz'. In all these cases, the queries are now optimized as expected.

VER-77154 Tuple Mover Previously, the Tuple Mover attempted to merge all eligible ROS containers without considering resource pool capacity. As a result, mergeout failed if the resource pool could not handle the mergeout plan size. This issue has been resolved: the Tuple Mover now takes into account resource pool capacity when creating a mergeout plan, and adjusts the number of ROS containers accordingly.

Vertica 10.1.1-1: Resolved Issues

Release Date: 5/13/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-76899 Optimizer The NO HISTOGRAM event was set incorrectly on the dc_optimizer_events table's hidden epoch column. As a result, the suggested_action column was also set incorrectly to run analyze_statistics. This issue is resolved: the NO HISTOGRAM event is no longer set on the epoch column.
VER-76971 Backup/DR Object restore now functions properly for objects >1 TB on Eon Mode clusters running on S3.
VER-76994 Optimizer Using unary operators as GROUP BY or ORDER BY elements in WITH clause statements caused Vertica to crash. The issue is now resolved.
VER-77020 Client Drivers - ADO Previously, cancellation of an active query issued by Vertica's ADO.NET driver did not work; the query ran to completion instead of being canceled. This problem has been corrected and active queries are now properly interrupted and terminated by cancel requests.
VER-77021 Optimizer If you called a view and the view's underlying query invoked a UDx function on a table with an argument of '*' (all columns), Vertica crashed if the queried table later changed--for example, columns were added to it. The issue has been resolved: the view now returns the same results.
VER-77079 Machine Learning Vertica now properly handles combined use of the ROC function and the predict_svm_classifier.

Vertica 10.1.1: Resolved Issues

Release Date: 4/30/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-76360 Catalog Sync and Revive, Depot, EON Previously, when a node belonging to a secondary subcluster restarted, it lost files in its depot. This issue has been fixed.
VER-76361 Execution Engine The REGEXP_REPLACE function now properly accepts and returns LONG VARCHAR values.
VER-76400 Kafka Integration Kafka UDx now officially supports SASL SCRAM-SHA-256 AND SCRAM-SHA-512
VER-76457 Data Export Previously during export to Parquet, Vertica wrote the time portion of each timestamp value as a negative number for all timestamps before POSTGRES EPOCH DATE (2000-01-01). Due to this some tools (e.g. Impala) could not load such timestamps from parquet files exported by Vertica. This is fixed now.
VER-76475 Execution Engine With Vertica running on machines with very high core counts, complex memory-intensive queries featuring an analytical function that fed into a merge operation sometimes caused a crash if the query ran in a resource pool where EXECUTIONPARALLELISM was set to a high value. This issue has been resolved.
VER-76486 Depot System tables continued to report that a file existed in the depot after it was evicted, which caused queries on that file to return "File not found" errors. This issue has been resolved.
VER-76522 Installation Program, UI - Management Console The Vertica Management Console installer has been optimized to reduce its storage footprint.
VER-76581 Recovery Before this fix, when DFS files go missing on one or more nodes, it would cause the LGE and expected recovery epoch to be pushed to the createEpoch of the DFS file. This recovery epoch might be less than AHM epoch and prevent normal startup with auto recovery as well as ASR. In another scenario, if DFS file is missing on multiple nodes, it would also prevent startup with auto recovery with insufficient UP nodes error. Here, ASR would also fail as it does not try to recover the DFS files as part of specified recovery and relied on auto Recovery to recover missing files.

After this fix, we no longer consider DFS files in determining LGE and expected recovery epoch so, missing DFS files cannot push recovery epoch to behind AHM. Also, ASR will try to recover missing DFS files from replicas on other nodes and drop any unrecoverable DFS files (those that are missing on all nodes in cluster). This will prevent auto recovery later on from erroring out due to insufficient UP nodes. The cluster startup with ASR and --force flag will now succeed.
VER-76631 Data Export, Optimizer Previously, during export to parquet Vertica checked the write permissions for the output location on all UP nodes in the cluster. This caused errors when some nodes that are not participating in the export didn't have write permissions for the output location. This is now fixed by Vertica checking write permissions only on participating nodes.
VER-76655 UI - Management Console In the wizard mode of the MC Database Designer, k-safety warning dialog had OK button unresponsive under certain conditions. The issue was resolved.
VER-76700 Admin Tools When adding a node to an Eon-mode database with Administration Tools, users were prompted to rebalance the cluster, even though this action is not supported for Eon. This issue was resolved: now Administration Tools skips this step for an Eon database.
VER-76756 Optimizer, Security For a view A.v1, its base table B.t1, and an access policy on B.t1: users no longer require a USAGE privilege on schema B to SELECT view A.v1.
VER-76769 Execution Engine In queries that used variable-length-optimized joins, certain types of joins incurred a small risk of a crashing the database due to a problem when checking for NULL join keys. This issue has been resolved.
VER-76802 Client Drivers - JDBC, ComplexTypes The JDBC client driver now returns the correct type ID for complex types.
VER-76803 Client Drivers - ODBC, Client Drivers - VSQL, Client Drivers – OLEDB, Security Vertica FIPS support is restored.

Known issues Vertica 10.1.1

Updated: 4/30/21

Vertica makes every attempt to provide you with an up-to-date list of significant known issues in each release. We will update this list as we resolve issues and as we learn of new issues.

Issue

Component

Description

VER-75057 ComplexTypes In an external table, if a struct contains an array field and the struct is null, the whole struct (ROW) is treated as null in Vertica. This behavior differs from structs without arrays, where a null struct is treated as a ROW where all fields are null.
VER-70468 Documentation Creating a load balance group for a subcluster with a ROUNDROBIN load balance policy only has an effect if you set the global load balancing policy to ROUNDROBIN as well. This is the case, even though the LOAD_BALANCE_GROUPS system table shows the group's policy as ROUNDROBIN.
VER-69803 Hadoop The infer_external_table_ddl meta-function will hang if you do not set authentication information in the current session when analyzing a file on GCP.
VER-64916 Kafka Integration When Vertica exports data collector information to Kafka via notifier, the serialization logic does not properly handle Vertica internal data type translation. When importing or copying that data back into the Management Console, Vertica uses the raw data in Kafka and inserts garbled data into the extended monitoring database.
VER-64352 SDK Under special circumstances, a sequence of statements to create and drop Python libraries fails. For example, the following session statements attempt to create Python libraries lib1 and lib2:

CREATE LIBRARY lib1 AS :lib1_path DEPENDS :numpy_path LANGUAGE 'Python';
DROP LIBRARY lib1;
CREATE LIBRARY lib2 AS :lib2_path DEPENDS :sklearn_path LANGUAGE 'Python';

Here, lib1 is a Python library that imports module numpy, while lib2 imports module sklearn, which relies on numpy. After lib1 is dropped, the CREATE LIBRARY statement for lib2 fails.
VER-62983 Hadoop When hcatalogconnector schemas are created with custom_partitions enabled, poor performance has been observed when there are many (500+) partitions. By default, custom_partitions are disabled on hcatalogconnector schemas.
VER-61420 Data Removal - Delete, Purge, Partitioning Partition operations, such as move_partitions_to_table, must split storage containers that have both partitions that match the operations and partitions that do not. Version 9.1 introduced an inefficiency whereby such a split may separate a storage container into one more storage container than necessary. Note that any extraneous containers created this way will eventually be merged by the TM.
VER-61069 Execution Engine In very rare circumstances, if a vertica process crashes during shutdown, the remaining processes might hang indefinitely.
VER-48020 Hadoop Canceling a query that involves loading data from ORC or Parquet files can be slow if there are too many files involved in the load.

What's New in Vertica 10.1

Take a look at the Vertica 10.1 New Features Guide for a complete list of additions and changes introduced in this release.

Backup, Restore, Recovery, and Replication

Support for Object-Level Backup and Restore in Eon Mode

You can now perform object-level backup, restore, and replication in Eon Mode databases. You can back up individual schemas or tables, and restore or replicate individual tables or schemas from full or object-level backups.

See Creating Backups and Restoring Backups for information about object-level operations.

Support for Google Cloud Storage

Vertica now supports backup and restore of Enterprise Mode and Eon Mode databases on Google Cloud Platform (GCP) to Google Cloud Storage (GCS). Supported backup and restore tasks include:

Support for backup and restore to GCS is limited to databases on GCP. For example, you cannot backup a database with communal storage on AWS to a GCS location.

See Configuring Backups to and from Cloud Storage for details about configuring backups from Vertica on GCP to GCS. For a complete guide on Vertica and GCP, see Vertica on Google Cloud Platform.

New [CloudStorage] Section in Configuration Files

Configuration files have a new [CloudStorage] section that replaces the [S3] section. S3 configuration parameter names were changed from s3_parameter_name to cloud_storage_parameter_name. For databases that use the S3 protocol, the renamed parameters are functionally identical to the original S3-prefixed parameters.

Using S3-prefixed names generate a warning and deprecation message.

For details, see [CloudStorage] and Deprecated and Removed Functionality.

New Configuration File Parameters

vbr configuration files support new configuration parameters:

For details, see [CloudStorage] and [Transmission] Data Transmission .

New Sample VBR .ini File

A new sample vbr .ini file, backup_restore_cloud_storage.ini, provides guidance on creating a configuration file to back up or restore an Enterprise Mode or Eon Mode database to a supported cloud storage location.

The new sample configuration file supersedes backup/restore .ini files from earlier Vertica versions: backup_restore_S3.ini and eon_backup_restore.ini.

Database Management

Node Health Checks: Local Storage Locations

Automatic node health checks now check the following local storage locations for read-write access:

Data Types

Mixed Structs and Arrays in Parquet External Tables

Columns in the Parquet format can contain structs, represented in Vertica by the ROW type, and arrays, represented by the ARRAY type. For external tables and the Parquet format only, you can define a column for heterogeneous combinations of these two types: a struct containing array fields or an array of structs. These types can be nested up to the maximum nesting depth.

For details, see Reading Combinations of Structs and Arrays.

Complex Types Support in ORC Files

When using external tables with ORC data, you can now use the ROW data type to represent ORC structs. The syntax is the same as for Parquet files. With this change, the previous method of handling struct fields as expanded columns is deprecated.

You can now declare, but not query, columns for arrays and maps in external tables, allowing you to consume the column and use the rest of the ORC data.

For details, see Reading Complex Types from ORC Files.

Eon Mode

Depot Pinning Enhancements

New meta-functions support pinning projections to database depots:

Also, SET_DEPOT_PIN_POLICY_PARTITION now supports depot pinning partitions for specific projections.

Duplicating an Existing Subcluster

You can now create a duplicate of an existing subcluster. When you pass the new --like option to the admintools db_add_subcluster tool, it creates a subcluster that inherits most configuration settings from the source subcluster. This feature is useful if you want to scale your database for query throughput by creating more secondary subclusters. For details, see Duplicating a Subcluster.

Apache Kafka Integration

Encrypted Notifiers

You can now configure encryption for new and existing notifiers with the following parameters:

For details on these and other parameters, see CREATE NOTIFIER and ALTER NOTIFIER.

Spark Integration

Support for New Spark and Scala Versions

A new version of the Spark Connector supports Scala 2.12 with Spark versions 2.4.1 and 3.0. See Vertica Integration for Apache Spark in the supported platforms section for details.

Loading Data

Parquet Metadata Caching

When reading data in the Parquet format, Vertica now caches the metadata for each Parquet file during planning for use during execution. This caching reduces the number of reads and the network latency they add. If the file storage location charges for API calls, this caching can also reduce costs.

Vertica uses TEMP storage for the cache, and only if TEMP storage is on the local file system.

For details, see Loading Parquet Data.

JSON and Avro Parsers Support Native Arrays

You can now load one-dimensional arrays of primitive types from JSON and Avro data. You can continue to use flexible complex types to load arrays of any dimensionality. For details, see Loading JSON Data and Complex Data Types for favroparser.

Parquet Parser Supports Loose Schema Matching

By default, the Parquet parser uses strong schema matching. This means that the load must consume all columns in the Parquet data in the order they occur in the data. You can, instead, use loose schema matching, which allows you to select the columns you want and ignore the rest. Loose schema matching depends on the names of the columns in the data rather than their order, so the column names in your table must match those in the data. Types must match or be coercible. For details on how to use loose schema matching, see Loose Schema Matching on the PARQUET (Parser) reference page.

You can use loose schema matching for columns of primitive types and one-dimensional arrays of primitive types. For other complex types, use flexible complex types instead. See Using Flexible Complex Types.

Machine Learning

In 10.1 Vertica has added several enhancements to Machine Learning:

XGBoost

Vertica now provides robust support for XGBoost (Extreme Gradient Boosting), a popular supervised-learning algorithm used for regression or classification on large datasets. XGBoost relies on sequentially-built shallow decision trees to provide high accuracy with a low probability of overfitting the data and a highly scalable training method. The name of this tree boosting method is a direct reference to its ability to perform training very fast in a distributed computing environment.

In release 10.1, Vertica provides the ability to use the XGBoost algorithm for regression and classification of data in Vertica, by providing new and updated functions which support the XGBoost algorithm.

XGBoost training functions create XGBoost models, either an XGBoost classifier model or XGBoost regressor model. All actions for Vertica models are supported for XGBoost models, just as they are for Random Forest for example, including exporting and importing the model. The XGBoost algorithm and XGBoost functions and models are fully implemented within Vertica, and do not require the use of any outside tools.

Soundex

Vertica now supports Soundex, a widely-used phonetic approximation algorithm that turns English words into a 4-character code. Ideally, this code is shared by phonetically-similar words. It was patented in the 1920s and is present in some form in DB2, Ingres, MySQL, MS SQL Server, Oracle, PostgreSQL, and SQLite.

Soundex is useful for queries along the lines of "Find me people with names similar to Smith (e.g., Smythe)".

Management Console

Password Security Enhancements

You can now set additional requirements for MC user passwords and login security. These include new options for managing:

For details, see Managing MC User Passwords.

Self-service Password Resets

Users must now set security questions when logging in for the first time. They can then reset their passwords from the MC login screen with the Forgot Password function.

New Create New Database Cluster Wizard for Amazon Web Services

There is a new Management Console wizard to create an Eon Mode database cluster on Amazon Web Services (AWS). The wizard includes an intuitive design and tooltips to provide guidance when making selections.

To create a database cluster using the new workflow, select Create a New Vertica Database Cluster from the Management Console home page.

Instance Options When Provisioning a Subcluster in Eon Mode on AWS or GCP

When you are provisioning a subcluster on AWS or Google Cloud Platform (GCP), you can create a subcluster using any instance type, volume type, and volume size. Previously, you had to use the same instance type and data settings as the existing cluster instances. Using different instance types provides flexibility to create subclusters for specific workloads or user groups.

Because a cluster can have different instance types with different volume allocations, you can no longer drop or create a database from Database Cluster View on AWS or GCP. This prevents Vertica from using incorrect instance types when creating the database. You have the option to terminate the cluster on Database Cluster View, and you can create a database from the Management Console home page.

See Managing Workload Resources in an Eon Mode Database for details about optimizing subclusters by workload.

New Option to Load Test data with Amazon Web Services (AWS) CloudFormation Template (CFT)

There is new option to load Vertica TestDrive data when you are provisioning a 4 node AWS cluster using CFT. Loading TestDrive data lets you preview how Vertica operates in production. By default, this option is set to No.

See CloudFormation Template (CFT) Overview and Launch MC and AWS Resources with a CloudFormation Template for information about using CFTs with Vertica.

Query Management

New Alias for IGNORECONST Hint

Directed queries now support :v as an alias of IGNORECONST.

SDK Updates

Scalar Functions Can Return Multiple Values

A user-defined scalar function (UDSF) can now return more than one value. Multiple values, of scalar types, are returned in a ROW. For details on how to work with multiple values, see Scalar Functions (UDSFs). For an example, see C++ Example: Returning Multiple Values.

Security and Authentication

Default Password Hashing Algorithm

For details, see Hash Authentication Parameters.

New PROFILE Parameters

For details, see Profiles.

Assign Pre-hashed Passwords to Users

CREATE USER and ALTER USER now accept pre-hashed passwords. For details, see Setting and Changing User Passwords

CA Bundles

You can now create Certificate Authority (CA) bundles. These contain root CA certificates that can be used to validate connections. For details, see Managing CA Bundles.

SQL Functions and Statements

IMPLODE Function for Arrays

The IMPLODE function takes a column of values and returns an array containing them. It is the inverse of EXPLODE. The two functions can be used together to explode an array, filter values from it, and then re-assemble the array.

For details, see Arrays and Sets (Collections).

EXPLODE Function Can Expand Multiple Arrays

The EXPLODE function can now expand more than one array in a single call, removing the need to use subqueries to explode multiple arrays. The function can now also return columns in any order; the array column(s) need not be first. A new parameter specifies the number of arrays to explode; all non-array columns and all array columns after the specified number are passed through unmodified.

CREATE OR REPLACE VIEW

CREATE privileges on a view's schema and DROP privileges on the view are now sufficient for replacing an existing view with CREATE OR REPLACE VIEW. Previously, only the view owner could use CREATE OR REPLACE VIEW while specifying an existing view.

For details on these privileges, see Creating Views.

Supported Data Types

Avro Logical Types for favroparser and KafkaAvroParser

Vertica supports Avro logical types. Use Avro logical types to annotate primitive or complex types to create a derived type. Below is a list of the logical types that Vertica supports:

For details, see Logical Data Types for favroparser.

Supported Platforms

Microsoft Edge is a supported web browser for Vertica Management Console.

Tables

Higher Limit on Table Column Definitions

Vertica tables now support up to 9800 column definitions.

Expanded Support for SET USING/DEFAULT Expressions

DEFAULT and SET USING expressions in flattened tables now support correlated subqueries, and can combine these with expressions that reference values in the current table. For details, see Expressions with correlated subqueries.

User-Defined Extensions

Higher Limit on Function Arguments

User-defined functions, such as transform and scalar functions, now support up to 9800 arguments.

What's Deprecated in Vertica 10.1

This section describes the two phases Vertica follows to retire Vertica functionality:

Deprecated

The following Vertica functionality was deprecated and will be retired in future versions:

Release Functionality Notes
10.1 Reading structs from ORC files as expanded columns Instead, define as ROW type (see Reading Complex Types from ORC Files).
MD5 hashing algorithm for user passwords Instead, use SHA-512 (see Hash Authentication Parameters).

S3 vbr configuration parameters:

  • s3_backup_file_system_path
  • s3_backup_path
  • s3_encrypt_transport
  • s3_concurrency_backup
  • s3_concurrency_restore
  • s3_encrypt_at_rest
  • s3_sse_kms_key_id

Superseded by new vbr configuration parameters that accept values for cloud storage locations:

  • cloud_storage_backup_file_system_path
  • cloud_storage_backup_path
  • cloud_storage_encrypt_transport
  • cloud_storage_concurrency_backup
  • cloud_storage_concurrency_restore
  • cloud_storage_encrypt_at_rest
  • cloud_storage_sse_kms_key_id

If a vbr configuration file contains an s3-prefixed parameter, vbr returns a warning.

ARRAY_CONTAINS function Instead, use CONTAINS.
flatten_complex_type_nulls parameter to the ORC and Parquet parsers  
Vertica JDBC API com.vertica.jdbc.kv package

Affects the following interfaces:

  • VerticaRoutableConnection
  • VerticaRoutableExecutor
  • VGet
  • VHash

Removed

The following functionality is no longer accessible as of the releases noted:

Release Functionality Notes
10.1

Eon Mode meta-functions:

  • SET_DEPOT_PIN_POLICY
  • CLEAR_DEPOT_PIN_POLICY

Superseded by:

  • SET_DEPOT_PIN_POLICY_PARTITION
  • SET_DEPOT_PIN_POLICY_TABLE
  • CLEAR_DEPOT_PIN_POLICY_PARTITION
  • CLEAR_DEPOT_PIN_POLICY_TABLE
Reading structs from Parquet files as expanded columns Superseded by Creating Tables Using Structs.
Parquet parser skip_strong_schema_match parameter Superseded by Using Flexible Complex Types.

Array-specific functions:

  • array_min
  • array_max
  • array_sum
  • array_avg
Superseded by functions that operate on collections, including arrays:
  • APPLY_MIN
  • APPLY_MAX
  • APPLY_SUM
  • APPLY_AVG
HiveMetadataCacheSizeMB configuration parameter Superseded by ParquetMetadataCacheSizeMB .

For more information see Deprecated and Retired Functionality in the Vertica documentation.

Vertica 10.1.0-3: Resolved Issues

Release Date: 3/17/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-76041 EON, Tuple Mover Commit resync is now disabled for background Tuple Mover jobs.
VER-76046 Execution Engine Calls to export_objects sometimes allocated considerable memory while user acesss privileges to the object were repeatedly checked. The accumulated memory was not freed until export_objects returned, which sometimes caused the database to go down with an out-of-memory error. This issue has been resolved: now memory is freed more promptly so it does not excessively accumulate.
VER-76059 Tuple Mover Occasionally, the Tuple Mover dequeued DVMERGEOUT and MERGEOUT requests simultaneously and executed only the DVMERGEOUT requests, leaving the MERGEOUT requests pending indefinitely. This issue has been resolved: now, after completing execution of any DVMERGEOUT job, the Tuple Mover always looks for outstanding MERGEOUT requests and queues them for execution.
VER-76061 Monitoring The output of get_expected_recovery_epoch is no longer truncated in clusters with more than 20 nodes.
VER-76136 Cloud - Azure Loads on large Azure clusters no longer fail due to storage service throttling.
VER-76137 Backup/DR Vbr now supports restores from MinIO S3 locations containing special characters.
VER-76190 Depot System tables continued to report that a file existed in the depot after it was evicted, which caused queries on that file to return "File not found" errors. This issue has been resolved.
VER-76252 Data Export Parquet files exported by Vertica now properly display date values when imported to Impala.
VER-76393 Data Collector, EON DC table files are now appropriately cleared when dc sync is disabled.

Vertica 10.1.0-2: Resolved Issues

Release Date: 2/23/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-75827 Hadoop In some configurations, the Vertica server process would crash while logging HDFS events. This issue has been resolved.
VER-75901 Tuple Mover When Mergeout Cache is enabled, the dc_mergeout_requests system table now contains valid transaction ids instead of zero.
VER-75913 Tuple Mover The Tuple Mover logged a large number of PURGE requests on a projection while another MERGEOUT job was running on the same projection. This issue has been resolved.
VER-75973 Hadoop Fixed a bug in Parquet Predicate Pushdown where sometimes the correct rowgroups were not pruned from the Parquet file based on predicates if the file was on HDFS.

Vertica 10.1.0-1: Resolved Issues

Release Date: 2/9/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER- 75904 Security Vertica no longer takes a catalog lock during authentication, after the user's password security algorithm has been changed from MD5 to SHA512. This is due to removing the updating of the user's salt, which is not used for MD5 hash authentication.

Vertica 10.1.0: Resolved Issues

Release Date: 2/1/2021

This hotfix addresses the issues below.

Issue

Component

Description

VER-36888 Admin Tools stop_db no longer returns early and now returns the proper return code.
VER-45474 Optimizer When a node is down, DELETE and UPDATE queries now use optimized query plans that account for the down node.
VER-68356 Security LDAPLink now properly trims whitespace from LDAPLinkSearchBase.
VER-70760 Optimizer Queries with subqueries on tables with TopK projections sometimes returned with an error. The issue has been resolved.
VER-71074 Security Previously, CREATE OR REPLACE VIEW could only replace an existing view if the current user was the existing view's owner. Now, DROP privilege is sufficient to replace the view (in addition to CREATE on the SCHEMA, as before).
VER-72232 ComplexTypes As a fix, the QUERY_EVENTS system table will no longer make the recommendation to collect stats on ARRAY & SET columns.
VER-72394 Execution Engine This issue would occur when the root operator would treat non-DML queries as DML plans. The way Vertica collects the processed row-count statistics differs between these two types, so the results were incorrect. Vertica now correctly detects whether a query represents a DML plan.
VER-72735 Security LDAPLink now checks for circular role/group assignment, assigns the first one returned (skipping the rest of the circular relationship), and then logs the error in DC_LDAP_LINK_EVENTS.
VER-72850 Optimizer Incorrect usage of meta-function EXPORT_TABLES in the SELECT statement of EXPORT TO VERTICA caused the database to crash. This issue has been resolved: incorrect usage of meta-functions now returns with an error.
VER-72978 Depot Resizing the database depot with alter_location_size were propagated sequentially to all nodes, which adversely affected performance in a database with many nodes. This issue has been resolved: depot size changes are now propagated to all nodes in parallel (simultaneously).
VER-73110 Backup/DR Previously, when migrate_enterprise_to_eon found projections that were Inconsistent with cluster segmentation, it wrote to the migration log file an incorrect anchor table name; it also provided only one projection, <projection-name>_b0. This issue has been resolved: now the log file error log file lists the names of all tables with problematic projections. You can use these names as arguments to meta-function REBALANCE_TABLE.
VER-73382 Execution Engine Queries sometimes threw a Sort Order Violation error when join output that was sorted like the target projection was consumed by a single Analytic function that only partially sorted the data as required by the target projection. This issue has been resolved.
VER-73811 Execution Engine Expressions of the form <expression> IN (<list of constants>) were not handled correctly in cases where <expression> was of type LONG VARCHAR or LONG VARBINARY. If the IN list was long, query execution could be slow and use excessive memory. This problem has been resolved.
VER-73841 Admin Tools Adding nodes on large, busy databases now runs fewer queries and takes less time.
VER-73881 Scrutinize Scrutinize now collects a memory report log.
VER-73937 Execution Engine Under some circumstances, the size of the METADATA resource pool was calculated incorrectly. This issue has been resolved.
VER-74043 Admin Tools Relaxed the checks of user storage locations during revive Eon database.
VER-74116 Optimizer Analytic functions triggered an Optimizer error when used with a LIMIT OVER clause. This issue has been resolved.
VER-74130 Depot, Tuple Mover Calls to meta-function enforce_object_storage_policy() on an Eon-mode database wrote a large number of errors to the Vertica log file. This issue has been resolved: Vertica now returns with a single error message that the meta-function is not supported in Eon mode.
VER-74182 Admin Tools The performance of db_remove_subcluster has been improved for subclusters with several nodes.
VER-74300 Backup/DR migrate_enterprise_to_eon migrated grants on external procedures even though the procedures themselves were excluded from migration. This inconsistency caused the migrated database to crash and further attempts at startup to fail. This issue has been resolved: the migration now excludes privileges that are granted on external procedures, and also checks that all migrated objects have consistent dependencies.
VER-74350 Execution Engine Adding support for tables with more than 1600 columns adversely affected performance of some operations. This issue has been resolved.
VER-74355 ComplexTypes ALTER TABLE...ALTER COLUMN did not correctly handle attempts to create multi-dimensional arrays, reporting the dimensionality incorrectly. Multi-dimensional arrays are not supported for internal tables, and Vertica now treats this as an error.
VER-74512 Depot System table DEPOT_PIN_POLICIES was erroneously populated with enterprise storage policies. This issue has been resolved: now, in Enterprise mode, DEPOT_PIN_POLICIES is always empty.
VER-74521 Optimizer When a LIMIT OVER query executed on a multi-node database, the data was always sent back to the initiator which applied the LIMIT across all nodes. This interim step was unnecessary for INSERT...SELECT queries, which read data from the same node where it is stored. This issue is now resolved: Vertica no longer transfers data to the initiator for INSERT...SELECT queries, and thus avoids the extra overhead previously incurred.
VER-74575 Optimizer In certain outer join operations that included a LIMIT OVER (PARTITION BY) clause, Vertica applied GUA (guaranteed uniqueness) optimization before populating the Top-K attribute, which pruned the inner view and caused an internal error. This issue has been resolved: now the optimizer defers GUA optimization until after it populates all query-related attributes.
VER-74576 Execution Engine When computing COUNT window aggregates over Fixed type columns that included UUIDs, the aggregate incremented the count on reading a row from the Fixed type column. It then also hit an assertion failure intended for aggregating over invalid types. This issue has been resolved: the assertion failure no longer occurs in Fixed type window aggregates.
VER-74588 Hadoop Queries to external tables with Hive partition columns could crash Vertica if a predicate on a partition column throws an error. This issue has been fixed.
VER-74616 Execution Engine Enabling inter-node encryption adversely affected query performance across a variety of workloads. In many cases, this issue has been addressed with improved performance.
VER-74707 Tuple Mover Enabling both mergeout caching (deprecated) and reflexive mergeout adversely affected performance on a new or restarted database. This issue has been resolved: when reflexive mergeout is enabled, mergeout caching is automatically disabled.
VER-74783 DDL - Table ALTER TABLE...OWNER TO threw an error when the target table was already owned by the specified user. This issue has been resolved: it now returns with NOTICE instead of ERROR.
VER-74795 Optimizer Previously, the Vertica optimizer ignored a PROJS hint that specified a live aggregate projection (LAP), and returned a warning. This issue has been resolved: the optimizer now supports PROJS hints that specify a LAP and uses the projection if it is feasible for that query.
VER-74918 Security LDAPLink now properly supports instances where the number of members returned for an attribute exceeds 1500.
VER-75051 Execution Engine An internal memory error sometimes caused simple CASE statements of this format to fail:

CASE expression
WHEN comparand THEN result
...
[ ELSE result ]
END

This issue has been resolved.
VER-75105 Execution Engine Passing a volatile expression such as RANDOM() as an argument to an inline function such as CORR returned incorrect output if the argument appeared multiple times in the function body. This issue has been resolved: now, when you pass a volatile expression as an argument multiple times to an inline function, Vertica returns an error message.
VER-75196 Execution Engine Calling ALTER TABLE REORGANIZE immediately after setting group partitioning with ALTER TABLE sometimes caused Vertica to crash. This issue has been resolved.
VER-75265 Execution Engine When a subquery was used in an expression and implied a correlated join with relations in the parent query, and also aggregated output, it sometimes returned multiple rows for each matched join key and returned unexpected results. This issue has been resolved.

Known issues Vertica 10.1.0

Updated: 2/1/21

Vertica makes every attempt to provide you with an up-to-date list of significant known issues in each release. We will update this list as we resolve issues and as we learn of new issues.

Issue

Component

Description

VER-76269 Optimizer Executing EXPLAIN COPY on a new table fails.
VER-75057 ComplexTypes In an external table, if a struct contains an array field and the struct is null, the whole struct (ROW) is treated as null in Vertica. This behavior differs from structs without arrays, where a null struct is treated as a ROW where all fields are null.

Legal Notices

Warranty

The only warranties for products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. Micro Focus shall not be liable for technical or editorial errors or omissions contained herein.

The information contained herein is subject to change without notice.

Restricted Rights Legend

Confidential computer software. Valid license from Micro Focus required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license.

Copyright Notice

© Copyright 2006 - 2021 Micro Focus, Inc.

Trademark Notices

Adobe® is a trademark of Adobe Systems Incorporated.

Microsoft® and Windows® are U.S. registered trademarks of Microsoft Corporation.

UNIX® is a registered trademark of The Open Group.