Adapter data not mapping to locker during baseline sync

Yes, there is a second link/adapter which can contribute this data - there are two inbound paths, one for pending user data (Aurion Pending Employee > Employee) and one for current user data (Aurion Employee > Employee). There would have been a pending user data entity joined to the locker many months ago (before the user first started) but it has since been deleted since the user commenced work and moved to the adapter which isn't updating.

When I look at the pending user link (Aurion Pending Employee > Employee) I see a join on the locker's "Delete Join" screen, but no corresponding one on the adapter's screen. So I am guessing the locker join hasn't been cleaned up when the adapter entity was deleted, and the (higher priority) 'phantom join' is blocking the flow of data. What do you think?

That could be the case. A way to check - if possible - would be to delete that 'phantom join' (as long as you can see an appropriate join on the other link) and run another baseline sync to see if the correct data flows for that particular locker entity.

Can you share the join strategy that is being used in this environment? And do you know the normal flow of data for a user transitioning from pending to active? Would they exist in both adapters before the pending one disappears, or would the pending disappear before they're found in the active adapter?

All links are using the connection-oriented join strategy.

After deleting he locker-only join on the 'Aurion Pending Employee > Employee' link and running a baseline sync on the 'Aurion Pending Employee > Employee' link, the locker-only join on the 'Aurion Pending Employee > Employee' link is recreated and the field in the locker retains the old value from six months ago.

After deleting the locker-only join on the 'Aurion Pending Employee > Employee' link and running a baseline sync on the 'Aurion Employee > Employee' link, the locker-only join on the 'Aurion Pending Employee > Employee' link is recreated and the field in the locker retains the old value from six months ago.

I don't know if the 'pending' user record is deleted before or after the 'current' user record appears.  I do know that current users who terminate remain in the 'current' adapter (with terminated details) and when they are rehired they reappear in the 'pending' adapter simultaneously, which is why the pending data is given priority.

Perfect, thanks. And can you confirm (just for posterities sake) the join criteria between 'Aurion Pending Employee' adapter and 'Employee' locker, and that there's no entity in the 'Aurion Pending Employee' adapter for the locker entity on that join criteria?

Aurion Pending Employee > Employee link join criteria is same, and there is no existing adapter entity matching the locker's join criteria on that link.

Hi Adrian

I've been looking into this issue and am documenting my findings here, as well as providing some options for moving forward. If you have any questions about this, feel free to ask.

Reproduction

This issue is easily and consistently reproducible. The process to follow to reproduce is

1. Create two connectors, A and B, with the same schema.
2. Create adapters for the connectors
3. Create a locker with the same schema as the connectors/adapters
4. Create links for both adapters with the following settings, configured to allow inbound synchronization from the adapters to the locker for all fields. Incoming de-provisioning disabled. On the locker, configure the priorities so that adapter A has priority over adapter B for all fields.
5. Add an entity to the data source for connector A, import the connector and run a sync on the associated link so that the entity is provisioned into the locker
6. Remove the entity from data source of connector A, and add it to the data source for connector B. Change value of one of the fields.
7. Import both connectors, then run a sync on link A, then on link B. If link B is run first, run a baseline sync on it after the sync on Link A completes.
8. In the entity viewer for the locker, view the origin information for the moved entity. For all fields, only a single ancestor should be listed, and the referenced entity should not exist. The change field still has the old value.

Quick Fix

The following query will update all locker origin information, removing priority calculation values for those that came from adapter entities that no longer exist. If this fix is to be used, please run the inner SELECT query first and verify that only the appropriate locker entity origin information will be updated.

UPDATE "LockerOrigin"
SET "SourcePartitionId" = null,
    "SourceEntityId" = null,
    "SourceField" = null
WHERE "LockerOriginKey" IN (
    SELECT lo."LockerOriginKey"
    FROM "LockerOrigin" as lo
    LEFT JOIN "Entity" as e
        ON lo."SourcePartitionId" = e."PartitionId"
        AND lo."SourceEntityId" = e."EntityId"
    WHERE lo."SourceEntityId" is not null
    AND e."EntityId" is null
);

After running this command, run a baseline sync on the users link and the locker entity should be updated correctly. The origin information view in the UI will be empty any field that isn't updated with a different value to what it already had.

Full Fix

The full fix for this issue are updated versions of several PostgreSQL function. There are no changes required to the Broker binaries, and thus no patch dlls, however these changes should still be handled in the same way, as they are still modifying the logical processes of the application.

The following commands will update four functions so that origin information relating to removed source entities will not be considered in field priority calculations. Ensure that no synchronizations are in progress while running these commands; stopping Broker would be ideal. If rollback is needed, the original versions can be found in <installDir>\Database\IdentityBroker Install PostgreSql.sql.

CREATE OR REPLACE FUNCTION public.get_entity_id_origins(
    entity_ids uuid[])
    RETURNS SETOF "Origin" 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
    BEGIN
        RETURN QUERY (
            SELECT o.*
            FROM "Origin" as o
            LEFT JOIN "LockerEntity" as le 
                ON o."SourcePartitionId" = le."LockerPartitionId"
                AND o."SourceEntityId" = le."LockerEntityId"
            WHERE (o."SourceEntityId" is null or le."LockerEntityId" is not null)
            AND o."EntityId" = ANY(entity_ids)
        );
    END;
$BODY$;

