Release Notes

Vertica
Software Version: 9.2.x

 

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: 6/17/2019

About Vertica Release Notes

What's New in Vertica 9.2.1

What's Deprecated in Vertica 9.2.1

Vertica 9.2.1-2: Resolved Issues

Vertica 9.2.1-1: Resolved Issues

Vertica 9.2.1: Resolved Issues

Vertica 9.2.1: Known Issues

What's New in Vertica 9.2

What's Deprecated in Vertica 9.2

Vertica 9.2.0-7: Resolved Issues

Vertica 9.2.0-6: Resolved Issues

Vertica 9.2.0-5: Resolved Issues

Vertica 9.2.0-4: Resolved Issues

Vertica 9.2.0-3: Resolved Issues

Vertica 9.2.0-2: Resolved Issues

Vertica 9.2.0-1: Resolved Issues

Vertica 9.2.0: Resolved Issues

Vertica 9.2.0: Known Issues

About Vertica Release Notes

The Release Notes contain the latest information on new features, changes, fixes, and known issues in Vertica 9.2.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/vetica/community-edition.

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

Downloading Hotfixes

Hotfixes are available to Premium Edition customers only. Each software package on the https://support.microfocus.com/downloads/swgrp.html site is labeled with its latest hotfix version.

What's New in Vertica 9.2.1

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

Performance Improvements

Local Caching of Storage Containers

You can now configure Vertica so it can cache on local disk the data of S3 storage containers. This is especially useful for operations that require the Execution Engine to reopen and read the same storage container multiple times. The extra overhead incurred by multiple reads can significantly impact performance and usage costs.

Backup, Restore, Recovery, and Replication

Object Restore in Eon Mode

You can now restore objects (schemas or tables) in Eon Mode from a full backup. (In Enterprise Mode you can restore objects from either a full or object backup.)

Eon Mode

Support for Bundle Metadata Storage

Vertica internally stores physical table data in bundles, together with metadata on the bundle contents. The query optimizer uses bundle metadata to look up and fetch the data it needs for a given query.

With release 9.2.1, Vertica now supports storing bundle metadata in the database catalog. This is especially beneficial in Eon mode: instead of fetching this metadata from remote (S3) storage, the optimizer can find it in the local catalog. By minimizing S3 reads, query planning and overall execution can be significantly faster.

S3 Storage of Temporary Data

Eon users are likely to have limited local disk space, which might be insufficient to handle the large quantities of temporary data that some DML operations are liable to generate. You can now create storage locations on S3 to store temporary data. For details, see S3 Storage of Temporary Data.

UseDepotForWrites Configuration Parameter

Configuration parameter UseDepotForWrites can now be set at database and session levels.

New Default Value for Depot Size

The default size of the depot for new Eon Mode databases is now 60% of disk space in the file system that stores the depot. In earlier releases, this value was 80%. Existing databases are unaffected by this change.

Eon Mode-related System Table Changes

The following system tables used by Eon Mode have changed:

These columns supersede the TIME column, which was removed.

SQL Functions and Statements

Extended COPY support for EXPLAIN

In previous releases, EXPLAIN COPY returned a query plan only in Graphviz DOT notation. COPY statements now support all EXPLAIN output options, excluding ANNOTATED:

Unqualified, EXPLAIN generates a text query plan together with Graphviz DOT notation.

Per-Connection Override of Default TLS Settings

The CONNECT statement has a new, optional parameter, TLSMODE, that you can use to override the global TLS enforcement level set in ImportExportTLSMode. For more information, see Security Parameters.

Altering Spread Options

You can now alter the spread daemon 's Timeout Token setting using the SET_SPREAD_OPTION function. You may want to change this option when Vertica in Azure.

Statistics

Partition-Based Statistics

Support for collecting data statistics is now extended to include partition-based statistics, with these three new functions:

Apache Hadoop Integration

HadoopConfDir Can Be Set at Session Level

The HadoopConfDir configuration parameter specifies a directory path to search for HDFS configuration files. Each set of files describes a Hadoop cluster. Vertica uses the information in these files to interact with HDFS. Usually you set this parameter to point to one or more stable Hadoop clusters that your database needs access to.

Sometimes you need to be able to change this path on the fly, for example to point to a test environment. Changing the value globally would affect all Vertica connections to Hadoop clusters and potentially break existing queries or external-table definitions. You can now set this parameter at the session level to avoid this problem.

Vertica on the Cloud

New Support for AWS Instance Types

As of Vertica 9.2.1, Vertica supports the following Amazon Web Service (AWS) instance types:

Flex Tables

Setting FlexTableRawSize on a Session

As of Vertica 9.2.1, you can set the FlexTableRawSize configuration parameter at both the database and session levels. In previous releases, you could only set the FlexTableRawSize configuration parameter at the database level.

Security and Authentication

Improved TLS Support for Import/Export

COPY FROM VERTICA and EXPORT TO VERTICA can now require encryption of plan metadata during transmission. Previously, when making the connection using CONNECT, Vertica attempted to use TLS and fell back to plaintext for metadata. This is still the default behavior, but you can now require encryption and, optionally, require certificate validation by setting the ImportExportTLSMode configuration parameter. For more information, see Configuring Connection Security in the Administrator's Guide.

What's New in Vertica 9.2

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

Upgrade and Installation

Identifying and Removing Unsupported Projections

