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

Forum|Forum|8 years ago
January 31, 2018
3 replies
13 views

+1

ScottL
Participating Frequently

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!

+2

Jim_Knicely
Participating Frequently
Forum|Forum|8 years ago
January 31, 2018

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