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

ScottL

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!

Jim_Knicely

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

ScottL

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

Jim_Knicely

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

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