Pre-join and range segmentation projections are no longer supported. If a table's only superprojection is one of these projection types, the projection is also regarded as unsafe.

Before upgrading to any Vertica release higher than 9.0.x, 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.

Download and run the pre-upgrade script

Vertica provides a pre-upgrade script that analyzes your current database and identifies unsupported projections. You can download this script and obtain detailed information on its usage at this location:

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

Eon Mode

Version 9.2 introduces the following new Eon Mode features.

Branching Eon Mode Databases

You can now create branches of your Eon Mode database. A branch is a snapshot of your database that you revive like you would a regular Eon Mode database. Usually, you revive a branch on a new cluster while your master database continues to run. Changes you make to a branch do not affect the master database, and changes to the master do not affect branches.

Currently, branches are data-immutable: you cannot perform an action on a branch that changes the underlying storage containers. The master database and branches share the same storage containers. Therefore, creating a branch is fast and does not significantly increase the size of your communal data storage because a branch does not need its own copy of the data.

Branches are useful for:

See Branching an Eon Mode Database for more information.

Changes to the NODE_SUBSCRIPTIONS_TABLE System Table

As of Vertica 9.2., the NODE_SUBSCRIPTION_TABLE no longer has the following fields:

In addition, the NODE_SUBSCRIPTION_TABLE contains a new field creator_tid, which is the transaction ID of the transaction that created the subscription.

Added Information about Upload Tasks

The DEPOT_UPLOADS system table includes information about currently running and queued uploader tasks.

Performance Improvements

Faster Refresh of Tables with Multiple Live Aggregate ProjectionsFaster Refresh of Tables with Multiple Live Aggregate Projections

As of Vertica 9.2, the optimizer applies a refresh operation on each table Live Aggregate Projection as a separate transaction, and applies the fast plan to each LAP. This reduces the total time required to refresh tables with multiple LAPs.

Note: This enhancements applies only to live aggregate projections.

Refactoring and Resizing the Database Catalog

Vertica 9.2 significantly reduces how much memory the database catalog consumes. It does this by consolidating the amount of metadata it maintains to track ROS storage containers. In order to achieve these memory savings, the upgrade program refactors catalogs across the cluster with the new design.

Management Console

Create and Revive: Select Vertica Version and Operating System

When you create or revive a Vertica cluster using Management Console, you can now select from a list of Vertica database versions with a Linux operating system.

During a revive of your database, when you select a Vertica version that is higher than the version of the original database in the communal storage, Vertica upgrades your database to match the Vertica version you selected. This upgrade may cause the database revive to take longer. To bypass this upgrade, select the Vertica version of your original database.

Note: After your Vertica database has been upgraded, you cannot downgrade your database later.

Encrypt EBS Volumes

Instance types c4, r4, and m4 are the only instance types that allow you to encrypt EBS volumes.

Provision a Vertica Cluster in a Different Subnet and Security Group

By default, Vertica creates your cluster in the same subnet as your Management Console instance. If you want to manage all Vertica clusters in the same VPC, you can provision your Vertica database in a different subnet than the Management Console instance. To do so, on the AWS Credentials page, select Show Advanced Options and enter a value in the Subnet field.

Disable MC Connections that Use TLS 1.1 and Earlier

By default, Management Console allows connections via web browsers using TLS 1.0, TLS 1.1, and TLS 1.2. Vertica 9.2 introduces a Management Console setting that allows you to disable connections from browsers using TLS 1.0 and 1.1.

Add Tags to Instances

When you add one or more EC2 instances to scale up your cluster, you can enter tag values for each instance. By default, Management Console pre-fills with the tag values for the first existing instance. You can accept these defaults, or create new tag values. Tagging instances is optional.

Loading Data

External Tables Support Google Cloud Storage

You can now define an external table to read data in Google Cloud Storage (GCS) using the gs URL scheme. You must enable S3 compatibility mode in GCS. For more information, see Loading from Google Cloud Storage.

Connection Load Balancing Policies

Vertica 9.2 introduces connection load balancing policies to give you more control over how client connections are balanced across your database. This feature lets you create rules that designate groups of nodes to handle client connections from specific IP address ranges.

This feature is useful for:

See About Connection Load Balancing Policies for more information.

Machine Learning

Machine Learning functions were enhanced as follows:

The following evaluation functions now support classification algorithms with predict functions of type VARCHAR:

CROSS_VALIDATE was updated with new algorithm arguments and new settings for parameter cv_metrics.

RF_CLASSIFIER response-column argument supports all numeric data types.

The following functions now support Boolean values for predictor and response arguments:

Table Data Management

Refreshing SET USING Columns in Live Aggregate Projections

You can now call REFRESH_COLUMN REBUILD on SET USING columns that are specified in a live aggregate projection.

Database Management

Reflexive Mergeout

In past releases, the Tuple Mover typically performed mergeout to consolidate ROS containers on two occasions:

This release introduces a reflexive approach to mergeout: the Tuple Mover continuously monitors DML activities such as COPY and table partitioning. Instead of waiting until the MergeOutInterval-specified time span elapses, it executes mergeout as needed, giving special attention to projections that are subject to frequent DML activity. This approach can help avoid performance degradation and ROS pushback.

For details, see Mergeout in the Administrator's Guide.

SQL Functions and Statements

Zstandard Compression Support

CREATE TABLE, CREATE PROJECTION, and COPY now support Zstandard compression. For details, see Encoding Types.

ALTER TABLE Enhancements

