Marten keeps recreating indexes

[nkosi23]

I have about 10 indexes for which Marten keeps creating a migration script looking like the below even though the indexes have been successfully created in the DB:

drop index if exists abccorp_online_operations.bw_idx_entityid_someprop;
CREATE INDEX bw_idx_entityid_someprop ON abccorp_online_operations.mt_doc_abccorp_people_documents USING btree ((CAST(data ->> 'EntityId' as varchar)), (data -> 'SomeProp'));

storeOptions.Schema
        .For<ABCCorpPeopleDocument>()
        .DocumentAlias("abccorp_people_documents")
        .Index((fun x -> (x.EntityId, x.SomeProp) :> obj), (fun x -> x.Name <- "bw_idx_entityid_someprop"))

While I have changed the names for privacy reason, their length is pretty much intact as I suspect that this problem may be somehow related to the one where Marten didn't detect that objects with truncated names (because the name was too long) have been created in the DB. Some observations:

• The documents in question both define a DocumentAlias and a name for their index . However, this is the case for other document types that do not have this issue.
• Moreover, not all indexes of the document types affected have this issue: for example for one of the types having 4 indexes, Marten tries to recreate 3 of the indexes (and the one correctly detected has a name longer than the one of some of the indexes being recreated).
• I initially suspected that the problem could be with case detection: using uppercase characters for the index name set in marten while postgresql automatically lowercases everything. Marten-side, I was using camel-cases index names such as: "bw_idx_entityId_someProp". However, lower casing everything did not solve the issue.

Since the objects are correctly created in postgresql, this looks like a Marten-side change detection issue.

[mysticmind]

Based on your comments, I am assuming the names are not truncated in the Postgres index definition added in db. I will take a look and revert, most likely a delta detection which is seeing a difference causing it to recreate the indexes.

Edit: A minimal repro would help.

[nkosi23]

Thanks a lot for your swift feedback, on my side i'll look into making the repro

[nkosi23]

Okay, I have been able to create a repro
MartenRepro.zip

if you run dotnet run -- db-patch migration3.sql you should see that one of the indexes keeps being recreated:

drop index if exists marten_repro_schema.bwsp_idx_goho705dg4q_poho705d;
CREATE INDEX bwsp_idx_Goho705dg4q_poho705d ON marten_repro_schema.mt_doc_abccorp_people_documents USING btree ((data ->> 'Goho705dg4q33r68'), (data ->> 'Poho705dg4q33r68dfgr'));

[mysticmind]

@nkosi23 Thanks for the repro, will take it from here

[mysticmind]

@nkosi23 Quick note, I had troubleshooted and spent time, yet to zero in on it. I will keep you posted.

[nkosi23]

@mysticmind Thanks a lot for your efforts!

[WilvanBil]

I encounter the same issue not only with indexes, but also with sequences.

CREATE SEQUENCE IF NOT EXISTS
public.mt_foosequence MAXVALUE 99999999
CYCLE;
CREATE SEQUENCE IF NOT EXISTS
public.mt_barsequence MAXVALUE 9999
CYCLE;

is what the validation returns.

[mysticmind]

@WilvanBil I am still troubleshooting to figure out a fix for the original issue raised, will also look at the issue what you have outlined with sequences. Can you please provide a repro?