SupportVersions does not work (suggested fix included).

[hemn]

When starting the Workflow service, we use functionality of SupportVersions:

Code: Select all

var host = new WorkflowServiceHost(service, new Uri($"{baseAdress}/"));
host.SupportedVersions.Add(supportedServiceVersion);

This functionality does not work in dotConnect for PostgreSQL.

There are two sql-functions in dotConnect that incorrectly register routes with metadata:
- InsertDefinitionIdentity(p_identityMetadata)
- CreateLockOwner(.., p_identityMetadata, ...)

These functions take only the first xml node of the <Identity> route from the metadata (p_identityMetadata), and register only it.
The solution is to register the nodes of all <Identity> routes.

Example metadata (p_identityMetadata):

Code: Select all

<IdentityMetadata>
  <IdentityCollection>
     <Identity>
         <DefinitionIdentityHash>2ae8cdc4-f5ca-3d5a-1e41-73cecbae7247</DefinitionIdentityHash>
         <DefinitionIdentityAnyRevisionHash>2ae8cdc4-f5ca-3d5a-1e41-73cecbae7247</DefinitionIdentityAnyRevisionHash>
         <Name>TaskRouteWorkflow</Name>
         <Package>TaskRouteWorkflow</Package>
        <Major>2</Major>
        <Minor>1</Minor>
         <Build>2</Build>
    </Identity>
    <Identity>
        <DefinitionIdentityHash>6641ae1b-fe42-6d2a-c0e1-7a1bb58a0f87</DefinitionIdentityHash>
        <DefinitionIdentityAnyRevisionHash>6641ae1b-fe42-6d2a-c0e1-7a1bb58a0f87</DefinitionIdentityAnyRevisionHash>
        <Name>TaskRouteWorkflow</Name>
        <Package>TaskRouteWorkflow</Package>
        <Major>2</Major>
        <Minor>1</Minor>
        <Build>3</Build>
    </Identity>
</IdentityCollection>

Everything is fine with implementation of these functions in dotConnect for Oracle, and in the standard MSSQL repository.
There all the <Identity> nodes are registered.

The problem only appears in dotConnect on PostgreSQL.
Example of a patch for PgSqlInstanceStoreLogic.sql:

Code: Select all

@@ -81,11 +81,12 @@
 $$
    SELECT ((xpath($2 || '/text()', $1))[1]::text);
 $$ LANGUAGE 'sql' IMMUTABLE;
 
 CREATE OR REPLACE FUNCTION InsertDefinitionIdentity (p_identityMetadata XML)
-  RETURNS BIGINT AS
+  RETURNS void AS
 $BODY$
-DECLARE p_surrogateIdentityId bigint;
 BEGIN
   IF (p_identityMetadata IS NOT NULL) THEN
+    INSERT INTO DefinitionIdentityTable (DefinitionIdentityHash, DIARevisionHash, Name, Package, Build, Major, Minor, Revision)
     SELECT
-      SurrogateIdentityId INTO p_surrogateIdentityId
-    FROM DefinitionIdentityTable
-    WHERE DefinitionIdentityHash = (EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/DefinitionIdentityHash'))::uuid;
-    IF (p_surrogateIdentityId IS NULL) THEN
-      INSERT INTO DefinitionIdentityTable (DefinitionIdentityHash, DIARevisionHash, Name, Package, Build, Major, Minor, Revision)
-        VALUES (EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/DefinitionIdentityHash')::uuid, EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/DefinitionIdentityAnyRevisionHash')::uuid, EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/Name'), EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/Package'), EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/Build')::int, EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/Major')::int, EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/Minor')::int, EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/IdentityCollection/Identity/Revision')::int) RETURNING surrogateidentityid INTO p_surrogateIdentityId;
-      RETURN p_surrogateIdentityId;
-    END IF;
+      EXTRACTVALUE(T.IdentityXml, '/Identity/DefinitionIdentityHash')::uuid,
+      EXTRACTVALUE(T.IdentityXml, '/Identity/DefinitionIdentityAnyRevisionHash')::uuid,
+      EXTRACTVALUE(T.IdentityXml, '/Identity/Name'),
+      EXTRACTVALUE(T.IdentityXml, '/Identity/Package'),
+      EXTRACTVALUE(T.IdentityXml, '/Identity/Build')::integer,
+      EXTRACTVALUE(T.IdentityXml, '/Identity/Major')::integer,
+      EXTRACTVALUE(T.IdentityXml, '/Identity/Minor')::integer,
+      EXTRACTVALUE(T.IdentityXml, '/Identity/Revision')::integer
+       FROM (SELECT unnest(xpath('/IdentityMetadata/IdentityCollection/Identity', p_identityMetadata))::xml as IdentityXml) as T
+       WHERE EXTRACTVALUE(T.IdentityXml, '/Identity/DefinitionIdentityHash')::uuid NOT IN (SELECT DefinitionIdentityHash FROM DefinitionIdentityTable);
   END IF;