ALTER TABLE supports two new options:

CREATE SEQUENCE Enhancements

CREATE SEQUENCE now supports the option IF NOT EXISTS.

New Flex Table Function Parameters

The following flex table functions have new parameters:

MAPITEMS: max_key_length and max_value_length

MAPKEYS: max_key_length

MAPKEYSINFO: max_key_length

MAPVALUES: max_value_length

Apache Hadoop Integration

Structs in External Tables

Vertica now supports reading structs in ORC and Parquet files when defining external tables. Structs are expanded into multiple columns. For example, if a struct in the data has three fields, define three columns in Vertica to hold those values. For more information, see Using Structs.

Output Permissions for Exported Data

EXPORT TO PARQUET supports two new parameters, fileMode and dirMode, to specify Unix-style permissions for the output files and directories. These parameters are valid only when writing to HDFS.

New Functions to Test Configuration

The KERBEROS_HDFS_CONFIG_CHECK function has been replaced with several functions to allow finer-grained testing. EXTERNAL_CONFIG_CHECK, calls all of the others, for those who prefer a single test with complete output. See Vertica product documentation for a list functions called by EXTERNAL_CONFIG_CHECK.

User-Defined Extensions

Support for Multi-Level Library Dependencies

The DEPENDS clause of CREATE LIBRARY and ALTER LIBRARY can now specify multi-level libraries or packages as dependencies. Vertica follows the library path to include all subdirectories of that library. For example, the following CREATE LIBRARY statement enables UDx library mylib to import multiple Python modules from subdirectories of package site‑packages:

=> CREATE LIBRARY mylib AS '/path/to/python_udx' DEPENDS '/path/to/python/site-packages' LANGUAGE 'Python';

Python UDL support for Filters and Parsers

Vertica user defined load functions now support filters and parsers created in Python.

System Tables

VIEW_TABLES

VIEW_TABLES shows details about view-related dependencies, including the table that references a view, its schema, and owner. You can query this table to examine these dependencies before you alter or drop an object.

Voltage SecureData Integration

In Vertica 9.2, the way you configure the Voltage SecureData integration functions has changed. Previously, all of the configuration settings could be set using the VoltageSecureConfigure function. In 9.2.x, the options that should be set globally for all users have been moved to a new function named VoltageSecureConfigureGlobal. In order to use the SecureData integration, you must set the SecureData Appliance's policy URL using this function. You can no longer use a session parameter to store the policy URL, or other options available through the VoltageSecureConfigureGlobal function.

Note: Until you use the VoltageSecureConfigureGlobal function to set the policy URL for the Voltage SecureData integration, any query that uses the integration will return an error.

See Configuring Access to SecureData for more information.

What's Deprecated in Vertica 9.2.1

The following Vertica functionality was deprecated in this release. This functionality will be retired in a future Vertica version:

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

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

Vertica 9.2.1-2: Resolved Issues

Release Date: 6/17/2019

This hotfix addresses the issues below.

Issue

Component

Description

VER-67632 Execution Engine Some queries with complex predicates ignored cancel attempts, manual or via the runtime cap, and continued to run for a long time. Cancel attempts themselves also caused the query to run longer than it would have otherwise. This issue has been fixed.
VER-67593 Client Drivers - ODBC, Data Removal - Delete, Purge, Partitioning Queries use the query epoch (current epoch -1) to determine which storage containers to use. A query epoch is set when the query is launched, and is compared with storage container epochs during the local planning stage. Occasionally, a swap partition operation was committed in the interval between query launch and local planning. In this case, the epoch of the post-commit storage containers was higher than the query epoch, so the query could not use them, and it returned empty results. This problem was resolved by making SWAP_PARTITIONS_BETWEEN_TABLES atomic.

Vertica 9.2.1-1: Resolved Issues

Release Date: 5/23/2019

This hotfix addresses the issues below.

Issue

Component

Description

VER-67038 Build and Release A new error message in Export To Parquet did not have an error code. This issue has been fixed.
VER-67339 Backup/DR Dropping the user that owns an object involved in a replicate or restore operation during that replicate or restore operation no longer causes the nodes involved in the operation to fail.
VER-67362 Data Export, Hadoop Dictionary size is now accounted for when estimating row group sizes during data export to parquet format. As a result row groups can no longer exceed their specified size.
VER-67400 UDX Flex table views now properly show UTF-8 encoded multi-byte characters.
VER-67408 Client Drivers - Installation Package Incorrect permissions on ODBC driver message files potentially prevented the driver from retrieving error messages in some situations, such as the user not belonging to the same group as the message files' owner. This problem has been corrected.

VER-67409

 

DDL - Table The database statistics tool SELECT ANALYZE_STATISTICS no longer acquires a GCL-X lock when running against local temp tables.
VER-67410 Optimizer Vertica now supports subqueries in the ON clause of a query.
VER-67411 Execution Engine On rare occasions, a query that exceeded its runtime cap automatically restarted instead of reporting the timeout error. This issue has been fixed.
VER-67412 Installation Program If a standby node had been replaced in past with failed nodes with deprecated projections, the identify_unsupported_projections.sh script could not reflect on the standby node. When upgrading to 9.1 or later, the standby might may not start due to failure of the 'U_DeprecateNonIdenticallySortedBuddies' and/or 'U_DeprecatePrejoinRangeSegProjs' task. This issue has been resolved.
VER-67414 Optimizer Queries that perform an inner join and group the results now return consistent results.
VER-67425 Hadoop Vertica can now read tinyint values in parquet files generated by Apache Spark.
VER-67459 Execution Engine The run_index_tool meta-function can now be cancelled. Together with projection name filtering and using multiple threads, dbadmins should be less reluctant to run this potentially long-running tool.

