Design and Implementation of the Native Repository

Last modified 19 Nov 2021 12:33 +01:00
Since 4.4
This functionality is available since version 4.4.

Repository responsibilities

These are based on RepositoryService interface and there should be no difference compared to the legacy repository. Each point contains design/implementation notes.

  • Repository stores midPoint objects (subtypes of ObjectType), assigning their OID if missing. For this m_object table with oid (UUID) and fullObject (BYTEA) columns would be enough. However, this is not acceptable for searchObjects performance see note there.

  • Repository assigns any missing container IDs (CID) for object’s containers. These IDs are unique inside a single object and assigned loosely sequentially. For this m_object.cid_seq is stored in the database and it contains the next available CID. IDs are assigned only to multi-valued containers, e.g. object’s metadata does not need any CID.

  • Repository allows for searching for objects and selected containers with reasonable performance. This is why objects stored as documents (fullObject) is not enough. Various types are stored in specific sub-tables instead with values extracted to separate columns. There are other related tables, typically for containers and other multi-value properties. Not all values are by default searchable/indexable in this way and it must be possible to specify what other properties are indexed. These can be properties from the default schema but also from schema extensions.

  • Each object is a separate "aggregate" and any inter-object references are soft, not constraints on the database level. For example, multi-value container roleMembershipRef is stored in m_ref_role_membership table which has a foreign key to the common OID pool m_object_oid (this is necessary for any FK pointing to object OID if multiple types are possible, FK to m_object does not work this way). But the targetOid is not a FK as it points to another object.

Design notes

Modify Efficiency concerns

Modify efficiency for distributed full-object (AccessCertificationCampaign vs its case):

  • ADD modification for Case container of AccCertCampaign should not fetch other cases. Update of full object (but without cases), version, etc. works as usual.

  • DELETE modification for Case container of AccCertCampaign should not fetch any cases, only delete the case row (and update main full object).

  • REPLACE modification for case does not need to read any cases, just delete all the rows.

  • Modification going inside any Case should fetch only the needed case before applying the modification.

This mechanism can be reused if full-object for assignments is split from object’s full-object in the future (not sure about the correctness of the column name fullObject).

Index-only attributes/extensions modify efficiency:

  • REPLACE modification does not require any fetch, it simply obliterates the previous value in the DB.

  • ADD/DELETE modification currently requires the fetch of the index-only property, which can be achieved by selective fetch of ext→'id-of-index-only-property'. Modification is then applied to prism object as usual. Value in the DB is overwritten by the new complete value for the property (like for REPLACE).

    • In the future, ideally, this would not need any fetch access (except for reindex) and issue fine-tuned SQL operations. This may also require more flexible storage options for extensions/attributes, e.g. promoting the index-only attribute to a separate array column or separate table even.

Iterative search is a solution for long-running operations modifying many objects. Doing all the work in a single transaction is not a good design for various reasons:

  • It does not perform well, even if done on objects read iteratively (to scale without running out of memory). Long transaction my lock objects and slow down other transactions, and/or cause inconsistencies and retries. Finally, if something goes wrong on 99% the whole work still needs to be done again.

  • It causes inconsistencies as reported in MID-4414. Long database reads (recompute, recon) can return data that are outdated. It seems that the databases using READ_COMMITTED isolation will return the data as they were at the beginning of the transaction, which is the beginning of the search.

Possible solutions:

  • Search returning only OIDs - but this does not scale well. When the code gets to the last OID, the object can be changed and not matching the original search.

  • Splitting the single search to multiple searches in separate transactions. This makes the consistency problems less serious and also assures that the work progresses.

  • Due to complexity of possible work on the found objects any pure database-based solution is out of question. Sometimes we use the results for reporting, but sometimes we call modify object on each of them.

  • Optimistic locking (version check) - but if the object is modified much later this more and more likely requires operation restart.

