Usage of xmin column as concurrency token

[roji]

From @roji on April 4, 2016 8:7

Although PostgreSQL has no native support for computed columns, tables implicitly include an xmin system column which can be used as a rowversion or concurrency token. Probably need to treat it as a shadow property.

Copied from original issue: npgsql/npgsql#1016

[roji]

Submitted dotnet/efcore#5367

[roji]

Reopening to introduce an extension method which would allow the following:

modelBuilder.Entity<Chassis>().UseXminAsConcurrencyToken();

As a shortcut for:

modelBuilder.Entity<Chassis>(b =>
{
    b.Property<uint>("xmin")
        .HasColumnType("xid")
        .ValueGeneratedOnAddOrUpdate()
        .IsConcurrencyToken();
});

[jgilm]

Using the extension method does not work for Code First migrations. It causes the column to be added in the migration which causes the migration to fail. For example:

            migrationBuilder.AddColumn<uint>(
                name: "xmin",
                table: "testtable",
                type: "xid",
                nullable: false,
                defaultValue: 0u);

I tried adding .Ignore("xmin"); which does not work.

It is possible to manually remove the AddColumn reference from the migration and then the concurrency token concept works. It would be nice if the migration did not include that column at all.

[roji]

An AddColumn migration may get created, but it should get ignored by the migration SQL generator (so there will be no DDL which attempts to create the column). What error exactly did you get?

[jgilm]

Sorry, I should have been more thorough. This is the error I get in the package manager console when applying the migration (via Update-Database) that only adds the xmin column:

Applying migration '20170112163950_xmin'.
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "testtable" ADD "xmin" xid NOT NULL DEFAULT 0;
Npgsql.PostgresException: 42701: column name "xmin" conflicts with a system column name
   at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage)
   at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode)
   at Npgsql.NpgsqlDataReader.NextResultInternal()
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteNonQueryInternal()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
42701: column name "xmin" conflicts with a system column name

I'm using Npgsql.EntityFrameworkCore.PostgreSQL (and .Design) nuget v1.1.0. I have the models, migrations, and DbContext within a .Net core class library and using the class library in a .net core console app (if any of that matters).

For the above migration, the only configuration change is adding entityBuilder.ForNpgsqlUseXminAsConcurrencyToken(); to one table. If I manually take out the AddColumn from the migration, the migration succeeds (because there is now nothing in the Up() method) and the concurrency token works correctly.

[sunliil]

I have same issue as @jgilm has. Is it ok now to remove AddColumn() with "xmin" from migration?

[roji]

@sunliil and @jgilm, sorry this hasn't received attention sooner (have been super busy with the Npgsql 3.2.0 release). Can you please open a new issue for this?

[jgilm]

I've added a new issue #145 with the comments copied. Thanks!

[roji]

@jgilm thanks, I'll try to get around to this soon.

@sunliil it's perfectly fine to manually remove the AddColumn migration.

[SuperJMN]

Oops, I have just called this method and I'm getting added the xmin column in a migration!

            migrationBuilder.AddColumn<uint>(
                name: "xmin",
                schema: "SGDTP",
                table: "T_TRANSICIONES_ESTADOS",
                type: "xid",
                nullable: false,
                defaultValue: 0u);

I thought this was fixed long ago!

[sreeky]

Any one has any example code sample of implementing the concurrency in ef core with generic repository. Please share.

[roji]

Guys - as I wrote above, the migration may get generated, but it should be ignored when you actually run it. Have you actually tried to update the database, are you seeing an error?

[R-W-C]

Hi,

What is exactly required to have optimistic concurrency with Entity Framework Core 2.0 and PostgreSql?

Calling UseXminAsConcurrencyToken() and catch exceptions of type DbUpdateConcurrencyException (with a retry mechanism) doesn't seem to be sufficient.

Should I do anything else with xmin?

At roji (or somebody else): Can you provide a small example?

[roji]

Calling UseXminAsConcurrencyToken() and catch exceptions of type DbUpdateConcurrencyException (with a retry mechanism) doesn't seem to be sufficient.

Why not exactly? What exactly are you expecting out of optimistic concurrency beyond an exception when a concurrent update occurs?