Vertica9.2.1: Resolved Issues

Release Date: 5/8/2019

To see a complete list of additions and changes introduced in this release, refer to the Vertica 9.2.1 New Features Guide.

Issue

Component

Description

VER-36308 UI - Management Console The Vertica MC linux service : /etc/init.d/vertica-consoled status would always display status OK without showing the actual MC state (Running or Stopped). This issue has been resolved.
VER-36987 Optimizer - Statistics and Histogram If DROP_STATISTICS specified a table and the argument HISTOGRAMS, the function dropped all statistics for the table, including row count. This issue has been resolved: the function now drops only the histograms for that table.
VER-38419 Optimizer - GrpBy and Pre Pushdown Aggregate functions can now be pushed down into UNION-ALL subqueries, if it is safe to do so.
VER-43373 DDL - Table CREATE TABLE LIKE...INCLUDING PROJECTIONS generated projections for the cloned table that aliased the new anchor table table with the name of the original anchor table. Projections for tables that are created with CREATE TABLE LIKE no longer use this aliasing mechanism.
VER-55257 Client Drivers - ODBC Issuing a query which returns a large result set and closing the statement before retrieving all of its rows can result in the following error when attempting subsequent operations with the statement: "An error occurred during query preparation: Multiple commands cannot be active on the same connection. Consider increasing ResultBufferSize or fetching all results before initiating another command."
VER-56790 Client Drivers - JDBC, Client Drivers - ODBC Previously, certain server errors were not correctly propagated to the application, which resulted in the correct native error code and the original server error message not being seen by the application when it called SQLGetDiagRec(). This problem has been corrected.
VER-58419 UI - Management Console 'Export Design' button wasn't getting enabled after design was completed. This issue has been fixed.
VER-59114 Client Drivers - VSQL Sometimes vsql was not able to locate a Kerberos Key Distribution Center (KDC) that was identified only via specially-named DNS records. This issue has been been fixed.
VER-62414 Hadoop Previously, Vertica sometimes generated Parquet files with too many rowgroups in a file. Loading such files using external Parquet tools caused a performance degradation or out of memory errors. This has been fixed.
VER-63297 Client Drivers - ADO Previously, if an integer column value was split across multiple reads by the ADO.NET driver, it was not correctly captured. This has been corrected.
VER-63389 Data Export, Hadoop Previously, Vertica sometimes generated Parquet files with too many rowgroups in a file. Loading such files using Vertica caused a performance degradation. This has been fixed.
VER-63769 Data Export, Error Handling, Hadoop Previously, Parquet and ORC external tables would throw an error if the load path contained an empty folder. A new parser option 'allow_no_match' when set to 'true' now returns an empty result set instead of an error.
VER-64592 DDL - Table Vertica was unable to add a foreign key constraint to a table if the table was referenced by a key in another table that used the same constraint name. Now, Vertica checks for constraint name conflicts only within the same table.
VER-64912 Backup/DR Altering the owner of a replicated schema with the CASCADE clause caused a node crash on the target database. This issue has been fixed.
VER-64941 DDL - Table Previously, ALTER TABLE...ADD COLUMN required you to explicitly list all existing projections that you wished to update with the new column. You can now update all projections by qualifying ADD COLUMN with the new option ALL PROJECTIONS.
VER-65065 Kafka Integration Vertica now properly supports TLS certificate chains for use with Kafka Scheduler and UDx.
VER-65082 Execution Engine Under certain workloads, glibc can accumulate a significant amount of free memory in its allocation arenas, free memory that nevertheless consumes physical memory as indicated by RSS usage. Vertica now detects and automatically consolidates and returns much of that free memory back to the operating system.
VER-65137 Data load / COPY Rejected data and exceptions files during a COPY statement are created with file permissions 666.
VER-65154 License Due to a miscalculation, the license auditor running in sampling mode showed an incorrectly large standard error. The standard error is the number that appears after the +/- sign on the data size line in license compliance messages. This issue has been fixed.
VER-65249 UI - Management Console In the MC, the license page would periodically not show all the licenses that the database may contain. This issue has now been resolved.
VER-65257 Hadoop Export to Parquet previously crashed when the export included a combination of Select statements. This issue has been fixed.
VER-65333 Nimbus Subscriptions After upgrading to 9.2, the Eon database crashed after running rebalance_shards. This issue has been fixed.
VER-65338 UI - Management Console The MC license page would periodically not show the graph representing the license usage. This issue has now been fixed.
VER-65419 Security Setting a row-based access policy on an underlying table of a view denied the user access to the view. This issue is fixed so the user can see the view as intended.
VER-65484 Backup/DR Removed the redundant "Missing info files: skipping..." warnings in the vbr log when performing backup tasks.
VER-65501 Backup/DR Running the remove task using a vbr config file with an incorrect "snapshotName" removed entries from the backup manifest file. This problem has been resolved.
VER-65604 Security Passwords with a semicolon are no longer printed in the log.
VER-65718 Tuple Mover If you upgraded a database to version 9.2.0 from any earlier release and configuration parameter MergeoutCache was set to 1 (enabled), the upgraded database was unable to start. Now, if MergeoutCache is set to 1 in the earlier release, the upgraded database ignores this setting. To enable MergeoutCache, you must also set EnableReflexiveMergeout to 0 (disabled).
VER-65776 UI - Management Console While logged into the MC as a non-admin user, the table in the Load page's "Continuous tab" would periodically not show all the current running Vertica schedulers that are in use. This issue has been resolved.
VER-65801 Catalog Engine, Subscriptions Database started with missing shards. This issue is now resolved.
VER-65818 Client Drivers - ADO The way the ADO.NET driver previously tracked statements associated with a connection to ensure that they were always closed when the connection was closed created a memory leak in some use cases. This problem has been corrected.
VER-65866 Execution Engine, Nimbus, S3 On rare occasions, a bug in S3 streaming read caused fatal errors. This issue has been resolved.
VER-66058 DDL In Eon mode with DFS, if a node was down, queries was liable to fail and return the error "System is not k-safe ..." This issue has been resolved.
VER-66080 Execution Engine Long 'like' patterns with many non-ASCII characters such as Cyrillic were liable to crash the server. This issue has been resolved.
VER-66088 Scrutinize The scrutinize command previously used the /tmp directory to store files during collection. It now uses the specified temp directory.
VER-66095 Data load / COPY The FJsonParser no longer fails when loading JSON records with more than 4KB of keys and the option reject_on_duplicate_key=true.
VER-66136 UI - Management Console Running the 'Explain" query option for an unsegmented projection on the MC Query Plan page could trigger the error: "There is no metadata available for this projection". This issue has been resolved.
VER-66228 Optimizer Vertica now disables fast const loading if you are using hierarchical partitioning.
VER-66240 Optimizer - Statistics and Histogram The database statistics tool SELECT ANALYZE_STATISTICS no longer fails if you call it when the cluster is in a critical state.
VER-66308 Hadoop Previously, NULLs in text struct data when loaded through the HCatalog Connector produced a null pointer exception. This has been fixed.
VER-66327 Client Drivers - JDBC, Security JDBC could fail with a stack overflow error when receiving more than 512 MB of data from Vertica. This issue was due to an incompatibility between Java's and OpenSSL's implementations of TLS renegotiation. Vertica now contains the tls_renegotiation_limit parameter. You can set this parameter to 0 to disable SSL/TLS renegotiation and avoid the issue.
VER-66345 Data load / COPY Vertica's Parquet file parser would occasionally incorrectly reject rows from files containing a column with a max definition level of zero and one or more columns with a nonzero max definition. This issue has been fixed.
VER-66489 Optimizer During an optimized merge, Vertica tried to plan the DELETE portion of the MERGE query twice, sometimes triggering an error. This issue has been resolved.
VER-66589 Execution Engine Partition pruning did not work when querying a table's TIMESTAMP column, where the query predicate specified a TIMESTAMPTZ constant. This issue has been resolved.
VER-67056 Admin Tools Sometimes during database revive, admintools treated s3 and hdfs user storage locations as local filesystem paths. This led to errors during revive. This issue has been resolved.

