********************************************************************************************** READ BEFORE UPGRADING! ********************************************************************************************** As of release 9.1, the SELECT and ORDER BY clauses of all projection buddies must specify columns in the same order. Projections with non-compliant buddies are regarded by the Vertica database as unsafe. By removing support for non-compliant (unsafe) projections, Vertica can move forward with other work that will facilitate catalog management and improve overall database performance. Before upgrading to 9.1 or higher, current users are strongly urged to check that all projection buddies in the current database comply with these new requirements. If the upgrade encounters any unsafe projections, it will fail. You must then revert to the previous installation. PRE-UPGRADE SCRIPT ================================ To facilitate your upgrade, Vertica is providing a pre-upgrade script. This script analyzes your current database and identifies unsafe projections. It then generates a DDL script that you can use before the upgrade, to remedy these projections and ensure compliance with system K-safety. WHAT THE SCRIPT DOES ================================ The pre-upgrade script runs a set of queries that analyze projections in your database, and identifies any that are unsafe. It exports DDL that provides several remedies for these projections, depending on the projection type: * Standard segmented projection: Adds extra projection buddies with identical selection lists and sorting, and identical segmentation, but different offsets. * Standard unsegmented projection: Adds extra projections with identical selection lists and sorting, but different node set. * Expression projection: Drops unsafe projections and replaces them with projections that comply with new requirements. * Projection with non-elastic segmentation: Drops unsafe projections and replaces with projections that comply with new requirements. WHAT THE SCRIPT DOES *NOT* DO ================================ The pre-upgrade script ignores the following projection types, which require special handling: * Pre-join projections (deprecated since release 8.0) * Projections with range segmentation (deprecated since release 6.1.1) In both cases, you must modify all unsafe projections so their buddies have identical selection lists and sorting. NOTE_1: Vertica plans to remove support for these deprecated projection types in an upcoming release. After identifying these projections, consider using this occasion to drop them altogether. NOTE_2: Vertica always enforces identical column order in the SELECT and ORDER BY clauses of live aggregate projection buddies, so these can be safely ignored. PRE-UPGRADE REQUIREMENTS ================================ * Vertica release 8.x or higher * Sufficient disk space is available for generated output (varies according to database size) * maxmemorysize of SYSDATA resource pool increased to handle script and DDL overhead. Recommended size: 4GB if catalog size < 5GB * 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_differently_sorted_buddies.sh [ -U ] [ -w ] [ -d ] [ -s ] Arguments: -U : Username of superuser or dbadmin -w : Password -d : Location for the generated output files, by default /tmp -s : 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. SCRIPT OUTPUT ================================ The script generates three files: * deploy: A DDL script that performs the following tasks: - Modifies unsafe projections according to the projection type (see 'What the Script Does' above). - Refreshes new projections and existing projections that are not up-to-date. * segmented_projections: Identifies unsafe segmented projections. * unsegmented_projections: Identifies unsafe unsegmented projections. RECOMMENDED USAGE ================================ 1. Run the pre-upgrade script on your Vertica installation. 2. Evaluate the output files for unsafe projections. 3. If unsafe projections are detected, do one of the following: - Run the deploy script. - Manually modify unsafe projections so all buddies have identical selection lists and sorting, 4. Call EXPORT_CATALOG() to identify unsafe pre-join and range-segmentation projections, and either modify or drop them. 5. Run the pre-upgrade script again to confirm that all problems are resolved. If so, Vertica returns this message: Congratulations! No unsafe projections detected Otherwise, repeat steps 2-5. ESTIMATED OVERHEAD ================================ * Pre-upgrade script: Testing indicates that the script is likely to run as long as an hour or more, depending on the size of the database. * DDL script (in deploy file): The pre-upgrade script detects all projections that require refresh and exports DDL that calls REFRESH() on these. The DDL also refreshes all projections that it modifies. Because refresh operations can consume considerable time and resources, Vertica recommends that you run the DDL during off-peak hours. *** !IMPORTANT! ****************************************************************************************** Refresh operations should be relatively fast, regardless of table size. For this upgrade, REFRESH() populates new projection buddies by copying storage containers of their source buddies. ********************************************************************************************************** KNOWN ISSUES ================================ If you run the pre-upgrade 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.