Relativity and deltas
In theory, relativity of midPoint means that modifications do not rely on locking that much, but the single modify operation still needs to be atomic. When multiple deltas are applied to a single object, even if we don’t know or care what order they are applied, they must all be applied. It is not acceptable when only the last delta is applied to the original object. This does not mean that order of deltas is not relevant, only that all deltas must be respected. If the order is critical, then something above the repository must assure that deltas are executed in specified order.

In the end, the iterative search was introduced which loads objects using a filter, ideally in smaller batches, and uses provided handler to process each object. The handler may add it to report data or call possibly heavy-weighted modify operation on it. There are still many variables in this solution - how the transactions are organized, how the candidates (possibly millions) are iterated over (paginated), how the convergence is assured, etc.

The following solution is used for the Native repository:

  • Search uses paging based on strict OID ordering (on top of optionally provided business ordering).

  • Page is reasonably small (by default 100, can be configured) which makes the data resonably fresh.

  • Handling is called out of the reading transaction. This is good also for edge situation when the search uses last possible connection and then needs another one inside the handler. Of course this does not prevent handler from "needing" two connections and blocking because of it.

  • Even with handling running inside the search, even if cursor (live result set) was used, the data is still current only at the time of the execution, not during the cursor reading. There is no benefit from doing the work inside the transaction.

  • The problem with using some internal search for reading the whole page is that it requires more memory than executing the handler while reading the cursor. But with each page limited to small maximum size (100 by default) this is not an issue.

  • Another issue is that for complex selects the query execution can take prohibitively long to read just 100 objects. This can be prevented by avoiding complicated conditions and ordering and leaving just ordering by OID which is actually pretty fast, even if the table is scanned. Full table scan in the order of OID index can be perfectly fine if a lot of data matches the condition, in the opposite case usage of indexes is very likely.

  • Technically, cursor like reading is possible in new repository too, SQLQuery#getResults() returns normal JDBC ResultSet. To avoid blocking the connection and having long-running read-only transaction (should not be a big deal for read-only, but still) we still prefer the solution with handler running outside the search for each page when the connection is freed.

Possible inconsistencies

  • When object is handled it may have been possibly changed by some other process since read by the search iteration. While problem in theory, in practice the object is mostly fresh "enough" to compute the deltas and apply them. Applying new delta using modifyObject does not destroy any non-conflicting deltas applied in the meantime. This repository call corresponds with ModelService.executeChanges() on the model level or with functions midpoint.executeChanges() or midpoint.modifyObject() for script expressions.

  • If the fresh object is absolutely needed, one may use modifyObjectDynamically. This is new as of 4.4 and intended mostly for internal use - there is no direct alternative on the model level or a function available for script expressions.

  • There are inconsistencies between the iterations (pages) of the search. This is hardly a problem for iterative change executions, as we are concerned only with the consistency of each object. It can be problem for reports, however, but this must simply be accepted. If totally transactional reports are necessary, they must be preformed on SQL level and only the externalized data are available - this is beyond the topic of iterative search.

Developer notes

How to add a new field/column?