Known issues Vertica 9.2.1

Updated: 5/8/19

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.

Known Issues

Issue

Component

Description

VER-55470 AP-Advanced

After calling the IMPUTE function with the fourth argument being 'mode', a temporary table named impute_201701_80425147_p1 is created. This table is required to query the view generated by IMPUTE.

Workaround: Drop the temporary table when you no longer need the view.

VER-58168 Recovery A transaction that started before a node began recovery is referred to as a dirty transaction in the context of recovery. A recovering node must wait for or cancel such a transaction in order to recover tables modified by the transaction. In some rare instances such transactions may be hung and can not be canceled. Tables locked by such a transaction can not be recovered and therefore a recovering node can not transition to 'UP'. Usually the hung transaction can be stopped by restarting the node on which the transaction was initiated, assuming this is not a critical node. In extreme cases or instances where the initiator node is a critical node, restart the cluster.
VER-60797 License AutoPass format licenses do not work properly when installed in Vertica 8.1 or older. In order to replace them with legacy Vertica license, users need to set the knob AllowVerticaLicenseOverWriteHP=1.
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-63720 Recovery When fewer nodes than the node number of the cluster are specified in a vbr configuration file, the catalog of the restored library will not be installed on nodes that were not specified in the vbr configuration file.
VER-64916 Kafka Integration When Vertica exports data collector information to Kafka via notifier, the sterilization 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-64997 Backup/DR, Security

A vbr restore operation can fail if your database is configured for SSL and has a server.key and a server.crt file present in the catalog directory.

Workaround: Move your SSL authentication related files (server.crt, server.csr and server.key) from the catalog directory before performing the restore.

VER-65742 Security

When users are given permissions on a view through schema inherited privileges and directly on the view, only the owner of the view has access.

Workaround: Only use inherited privileges or privileges directly on the object, not both.

VER-66025 Catalog Engine, Spread The database can fail if you create nested fault groups after dropping and re-adding nodes.
VER-66455 Optimizer

When there is an enabled constraint on a table, a DML on that table often involves an internal query checking for constraint integrity. If the constraint-checking query spills and fails, Vertica retries the DML but the DML eventually still fails with the message, "DDL statement interfered with query replan".

Workaround: Re-issue the DML and make sure the constraint-checking query won't fail by increasing memory, load/update data in smaller increments, or by enabling join spill.

