Aria Automation Search for Deployments does not work when searching for "can"

Products

• VCF Operations/Automation (formerly VMware Aria Suite)

Issue/Introduction

Searching for deployments with common english words (including but not limited to "can", "a", "it", "is", "the", "we") does not return any results even though there are deployments or resources in the environment that start with the search term.

Environment

Aria Automation (vRA) 8.x 

Cause

The internal postgres database uses the 'english' dictionary which contains stop words that prevent searching of common words.

Resolution

There is no resolution, but to work around the issue, you can modify the postgres built-in 'english' text search configuration to update the 'english' search configuration and change the dictionary for the specific tokens from english_stem to simple.

By altering the configuration for `catalog-db` we can effectively disable stop-word removal and stemming for any query using the 'english' configuration. 

Workaround:

1. Connect to the catalog-db
    a. SSH to one Automation node and run this to connect to the catalog-db
   
   vracli dev psql catalog-db
   
   b. type 'yes' to continue
   
   
2. Verify the default behavior (the problem)
   In postgres run the following query to validate the behavior:
   
   catalog-db=# SELECT to_tsvector('english', 'it is a test');
   

   Expected output:

    to_tsvector
-------------
 'test':4
(1 row)
   Observe that words 'it', 'is', and 'a' are not tokenized and are ignored.

3. View the current english text search configuration:
   
   catalog-db=# \dF+ english
   
   Expected output:

   Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
      Token      | Dictionaries
-----------------+--------------
 asciihword      | english_stem
 asciiword       | english_stem
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | english_stem
 hword_asciipart | english_stem
 hword_numpart   | simple
 hword_part      | english_stem
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
 url_path        | simple
 version         | simple
 word            | english_stem

4. Update the 'english' text search configuration
   
   catalog-db=# ALTER TEXT SEARCH CONFIGURATION english ALTER MAPPING FOR asciihword, asciiword, hword, hword_asciipart, hword_part, word WITH simple;
   
   
5. Verify New english text search configuration:
   
   catalog-db=# \dF+ english

Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
      Token      | Dictionaries
-----------------+--------------
 asciihword      | simple
 asciiword       | simple
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | simple
 hword_asciipart | simple
 hword_numpart   | simple
 hword_part      | simple
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
 url_path        | simple
 version         | simple
 word            | simple
   
   
   
6. Test the change to see of the tokens are created similar to 'simple' config:

   catalog-db=# SELECT to_tsvector('english', 'it is a test');

7. Expected Result:

            to_tsvector
------------------------------
 'a':3 'is':2 'it':1 'test':4
(1 row)

8. Update the dep_search table to trigger re-indexing
   catalog-db=# update dep_search set refresh_due_at = CURRENT_TIMESTAMP;

9. After making the above changes verify the search using the UI. 

Additional Information

PostgreSQL: Documentation: 17: 12.6. Dictionaries Stop Words