AW: posgres 12 bug (partitioned table)

Hi Community

Related to ef core I found another issue. I try to convert a database column from bool to int with ef core migrations.
So if I generate the migration file it will look like as following: ALTER TABLE "Customer" ALTER COLUMN "State" TYPE INT
But in Postgres this statement need the “using” extension so it should look like this: ALTER TABLE " Customer" ALTER COLUMN " State " TYPE INT USING " State"::integer;

In my opinion ef core should be generate the right statement (with using statements) out of the box, so is there an issue with the NPG Provider?

Thank you in advance.

Kind regards
Kenan

Von: Wilm Hoyer <W(dot)Hoyer(at)dental-vision(dot)de>
Gesendet: Mittwoch, 12. August 2020 11:35
An: Pavel Biryukov <79166341370(at)yandex(dot)ru>; Andres Freund <andres(at)anarazel(dot)de>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Robert Haas <robertmhaas(at)gmail(dot)com>; PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Betreff: AW: posgres 12 bug (partitioned table)

Hi,

while the request for returning xmin of partitioned tables is still valid, i’d like to add some information and a possible workaround.

On 2020-08-11 21:31:52 +0300, Pavel Biryukov wrote:
Entity Framework is an ORM for .Net (and .Net Core). It has different providers
for different databases (NpgSql for Postgres). It uses Optimistic concurrency.
The common use case is to use xmin as "concurrency token".

In code we make "var e = new Entity();", "dbContext.Add(e)" and
"dbContext.SaveChanges()" (smth like that), and EF Core constructs sql for us,
classical ORM;

When new row is inserted, EF makes an insert with "RETURNING xmin" to keep it
as concurrency token for further updates (update is made like "where id = [id]
AND xmin=[xmin]" to be sure the row hasn't been updated by other clients).

Neither the Entity Framework, nor npgsql rely on the column xmin. Both don’t know about this column in their codebase.
In the case oft he EF i’m sure that this holds true for all versions, since it is designed as DBMS independant, and as such will never know anything about a PostgreSQL specific column.
Also you can use any ADO.Net provider to connect to a concrete DBMS – i for example use dotConnect for PostgreSQL because it provided more features and less bugs as Npgsql at the time of decission.
As for Npgsql i have only checked that the current HEAD has no reference to xmin in its source code.

With that in mind, i assume the OP included the column xmin in his Entity Model by himself and set the ConcurrencyMode to fixed for that column.
As xmin is a system column that the EF should never try to update (PostgreSQL will reject this attempt, i think), i’d suggest using a self defined column (row_version for example) and either use triggers on update and insert to increment its value (works even with updates outside of EF) or let the EF do the increment.

Regards
Wilm Hoyer.