Identifying Unsupported Projections

Starting with release 9.1 and continuing with release 9.2, Vertica has removed support for the following projections:

  • Release 9.1: Projection buddies with different SELECT and ORDER BY clauses. All projection buddies must specify columns in the same order. Projections with non-compliant buddies are regarded by the Vertica database as unsafe.

  • Release 9.2: Pre-join and range segmentation projections. If a table's only super-projection is one of these projection types, the projection is also regarded as unsafe.

Before upgrading to 9.1 or higher, run the pre-upgrade script that Vertica has provided, to check your database for unsupported projections. This script examines your current database and sends to standard output its analysis and recommendations. If it identifies unsupported projections, it lists them. If the script finds projection buddies with different SELECT and ORDER BY clauses, it also generates a deploy script. This script remedies projections so they comply with system K-safety.

If the upgrade encounters unsupported projections, it is liable to fail. You must then revert to the previous installation.

Pre-Upgrade Script Download

Download the pre-upgrade script, identify_unsupported_projections.sh, from the following location:

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

Pre-Upgrade Requirements

  • Vertica release 8.x or higher
  • Sufficient disk space available for generated output, varies according to database size—for example, 4GB if catalog size < 5GB.
  • maxmemorysize of SYSDATA resource pool increased to handle script and DDL overhead.
  • All nodes in the cluster are UP.

Syntax

While the database is running, execute the pre-upgrade script on one of its server nodes:

./identify_unsupported_projections.sh
          [ ‑U username ]
          [ ‑w password ]
          [ ‑p port ]
          [ ‑d path ]
          [ ‑s temp‑schema ]

Arguments

-U username Username of superuser or dbadmin
-w password Password
-p port TCP port or local socket file extension where server listens for connections, by default 5433
-d path Location for the generated output files, by default /tmp
-s temp‑schema Name of the temporary schema that the pre-upgrade script creates, by default upgrade_vertica. Set this argument only if your database already has a schema with that name.

Recommended Usage

  1. Run the pre-upgrade script on your Vertica installation.
  2. Evaluate script output for unsupported projections.
  3. If unsupported projections are detected, do one or more of the following:
    • Remove pre-join and range segmentation projections.
    • Fix all projections with different SELECT and ORDER BY clauses: run the deploy script, or manually modify non-compliant projections so all buddies have identical selection lists and sorting.
  4. Run the pre-upgrade script again to confirm all problems are resolved. If so, Vertica returns this message:
    Congratulations! No unsafe projections detected

    Otherwise, repeat steps 2-4.

Deploy Script

If the pre-upgrade script detects projection buddies with different SELECT and ORDER BY clauses, it generates a deploy script that performs the following tasks:

  • Provides a remedy for unsupported projections.
  • Refreshes new projections and existing projections that are not up-to-date.

Remedies vary according to the projection type:

Projection type Deploy script remedy
Standard segmented projection

Drops unsafe projection buddies, replaces with projection buddies that have identical selection lists and sorting, and identical segmentation but different offsets.
Standard unsegmented projection

Drops unsafe projections, replaces with projections that have identical selection lists and sorting, but different node set.
Expression projection Drops unsupported projections, replaces with projections that comply with new requirements.
Projection with non‑elastic segmentation Drops unsupported projections, replaces with projections that comply with new requirements.

Pre-join projection

Range segmentation projection

None, you must manually remove these projections. If any projection serves as its anchor table's only superprojection, first create a superprojection to replace it.

Consider using flattened tables to replace pre-join projections.

Vertica always enforces identical column order in the SELECT and ORDER BY clauses of live aggregate projection buddies, so these can be safely ignored.

Estimated Overhead

  • Pre-upgrade script: Depending on database size, runs up to an hour or more.

  • Deploy file: Calls Vertica meta-function REFRESH on all projections that require refresh, including all replacement projections.

Refresh operations should be relatively fast, regardless of table size. To remedy unsupported projections, REFRESH populates new projection buddies by copying storage containers of their source buddies.

Handling Failed Upgrades

If your upgrade fails because of unsupported projections, you must revert to the previous Vertica installation:

  1. For each host on the cluster, find and remove the RPM or DEB package.
  2. Reinstall the earlier version's package.
  3. Start the reinstalled database and verify that it runs correctly.
  4. Perform a full backup on the database.
  5. Run the pre-upgrade script on the Vertica installation.

Known Issues

If you run the script on Vertica 8.x, the DDL output is liable to reverse the keywords ENCODING and ACCESSRANK. This problem is observed in all 8.x versions that precede hotfix version 8.1.1.