Efcore.pg: Conflict with __EFMigrationsHistory table in schema other than public

[floyd-may]

Given two DbContexts each with their own default schema, Let's say ContextOne with the default schema of public and ContextTwo with an alternate schema of, say, example, I cannot use both DBContexts in the same database without overriding the default table name of the migrations history table.

The culprit appears to be this SQL that searches for the migration history table:

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');

This will return a result if the __EFMigrationsHistory table exists in any schema. So, if, say, ContextTwo runs its migrations first, creates its __EFMigrationsHistory table in its schema of example, then ContextOne runs its migrations, the above SQL falsely reports that its __EFMigrationsHistory table exists. The migrations then fail because the inserts into the migration history table (assumed to be on the default public schema) attempt to insert into a table that doesn't exist.

The workaround that I've used is to override the migration history table's name such that it's unique across all schemas.

[austindrenski]

Thanks for reporting this. This looks like an issue we've seen reported in the past. If so, this should only be an issue during initial creation (e.g. context.Database.EnsureCreated()). So non-initial migrations should be unaffected by the existence check issue.

I'll look into patching for the history table later today.

[floyd-may]

Yes, I agree that this is only an issue on initial creation; if both __EFMigrationsHistory and example.__EFMigrationsHistory are present I think it should Just Work (tm). Thanks for looking into this!

[roji]

Unless I'm mistaken, our current behavior is the general EF Core behavior - same as for SQL Server, Sqlite - the context name isn't prepended/appended anywhere (see also the docs one this). As such, I don't think we should be doing this, although it's certainly possible to open this discussion on the EF Core repo.

Regardless, here are some comments/thoughts...

First, it's important to understand that EnsureCreated() and migrations are mutually exclusive. If one uses EnsureCreated(), then there's no __EFMigrationsHistory (because there aren't and won't be any migrations). As a general rule, EnsureCreated() is considered a pretty lightweight prototyping feature, and isn't really meant for complex scenarios such as having multiple contexts in the same database. See #679 and the resulting https://github.com/aspnet/EntityFrameworkCore/issues/13798 which are quite similar in trying to push complex scenarios onto EnsureCreated(), which it isn't really supposed to deal with.

Assuming you go with a full migrations approach instead, then the workaround (or solution) is simply to manually define the migration table name for each table, as per the docs. There could still be some sense in asking EF Core to include the context name in the migration table automatically, saving you the trouble of doing it. However, the common scenario is probably to have one context per database, and in that case it would actually make the situation needlessly complex - it can be quite useful to know you can find the migration table as __EFMigrationsHistory table and not have to start looking.

So to sum it up, I personally don't think this feature is needed much, or makes much sense, but in any case this isn't something we should be doing on our own in Npgsql. Will leave this open for further discussion though.

[floyd-may]

@roji I appreciate where you're coming from here; however, I think you might be missing the essence of the issue. First off, I'm not using EnsureCreated(), I'm using migrations, so I don't think that part is relevant. I have read the docs, and I am using x => x.MigrationsHistoryTable(...). I had originally configured my two DBContexts as such:

/* snip - DBContext 1 */
x => x.MigrationsHistoryTable(HistoryRepository.DefaultTableName, "schema1")
/* snip - DBContext 2 */
x => x.MigrationsHistoryTable(HistoryRepository.DefaultTableName, "schema2")

This scenario does not work. The SQL I pasted above (captured from the EF logs) improperly assumes that any table in any schema that matches the configured name for the migration history is the right table. If you examine the inner bit of that SQL (modified to use SELECT * rather than SELECT 1:

SELECT * FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory';

This returns rows from every schema, not just public. In my _humble_ opinion, I should be able to just control the schema (say, via the MigrationHistoryTable(...) call above) such that schema1.__EFMigrationsHistory and schema2.__EFMigrationsHistory can coexist harmoniously. It seems to me that the above SQL is just missing a WHERE condition for the configured schema, or public if the schema is unset. Hope this clarifies.

[roji]

@floyd-may it does seem like I've misunderstood the issue, sorry about that.

You should definitely be able to define two history tables in the same database, including two with the same name but in different schemas. However, looking at the code I'm not exactly seeing an issue. The SQL you're quoting seems to come from here. The code does check whether a Table Schema is non-null, and adds the schema check for that case. Are you saying you're not seeing the clause generated in SQL even when specifying the history table with schema names as above? If so we'll definitely look into it.

[floyd-may]

I'm saying that this SQL is what I saw in the logs:

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');

Immediately following that, I saw a failed insert into someschema.__EFMigrationsHistory complaining that the table did not exist. I inferred from that that the above SQL should have a portion of the WHERE clause filtering by schema. I did open #795 yesterday, and it's possible that this issue is also already fixed in v2.2 which I haven't upgraded to yet.

[roji]

@floyd-may I understand but could not reproduce the issue: I successfully created two contexts with the same migration history table name - but different schemas - and I can see both in the database. The query I'm seeing is:

SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='foo' AND c.relname='__CrapMigrations')

Can you please test on v2.2 to see if the problem goes away, and if not, post exact, detailed step-by-step instructions on how to reproduce the issue?

[floyd-may]

Will report back when we can upgrade and test. Thanks!

[floyd-may]

Just dropping a note here. I didn't realize that in order to update EF Core to 2.2 I've got to ṷ͙̥̽̑pͨ̓͛̉g̭̬̥̲̰̘ͬͪͬͫ̀ͅr̫̳̞͕̗͕͖͑̿á͔͓̏ͪ̓̇d̘̥̜̬ͣ͆ͧe̞̙͉͓̼̝̺͐̀͆̈́͆ ̝̭̩͇͓̞̍ͬ͑a̝̫̯̮̲͑̑͗ͦ́͊̆ḽ̔̄l̘̩̖̳͗ͯ̽ͫͯ ̝̰͓͕ͮ̽̆ͯͫͅt͊̋͋̈̆͗h̳̖̰̿ͥͨ̃͛̄è̮̬̲̥͇̼ͪͥ̐̚ ̗̳̟̫̻̓ͫ̉̌͑t̼̞̻̟̅ͥͤ͗h̞̺̞͇̱ͨ̌̿ͤ̑ͦͅi͈̳͙̺̣n͍̲̜͙ͤ͋ͦ̈́ͮͯg̫ͬ̉̓̋͗̎s͓̹̱͈͈͉ͣ̈́ so this may drag on for a bit before I can confirm that this is fixed in 2.2.

[roji]

Thanks for the note @floyd-may

[austindrenski]

Closing based on age, but can reopen if there's a repro for version >=2.2.0.

[floyd-may]

We're likely to skip 2.2 and go straight to 3.0 given the dependency rats nest that is ASP.Net Core 2.2 that they've hopefully fixed in 3.0. Thanks for the hard work y'all put into this project!!!