JasperFx / marten

.NET Transactional Document DB and Event Store on PostgreSQL
https://martendb.io
MIT License
2.74k stars 428 forks source link

Marten recreates index with `decimal` field #3266

Open Leh2 opened 3 weeks ago

Leh2 commented 3 weeks ago

We have encountered an issue with the creation of indexes for decimal fields in MartenDB. The problem arises because MartenDB generates the index with a decimal type, but when the index is fetched from PostgreSQL, it is specified as numeric. This discrepancy leads to comparison failures and repeated attempts by Marten to recreate the index.

Code Example:

    [Fact]
    public async Task index_with_decimal_should_match_DDL()
    {
        StoreOptions(_ =>
        {
            _.Schema.For<Company>()
                .Index(c => c.SomeDecimal);
        });

        await theStore.Storage.ApplyAllConfiguredChangesToDatabaseAsync();
        await theStore.Storage.Database.AssertDatabaseMatchesConfigurationAsync();
    }

    public class Company
    {
        public Guid Id { get; set; }
        public decimal SomeDecimal { get; set; }
    }

Expected Index Creation:

MartenDB attempts to create the index as follows:

CREATE INDEX mt_doc_company_idx_some_decimal
ON public.mt_doc_company
USING btree ((CAST(data ->> 'SomeDecimal' as decimal)));

Actual Index in PostgreSQL:

When the index is fetched from PostgreSQL, it appears as:

CREATE INDEX mt_doc_company_idx_some_decimal
ON public.mt_doc_company
USING btree ("((data ->> 'SomeDecimal'::text)::numeric)");

This difference between decimal and numeric types causes Marten to continuously attempt to recreate the index, as it perceives the existing index as incorrect.

Note: Using duplicated fields works as expected without this issue. Postgres version: 13.14