VER-66756 Installation Program If a standby node had been replaced in past with failed nodes with deprecated projections. The identify_unsupported_projections.sh script could not reflect on the standby node. When upgrading to 9.1 or later, the standby node may not start due to failure of the 'U_DeprecateNonIdenticallySortedBuddies' and/or 'U_DeprecatePrejoinRangeSegProjs' task.
VER-67210 AP-Advanced

If a node runs out of memory while training a machine learning model, sometimes a query may fail with "Error during cleanup for User Defined Function load_rows_into_blocks: VIAssert(size >= 0) failed"

Workaround: Retry the query when more memory resources are available.

VER-67228 AMI, License An Eon Mode database created in one hourly listing can only be revived into another hourly listing with the same operating system. For example, an Eon Mode database created in the Red Hat hour listing cannot be shutdown then revived into a cluster created using the Amazon Linux listing.

 

What's Deprecated in Vertica 9.2

The following Vertica functionality was deprecated in this release. This functionality will be retired in a future Vertica version:

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

Vertica 9.2.0-7: Resolved Issues

Release Date: 4/11/2019

This hotfix addresses the issues below.

Issue

Component

Description

VER-66705 Data Load / Copy The FJsonParser no longer fails when loading JSON records with more than 4KB of keys and the option reject_on_duplicate_key=true.
VER-66715 Data Removal - Delete, Purge, Partitioning Vertica no longer displays an error if a partition operation to a table is committed after a single-node query has started but not yet reached the local planner stage on that same table.
VER-66730 UI - Management Console Running the 'Explain" query option for an unsegmented projection on the MC Query Plan page could trigger the error: "There is no metadata available for this projection". This issue has been resolved.
VER-66742 UI - Management Console The MC would not properly display the license chart on the license tab when license usage exceeded 10TB. This issue has been resolved.
VER-66750 Kafka Integration Vertica now properly supports TLS certificate chains for use with Kafka Scheduler and UDx.
VER-66765 Hadoop Export to Parquet previously crashed when the export included a combination of Select statements. This issue has been fixed.
VER-66808 Backup/DR Altering the owner of a replicated schema with the CASCADE clause caused a node crash on the target database. This issue has been fixed.
VER-66810 Optimizer Statistics and Histogram The database statistics tool SELECT ANALYZE_STATISTICS no longer fails if you call it when the cluster is in a critical state.
VER-66838 Optimizer Vertica now disables fast const loading if you are using hierarchical partitioning.
VER-66839 Client Drivers - JDBC, Security JDBC could fail with a stack overflow error when receiving more than 512 MB of data from Vertica. This issue was due to an incompatibility between Java's and OpenSSL's implementations of TLS renegotiation. Vertica now contains the tls_renegotiation_limit parameter. You can set this parameter to 0 to disable SSL/TLS renegotiation and avoid the issue.
VER-66856 Optimizer During an optimized merge, Vertica would attempt to perform planning of DELETE portion of the MERGE query twice, sometimes triggering an error. This issue has been resolved.

Vertica 9.2.0-6: Resolved Issues

Release Date: 3/21/2019

This hotfix addresses the issues below.

Issue

Component

Description

VER-66271 Scrutinize

The scrutinize command previously used the /tmp directory to store files during collection. It now uses the specified temp directory.

VER-66411 S3 Eon mode contained an unnecessary check for files on communal storage before removing those files. That check could slow the delete operation and increase request costs. The check has been removed.
VER-66498 Data load / COPY

Rejected data and exceptions files during a COPY statement are now created with file permissions 666.

VER-66526 DDL

In Eon mode with DFS, if a node was down, queries could fail with the error "System is not k-safe ..."

This issue has been fixed.

VER-66527 Execution Engine

Some long 'like' patterns with many non-ASCII characters could cause Vertica to fail.

This issue has been fixed.

Vertica 9.2.0-5: Resolved Issues

Release Date: 3/5/2019

This hotfix addresses the issues below.

Issue

Component

Description

VER-66420 Data load / COPY

Parquet files containing columns with a maximum definition level of zero could have rows erroneously rejected during an external table query or COPY statement.

This issue has been fixed.

Vertica 9.2.0-4: Resolved Issues

Release Date: 2/14/2019

This hotfix addresses the issues below.

Issue

Component

Description

VER-66009 Execution Engine, Nimbus, S3

There was a bug in S3 streaming read that, in rare occasions, lead to fatal errors.

This issue has been fixed.

VER-65851 Security

Setting a row-based access policy on an underlying table of a view denies the user access to the view.

This issue is fixed.

Vertica 9.2.0-3: Resolved Issues

Release Date: 1/29/2019

This hotfix addresses the issues below.

Issue

Component

Description

VER-65822 Data load / COPY

Occasionally, an empty response from S3 (response code 0) could cause some database operations to fail.

This issue has been fixed.

VER-65772 UI - Management Console

Due to a miscalculation, license auditor running in sampling mode showed a much larger standard error than it actually was (this is the number you see after the +/- sign on the data size line in license compliance messages).

This issue has been fixed.

VER-65767 and VER-65765 Execution Engine In Eon mode, the number of List requests made to S3 when querying out-of-depot ROS files has been reduced.
VER-65736 Backup/DR

Running the remove task using a vbr config file with an incorrect "snapshotName" removed entries from the backup manifest file.

This problem has been resolved.

VER-65556 UI - Management Console

