With the release of version 5.0, Alfresco officially stopped supporting the Lucene indexing subsystem, leaving Solr as Alfresco’s lone indexing solution. A major benefit of leveraging Lucene was its in-transaction indexing, making content searchable as soon as it was created. Solr, however, utilizes asynchronous indexing which leads to “eventual consistency”, meaning newly created content is not searchable right away. This poses problems when building solutions for clients that need a high-velocity system capable of ingesting and then quickly presenting newly created content from Alfresco to end users. Luckily, Alfresco has a solution: Enter Transactional Metadata Query.
What is a Transactional Metadata Query?
With the release of version 4.2, Alfresco began supporting a system called Transactional Metadata Query. This system allows particular CMIS and FTS language queries to be run directly against database indexes instead of the Solr index. Having CMIS and FTS queries run directly against the database allows content to be retrieved as soon as it is committed, as opposed to searching against the “eventually consistent” Solr indexes. By default, the Transactional Metadata Query system takes a CMIS or FTS query and parses the query to determine if each part is supported by the database query engine (Please see What Queries are Supported with Transactional Metadata Query? below). If the entire query is supported, it is run directly against the database. If the entire query is not supported by the database query engine, it is run against Solr instead.
What Queries are Supported with Transactional Metadata Query?
Earlier this year one of our clients was experiencing confusing results when performing FTS queries in HPI. In their situation, querying for a document with a particular status and category returned the proper results. However, when the same status and category were searched for within a particular time period, documents with the incorrect status or incorrect category would appear in the results. What made the issue so difficult to understand was that the queries themselves and the metadata in Alfresco all appeared to be correct, but the values being returned by the queries using a specific date range were not matching what was in Alfresco. How could one query return the correct documents with the correct data, but simply adding date parameter to our search cause such odd data to return? The issue was two-fold: there was an issue with our Solr index, and FTS queries using date searches are not supported by the Transactional Metadata Query system.
Alfresco lists a number of supported and unsupported CMIS and FTS queries on their Alfresco Docs site, but these do not appear to be a comprehensive lists. Listed below are currently known supported and unsupported predicates and logical operators. (For more information on what is and isn’t supported, visit Alfresco’s community pages: http://docs.alfresco.com/4.2/concepts/intrans-metadata-query.html, http://docs.alfresco.com/5.0/concepts/intrans-metadata-query.html
Other operators
In the following cases, the query will go to the database but the result might not be as expected. In all other unsupported cases, the database query will fail and fall back to be executed against the Solr 4 subsystem.
- IS NOT NULL
- IS NULL: Currently, this operator will only find properties that are explicitly NULL as opposed to the property not existing.
- SORT: The multi-valued and mltext properties will sort according to one of the values. Ordering is not localized and relies on the database collation. It uses an INNER JOIN, which will also filter NULL values from the result set.
- d:mltext: This data type ignores locale. However, if there is more than one locale, the localized values behave as a multi-valued string. Ordering on mltext will be undefined as it is effectively multi-valued.
- UPPER() and LOWER(): Comparison predicates provide additional support for SQL UPPER() and LOWER() functions (that were dropped from a draft version of Alfresco CMIS specification but are supported for backward compatibility).
Other Situations
- Performing DATE searches will automatically go against Solr
- Queries against Boolean properties will automatically go against Solr
As previously stated, Alfresco’s list of supported and unsupported queries does not appear to be comprehensive. If you want to know definitively if your query is running against the database or Solr, increase the logging level to DEBUG on the DbOrIndexSwitchingQueryLanguage class. This class will print a log stating whether your query was supported to run against the database or if it ran against Solr as a fall back.
log4j.logger.org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage=debug
We suggest running all queries using the CMIS and FTS languages in order to take advantage of Transactional Metadata Query’s ability to retrieve information immediately from the database instead of waiting for “Eventually Consistent”
Solr indexes. To avoid the possibility of having unexpected or inconsistent search results, please refer the the information listed above (as well as Alfresco’s own documentation) in order to craft a Transactional Metadata Query.
[…] database compliant query (check out our previous posts on utilizing the Alfresco database and Alfresco TMQ Queries for more information) that runs against the database does not perform well and can time out, […]