PostgreSQL - batch update failed

[suchym]

Vita generated this script that failed with the message: 42601: syntax error at or near "DELETE" POSITION: 56 The generated script: UPDATE pathway."Pathway" SET "OrderId" = CAST(0 AS smallint), "ModifyDate" = '2023-02-02T14:29:25.20403', "Timestamp" = '2023-02-02T15:19:51.23444' WHERE "Id" = 1; DELETE FROM pathway."Edge" WHERE "Id" = ANY(@P0) DELETE FROM pathway."Place" WHERE "Id" = 3; DELETE FROM pathway."Transition" WHERE "Id" = 2;

I think that the problem is in the line: WHERE "Id" = ANY(@P0) - the semicolon is missing. Parameter @P0 is an integer array parameter containing two integers. The script is logically correct. Thank you, VITA. Is there any easy workaround? Thank you in advance

[rivantsov]

I will look at it, but I need some more context. How the update operation was constructed? This DELETE with Any, it looks like it is made from Linq and then scheduled for execution on SaveChanges? Pls provide some pseudo code or general sequence what was going on

[suchym]

VITA_UPDATE Script.pptx The presentation will show you very briefly what Pathway, Place, Transition, and Edge represent. In the second slide, you can see that one place and one transition, along with two edges (not visible), are removed.

The Pseudo code looks like this: Find what changed (removed place, transition, and two edges) This code is executed for each entity

public void Delete(IList<int> identifiers)
        {
            if (identifiers == null || !identifiers.Any())
                return;

            foreach (var pk in identifiers.Select(identifier => Session.CreatePrimaryKey<TEntity>(identifier)))
            {
                Delete(pk);
            }
        }

public TEntity Delete(object id)
        {
            Entity = this.GetEntity(id);
            if (Entity == null) return null;
            Session.DeleteEntity(Entity);
            return Entity;
        }

.... .... and then this line is called at the end Session.SaveChanges();

Place, transition contains one identifier and edges two, the script is generated correctly from my point of view but only the semicolon is missing. Pathway is updated due to modify date.

[suchym]

I ran the 'TestMics_DeleteMany' test and the command text is without ; You can see the delete script:

DELETE FROM misc."Driver" 
    WHERE "Id" = ANY(@P0)

In this case, missing semicolon doesn't matter.

[rivantsov]

wow, fancy chemistry! ok, got it, will look at it over weekend and push the fix

[rivantsov]

pushed fix, v 3.5.1, should be working now

[suchym]

The problem persists even after updating VITA to 3.5.1. version:

UPDATE pathway."Pathway"
    SET "ModifyDate" = '2023-02-06T19:09:20.07359', "Timestamp" = '2023-02-06T20:09:15.20968'
    WHERE "Id" = 1;
DELETE FROM pathway."Edge" 
    WHERE "Id" = ANY(@P0)
DELETE FROM pathway."Place" 
    WHERE "Id" = 3;
DELETE FROM pathway."Transition" 
    WHERE "Id" = 2;

[rivantsov]

sorry, looking at it...

[rivantsov]

fixed, pushed 3.5.2, try it

[suchym]

It works. Thank you very much for the fix.