BEFORE YOU UPGRADE TO VERTICA 9.1: Fix unsafe buddy projections

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners

As of Vertica 9.1, the SELECT and ORDER BY clauses of all projection buddies must specify columns in the same order. Before upgrading to Vertica 9.1 or higher, current users are strongly urged to run a pre-upgrade script that checks that all projection buddies in the current database comply with these new requirements.

Warning: If the upgrade encounters non-compliant projections, it regards them as unsafe and the upgrade will fail. You must then revert to the previous installation.

Solution: Download and run the pre-upgrade script.

The pre-upgrade 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.

You can run this script starting now. To get more information and download the pre-upgrade script, go to the 9.1 Pre-Upgrade Script page at


  • sreeblrsreeblr Employee, Registered User

    may need quite some effort to make changes and get change approval from client. Any workarounds to run as is post upgrade?

  • SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners

    Not at this time. It must be run before upgrading.

  • ScottLScottL Employee, Registered User

    Hi Sarah - Are there release notes for 9.1? I can only find the 9.0 release notes. Thanks

  • ScottLScottL Employee, Registered User

    Hi Sarah - when we run this script in a 3 node environment running Vertica and Linux 7.4:

    sh -U dbadmin -w xxxxxx

    We get the follow error: line 25: syntax error near unexpected token '$'do\4r''
    l line 25: 'do


  • ScottLScottL Employee, Registered User

    Downloaded a fresh copy of the script into another 3 node, Vertica and Linux 7.4, created a 'tmp' directory and got the following:

    sh -U dbadmin -w xxxxx
    ': not a valid line 2: unset: OUTPUTDIR line 3: $'\r': command not found line 27: syntax error near unexpected token $'do\r''
    ' line 27: `do

  • mleuchtenburgmleuchtenburg Registered User

    Hi Scott,

    I can reproduce that error if I first run unix2dos on the script and then run it, as you did, by directly calling sh (or bash, actually) on the script. I suggest you try running dos2unix on it and then run it again.

    I'm not sure how your copy of the script could have gotten that problem. When I untarred it (tar xzvf identify_differently_sorted_buddies.tar.gz) the script I got had correct line-endings. Was there some copying step which you performed which might have transformed the line-endings?

    BTW, the #! line at the top says bash. I'm not sure if the RHEL7.4 sh is just bash, or if it's some other shell with more limited features, but if you want it via '$SHELL', I'd recommend using bash directly and not depending on sh to have the same features. On at least some OSes it is not the same. I don't know if it depends on bash-specific features, but I'm sure when we tested it internally we would have run it in bash.

  • ScottLScottL Employee, Registered User

    Thanks for responding! We just solved it at the same time you posted your response! We opened a ticket with Vertica support yesterday and it appears it was caused by downloading it to Windows and then copying it over to Linux. When we did a direct download to the server it worked correctly. Lesson learned! Thanks again for your help!

  • mleuchtenburgmleuchtenburg Registered User

    Glad to help! BTW the thing that tipped us off was the \r symbol. Unix line-endings are \n whereas Windows ones are \n\r. Something to watch out for in the future.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file