Analyze_constraints not working with locale property in Vertica version 7.2

tangiblegeek

locale= LEN_S1

 

The above property is what I have set locale as to make Vertica case-insensitive. But unfortunately that does not allow me to use analyze_constraints through a UDX, eventuallly inserting duplicates in the table.

 

Is there a value which can be used for locale so that Vertica will be case insensitive as well as analyze_constraints will work in UDX??

Sharon_Cutter

You shouldn't use a locale (LEN_S1 or any other) to make Vertica case-insensitive. Doing this prevents queries from being optimized in the normal way.  I've seen this negative impact firsthand - one of my clients had done this.  We undid it :-)  

 

Much better to fix your data model.

 

  --Sharon

 

 

tangiblegeek

Hi Sharon,

 

I have an update on this:

 

I was facing that issue in Vertica version 7.2.1, but we have had an upgrade and are now using version 7.2.3. In this version we are now able to set the database to case insensitive and also have constraints effective at the database level. 

 

So all we had to do was remove analyze_constraints from the UDX code. And the locale value is the same as mentioned earlier.

 

Thanks,

Abhilash Sharma