PostgresException 42804 UNION with xid and text not match on include

[willignicolas]

Hello,

We try to migrate from MSSQL to Postgresql on our EntityFramework 6 code first application.

We have migrate a ByteArray rowversion field in MSSQL to the xmin field mapped on a string property on the EF model by follow this documentation : https://www.npgsql.org/ef6/index.html#optimistic-concurrency

Insert, update and select works fine. But in a few case with multiple include we have the error :

PostgresException: 42804: les UNION types xid et text ne peuvent pas correspondre

Same error on PGAdmin if I log the generated entityframework sql query but with a few more details

ERROR: ERREUR: les UNION types xid et text ne peuvent pas correspondre LINE 177: CAST (NULL AS text) AS "C13",

Have you any idea how we can resolved this issue ?

Thanks.

Threre is the full generated sql query :

SELECT 
    "UnionAll1"."C2" AS "C1",
    "UnionAll1"."patientid" AS "C2",
    "UnionAll1"."patientus" AS "C3",
    "UnionAll1"."patientdt" AS "C4", 
    "UnionAll1"."structureid" AS "C5",
    "UnionAll1"."favoriteprescriberid" AS "C6",
    "UnionAll1"."amosupportid" AS "C7", 
    "UnionAll1"."attestationamcsupportid" AS "C8",
    "UnionAll1"."vitaleamcsupportid" AS "C9",
    "UnionAll1"."kindtype" AS "C10",
    "UnionAll1"."firstname" AS "C11", 
    "UnionAll1"."lastname" AS "C12",
    "UnionAll1"."address1" AS "C13",
    "UnionAll1"."address2" AS "C14",
    "UnionAll1"."phonenumber" AS "C15",
    "UnionAll1"."cellnumber" AS "C16",
    "UnionAll1"."socialsecuritynumber" AS "C17",
    "UnionAll1"."birthday" AS "C18",
    "UnionAll1"."birthrank" AS "C19",
    "UnionAll1"."beneficiarytype" AS "C20", 
    "UnionAll1"."parentid" AS "C21",
    "UnionAll1"."comment" AS "C22",
    "UnionAll1"."notes" AS "C23",
    "UnionAll1"."vitaleread" AS "C24",
    "UnionAll1"."creationdt" AS "C25", 
    "UnionAll1"."zoneid" AS "C26",
    "UnionAll1"."longitude" AS "C27",
    "UnionAll1"."latitude" AS "C28",
    "UnionAll1"."isvulnerable" AS "C29",
    "UnionAll1"."deathdate" AS "C30", 
    "UnionAll1"."invalid" AS "C31",
    "UnionAll1"."invaliditystartdate" AS "C32",
    "UnionAll1"."invalidityenddate" AS "C33",
    "UnionAll1"."C3" AS "C34",
    "UnionAll1"."C4" AS "C35", 
    "UnionAll1"."certifiednir" AS "C36",
    "UnionAll1"."albusoinspatientid" AS "C37",
    "UnionAll1"."email" AS "C38",
    "UnionAll1"."localityid" AS "C39",
    "UnionAll1"."xmin" AS "C40", 
    "UnionAll1"."C1" AS "C41",
    "UnionAll1"."pathologyhistoryid" AS "C42",
    "UnionAll1"."pathologyhistoryus" AS "C43",
    "UnionAll1"."pathologyhistorydt" AS "C44", 
    "UnionAll1"."pathologyid" AS "C45",
    "UnionAll1"."patientid1" AS "C46",
    "UnionAll1"."enddate" AS "C47",
    "UnionAll1"."startdate" AS "C48",
    "UnionAll1"."manuallycreated" AS "C49", 
    "UnionAll1"."xmin1" AS "C50",
    "UnionAll1"."C5" AS "C51",
    "UnionAll1"."C6" AS "C52",
    "UnionAll1"."C7" AS "C53",
    "UnionAll1"."C8" AS "C54",
    "UnionAll1"."C9" AS "C55", 
    "UnionAll1"."C10" AS "C56",
    "UnionAll1"."C11" AS "C57",
    "UnionAll1"."C12" AS "C58",
    "UnionAll1"."C13" AS "C59",
    "UnionAll1"."C14" AS "C60" 
    FROM ((SELECT  
        CASE  WHEN ("Extent2"."pathologyhistoryid" IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END  AS "C1",
        1 AS "C2",
        "Extent1"."patientid", 
        "Extent1"."patientus",
        "Extent1"."patientdt",
        "Extent1"."structureid",
        "Extent1"."favoriteprescriberid", 
        "Extent1"."amosupportid",
        "Extent1"."attestationamcsupportid",
        "Extent1"."vitaleamcsupportid",
        "Extent1"."kindtype",
        "Extent1"."firstname", 
        "Extent1"."lastname",
        "Extent1"."address1",
        "Extent1"."address2",
        "Extent1"."phonenumber",
        "Extent1"."cellnumber",
        "Extent1"."socialsecuritynumber", 
        "Extent1"."birthday",
        "Extent1"."birthrank",
        "Extent1"."beneficiarytype",
        "Extent1"."parentid",
        "Extent1"."comment",
        "Extent1"."notes", 
        "Extent1"."vitaleread",
        "Extent1"."creationdt",
        "Extent1"."zoneid",
        "Extent1"."longitude",
        "Extent1"."latitude",
        "Extent1"."isvulnerable", 
        "Extent1"."deathdate",
        "Extent1"."invalid",
        "Extent1"."invaliditystartdate",
        "Extent1"."invalidityenddate",
        CAST ("Extent1"."gender" AS int2) AS "C3", 
        CAST ("Extent1"."situation" AS int2) AS "C4",
        "Extent1"."certifiednir",
        "Extent1"."albusoinspatientid",
        "Extent1"."email",
        "Extent1"."localityid",
        "Extent1"."xmin", 
        "Extent2"."pathologyhistoryid",
        "Extent2"."pathologyhistoryus",
        "Extent2"."pathologyhistorydt",
        "Extent2"."pathologyid",
        "Extent2"."patientid" AS "patientid1", 
        "Extent2"."enddate",
        "Extent2"."startdate",
        "Extent2"."manuallycreated",
        "Extent2"."xmin" AS "xmin1",
        CAST (NULL AS uuid) AS "C5",
        CAST (NULL AS uuid) AS "C6", 
        CAST (NULL AS timestamp) AS "C7",
        CAST (NULL AS timestamp) AS "C8",
        CAST (NULL AS timestamp) AS "C9",
        CAST (NULL AS int2) AS "C10",
        CAST (NULL AS text) AS "C11", 
        CAST (NULL AS uuid) AS "C12",
        CAST (NULL AS bool) AS "C13",
        CAST (NULL AS text) AS "C14" 
        FROM "public"."patient" AS "Extent1" 
        LEFT OUTER JOIN "public"."pathologyhistory" AS "Extent2" ON "Extent1"."patientid" = "Extent2"."patientid" 
        WHERE "Extent1"."patientid" IN ('68c9d3d6-9107-444f-aade-93ca4c9355af'::uuid)) 
    UNION ALL (
        SELECT 
        2 AS "C1",
        2 AS "C2",
        "Extent3"."patientid",
        "Extent3"."patientus",
        "Extent3"."patientdt",
        "Extent3"."structureid",
        "Extent3"."favoriteprescriberid", 
        "Extent3"."amosupportid",
        "Extent3"."attestationamcsupportid",
        "Extent3"."vitaleamcsupportid",
        "Extent3"."kindtype",
        "Extent3"."firstname",
        "Extent3"."lastname", 
        "Extent3"."address1",
        "Extent3"."address2",
        "Extent3"."phonenumber",
        "Extent3"."cellnumber",
        "Extent3"."socialsecuritynumber",
        "Extent3"."birthday", 
        "Extent3"."birthrank",
        "Extent3"."beneficiarytype",
        "Extent3"."parentid",
        "Extent3"."comment",
        "Extent3"."notes",
        "Extent3"."vitaleread",
        "Extent3"."creationdt", 
        "Extent3"."zoneid",
        "Extent3"."longitude",
        "Extent3"."latitude",
        "Extent3"."isvulnerable",
        "Extent3"."deathdate",
        "Extent3"."invalid",
        "Extent3"."invaliditystartdate", 
        "Extent3"."invalidityenddate",
        CAST ("Extent3"."gender" AS int2) AS "C3",
        CAST ("Extent3"."situation" AS int2) AS "C4",
        "Extent3"."certifiednir", 
        "Extent3"."albusoinspatientid",
        "Extent3"."email",
        "Extent3"."localityid",
        "Extent3"."xmin",
        CAST (NULL AS uuid) AS "C5",
        CAST (NULL AS uuid) AS "C6", 
        CAST (NULL AS timestamp) AS "C7",
        CAST (NULL AS uuid) AS "C8",
        CAST (NULL AS uuid) AS "C9",
        CAST (NULL AS timestamp) AS "C10",
        CAST (NULL AS timestamp) AS "C11", 
        CAST (NULL AS bool) AS "C12",
        CAST (NULL AS text) AS "C13",
        "Extent4"."absenceid",
        "Extent4"."absenceus",
        "Extent4"."absencedt",
        "Extent4"."startdatetime", 
        "Extent4"."enddatetime",
        "Extent4"."reason",
        "Extent4"."reasontext",
        "Extent4"."patientid" AS "patientid1",
        "Extent4"."deleterdvduringabsence", 
        "Extent4"."xmin" AS "xmin1" FROM "public"."patient" AS "Extent3" 
        INNER JOIN "public"."absence" AS "Extent4" ON "Extent3"."patientid" = "Extent4"."patientid" 
        WHERE "Extent3"."patientid" IN ('68c9d3d6-9107-444f-aade-93ca4c9355af'::uuid))) AS "UnionAll1" 
ORDER BY "UnionAll1"."patientid" ASC, "UnionAll1"."C1" ASC 

[Emill]

The xid type is internally represented using a 32-bit unsigned integer (which is also not supported by EF6). Could you maybe try to represent it as a 32-bit signed integer instead of a string?

[Emill]

But the best way would be to try to avoid using xmin and instead use a custom field, I would say.

[willignicolas]

@Emill Thanks for your reply.

Sadly using a 32-bit unsigned integer generate error on insert, update and select.

And for now we have not find a alternative of xmin for our needs.

The MSSQL Timestamp field value is generated by the database. The value is unique across all the table of the database and allow concurrency check.

We need all this features because we have a mobile application that synchronize datas and work on disconnected mode.

xmin was naturally our target to replace MSSQL timestamp field because it match our requirements.

[Emill]

If you are about to migrate your setup anyway, maybe move over to the newer EF Core? EF6 is pretty old and very limited in terms of data type possibilities.

[willignicolas]

I see your point of view but migrate to Core our application is an another step of work that we cannot make yet.

[willignicolas]

No help from the NPGSQL team ?

It seems that is clearly a bug from the entityframework driver and the query generation.

Thanks for your help

[roji]

@willignicolas The EF6 provider is no longer being actively developed (like EF6 itself), and in any case, @Emill knows as much as anyone in the team here, probably more :)