License usage was showing 0% when Autopass license is used.

This issue has been fixed.

Vertica server hotfix upgrade is also required for this fix to take effect (same release number).

 

 

 

Vertica 9.2.0-2: Resolved Issues

Release Date: 1/3/2019

This hotfix addresses the issues below.

Issue

Component

Description

VER-65444 UI - Management Console

Management Console on AWS did not display the AMI version which may have caused user confusion if the MC was not hotfixed on a Vertica hotfix AMI.

This issue has been fixed.

VER-65395 UI - Management Console

When the Management Console on premises attempted to download the Vertica AMI repository file, an error occurred. The error did not cause any functionality failures since the repository file is not used for MC on premises.

This issue has been fixed.

Vertica 9.2.0-1: Resolved Issues

Release Date: 12/12/2018

This hotfix addresses the issues below.

Issue

Component

Description

VER-65348 DDL - Table

When copying a table using the CREATE TABLE LIKE command, all column default expressions were lost if one of the columns has a default value of a sequence.

Otherwise, default expressions were copied to the new table.

This issue has been fixed.

VER-65407 Eon, Subscriptions

After upgrading to 9.2, the Eon database crashed after running rebalance_shards.

This issue has been fixed.

Vertica9.2.0: Resolved Issues

Release Date: 11/27/2018

To see a complete list of additions and changes introduced in this release, refer to the Vertica 9.2 New Features Guide.

Issue

Component

Description

VER-63430 Optimizer

At times, query performance was sub-optimal when both the following occurred:

  • The query included a subquery that joined multiple tables in a WHERE clause.
  • The parent query included this subquery in an outer join that spanned multiple tables.

This issue been fixed.

VER-62662 Data load / COPY

Occasionally, a COPY or external table query could crash a node.

This issue has been fixed.

VER-48026 DDL - Table

If you moved a table with foreign keys to a new schema, attempts to drop the original schema without CASCADE returned a rollback error that referenced a foreign key dependency.

This issue has been fixed.

VER-62002 DDL - Table

Vertica placed an exclusive lock on the global catalog while it created a query plan for CREATE TABLE AS <query>.

Very large queries could prolong this lock until it eventually timed out. On rare occasions, the prolonged lock caused an out-of-memory exception that shut down the cluster.

This issue has been fixed.

VER-59212 Sessions

In cases where a user had privileges on a user resource pool but not the GENERAL pool, certain DDL statements would fail.

This issue has been fixed.

VER-63405 Catalog Engine, Spread

If a control node and one of its child nodes went down, attempts to restart the second (child) node sometimes failed.

This issue has been fixed.

VER-63839 Data load / COPY

The SKIP keyword of a COPY statement was not properly supported with the FIXEDWIDTH data format.

This issue has been fixed.

VER-62810 Data load / COPY

In a COPY statement, excessively long invalid inputs to any date or time columns could cause stack overflows, resulting in a crash.

This issue has been fixed.

VER-64716 Data load / COPY, FlexTable

When parsing an array, FJSON PARSER sometimes returned inconsistent results with Vertica versions prior to 9.1SP1.

This issue has been fixed.

VER-61431 Optimizer - Plan Stability

The optimizer did not consider active directed queries when it created plans for queries that included a LABEL hint.

This issue has been fixed.

VER-60716 FlexTable

The MAPITEMS function returned truncated map values.

The issue has been fixed.

VER-63650 DDL

In some cases, attempts to add a column with a NOT NULL constraint partially failed: Vertica added the column but omitted the constraint.

This issue has been fixed.

VER-63550 S3

S3Export was not thread safe when the data contained time/date values. Therefore, you could not use S3Export with PARTITION BEST when exporting time/date values.

This issue has been fixed.

VER-64421 Cloud - Amazon, UI - Management Console

At times, Management Console failed to add a new host to the database after a long wait.

This issue has been fixed.

VER-61289 Execution Engine, Hadoop

If a Parquet file metadata was very large, Vertica consumed more memory than was reserved and crashed when the system ran out of memory.

This issue has been fixed.

VER-63742 AMI, UI - Management Console

Management Console deployed a Vertica CloudFormation Template in an existing VPN/subnet failed to provision and revive the Vertica database with the user providing a VPN access CIDR.

This issue has been fixed.

VER-64645 UI - Management Console

SMTP alerts were not being received in the planned timeframe, even during network disconnection.

This issue has been fixed.

VER-64705 UI - Management Console

The permissions of /opt/vconsole/mcdb/derby/mcdb/tmp were not being maintained at a secure setting through restarts of the Management Console.

This issue has been fixed.

VER-61351 Admin Tools

Adding large numbers of nodes in a single operation could lead to an admintools error about parsing output.

This issue has been fixed.

VER-60695 Optimizer

The optimizer could not use a fast plan to perform a refresh operation on tables with multiple live aggregrate projections.

Now, the optimizer applies the refresh operation on each live aggregrate projection as a separate transaction, and applies the fast plan to each live aggregrate projection.

This significantly reduces the time required to refresh tables with multiple live aggregrate projections.

VER-63861 AP-Advanced

If you ran APPROXIMATE_COUNT_DISTINCT_SYNOPSIS on a database table that contained NULL values, the synopsis object that it returned sometimes was larger than the one it returned after the NULL values were removed.

This issue has been fixed.

VER-45444 DDL - Projection