CREATE OR REPLACE FUNCTION public.get_entity_id_field_origins(
    entity_id uuid,
    field text)
    RETURNS SETOF "Origin" 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$

    BEGIN
        RETURN QUERY (
            SELECT o.*
            FROM "Origin" as o
            LEFT JOIN "LockerEntity" as le 
                ON o."SourcePartitionId" = le."LockerPartitionId"
                AND o."SourceEntityId" = le."LockerEntityId"
            WHERE (o."SourceEntityId" is null or le."LockerEntityId" is not null)
            AND o."EntityId" = entity_id
            AND o."Field" = field
        );
    END;
$BODY$;

CREATE OR REPLACE FUNCTION public.get_locker_entity_id_origins(
    entity_ids uuid[])
    RETURNS SETOF "LockerOrigin" 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
    BEGIN
        RETURN QUERY (
            SELECT lo.*
            FROM "LockerOrigin" as lo
            LEFT JOIN "Entity" as e 
                ON lo."SourcePartitionId" = e."PartitionId"
                AND lo."SourceEntityId" = e."EntityId"
            WHERE (lo."SourceEntityId" is null OR e."EntityId" is not null)
            AND lo."EntityId" = ANY(entity_ids)
        );
    END;
$BODY$;

CREATE OR REPLACE FUNCTION public.get_locker_entity_id_field_origins(
    entity_id uuid,
    field text)
    RETURNS SETOF "LockerOrigin" 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
    BEGIN
        RETURN QUERY (
            SELECT lo.*
            FROM "LockerOrigin" as lo
            LEFT JOIN "Entity" as e 
                ON lo."SourcePartitionId" = e."PartitionId"
                AND lo."SourceEntityId" = e."EntityId"
            WHERE (lo."SourceEntityId" is null OR e."EntityId" is not null)
            AND lo."EntityId" = entity_id
            AND lo."Field" = field
        );
    END;
$BODY$;

After running these commands, run a baseline sync on the users link and the locker entity should be updated correctly, and the issue should not reoccur. The origin information view in the UI will continue to display incorrectly for any field that isn't updated with a different value to what it already had. This is something we're aiming to improve in Broker v6.

Thanks Beau, nice work.  I'll get it installed and tested and let you know how awesome you are :)

Hi Beau,

UNIFY Platform & Automation team applied the 'Full Fix' patch in my customer's DEV environment and when running the baseline sync the following error appears:

Npgsql.PostgresException (0x80004005): 42703: column le.PartitionId does not exist
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlDataReader.d__46.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.d__100.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior)
   at Dapper.SqlMapper.d__138`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
   at Unify.Framework.Data.PostgreSqlContextBase`2.InnerStoredProcedure(IDbConnection connection, String procedure, Object arguments, Nullable`1 timeout)
   at Unify.Framework.Data.PostgreSqlContextBase`2.StoredProcedure(String procedure, Object arguments, Nullable`1 timeout)
   at Unify.Product.Plus.PriorityRepository.<>c__DisplayClass14_0.b__0(IEnumerable`1 targetKeys)
   at Unify.Product.Plus.LinkSynchronizer`2.JoinAndMap(IEnumerable`1 filterResult, IDictionary`2 changesDict, IConnection[] pendingDeprovisionConnections)
   at Unify.Product.Plus.Link.SynchronizeLockerChanges(IEnumerable`1 changes)
   at Unify.Framework.Notification.NotifierDecoratorBase.Notify[TResult](ITaskNotificationFactory notificationFactory, Func`1 function)
   at Unify.Product.Plus.LinkAuditingDecorator.SynchronizeLockerChanges(IEnumerable`1 changes)
   at Unify.Product.Plus.LockerToAdapterSynchronizationJob.RunBase()
   at Unify.Product.Plus.SynchronizationJobExecutor.d__8.MoveNext()",Normal
20230220,23:18:30,UNIFYBroker,Void MoveNext(),Error,"Npgsql:
Npgsql.PostgresException (0x80004005): 42703: column le.PartitionId does not exist
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlDataReader.d__46.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.d__100.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior)
   at Dapper.SqlMapper.d__138`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
   at Unify.Framework.Data.PostgreSqlContextBase`2.InnerStoredProcedure(IDbConnection connection, String procedure, Object arguments, Nullable`1 timeout)
   at Unify.Framework.Data.PostgreSqlContextBase`2.StoredProcedure(String procedure, Object arguments, Nullable`1 timeout)
   at Unify.Product.Plus.PriorityRepository.<>c__DisplayClass14_0.b__0(IEnumerable`1 targetKeys)
   at Unify.Product.Plus.LinkSynchronizer`2.JoinAndMap(IEnumerable`1 filterResult, IDictionary`2 changesDict, IConnection[] pendingDeprovisionConnections)
   at Unify.Product.Plus.Link.SynchronizeLockerChanges(IEnumerable`1 changes)
   at Unify.Framework.Notification.NotifierDecoratorBase.Notify[TResult](ITaskNotificationFactory notificationFactory, Func`1 function)
   at Unify.Product.Plus.LinkAuditingDecorator.SynchronizeLockerChanges(IEnumerable`1 changes)
   at Unify.Product.Plus.LockerToAdapterSynchronizationJob.RunBase()
   at Unify.Product.Plus.SynchronizationJobExecutor.d__8.MoveNext()",Normal

Sorry about that. My previous comment has been updated with the correct commands.

The new code runs in DEV and I validated correct data for a number of test users, but I don't have data in this environment which was showing the problem.  I've requested deployment of the patch to PROD so it can be tested (there is no TEST environment for this customer).