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 Identifying Unsupported Projections in the Vertica documentation.

Updated: December 12, 2018

About Vertica Release Notes

What's New in Vertica 9.2

What's Deprecated in Vertica 9.2

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

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 enhancement 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

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

Vertica 9.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 HP 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. HP 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 HP 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 - 2018 Hewlett-Packard Development Company, L.P.

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.


Send documentation feedback to Micro Focus