About Transactional queries with Alfresco One

[spilby]

Hi,

we have just upgrade to Alfresco 5.0.2 (we had 4.1.6 version) to reduce the response time of our searches, because with millions of documents it becomes too low.

The main change is with the transactional queries, that search on DB instead of directly on Solr. But I need to confirm if I understand some concepts or not before I begin to develop.


1. At first, we need to activate it on alfresco-global.properties. By default, the value of solr.query.fts.queryConsistency is TRANSACTIONAL_IF_POSSIBLE. If I don't add this propery, my queries will do the search like TRANSACTIONAL_IF_POSSIBLE ¿Is ok?

To force the transactionality I need to put in my code

params.setQueryConsistency(QueryConsistency.TRANSACTIONAL);

Here is my first question… I don't know If I understand the difference between "TRANSACTIONAL" and "TRANSACTIONAL_IF_POSSIBLE". If I put "TRANSACTIONAL" in the solr.query.fts.queryConsistency all the queries (with the correct parameters) will execute like transacionals, but if I put "TRANSACTIONAL_IF_POSSIBLE" I need to set the setQueryConsistency? Is this the difference?


2. On the online documentation, in "Configuring an optional patch for upgrade" chapter says: "To enable the patch that adds the required indexes to the database, set the following property in the alfresco-global.properties file: system.metadata-query-indexes.ignored=false "

Is this property required to do the transactional queries? The "optional" confuses me.

And I need to specify somewhere the DB indexes that I need? Or these indexes are added automatically when we specify at the metadata xml model "index enabled=true"? Or are two difference things?


3. If I understand ok, the parameters that we use in query affect the transactional mode of the search. For example, if I use OR, the search not be transactional. Or if I use PATH do the same.

But if I use, por example, TYPE, search on DB fisrt and SOLR after if need it.

And search with a value of a own metadata defined at model.xml? Is transactional? Search for a value is considered full text search and goes directly to SOLR index?


Thanks for your time, I wait for your clarifications and hope that helps other users too.

[afaust]

Hello,

TRANSACTIONAL_IF_POSSIBLE will try to execute all your searches on the DB first but if the query is too complex to be executed there or transactional queries aren't supported (hint: optional patch), it will silently fall back to SOLR search, ensuring that users get a normal result instead of an error (result may be empty but still beats an error).

The property in alfresco-global.properties is required for the transactional query functionality (as it states in the doc) but optional for Alfresco as a whole since Alfresco will work without transactional queries (due to TRANSACTIONAL_IF_POSSIBLE). Alfresco provides all the necessary indices which you enable with that property (or rather, disable the ignoring of the indices).

There are various restrictions to what transactional metadata queries support and what not. See the documentation for details.
In general, do not use PATH, SITE, ANCESTOR, OR, any d:content, d:boolean or d:any (among others) properties in your query or it will not be executable against the DB. Also, any property checks must be expressed in a form that means "identical value check" as querying the DB does not provide the same tokenization / similarity capabilities as the SOLR index. E.g. instead of myroperty:"value" you'd have to use =myroperty:"value" and "value" must be written in the proper case the value is stored in the DB.

In my experience, transactional queries is nothing that can be easily used / understood by end users. It is more a tool for developers to ensure their technical queries can be executed even with SOLR not being available / trustworthy, e.g. due to an outage, out-of-date indices, reindexing. The transactionality requirement is also more relevant for developers than for end users.

Regards
Axel

[spilby]

Oks Axel, thanks for your responses!

Only two clarifications about it.

1. "Alfresco provides all the necessary indices which you enable with that property". These indexes, are the indexes that I specify on model.xml with index enabled=true? Ergo, if I add index enabled=true on 4 metadates, then Alfresco create these 4 indexes on DB.

If not, if are indexes only for DB and not are the same that the model indexes, I must specify on some place which metadates I want to index? I supose that Alfresco don't index all the metadates, because it's not efficient.

And I understand too that put or not index enabled=true don't affect if I use transactional queries, because these indexes only serve to SOLR, not to DB.

I must check what is faster. Search on DB, or search on SOLR a metadata indexed. Could you give me an example of how add a new index for a custom property that I add to model.xml? For example, the property doc:myDoc that I write on the next point.

2. "Do not use PATH, SITE, ANCESTOR, OR, any d:content, d:boolean or d:any (among others) properties in your query or it will not be executable against the DB".

Imagine that I create some properties like this…

 <type name="doc:myDoc">
   <title>Document</title>
   <parent>cm:content</parent>
   <properties>
      <property name="doc:level">
          <title>Level</title>               
          <type>d:text</type>
               <mandatory>true</mandatory>
                    <index enabled="true">
         <atomic>true</atomic>
         <stored>false</stored>
         <tokenised>both</tokenised>
          </index>
                </property>
           …   
        </properties>
 </type>   
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

If I want to search with a query, like for example @doc\\:level:"value", this is a d:content query that don't works on DB? And I can't put the =@ to find an exact value if I want to search transactionally on DB? Maybe I don't understant it ok, sorry.

[afaust]

1) Index configuration in the model is irrelevant for transactional queries. Alfresco adds DB table indices and due to the nature of the Alfresco schema, they affect the entire table of properties. There is no way to differentiate which properties should be supported and which shouldn't. The only technical differentiation is the type of property - e.g. d:boolean are generally not supported due low efficiency of DB indices (on some DBs at least) for this type of column.

2) You can't transactionally search for d:content because the actual textual content values are not stored in the database. They are stored in the respective files on disk and it would be disastrous if the transactional query would inspect those…

Regards
Axel

[spilby]

Ok, a lot of thanks again Axel! Your responses are always useful!