In many cases, Tuple Mover operations were adversely impacted by the high default number of segmentation and sort columns in a superprojection.

This issue has been fixed.

VER-64112 Optimizer

Very large expressions could run out of stack and crash the node.

This issue has been fixed.

VER-51210 Execution Engine, Optimizer

Removed misleading documentation that suggested ANY and ALL operators can be used to evaluate arrays.

Attempts to do so now throw an error.

VER-64351 Tuple Mover

When executing heavy workloads over an extended period of time, the Tuple Mover was liable to accumulate significant memory until its session ended and it released the memory.

This issue has been fixed.

VER-63844 DDL - Projection

The catalog stored incorrect information about pinned projections.

This issue has been resolved.

VER-63841 Execution Engine

In some regular expression scalar functions, Vertica would crash for certain long input strings.

This issue has been fixed.

VER-62988 Data load / COPY, Hadoop

Queries involving a join of two external tables loading Parquet files sometimes caused Vertica to crash. The crash happened in a very rare situation due to memory misalignment.

This issue has been fixed.

VER-58472 UI - Management Console

Management Console could not import multiple clusters when the host had the same private IP address as the private IP address of a previously imported cluster.

This issue has been fixed.

VER-61741 Optimizer

When a projection was created with only the PINNED keyword, Vertica incorrectly considered it a segmented projection. This caused optimizer internal errors and incorrect results when loading data into tables with these projections.

This issued has been fixed.

IMPORTANT: The fix only applies to newly created pinned projections. Existing pinned projections in the catalog are still incorrect and need to be dropped and recreated manually.

VER-61351 Admin Tools

Adding large numbers of nodes in a single operation could lead to an admintools error parsing output.

This issue has been fixed.

VER-63728 Catalog Engine

Having frequent CREATE/INSERT/DROP tables caused a memory leak in the catalog.

This issue has been fixed.

VER-63044 Optimizer

The MERGE USING and INSERT SELECT operations, which selected data to be inserted or merged via a query with subqueries under Outer Joins, would sometimes result in an Internal Error.

This issue has been fixed.

VER-62249 Optimizer

Vertica evaluated permissions on external sources when creating an external table while holding GCLX. Sometimes this caused GCLX to timeout if there were issues with the file system of the external sources.

This issue is now fixed.

VER-57071 Optimizer

In a materialized WITH statement, when there are WITH clauses that have the same alias as regular tables in the FROM clause, Vertica sometimes failed to parse the query correctly.

This issue has been fixed.

VER-64269 UDX

Queries with CASE-like expressions over inline functions/SQL macros that return strings and that evaluate CASE expressions internally sometimes returned an error such as "Function can't be used with an operator."

This issue has been fixed.

VER-47310 Installation Program

Vertica was not installing properly on OpenStack virtual machines.

This issue has been fixed.

VER-63969 Front end - Parse & Analyze

When a meta-function was used in a materialized WITH clause, Vertica process could crash with a "VAssert(0)" error.

This issue has been fixed.

VER-64858 Optimizer

An historical query (i.e., at epoch) did not fold stable functions into constants properly, which caused significant performance degradation compared to a non-historical query.

This issue has been fixed.

VER-64378 Data load / COPY

A COPY statement with AUTO copy mode failed to handle TRICKLE copy mode set as the table loading mode properly, which caused loaded data to fail over to ROS containers when the WOS pool was full.

This issue has been fixed.

VER-60916 DDL, Hadoop

Creating a view can be expensive if a view query is complex or references external storage.

This is because Vertica plans the view query during view creation, although the plan is only for validation purpose and is dropped after view creation.

You can optionally turn off planning by setting DisableViewQueryPlanning to true.

VER-60247 Kafka Integration

You could not configure the scheduler and add microbatches dynamically when the scheduler was running.

This issue has been fixed.

Known issues Vertica 9.2

Updated: November 27, 2018

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.

Known Issues

Issue

Component

Description

VER-61584 Subscriptions

The VAssert(madeNewPrimary) failure only occurs while a node or nodes are shutting down or are in unsafe mode.

VER-61780 Scrutinize Scrutinize can generate UnicodeEncodeError if the system locale is set to a language that has non-ASCII characters.
VER-62983 Hadoop

When HCatalog Connector 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 HCatalog Connector schemas.

VER-41895 Admin Tools

On some systems, admintools will fail to parse output while running SSH commands on hosts in the cluster.

In some situations, if the admintools operation needs to run on just one node, then there is a workaround.

Users can avoid this issue by using SSH to connect to the target node and running the admintools command on that node directly.

VER-48041 Admin Tools

On some systems, occasionally admintools will not be able to parse the output it sees while running SSH commands on other hosts in the cluster.

The issue is typically transient and there is no known work-around.

VER-62414 Hadoop

Loading ORC and Parquet files with a very small stripe or rowgroup size can lead to a performance degradation or run into an out of memory condition.

Workaround: Tuning the configuration parameter "HiveSourceSizeMB" to a value greater than 512 will resolve this issue.

VER-55257 Client Drivers - ODBC

Issuing a query that returns a large result set and closing the statement before retrieving all of its rows can result in the following error when attempting subsequent operations with the statement:

"An error occurred during query preparation: Multiple commands cannot be active on the same connection. Consider increasing ResultBufferSize or fetching all results before initiating another command."

Workaround: Set the ResultBufferSize property to 0 or retrieve all rows associated with a result set before closing the statement.

 


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 - 2019 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.