Scaffold error when two indexes exist on the same column set #11846
Comments
|
#4150 Index column sort order is not supported in metadata at all. Hence, the scaffolded model did not expect same index to appear multiple times. Perhaps, it can avoid throwing error but that would still ignore other index and keep only 1 (without ordering specs). |
|
@ajcvickers I'm actually really sad you removed that tag. I can't scaffold my model automatically using EFCore because of that and I need a long term solution to that index issue. Any chance you could implement quickly @smitpatel suggestion for it to at least not throw and expection and just not create those problematic indexes? Thanks! |
|
@TheBaradhur the “consider-for-next-release” label is assigned temporarily for release planning. This is now assigned to the 2.2.0 milestone (at least for now), which has more significance. |
|
@divega thank you for the update. But considering that 2.1 is not even officially released, I'm looking at months, if not a year, before this issue can be fixed. I will have to find other ways to scaffold my db until then. |
|
It could be fixed in the Npgsql provider |
|
@ErikEJ is right, I'll make a change in the Npgsql provider to make sure it does not throw in this case, at least as a workaround until the core issue is resolved. |
When scaffolding, we have an Npgsql-specific IndexMethod annotation to represent PostgreSQL's index methods. Previously, we would always output this annotation on indices, even when the method was the default (btree); NpgsqlAnnotationCodeGenerator would elide it as by- convention. Although this is cleaner, it caused issues whenever two indices where defined on the same column: dotnet/efcore#11846 We now scaffold the annotation only for non-default index methods as a workaround; the issue should now affect much less people. Fixes #228
When scaffolding, we have an Npgsql-specific IndexMethod annotation to represent PostgreSQL's index methods. Previously, we would always output this annotation on indices, even when the method was the default (btree); NpgsqlAnnotationCodeGenerator would elide it as by- convention. Although this is cleaner, it caused issues whenever two indices where defined on the same column: dotnet/efcore#11846 We now scaffold the annotation only for non-default index methods as a workaround; the issue should now affect much less people. Fixes #228 (cherry picked from commit 38d2176)
|
@divega, @smitpatel, I've pushed a workaround which makes this issue go away for the majority of users, making this less urgent (I now include the IndexMethod annotation in the scaffolded database model only if it's the PostgreSQL non-default one). The issue is still there but shouldn't bother most people anymore. |
|
However, EF Core is still incapable of scaffolding two indices on the same column (ascending and descending) which does seem to be a serious issue (and has nothing to do with PostgreSQL). |
|
The value of DESC columns in indexes is debateable |
|
@ErikEJ I think that depends on specific databases... I admit I'm not sure exactly what the impact is in PostgreSQL, but it seems like if it's supported in DDL EF Core should be able to render it... |
|
@ErikEJ We use double indexes on same column with a tolower conditions in our PostgreSQL db, a few with DESC, in a lot of different tables. Especially the ones where we are dealing with 10th of thousands of rows in the table. Having researched the internet about that double index issues in EFCore, I can tell you that it is not uncommon. |
|
DESC on a column in an index is useful when queries have ORDER BY clauses on multiple columns with mixed order. In SQL Server, very often tables are clustered (i.e. organized) on the PK, hence you can think of indexes on those tables as always containing an implicit reference to the PK column or columns. For example if on SQL Server you want to execute: SELECT *
FROM Customers
ORDER BY CutomerID, Name DESCThen an index on I don’t know much about PostgreSQL, but the information I was able to find says it does not support index organized tables. That means that for DESC to be useful on an index on PostgreSQL, you need to have an index on more than one column, and the same or opposite mixed order on both the index and the queries. AFAIK, having DESC on an index on a single column should never be necessary on PostgreSQL, as indexes can be used to return results ordered in the opposite direction. Re Some interesting reads: https://use-the-index-luke.com/sql/sorting-grouping/order-by-asc-desc-nulls-last |
|
@divega an index on To summarize, I think there are several legitimate reasons why someone would have multiple indices on the same column set; between expression indices, |
Makes sense. This is what I was expecting to hear 😄
Agreed. However I wonder if it would be better to change EF Core to separate the common case (index over a sequence of columns with implicit ASC on each of them, which we already support) from all other variations. EF Core would still reason about the column set for the simple scenario, but the index definition would become "custom"/provider-specific (and opaque to EF Core) for all other cases. Then we would never try to match a simple index against custom indexes based on the columns. |
|
Closing this in favor of #4150 |
|
It would be helpful after #17083 is fixed. And at least ignore the second index in VisitIndex instead of simply crash . For something like CREATE NONCLUSTERED INDEX [Z_Common] ON [Table]
([A],[B])
INCLUDE([C],[D])
//WHERE ([Z] = 1)
CREATE NONCLUSTERED INDEX [Z_0] ON [Table]
([A],[B])
INCLUDE([C],[D],[E],[F])
WHERE ([Z] = 0) |
|
Named indexes are now supported, so this can be fixed properly |
|
Got about the same issue reported for Pomelo:
Basically, the |
|
I've just checked this, and everything seems to be working just fine as of 6.0.0-preview.7 - #25917 adds a test for scaffolding two SQL Server indexes on the same column (with different fill factors). I've also checked the Npgsql provider, where ascending/descending is supported, and that both scaffolds successfully and gets applied back when creating a new database. Note that this does not mean that ascending/descending is supported (aside from PostgreSQL). @lauxjpn hopefully you can give this a try to confirm that it works with the MySQL provider as well. |
roji
added
closed-fixed
|
Fixed by using named indexes in metadata. |
|
Yep |
|
Can maybe be considered a dup of #21089 |
In npgsql/efcore.pg#228, @berets76 describes an issue with the way indices are managed during scaffolding.
PostgreSQL supports multiple index "methods", and the Npgsql provider represents these via a simple string annotation on the index - all indices in a scaffolded DatabaseModel contain this annotation (note that if the default method is detected, the annotation is later eliminated by convention in
NpgsqlAnnotationCodeGenerator).Now, consider the following database schema:
Two indices exist on the same column, which should be fine. However, trying to scaffold this throws the following:
It seems that at some point during the scaffolding process, the two indices provided by the DatabaseModel get "collapsed" to one, and we get the error since the annotation is already set. This is probably because at some point EF Core looks up the index, keying only by the column list, and omits other index characteristics (e.g ascending/descending).
Thanks again to @berets76 for reproducing and analyzing, I only provided the writeup.
The text was updated successfully, but these errors were encountered: