Efcore.pg: After upgrade to 3.0, error during update database with identity schema

[TPucke]

I have upgraded my solution from Npgsql.EntityFrameworkCore.PostgreSQL 2.2.4 to 3.0, and the first new migration that I added contains some code I didn't expect, modifying two Identity entities as follows (FYI most of my Identity tables use Guid as keys so this only happened on the 2 entities that I could not control the key type using Generics):

migrationBuilder.AlterColumn<int>(
    name: "Id",
    table: "AspNetUserClaims",
    nullable: false,
    oldClrType: typeof(int))
    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn)
    .OldAnnotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn);

migrationBuilder.AlterColumn<int>(
    name: "Id",
    table: "AspNetRoleClaims",
    nullable: false,
    oldClrType: typeof(int))
    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn)
    .OldAnnotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn);

I trust this is as expected, probably a new technique in 3.0. However when applying this to my PG server v9.6 it failed with the below output. I'm not sure how to recover and perhaps there is something regarding backward compatibility to be considered here. Any advice will be appreciated.

Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Applying migration '20191007202528_AddUserAttributes'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "AspNetUsers" ADD "Employer" text NULL;
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "AspNetUsers" ADD "FirstName" text NULL;
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "AspNetUsers" ADD "LastName" text NULL;
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "AspNetUserClaims" ALTER COLUMN "Id" TYPE integer;
ALTER TABLE "AspNetUserClaims" ALTER COLUMN "Id" SET NOT NULL;
ALTER SEQUENCE "AspNetUserClaims_Id_seq" RENAME TO "AspNetUserClaims_Id_old_seq";
ALTER TABLE "AspNetUserClaims" ALTER COLUMN "Id" DROP DEFAULT;
ALTER TABLE "AspNetUserClaims" ALTER COLUMN "Id" ADD GENERATED BY DEFAULT AS IDENTITY;
SELECT * FROM setval('"AspNetUserClaims_Id_seq"', nextval('"AspNetUserClaims_Id_old_seq"'), false);
DROP SEQUENCE "AspNetUserClaims_Id_old_seq";
Failed executing DbCommand (47ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "AspNetUserClaims" ALTER COLUMN "Id" TYPE integer;
ALTER TABLE "AspNetUserClaims" ALTER COLUMN "Id" SET NOT NULL;
ALTER SEQUENCE "AspNetUserClaims_Id_seq" RENAME TO "AspNetUserClaims_Id_old_seq";
ALTER TABLE "AspNetUserClaims" ALTER COLUMN "Id" DROP DEFAULT;
ALTER TABLE "AspNetUserClaims" ALTER COLUMN "Id" ADD GENERATED BY DEFAULT AS IDENTITY;
SELECT * FROM setval('"AspNetUserClaims_Id_seq"', nextval('"AspNetUserClaims_Id_old_seq"'), false);
DROP SEQUENCE "AspNetUserClaims_Id_old_seq";
Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "ADD"
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.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.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: syntax error at or near "ADD"
    Position: 50
    File: scan.l
    Line: 1128
    Routine: scanner_yyerror
42601: syntax error at or near "ADD"

[TPucke]

More information, maybe it's helpful. I installed server version 11 and deployed the same migrations to a fresh database successfully. As I mentioned in the original post, maybe there is something to be addressed with backward compatibility to earlier versions.

Or possibly I should declare a server version somewhere in the application prior to generating the migration? Is there a way to do that?

[roji]

You should probably read the 3.0 release notes.

tl;dr version 3.0 switched from the older PostgreSQL serial columns to the newer identity columns introduced in PG 10. That is why you get the migration after upgrading, and also why it won't work on pre-10.

See the release notes for how to opt out of this, is that's what you want to do (although it is recommended to make this switch).

[Wurthma]

You should probably read the 3.0 release notes.

tl;dr version 3.0 switched from the older PostgreSQL serial columns to the newer identity columns introduced in PG 10. That is why you get the migration after upgrading, and also why it won't work on pre-10.

See the release notes for how to opt out of this, is that's what you want to do (although it is recommended to make this switch).

Thank you. I changed the NpgsqlValueGenerationStrategy.SerialColumn to NpgsqlValueGenerationStrategy.IdentityByDefaultColumn

I'm using Postgree SQL 12.