[Emill]

I would rather say it's a limitation in the core of Entity Framework 6, i.e. that you cannot have any other data types for your fields than the basic types int, string, date, etc. We have tried working around this as much as possible; for simple queries using "text" for unknown data types works. This is the case when the query can be written in such a way that the data type does not need to be enforced as when the server can infer the type from the context, i.e. when quotes can be used without specifying a type, as 'unknown type'. For more advanced queries, PostgreSQL requires specifying the type using a cast like CAST ('unknown type' AS text) since Npgsql uses the binary protocol. In that case EF6 breaks if you have lied about the data type.

[willignicolas]

ok thanks for your reply and your help.

[willignicolas]

Hello @Emill

We explore some other solution like sequence and put nextval on default value of create table (we don't use code first migration)

CREATE TABLE Absence( AbsenceId [xxxxxx] NOT NULL PRIMARY KEY, AbsenceTs bigint NOT NULL default nextval(' rowversion'), ...

Now we try to find a solution to use nextval on update generated query like this :

Update Account Set AccountTs= nextval('seqname'), ... where AbsenceId=<value> and AccountTs=<value> ;

Is there any solution to tell EF6 Npgsql provider to use "nextval" function on all update for the AccountTs property ?

Thanks for your help.

[Emill]

Not what I know of. I think you should be able to define a Trigger directly in PostgreSQL though to do this on every update.

[willignicolas]

Ok thank you for your reply.