Example - let’s add operationalState column for m_node table.

  • Let’s start in the SQL schema, locate the table m_node and add operationalState column to it. Consider logical order of the columns - even though it’s not relevant for upgraded DB, it is relevant for future readability. Notice, how columns for items from subcontainers are grouped in existing tables. Also, we name the columns "Java style", although Postgres doesn’t care aboute casing. The same name will be used in so called "M-class" (MNode in our example). Sometimes the name contains container name prefix, this is not used for metadata, activation and similar common containers - even these are still pure camel-case names.

  • What type is it? Simple things are easy (TEXT, INTEGER), some items require multiple columns (references, poly-strings), follow the patterns used previously for such cases. Our operationalState is enum type - great! Read the next bullet how to do that. Some multi-value types can be represented by arrays like TEXT[] or JSONB columns. If we’re talking about whole new multi-value container, whoa-whoa…​ that’s out of scope of this humble instruction sheet!

  • If the type is previously unused enum, we’ve got just a bit more work to do. First we need to add CREATE TYPE for it to the schema. Find the section with custom types, read its intro comment and add the enum specification. Very likely it will be "schema enum", not specialized repository internal enum - that’s typical. Use the class name for the custom type to make things as obvious as possible, so it’s NodeOperationalStateType for our case. Copy/paste the values to avoid any mistake…​ other than copy/paste error, that is. Don’t forget to mention this enum in the SqaleRepoContext constructor. Alphabetic order, please!

  • Let’s change the "M-class", MNode in our example. Simply add public field for the column, like public NodeOperationalStateType operationalState. Keep the order consistent with the table. BTW, "M" does not stand for "mapping", we will see mapping class later.

  • Now it’s great time to update SqaleRepoAddDeleteObjectTest! Find the method testing mapping for this class and add value and assert for the new item. Feel free to add the method if this entity/object type is not yet tested - just like I did. Run the test, it should fail for the new attribute, which is a good sign.

  • We need to declare the new column in a "Q-class" which extends from Querydsl type hierarchy. Technically it’s mapping for Querydsl, but it’s still not "our" mapping for midPoint (soon, I promise!). For our example, it’s QNode - and there are two sections:

    • First, static column metadata, find good example from other class if necessary. In our case, I’ll add:

      public static final ColumnMetadata OPERATIONAL_STATE =

      For enum types we use Types.OTHER, again, see existing examples from other classes for your type.

    • Next, we add non-static column (or attribute) path:

      public final EnumPath<NodeOperationalStateType> operationalState =
        createEnum("operationalState", NodeOperationalStateType.class, OPERATIONAL_STATE);

      The name of the column (the same like the name of the field in M-class) appears twice here, because we want the same name again for Q-class paths. Previously specified column metadata are used. As before, see examples from other Q-classes to use the right create* method and path type.

    Keep the order consistent with SQL and M-class in both sections. Good, now Querydsl knows what to do with our field in the M-class.

  • Now it’s time to add the insert code. Finally, we’re getting to the "mapping class" - QNodeMapping in our case. Locate toRowObjectWithoutFullObject and add the following line there:

    row.operationalState = node.getOperationalState();

    As always, follow the order from SQL and M-class. The code for enum and many other types is as trivial as shown above, but there is great support for refs, poly-strings and many more too - just find the examples in other Q-Mapping classes.

  • Nearly there! We still need one more thing to support searching and modifications too. Go to the constructor of the mapping class (QNodeMapping for us) and add (respecting the right order again, of course!):

    addItemMapping(F_OPERATIONAL_STATE, enumMapper(q -> q.operationalState));

    I mean, seriously, can it be more auto-magical than this? It is possible to write test to this as well, but honestly, we don’t bother when adding a new mapping for well-working type. Just be sure to use the right item name (F_OPERATIONAL_STATE imported statically from NodeType), proper mapper method (enumMapper) and proper path (q.operationalState, which is that final non-static field we added on the Q-class).

  • And SQL alter script, of course! OK, the alter script is not yet in place (this is written before 4.4 is out), but find it and add the modification there using the newest fashionable method available. Hopefully, it’s something utilizing apply_change and - for your sake - same examples are already available.

To see the whole success story, check this commit (add-test was committed separately and linked previously).

Possible future improvements

Retry mechanism (if/where necessary) may not be necessary if tests don’t show that. SELECT …​ FOR UPDATE seems to do its work just fine.

Minor, tentative, questinable, don’t implement while it’s in this list:

  • Dereferencing (@) for extension refs stored in JSONB.

  • Grouping is not supported and the semantics for SQL without ORM is unclear. This is not used by midPoint itself and there are no tests for it.

  • Arbitrary ordering for iterative search - currently only one path is supported. Fixes for NULL values are needed - TODO in the code. Multi-path ordering throws - this can probably wait a bit.