Efcore.pg: Npgsql generates PERFORM command in migration (when switching from SERIAL to IDENTITY)

[mrclayman]

Yesterday, I migrated our project from ASP.NET Core 3.0 to 3.1 but during the testing phase that involved applying some pending migrations, I ran into this issue:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (447ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE prefab_service.tags ALTER COLUMN id TYPE integer;
      ALTER TABLE prefab_service.tags ALTER COLUMN id SET NOT NULL;
      ALTER SEQUENCE prefab_service.tags_id_seq RENAME TO tags_id_old_seq;
      ALTER TABLE prefab_service.tags ALTER COLUMN id DROP DEFAULT;
      ALTER TABLE prefab_service.tags ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
      PERFORM setval('prefab_service.tags_id_seq', nextval('prefab_service.tags_id_old_seq'), false);
      DROP SEQUENCE prefab_service.tags_id_old_seq;
Failed executing DbCommand (447ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE prefab_service.tags ALTER COLUMN id TYPE integer;
ALTER TABLE prefab_service.tags ALTER COLUMN id SET NOT NULL;
ALTER SEQUENCE prefab_service.tags_id_seq RENAME TO tags_id_old_seq;
ALTER TABLE prefab_service.tags ALTER COLUMN id DROP DEFAULT;
ALTER TABLE prefab_service.tags ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
PERFORM setval('prefab_service.tags_id_seq', nextval('prefab_service.tags_id_old_seq'), false);
DROP SEQUENCE prefab_service.tags_id_old_seq;
Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "PERFORM"
   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 "PERFORM"
    Position: 1
    File: scan.l
    Line: 1150
    Routine: scanner_yyerror
42601: syntax error at or near "PERFORM"

It appears the engine generates a PERFORM instead of a SELECT which I would expect should be applicable. (In my past experience, PERFORM is only usable in a PL/pgSQL script.) The commands executed are related to the transition to the identity mechanism that EF Core 3 appears to be using now.

This issue does not occur with .NET Core 3.0.

If any more information is required, I'll be happy to oblige.

[roji]

This was introduced in #1089 - I think we didn't look at this close enough (and real EF migration tests are sorely lacking, https://github.com/aspnet/EntityFrameworkCore/issues/19039).

I'll take a deeper look and either revert this entirely, or make the migration generate a DO block if that's necessary.

[deNoor]

Rollback to 3.0.1 version if you cant wait for 3.1.1, it works with Core 3.1.

[roji]

This is further tracked in #1157.