We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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?

thanks!

Comments

  • 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)

    See:
    https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AnalyzingData/MachineLearning/MachineLearning.htm

Leave a Comment

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