xmin is only an auto-updating column that allows EF Core to detect that a change occurred by someone else - you're definitely not supposed to examine it yourself or do anything with it. Read the docs for more detail on optimistic concurrency.

[WhatFreshHellIsThis]

@roji you definitely need to examine and "do things" with the concurrency token in an asp.net webapi scenario. You seem to be forgetting web server scenarios where the client must provide the concurrency token, which means it needs to get it, store it and send it back to be rehydrated at the server so that concurrency checking can work.

This also might explain why I'm struggling to get this ForNpgsqlUseXminAsConcurrencyToken() method to work if it was written without regard to disconnected client scenarios as is common with asp.net applications.

[WhatFreshHellIsThis]

I couldn't get the ForNpgsqlUseXminAsConcurrencyToken() method to work with a disconnected WebApi client and migrations are not an issue for me, but for any future users looking into this ...

This worked for me with a web api client supplying the token on update:

Model:

 public partial class Widget
    {
        public long Id { get; set; }
        public uint ConcurrencyToken { get; set; }
        public string Name { get; set; }
...etc...

In OnModelCreating:

 modelBuilder.Entity<Widget>()
                .Property(e => e.ConcurrencyToken)
                .HasColumnName("xmin")
                .HasColumnType("xid")
                .ValueGeneratedOnAddOrUpdate()
                .IsConcurrencyToken();

And in the controller this will update the original value of the concurrency token to trigger checking:

 ct.Entry(oFromDb).OriginalValues["ConcurrencyToken"]=oIn.ConcurrencyToken;

[roji]

@WhatFreshHellIsThis I understand what you're saying, and your sample should definitely work, can you please open a new issue for that and I'll take a look? If you can include a minimal code sample (without web) that would be even better.

[WhatFreshHellIsThis]

I'm not sure there is a solution you can do with that single ForNpgsqlUseXminAsConcurrencyToken() call because the model needs the concurrency token and needs to be able to set it on update.

Unless I'm mistaken the ForNpgsqlUseXminAsConcurrencyToken() method will only ever be viable for a permanently connected client where the concurrency token doesn't need to be managed in the developers code.

[roji]

I'm not sure I understood that last part...

PostgreSQL is responsible for updating the value of xmin every time a row is modified - and EF Core retrieves that new value. As long as your application copies the original value of xmin, and then, after reconnecting with a new dbcontext, injects that into the change tracker as you showed above, EF Core should emit the proper query checking the original xmin value, no? I don't see why there should be a problem here...

[R-W-C]

Okay the following works fine, as long as the property in my Entity is called xmin:

builder.ToTable("mytable").ForNpgsqlUseXminAsConcurrencyToken();

But I want to have a different name for my property, let's say "ConcurrencyToken".

Now I do this:

            builder.ToTable("mytable");
            builder.Property(x => x.ConcurrencyToken)
            .HasColumnName("xmin")
            .HasColumnType("xid")
            .ValueGeneratedOnAddOrUpdate()
            .IsConcurrencyToken();

During migration this also leads to an empty migration file, so that's okay.

But when I call Add on the context, I get an error message

{Npgsql.PostgresException (0x80004005): 42703: column "ConcurrencyToken" does not exist
at Npgsql.NpgsqlConnector.d__155.MoveNext()

I tried all kind of things, but can't get it to work. What should I do to get it to work? An example would be great.

[freerider7777]

I'm starting to play with Postgres, I've created an entity with attributes on property (with no Fluent). Seems to work ok- xid column in DB, Rowversion property in class (you can use your ConcurrencyToken):

    [Timestamp]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [Column("xmin", TypeName = "xid")]
    public uint Rowversion { get; set; }

[mikylebaksh]

In an effort to provide further help (since this was one of the first links from google about concurrency tokens in postrgres using ef core), I expanded on @WhatFreshHellIsThis solution, and provided a more complete example here.

I hope this helps anyone like me who is new to both ef core and postgresql!

[WhatFreshHellIsThis]

@mikylebaksh The whole point is a disconnected scenario. Unless I'm mistaken, your example doesn't bring anything new to the table that I didn't already post here in this thread.