-  RETURN p_surrogateIdentityId;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE;
 
 CREATE OR REPLACE FUNCTION CreateLockOwner (p_lockOwnerId UUID,
@@ -116,2 +117,1 @@
 p_MachineName VARCHAR(128),
 p_identityMetadata xml)
   RETURNS BIGINT AS
 $BODY$
   DECLARE p_LockExpiration TIMESTAMP;
-  DECLARE p_surrogateIdentityId BIGINT;
   DECLARE p_workflowIdentityFilter SMALLINT;
   DECLARE p_surrogateLockOwnerId BIGINT;
 BEGIN
-  SELECT InsertDefinitionIdentity(p_identityMetadata) INTO p_surrogateIdentityId;
+  PERFORM InsertDefinitionIdentity(p_identityMetadata);
 
   IF (p_identityMetadata IS NOT NULL) THEN
     SELECT EXTRACTVALUE(p_identityMetadata, '/IdentityMetadata/WorkflowIdentityFilter')::SMALLINT INTO p_workflowIdentityFilter;
   END IF;
 
@@ -139,1 +139,4 @@
   END IF;
 
   INSERT INTO LockownersTable (Id, LockExpiration, MachineName, WorkflowHostType, EnqueueCommand, DeletesInstanceOnCompletion, PrimitiveLockOwnerData, ComplexLockOwnerData, WOPrimitiveLockOwnerData, WOComplexLockOwnerData, EncodingOption, WorkflowIdentityFilter)
     VALUES (p_lockOwnerId, p_lockExpiration, p_machineName, p_workflowHostType, p_enqueueCommand, p_deletesInstanceOnCompletion, p_primitiveLockOwnerData, p_complexLockOwnerData, p_WOPrimitiveLockOwnerData, p_WOComplexLockOwnerData, p_encodingOption, p_workflowidentityfilter) RETURNING surrogatelockownerid INTO p_surrogateLockOwnerId;
   IF (p_identityMetadata IS NOT NULL) THEN
-    INSERT INTO IdentityOwnerTable (SurrogateIdentityId, SurrogateLockOwnerId) VALUES (p_surrogateIdentityId, p_surrogateLockOwnerId);
+    INSERT INTO IdentityOwnerTable (SurrogateIdentityId, SurrogateLockOwnerId)
+    SELECT SurrogateIdentityId, p_surrogateLockOwnerId
+    FROM DefinitionIdentityTable
+    WHERE DefinitionIdentityHash in (SELECT unnest(xpath('/IdentityMetadata/IdentityCollection/Identity/DefinitionIdentityHash/text()', p_identityMetadata))::text::uuid);
   ELSE
     INSERT INTO IdentityOwnerTable (SurrogateIdentityId, SurrogateLockOwnerId)
       SELECT
         SurrogateIdentityId,
         p_surrogateLockOwnerId
@@ -975,1 +978,1 @@
         RETURN;
       ELSE
         SELECT COUNT(*) INTO p_count FROM DefinitionIdentityTable WHERE DefinitionIdentityHash = p_definitionIdentityHash;
         IF p_count = 0 THEN
           -- insert the new identity
-          SELECT InsertDefinitionIdentity(p_identityMetadata) INTO p_count;
+          PERFORM InsertDefinitionIdentity(p_identityMetadata);
         END IF;
         SELECT SurrogateIdentityId INTO p_surrogateIdentityId FROM DefinitionIdentityTable WHERE DefinitionIdentityHash = p_definitionIdentityHash;
       END IF;
     END IF;

[Shalex]

Thank you for your report and suggestion. We will investigate the question and notify you about the result.