Missing "public" schema fallback for migration table exists query

[Turnerj]

There is a subtle bug with how the migration table exists query works when dealing with a combination of defined and non-defined schemas in a database (I know that is unusual but it is what it is).

The problem is this block of code:

efcore.pg/src/EFCore.PG/Migrations/Internal/NpgsqlHistoryRepository.cs

Lines 15 to 40 in e5ade7f

	protected override string ExistsSql
	{
	get
	{
	var builder = new StringBuilder();
	builder.Append("SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE ");
	var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
	if (TableSchema is not null)
	{
	builder
	.Append("n.nspname=")
	.Append(stringTypeMapping.GenerateSqlLiteral(TableSchema))
	.Append(" AND ");
	}
	builder
	.Append("c.relname=")
	.Append(stringTypeMapping.GenerateSqlLiteral(TableName))
	.Append(");");
	return builder.ToString();
	}
	}

If you don't specify a schema, that query will actually match against all schemas rather than filter to the default public schema. This means if you have a migration in another schema, that will be picked up even though it isn't valid for the current migration.

As far as I can tell, you can probably just do this without the outer null-check to resolve it:

         builder 
             .Append("n.nspname=") 
             .Append(stringTypeMapping.GenerateSqlLiteral(TableSchema ?? "public")) 
             .Append(" AND "); 

This was against Npgsql.EntityFrameworkCore.PostgreSQL v6.0.8

---

Steps to recreate issue:

• Have two contexts, one with a schema, one without
• Run a migration for the context with the schema
• Attempt to run a migration for the context without the schema

[roji]

Thanks, makes sense! I submitted #2788 to fix this, I'll also backport this.

[Turnerj]

Cheers mate, appreciate the quick response on this!

[tnotheis]

After upgrading to version 7.0.11, I receive the following error when running my app:

Unhandled exception. Npgsql.PostgresException (0x80004005): 42P07: relation "__EFMigrationsHistory" already exists

Here's a little bit of context about my app:
I have multiple DbContexts, where each of them has its own connection string with its own user, where each user has a separate default schema. I was creating the __EFMigrationsHistory tables for each DbContext manually as part of an database initialization script, which I had to run once after creating a new database. This was working fine.

But with your change, all DbContexts seem to try to create a __EFMigrationsHistory in the "public" schema, which, of course, isn't possible.

Tomorrow I will try to create a minimal example for you to reproduce this, and open a dedicated issue. But maybe you can already have a look into this.

Thanks.