Reexamine migration table detection/creation

[roji]

In order to fix #2878 for 9.0, #3294 changed our migration history table creation strategy - we try to select from it without pre-checking whether it exists, and catch "table doesn't exist" exceptions, at which point we create it.

This unfortunately doesn't work if the migration process is wrapped in a user transaction - the error causes the transaction to go into a failed state, and everything breaks down. In addition, this (currently) causes an error to appear in the logs, confusing users and making them think the migration failed. Reexamine our strategy here.

The relevant tasks are MigrationsInfrastructureNpgsqlTest.{Can_apply_two_migrations_in_transaction,Can_apply_two_migrations_in_transaction_async}.

Note: the fix may need to be backported.

[roji]

Also take a look at #3436, #3496 when doing this.

[pantonis]

I ge this when I use Update-Database command.

[rudism]

Is there any workaround for this? My migration process includes executing some pre- and post-migration scripts that tear down and recreate various things in the database to support a legacy system which need to run in the same transaction as the migrations (so that everything gets rolled back in case of an error). I'm unable to upgrade from Npgsql 8 -> 9 currently as the SuppressTransaction = true when creating the history repository prevent the ability to run migrations in a transaction.

[rudism]

For now I'm working around this with my own modified implementation of the IHistoryRepository interface that inherits from NpgsqlHistoryRepository but overrides the CreateIfNotExists and CreateIfNotExistsAsync methods to omit the SuppressTransaction thing:

Code

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Npgsql;
using Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal;

namespace Cosmic.Data.Mapping;

#pragma warning disable EF1001
public class HackyHistoryRepository(HistoryRepositoryDependencies dependencies)
: NpgsqlHistoryRepository(dependencies), IHistoryRepository {

  bool IHistoryRepository.CreateIfNotExists() {
    try {
      return Dependencies.MigrationCommandExecutor.ExecuteNonQuery(
        GetCreateIfNotExistsCommands(),
        Dependencies.Connection,
        new MigrationExecutionState(),
        commitTransaction: true) != 0;
    } catch (PostgresException e) when (
        e.SqlState is PostgresErrorCodes.UniqueViolation
          or PostgresErrorCodes.DuplicateTable
          or PostgresErrorCodes.DuplicateObject) {
      return false;
    }
  }

  async Task<bool> IHistoryRepository.CreateIfNotExistsAsync(
      CancellationToken cancellationToken) {
    try {
      return (await Dependencies.MigrationCommandExecutor.ExecuteNonQueryAsync(
        GetCreateIfNotExistsCommands(),
        Dependencies.Connection,
        new MigrationExecutionState(),
        commitTransaction: true,
        cancellationToken: cancellationToken).ConfigureAwait(false)) != 0;
    } catch (PostgresException e) when (
        e.SqlState is PostgresErrorCodes.UniqueViolation
          or PostgresErrorCodes.DuplicateTable
          or PostgresErrorCodes.DuplicateObject) {
      return false;
    }
  }

  private IReadOnlyList<MigrationCommand> GetCreateIfNotExistsCommands() =>
    Dependencies.MigrationsSqlGenerator.Generate([new SqlOperation {
      Sql = GetCreateIfNotExistsScript(),
    }]);
}
#pragma warning restore EF1001

Then I inject it when configuring the database connection:

services.AddDbContext<MyDbContext>(static opts => opts
  .UseNpgsql(connectionString, static builder => { ... })
  .ReplaceService<IHistoryRepository, HackyHistoryRepository>();