The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

analyze_constraints fails with "query contains a SET operation tree that is too complex to analyze"

Using both vsql and DBViz, we executed "select analyze_constraints('');" against our database, which has 275 projections which have a total of 3GB of data. The command fails with this message:

14:21:36 FAILED [SELECT - 0 rows, 0.312 secs] [Code: 4963, SQL State: 54001] [Vertica]VJDBC ERROR: The query contains a SET operation tree that is too complex to analyze
select analyze_constraints('');
14:21:37 END Execution 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.312/0.000 secs [0 successful, 1 errors]

We'd like to avoid having to generate a list of all the projections and run each of them separately (they all work if they're named specifically (select analyze_constraints('xyz');) but we'd like to be able to automate this task using a shell script and naming each projection would require maintenance anytime an add or delete of a projection occurred.

Are there any system parameters or pool settings we can change to accommodate this?



  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    You can script it to run from the command line...

    /opt/vertica/bin/vsql -Atc "select 'select analyze_constraints(''' || t.table_schema || '.' || t.table_name || ''');' from tables t where exists (select null from table_constraints tc where tc.table_id = t.table_id) and not t.is_system_table;" | /opt/vertica/bin/vsql -o some_output_file.out

  • ScottLScottL Employee
    edited January 2018

    Geez, Jim, the ink on the web page was hardly dry when you answered! You're setting our expectations way too high now! :smiley: Thanks for your help!

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    @ScottL - The plan is to start using Vertica's built-in predictive analytic capabilities to answer questions even before they are asked B)


Leave a Comment

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