single call of a deferred update trigger

adrianf0

I have an extension application providing the relation extending the one in the base application (lsw_absence.absence). Logically, the data should be stored in a single relation. However, as it's not possible to extend a relation from another application, in the implementation, the extending application comes with a new 1:1 relation referencing the base one.

An update operation of a record stored across those two relations should fire a deferred update trigger only once per row. However, if data in both relations is changed simultaneously, the trigger will fire twice. Before trying db-costly solutions like temporary tables/session variables (to avoid duplicate execution of the trigger), is there any methodology recommended for REI3 users to handle such cases?

gabriel

Are you running the same trigger for both relations (original + extended)? In this case, the same trigger would fire twice, as it reacts to updates in both.

You can also register separate triggers for each releation. They would then fire independently from each other, and more importantly, both would fire only once.

adrianf0

On an update event of a logical single record distributed over two relations, I want to call a backend method (it's a REST API call synchronizing data with the external server). This synchronization method can be called only once per record. If the update change involves only one of two relations, it's ok, the synchronization method will be called once by a trigger of the first or the second relation. If the single update involves data in the two relations, two triggers will fire and the synchronization method needs to distinguish if was called already in the given SQL transaction (I understand that REI3, on the save event, executes such two SQL tables updates under a single SQL transaction, right?).

What adds complexity is the asynchronous nature of the REST calls in REI3. Even if the synchronization call is implemented as an idempotent operation (it produces the same result regardless of how many times it's executed), as the method is not an atomic request to the foreign server, when called twice in parallel it may produce data inconsistency (the proper handling would probably require a locking mechanism).

Thus, I was wondering if any methodology is recommended to REI3 developers to cover such scenarios.

gabriel

Yes, if two relations are updated in the same "save", it will stay within the same transaction. The same holds true for all changes done via triggers before/afterwards.

If your calls, triggered via multiple trigger events, are not necessarily idempotent, you are right and need to synchronize them. I do not have a good answer. My initial idea would be to spool these changes - eg. write from both triggers to a a spooler relation (which is safe as one or both writes happen within the same transaction) and then regulary check this relation for new data. You can then safely execute the rest call, clear the spooler entry and repeat. It is not amazing and has a little overhead due to the scheduler involved, but would solve your issue.